Hello again! Spring is in the air, and with spring comes “change” – a change in temperature, a change in your cleaning habits (spring cleaning anyone?), and with my help, hopefully a change in your Excel models! If we haven’t met before, my name is Antoine Bishara and I am an Excel Power User. I now spend a lot of time mentoring and teaching individuals on how to use Excel more effectively for their daily lives, and by doing so, how they can become better analysts, managers or executives.
Earlier I shared my top-5 favorite Excel tips (Office 365) (click here for the first article), and not wanting this season of change to pass us by without writing an article – I will now share my “second top-five” favorite Excel tips.
Often there are rows and/or columns that we want to hide – whether for presentation purposes or just simple aesthetics. Our default method of hiding columns and rows is to “right click” the applicable row or column and click “Hide”. Or, perhaps you use the following shortcuts: “CTRL – 9” to hide the row, and “CTRL – 0” to hide the column. Unfortunately, if you do this you likely cause anxiety and stress to anyone who reviews your model. Why? Because it causes summation issues, as well as multiplication issues, especially if the row you hid has data that is for you only. Consider the following simple income statement:
You can probably tell that the summation of the above does not equal $14,300. In fact, it should be much lower – this is easy to tell because of the simplicity of the above data set. Why did this happen? The formula appears to be doing the right thing, but the total is completely wrong. The reason why it’s doing this is because we have hidden row 6 – which includes the following:
A user scanning your model will not notice that row 6 is hidden. As such, our recommendation is that you never “hide” anything. Instead you should use the “grouping” functionality in Excel, which can be done by highlighting the appropriate row or column (using SHIFT – SPACE to highlight a row, and CTRL – SPACE to highlight a column), then enter the shortcut “ALT – A – G – G” or Click on the Data ribbon, then Group and Group again. This will place a little “+” next to the row/column which will allow you to expand and collapse that column (or group of rows/columns easily). Not only does this allow others to know where you have hidden things, it also allows you to remember that you have hidden something in that row/column.
If you still want to sum only “visible cells”, then use the subtotal (=subtotal(109,[Array])) formula instead of the sum formula. For a breakdown of the “subtotal” formula, click here.
#2: Live Sheet Names
When creating models, it’s a good idea to include some (if not all) of the following:
The company’s logo.
The company name.
The name of the sheet, since your model will contain several tabs.
And the currency – this is especially true if you’re dealing with a UK subsidiary and the numbers represent GBP vs. USD.
Each tab in the model, typically, will look something like this:
Note: prior to incorporating any of the tips make sure you save your model.
Issues can arise from the naming of tabs, as often you will have different tabs for different users, such as internal stakeholders and external stakeholders. As you change the name of the tab, you will need to update the title of each tab – this is not an issue because you can simply do this manually. However, when you are working with a 50-tab model, this can present its own challenges. Thus, in A5 you can use the following formula:
Now when you change the name of the tab, it automatically changes the name in the title.
#3: Data Visualization: Maps
I was recently teaching a class regarding data visualization. One of the most difficult things the client had was creating maps for presentation/data visualization. The issue will manifest itself for things like trying to reflect the geographic dispersion of sales using graphics vs. data. Let us use a simple example of a company whose sales are dispersed as follows in the United States:
New York: 16%
Rhode Island: 2%
Historically, we would have placed this data in a power point presentation, and perhaps find a website where we would pay for some archaic map that would “hopefully have the appropriate shading”. In Office 365, the brilliant programmers included a map function that allows us to map our data by geography.
In order to replicate the map above, the data is organized as follows:
Next, highlight the entire data set – from B13 to C22:
Then go to “Insert” on your ribbon, and click “Maps” and “Filled Maps”:
That’s it! Once you’ve finished, format your chart with your company’s colours and use it in your presentations. Try different data sets with this feature. You can easily graph provinces, states, and countries as well as zip codes; however, the last is quite challenging as you need a large enough zip code/postal code that it will show up on your map.
Note: This only works if you have Office 365, if you are using an older version of Office you can do one of two things: 1) convince your IT department that everyone needs office 365 or 2) Ask a friend with Office 365 to quickly build this for you. I’ve had more success with option #2.
#4: Error Functions
If you read my first article, you’ll know how much I love the “index/match” function; however, sometimes when using the function and Excel can’t find the data that you’re looking for, it returns a “#N/A” error, which is Excel’s way of saying “I can’t find what you’re looking for”. The #N/A errors not only create issues in your model (especially if you’re trying to multiply the cell by another number), but they are also unsightly.
Other times you may be doing a basic calculation of unit price per product shipped. You divide the total revenue you received by the number of units shipped of a particular product – easy. However, you have received a deposit for a product, but the product has not been shipped, so you may have a number for cash received but zero for items “shipped”. The result is that for that one product the unit price will be #DIV/0!. Basically, Excel is saying “Hey…you can’t divide anything by zero…it just doesn’t work!”.
Value can be anything, including a formula. In the first situation described, “value” is the result of index/match function, and if it outputs an “error” then Excel knows what to do with it. Sometimes you will want that output to be “0”, other times you would like it to output text. You decide.
You may be familiar with the sumif/averageif formulas which sums or averages specific items if certain conditions are met; however, sometimes you want to take it a step further: “if certain conditions are met, I would like you to multiply array #1 with array #2”.
For example, let us assume that we want to find out the revenue from selling hammers in Texas. The data we have is organized as follows:
There are two possible ways to potentially manage this:
1) Use a Sumif formula multiplied with an Averageif formula to find the total revenue earned from hammers in Texas. This is okay for a small data set, but as the data set increases (as well as the number of arrays) the formula becomes very complicated, very fast.
2) Calculate manually – i.e. multiply every single line item, then use a sumif function to sum the hammers that are sold in Texas. This is not a bad option; however, it requires reformatting your data set as well as incorporating two formulas.
Thankfully there is an easier way – using the SumProduct function. More advanced Excel users may think that the SumProduct function simply multiplies two or more arrays together, and doesn’t deal with conditions, but in fact it does.
In order to find out how many hammers were sold in Texas, we can create a selector for our user which allows them to enter the state that they are interested in looking at, in this case, Texas. Although this next step is unnecessary, you want to design your models so that it is easy for the user to use. Thus you should rework the data set as follows:
Then, input the following formula in cell H14:
Note the “- -” at the beginning of the formula. This creates a Boolean array if a specific condition is met. In this case we have turned every cell in column D to a 1 or 0 depending on whether or not it was equal to the word “Texas” (i.e. H11). Visually our formula does the following:
When we apply a SumProduct formula on this dataset, if the value in our reworked column D is 0 then the SumProduct of the three arrays will be zero, if it is a 1 the “sum product” will be the result of number of hammers multiplied by price per hammer multiplied by 1.
Excel is a powerful tool that allows us to be prepared, to analyze, and to help us conclude on a subject matter. However, we must never forget that Excel is just that – a tool. And like any tool, proficiency comes with practice. Never lose sight of that. You should also remember that Excel allows you to make decisions based on data that you’ve gathered – the value is not found in creating amazing models that go nowhere, the real value is being able to transform the data to help you make a decision.
If you would like to hold an Excel workshop for your team, or would like to learn more about the learning solutions we provide, please contact us at firstname.lastname@example.org.