Excel Tutorials
This blog is intended to make you excel in Excel. It showcases the most important functions we use at Sustainable Ships, as well as where you can find the best help (Google). If you are new with Excel, this tutorial might save you years of pain!
Blog is a work in progress!
Key takeaways
Never try to figure it out yourself. Google your question before you start if you do not know how to do the task you aim for. There are so many excellent sites that can help you figuring stuff out. Most likely, everything you can think of has already been tried and described. And what if you can’t find it online after 5 minutes of searching? Call a colleague or friend to ask your question and simply skip the problem for the moment if you can’t figure it out.
Use the NAME MANAGER to make your life easy. The NAME MANAGER allows you to name a cell in a workbook, so you don’t see a cell reference but an easy to interpret name in your function bar. For example, if you designate your ship type as ‘SHIPTYPE’, you will see SHIPTYPE in your function bar instead of ‘Tabname’!$A$13, which is a lot easier to work with. Try to use capital letters and adhere to the same naming convention throughout your sheet. To illustrate, for the Decarbonizer we use OPERATIONAL_PROFILE_CURRENT/FUTURE_PARAMETER_UNIT_DAILY, which would result in the following example: SAILING_FUTURE_ENERGY_REQUIRED_KW_DAILY. You have to use underscores because you cannot use spaces when naming parameters in Excel.
We cannot stress this enough: use matrix discipline! Matrix discipline means that you use the same structure and layout for all your rows and columns in your sheet. This also means you need to name everything the same, throughout Excel and Grid.is. For example in our case with the Decarbonizer: every technology tab has the input, output, emissions and costs values in the exact same column as the other technologies. That means when you reference to a technology, or when you need to combine them, the place of your required input is always the same. Lovely.
Awesome functions
XLOOKUP - Find values in rows/columns. Similar to a database query when looking for one value.
HLOOKUP - Looks for a value in the top row of a column, and returns the value from the row you specify.
VLOOKUP - Looks for a value in the leftmost column of a matrix, and returns the value from the same row and column that you specify.
FILTER - Quickly and easily filter a database via user input. Can be combined with Grid.is into a powerful tool. Use “,” for columns and “;” for rows.
TAKE - The TAKE function allows you to take the first or last rows or columns in an array. For example, you can take the first row in an array to serve as the headers of a table in grid.
INDIRECT - Refer to the value ‘within’ the cell, in order to find combined or calculated values. BE WARNED! Indirect is a volatile function, which will significantly decrease speed performance of both Excel and Grid, as it forces all cells in your workbook to be recalculated. Avoid this function in general, but especially for large sheets. Use IF statements and XLOOKUPS insteads.
& - Combine different cell values into a single one by adding & in between. For example, “Text A”&”Text B” becomes “Text AText B”. Served well in combination with INDIRECT and XLOOKUP. Better to use & and avoid CONCAT.
TRANSPOSE - Converts a column vector into a row vector and vice versa.
UNIQUE - Returns unique values from an array, can be used to return all unique values for a dropdown list. Use “<>” to filter out blanks, e.g. UNIQUE(FILTER(SHIP_TYPES,SHIP_TYPES<>""))
SUBSTITUTE - Replaces existing text with new text in a string, particularly helpful when replacing “” in strings (for example with SHORE_POWER).
