The Most Important Excel Functions For Analytics – Part 1/3

When I started my first job as an analyst for a supply chain management consulting firm, I had very little clue about how to use Excel. Out of necessity I quickly had to learn a set of functions to get my work done.

The below functions helped me prepare and clean data and also helped when building analytic models. I have included some of the insights and hacks for using these functions. If you get comfortable using the below, you can confidently tackle a huge amount of business analytic problems. In the following 3 posts, I’ll cover the top 10 most useful Excel functions for use in business analytics.There are definitely other Excel functions that people find useful, but these have been the most helpful for me.

PART 1 – SUMMARIZING THE DATA

In this post I will cover the first three and the most commonly used excel functions used in business analytics. These help to summarize and combine your information.

  • Pivot Tables
  • VLOOKUP
  • IFERROR

1. Pivot Tables

Why do I care?:

Pivot tables are extremely useful to quickly summarize excel data. You can slice, sort, count, total or average your data. This helps to do some high level data validation to ensure that you are getting reasonable totals and to ensure that all fields are showing. Pivot tables are like a BS filter for analytics.

What is an Example?:

The below article goes into more detail o an example of how to set up a pivot table.

How to create pivot tables

Insights:

Whenever I set up a pivot table, I usually ensure that getpivot is turned off. This allows the pivot table to be easily referenced when doing quick calculations. To uncheck GetPivotData:

  • Select any cell in a pivot table.
  • On the Ribbon, under PivotTable Tools, click the Options tab
  • Click the drop down arrow for Options
  • Click the Generate GetPivotData command, to turn the feature off

I also find the standard layout of a pivot table difficult to use for analytics. I prefer to have a plain data view, such that I can copy summarized data into a static table into another sheet. I normally change the report layout to ‘Tabular form’ and I also Remove ‘Subtotals’.

See the below link for more detail on editing pivot table layouts.

Edit pivot table layout

Lastly, note that pivot tables work well up to ~200k lines. After this they become slow and a database may be a better solution.

2. VLOOKUP

Formula:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Why do I care?:

The vlookup function is one of the most important for data blending in Excel. It is used to take information from one sheet and look up the values in another. It is the equivalent of a simple database join (more on database joins later.) This helps get all your information useful for your analysis in the one location.

What is an Example?:

Imagine you have some stock keeping units (SKUs) where you know the size but need to know the colour. The colour table is in a separate file or sheet. You can use the vlookup function to get the information from the ‘Colour’ sheet into the ‘Size’ sheet.

You would use the below code in the Size sheet on cell C3 and below to get the colour informtion for each of the SKUs.

‘Size sheet’

‘ Colour Sheet’

VLookup in ‘size sheet’, i.e. add in the colour column

Insights:

  • The reference range should always be ‘fixed’ with dollar signs. This prevents the range of the lookup from moving
  • The lookup table must always have the lookup key on the left and the answer somewhere on the right. There are ways to get around this with Index(Match), but that is generally not needed
  • The 0 value means you are looking for an exact match, this is what you will use almost all the time
  • A vlookup only finds the first instance of a value in a list, make sure all the lookup tables are unique
  • For large data sets, once the vlookup has found the value, it often pays to copy and paste special as values

3. IFERROR

Formula:

IFERROR (value, value_if_error)

Why do I care?:

IFERROR is most commonly used together with a vlookup. This is a way to ensure you control a vlookup if you do not find a match. When a vlookup fails to find a match it can blow up and throw any of the following errors at you: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

You can use iferror with a vlookup to default to a value, e.g. an average in a calculation, or a text string you can control e.g. ‘There is no match’.

What is an Example?:

Back to the SKU example, if we have a SKU that has no colour information, we can add an IFERROR statement to return “No Colour” if no match is found.

We add the below code, wrapped around the VLOOKUP in cell C3 and then copy the formula down to all cells below.

No IFERROR returns a #N/A in cell C3 as SKU 999 does not exist in the lookup table ‘Colour’.

Adding an IFERROR statement will add ‘No Colour’ if there is an error in the VLOOKUP

Insights:

  • The most useful application of IFERROR is in numeric columns where you do not want a #DIV/0 or an #N/A ruining a sum or follow up calculation. Here you may put a 0 or an averaged number in the formula
  • Often I use “” as a return value to keep he data clean if there are just text values in the column

For the next installment of the series, The Most Important Excel Functions For Analytics – Part 2/3, we will cover how to add in logic to customize your data.

Share on LinkedInEmail this to someoneTweet about this on TwitterShare on FacebookPrint this page

One Reply to “The Most Important Excel Functions For Analytics – Part 1/3”

Leave a Reply

Your email address will not be published. Required fields are marked *

*