Excel Tips for the College Graduate: Part 2
By David Zawitkowski, Citrin Cooperman –
June 8, 2016
Do you want to use Excel smarter by utilizing proper data analysis formulas?
Part one of this two-part article introduced you to keyboard shortcuts. Part two focuses on data analysis formulas. You'll learn how to use data analysis formulas to improve your analysis skills and increase your ability to communicate those findings.
While showing you useful formulas and techniques to analyze your data, I will also show you how to do it with keyboard shortcuts, where applicable.
Remove Unpleasant Return Values
Not a fan of seeing #DIV/0!or#NA in your cells containing formulas? No one is. Here’s a trick to eliminate those eyesores.
The formula is =IFERROR(Your Formula Here,0). This will enter a 0 if there is an error.Alternatively, you can have the output say something specific by putting “error” or “N/A” (or something else with the quotation marks) after the comma. See below for examples.
Sum Data Intelligently
This formula comes in handy when analyzing business concentration risk (i.e. top customers, suppliers, geographies, industries, etc). I have shown all unique Customers, Geographies, and Industries and summed sales by those categories. The formula is =SUMIF(range, criteria, sum range)
Note: the dollar sign ($) used inside of formulas locks in the column if placed before the column letter or row if placed before the row number.
Sum Data Using Multiple Criteria
The SUMIF formula discussed above is helpful, but what if you want to know how much Sales from customer one was sourced from New Jersey? SUMIFS (note the ‘S’) allows you to sum values by using more than one criterion (whereas SUMIF only allows for one criterion). The formula is =SUMIFS(sum range, criteria range1, criteria1, criteria range2, criteria2 …) In the example below, we were able to identify all of the New Jersey sourced Sales by each unique Customer.Experiment by using several criteria to really narrow down your analysis.
Rank Your Data
A good example of this is if you need to rank customers or accounts receivables balances. The formulas below provide a quick solution you can use without needing to sort rows. Specifically, this is useful when you are not able to sort rows in your spreadsheet.
The formula is =LARGE(range, rank number) and/or =SMALL(range, rank number)
If you just want to know the smallest or largest values, then use the following formulas: =MIN(range) and =MAX(range)
Match Your Ranked Data With Names
The ranking formula is even more powerful when you use it in conjunction with the Index/Match formula. After ranking sales values, for example, you may need to identify which customer matches with the sales numbers.
The formula is =INDEX(return value range, MATCH(lookup value, lookup range, match type))
Note: For match type, entering 0 returns exact match if available or an #NA error message if not available.
Analyze Performance Using Conditional Formatting
Have you ever went down a list and manually highlighted the cells with the highest or lowest values? There is a way to never have to do that again. Select the data you want to analyze and press Alt+h+l+t+t and change the 10 to 1 to highlight the highest percentage, then press Tab+g+Enter. The Tab+g changes the highlighting to green. With the same data selected, press Alt+h+l+t+b and change the 10 to 1 to highlight the lowest percentage, then press Enter. This time, leave the red highlighting as is.
Next, using keyboard shortcuts we’ve already learned, select the data you’ve just formatted in column E, press Ctrl+c, then select the data you want to format in the same manner and press Alt+e+s+t+Enter.All three columns will now highlight the best and worst growth rates by product and by branch. Your spreadsheet will look as shown below.
Formatting Name Text
This trick comes in handy when analyzing vendors, customers or employees with varying formats. Different systems will store names in all caps, all lowercase, or in proper case formatting. The formulas below allow you to choose how you show the names in your analysis or presentation.
David Zawitkowski is a senior analyst with the Transaction Advisory Services practice of Citrin Cooperman
, a full-service CPA firm and presently the 22nd largest in the United States. David works primarily with strategic buyers and private equity firms making stock and asset purchases and growth-equity investments. He has two undergraduate degrees from Villanova School of Business and an MBA from Rutgers Business School.
Read more by David Zawitkowski: