Aller au contenu principal
16 janvier 2016

3 inner plumbing tips for your own spreadsheets

By now you have been working for several years with Excel, gradually getting better at it and making simple or elaborate spreadsheets for private or business use. All in all, you are quite satisfied with your work and the results.

If you recognize yourself in this description, please take 5 minutes to read on and answer these 3 simple questions.

 

Is that specific cell a variable or an output cell?

How many times did you decided “on the fly” to define one additional variable and simply use the adjacent cell to place it there? By doing so, you have just promoted yourself to the MASTER of this spreadsheet; where other users must scramble, search and deduce to find them. You are the single person on our planet who knows the levers and their exact location in order to modify the output.

In an effort to share your power of understanding to other users, they will be grateful, consider using visual clues. Use formatting (e.g. coloured fonts, borders, coloured fills) and by location (e.g. at the top of your tab with frozen panes, in a separate tab), to make a clear distinction between cells with variables and calculated/output ones. Furthermore, you might consider protecting the latter so that you are sure nobody will temper with them afterwards by mistake; not even yourself.

 

What is the difference between +M24, +$M24, +M$24 and +$M$24?

This dollar sign thing in Excel; do you really and fully understand it? Honestly…?

Because it is both important for the structure (e.g. valid copying of rows or columns without generating wrong references) and saves you a lot of time when creating your spreadsheet, here is the rule again: The first dollar signs before the letter locks the COLUMN when copying this cell, the second one locks the ROW.

Example:  If cell A1 has “+M24”, meaning taking over whatever value or text is located in cell M24, copying this cell A1 to B1 will give “+N24” in B1. Copying cell A1 to A2 will give “+M25” in A2, and “+N25” if it is copied towards B2.

How does this rule work out when you define tables with desired multiplications between the top row (say 1, 2, 3) and the first column (say 100, 200, 300)?

If you do not use any dollar signs in the first cell of your table, being cell B2 in our example, copying this cell “+A2*B1” for all rows and all columns will generate seriously wrong output. Your cell D4 will have the incorrect formula “+C4*D3”, i.e. 24.000.000.000.000 x 2.400.000.000 instead of 300 x 3.

When you use the dollar signs correctly, it all works out and the output is correct; with cell D4 showing “+$A4*D$1”, being 300 x 3 = 900.

 

Do I fully understand the functionalities and implications of VLOOKUP?

From a given complexity stage onwards of the problem you want to solve with Excel, you will encounter people telling you to use VLOOKUP in your spreadsheet. Next to your nodding, what are the basics?

The data you want to search or analyse consists of a table with one or multiple rows (or columns in case of HLOOKUP), where the key indicator in the very first one.

What is the key indicator? It is the variable you are going to use to look up for related data.

Let’s look at a table with given temperatures at three different locations. The user enters the desired location (i.e. a variable) in the yellow cell A1, and its temperature will automatically be shown in cell B1.

In cell B2 we use the formula “=VLOOKUP(A1;A3:B6;2;0)” to fetch, or to perform a “vertical lookup” in Excel lingo, the appropriate temperature of the entered location.

 

What are the different parts of this VLOOKUP formula?

=VLOOKUP(A1; …) = is the key indicator we want to use for our search, in this case the value “Bandol” in cell A1

=VLOOKUP(…;A3:B6;…) = is the table in which we are going to look. The different key indicators are located in the first column, from A4 to A6 (with A3 being the header), the related data (i.e. temperature) in the next column (B4 to B6). The whole table therefore goes from A3 to B6, headers included.

=VLOOKUP(…;…;2;…) to indicate that we want the second column of our table, i.e. B

=VLOOKUP(…;…;…;0) with 0 or FALSE to clearly state that we want to find an exact match, not an approximate match (i.e. the next largest value that is less than the key indicator should it be 1 or TRUE here).

If the location is not present in the data, the user will get an N/A error message, which you can further customize with a combination of IF and ISNA function, but I’m getting carried away here. Note that there are also other formulas you can use individually or jointly for table, array or matrix searches, like e.g. INDEX or MATCH.

 

Small intermezzo: You might want to define a drop-down list in cell A1 so that the user can only pick one out of the three locations. Remember that the user is never wrong – if the spreadsheet generates errors from any interaction with the users, the spreadsheet is poorly designed (i.e. not enough protected, not enough assisting the user with his/her choices).

 

Did you know the 3 answers? Great! Continue to enjoy Excel and all its ramifications.

Did you struggle a bit with these 3 questions? Consider taking a ½ day out to invest in your Excel knowledge on March 3rd 2016. I’ll be giving a practical Excel course at the British Chamber of Commerce in Brussels.

http://britishchamber.be/event/microsoft-excel-%E2%80%93-pivot-tables-vlookups-and-other-techniques

For those who cannot make it on March 3rd, I shall be organizing an Excel webinar series later this year, broken down by 3 topics per session. Send me a quick message by LinkedIn if you want to participate and I will keep you posted.

 

P.S. And don’t get me started on pivot tables…

 

Martin van Wunnik

http://arsimaprojects.eu/en/content/trainings-workshops

Brussels (Belgium) – Jan 16th 2016