That’s good morning in Czech. Hello from Prague. I am here as part of our Euro trip. Next stop is Munich before we go home to Wellington. So far, this trip has been beautiful and fun.
A secret tip about tracing precedents in Excel
I want to share a very useful and almost secret Excel tip with you all. Imagine you are looking at a big, complex workbook with lots of calculations. You want to understand where everything is pointing to and how the workbook is set up.
Don’t worry, there is a feature in Excel to do this. It’s tracing precedents / dependents. If you select a cell and click on the trace precedents (or trace dependents) button in Formula ribbon, then Excel will draw arrows to all connected cells. This helps you visualize how everything is linked up.
Now comes the secret part. You can double click on the lines to go to the linked cells / ranges / tables!!!
I explain all of this in a short video. It has live music from Prague streets too. Czech it out
Howto trace precedents in Excel formulas? - YouTube
A few weeks back Jhouz asked a question in the Chandoo.org Forums “Is is possible to create a doughnut chart like this one in excel?”
This post will examine how to make it
Alert: It isn’t as straight forward as you may first think!
A couple of users responded with a Doughnut Chart
Which at first glance looks quite similar.
But the original author wanted round ends on the ends of the Doughnut segment. He also wanted a smooth chart.
A quick scan through the properties of a Doughnut Chart reveals there is no optionality to control the ends of the Doughnuts Segments. An alternative approach was required.
A Solution
Before starting, if you want to you can follow along using a sample file with the worked examples shown below: Download Here
The solution I posed was to use an X-Y Scatter chart for the line segments and apply a thick Line style.
The part of this approach that makes it work is that Line Styles have a property for the Lines End including an option for a round end.
The solution chart above consists of 2 lines
The first is the Background (Grey) line, which is a complete
circle
The second line is the green line, which is a segment of the circle equal to in this case 45% of a circle or 162 Degrees (0.45 x 360). It is in front of the Grey line.
To apply this technique I used a number of Named Formula, and based the chart on these named formula:
First for the Background Grey chart segment
To define the Grey segment I applied 3 Named Formula:
This works by using the Excel Row() and Offset() function to generate an array of Degrees from 0 to 360
The formula ROW(OFFSET(Sheet1!$A$1,,,360+1,1))
Will return ={1;2;3;4;5;6; …. ;358;359;360;361}
Note that we have taken the array 1 degree past 360 because the Row’s lowest value is Row 1, not row 0.
We then subtract 91 degrees from this to allow the Chart to start at the top of the circle.
The adjusted formula ROW(OFFSET(Sheet1!$A$1,,,360+1,1))-91
Returns: ={-90;-89;-88;-87; … ;268;269;270}
Finally the – in front of the array changes the direction of the circle from Anticlockwise to clockwise.
Returns: ={90;89;88;87; … ;-268;-269;-270}
The Radians() function is used to convert the array of
Degrees into an array of Radians
Returns: ={1.57;1.55;1.53; … ;-1.22;-1.23;-1.25}
The Radians above were rounded to 2 decimals places for display on this post, but Excel internally is using the full 15 decimal place precision.
We can now use this array of Radians to draw the background circle
To do this setup 2 new Named Formula
_x1: =COS(c1_Rad)
_y1: =SIN(c1_Rad)
Each of these will return an array of the X and Y values corresponding to each of the Radians from the previous c1_Rad array. The X and Y values will vary between -1 and 1. You may need these for Chart Scaling later.
If you want a circle of different radius simply multiply the x and y formulas like _x1: =COS(c1_Rad)*5 for a radius of 5 and the same for the _y1 named formula
To plot these we add a X-Y Scatter Chart.
Select a single cell. Then goto the Insert, Chart, Scatter Chart menu and select a Scatter Chart with Smooth lines. This will give you a blank chart.
With the Chart Selected, Right click on the chart area and choose Select Data…
Add a Series using the Add button. Use the Worksheet Name Sheet1 and Named Formula _x1 & _y1 for the X and Y values
You can leave the Series Name blank or enter a value like “Background Circle”. Note that you must enter the Sheet Name including the ! preceding the Named Formula name. Once you have accepted the inputs, if you return to the Edit Series dialog, notice that Excel now displays the Workbooks name instead of the Worksheets name. That’s quite ok.
You will now have a chart which looks like:
Finally Right click on the first series and select Format Data Series.
Set the Line Color to a Light Grey and set the Line Width to 12 . Check that Markers are set to None
Next the Foreground Green chart segment
To draw the front arc of the circle we add a few more Named Formula
_pct stores the value of the percentage of the circle directly from the reference cell on the worksheet eg: 45%
To draw an arc we only need to factor the 360 Degrees for a full circle back to the percentage required for the arc: ie: from 0 to 45% x 360 degrees = 162 Degrees. Hence drawing an Arc from 0 degrees to 162 Degrees.
To do this we use the same formula as before except that we set the range to the 45% of 360 degrees using the Named Formula:
With the Chart Selected, Right click on the chart area and choose Select Data…
X values: =Sheet1!_x2
Y values: =Sheet1!_y2
Next select the chart and ensure that the 45% circle is in front of the full circle
Select the Chart’s 2nd series and change the line width and line color to suit the impact you want.
Finally select the 45% line
Goto the Lines properties and set the Cap type to Round
Add the Measurement
With the Chart selected, goto the Insert, Text Box dialog and select a text box style and insert it.
With the text box selected, goto the Formula Bar and enter the Formula =_pct and press Enter or click the Tick icon to accept.
Finally with the text box selected, Change the Font Size to suit eg: 64 and Format the Text using an appropriate style from the Drawing Tools, Format Menu
Ensure the Text box is wide enough to display up to 100% include the percentage sign
The Final Chart
and with another value…
Other line type endings
Experiment with other Line Ends and see what you can make?
and Line Styles and Thicknesses?
Multiple Series
By careful use of chart series you can add multiple measurements to the same chart and use a combination of display properties to enhance your chart
Conclusion
In conclusion I have demonstrated a successful solution to Jhouz’s original post and then extended it a bit further.
The Author acknowledges that there is limited use for doughnut charts and only recommends them in limited circumstances.
I hope these enhancements allow you to better use and emphasise your data in your situation as well as add another Excel technique to your arsenal.
For every column chart that is done right, there are a dozen that get messed up. That is why lets talk about 5 simple rules for making awesome column charts.
Tip: Same rules apply for bar charts too.
Rule #1: Start at zero
The first rule is simple. Always start your column charts at zero. When looking at column (or bar) charts, our mind measures height of each column and compares. So, if a column starts at some arbitrary point instead of zero, it can mess with our perception of how each column compares with other. Don’t believe me. See yourself.
Sort your columns in a meaningful order. For example, sort them by descending order (of column heights), alphabetical order or chronological order. This will make reading the chart easy.
Rule #3: Slap a title on it
Give your chart a meaningful, clear title. Few examples of good and bad titles shown below.
For most charts you can use data labels instead of axis & grid-lines. This will keep the chart clean.
If you choose to go with Axis and gridlines, then make sure they follow below guidelines.
Axis label text should be relatively small & dull.
Grid lines & axis line should be dull too.
Do not display too many or too little major units on axis. You can change major unit size by selecting axis and pressing CTRL+1 (or axis options pane in Excel 2013).
Rule #5: Too much lipstick and you have a pig
Make sure the formatting (colors, fonts, special effects, backgrounds etc.) of your chart are really subtle and meaningful. If you use too many colors, you end up with a pig. People will then focus on all these colors, fonts instead of actual data.
Few ways to add wow factor to your chart without messing it up:
So there you go. Follow these rules and your column charts will stand tall.
Share your rules for making awesome column charts
While above rules capture the gist of making good looking column charts, there is more to learn and follow. So go ahead and share your rules and tips using comments. Teach us how you make stunning column charts (or bar charts). Post your comments below.
Make charts often? Check out these tips:
If your job involves analyzing & charting data, then check out below tips to learn more.
Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.
Count calls outside business hours – problem
So you are up for the challenge eh? Just follow the below steps.
Write a formula in calls table to identify if a call is outside office hours. Can be Yes/No or TRUE/FALSE
Write a single formula outside the tables to count how many calls were made outside office hours. The answer should be 693. You can use helper columns (or the column in problem#1) if you want.
Set up a Power Query to filter all outside office hours calls.
Successful in finding the answers? Please post them in comments.
If you have an answer, please post your formulas / VBA / M / spells in the comments area.
Recently, Microsoft Power BI introduced a very useful visualization, called key influencers visualization. As the name suggests, this is a chart of key parameters that effect a measure or outcome.
For example, you have customer satisfaction rating as a measure. Now you want to know which aspects of your data impact the ratings most? You can create the key influencer visual and Power BI finds all the top ranking influencers (using rules and machine learning).
The output can go like this:
How to read this chart?
Let’s look at top influencer for rating to be LOW: If role of the rating person is “Consumer” then their rating is 2.57 times likely to be low than other roles.
Likewise, if company size is <5000, then their rating is 1.48 times likely to be low than other company sizes.
As soon as I saw this chart in Power BI demo, I went…
Hot damn, that looks interesting!!! Can we get this in Excel?
Of course, Excel is a good few laps behind Power BI when it comes to data viz. But that won’t stop a data nerd, will it?
So here we go, a faked “key influencer chart in Excel”. Read on to learn how to create this yourself, from almost any data.
Key influencer chart in Excel – demo
Before we learn how to make this, let me present the chart itself.
Create your own key influencer chart in Excel…
So you are ready to make the chart? Just follow below steps and your key influencers will be identified, sorted and presented in a tidy chart.
Step 1: Arrange your data
This method works with data in one table. You can scale it to a dimensional model (star schema) with some creative pivot tables or cube formulas, but if you have gone that far, then you might as well jump to Power BI and save yourself a lot of agony.
Say your data is in a table like this. We want to investigate key influencers (from dimensions) of “Salary” column. This data is in a table named data.
Step 2: Calculate and sort influences
Now that we know our objective, let’s go ahead and crunch some numbers.
First, generate a list of all influences. This step is a bit manual, but not too hard. You can use Power Query to automate it if it gets too much.
We get something like this:
As indicated above, we need to calculate two kinds of averages.
average of each column=criteria
average of each column<>criteria
This is easily done by a couple of AVERAGEIFS formulas.
For example,
=AVERAGEIFS(data[salary], data[Dept], "Accounting") for "Accounting" average pay.
=AVERAGEIFS(data[salary], data[Dept], "<>Accounting") for all departments except "Accounting"
Let’s be smart then..,
Instead of writing formulas with manual criteria, we can tweak the column (data[Dept] for ex.) on the fly. After all, we know the column name.
So what does it do? This formula calculates average of data[Salary] where M5 (Accounting for ex.) is found in the column that has the same header as L5 (Dept).
Now that we have both averages, we can calculate the influence of something like this:
Influence = average of criteria / average of not criteria – 1
Order (rank) of influence = Individual influence’s rank in all influences
We can use simple arithmetic for 1 and RANK.AVG() for 2.
(Picture A)Summary of all the calculations so far…
Calculation Summary – Picture A
Step 3: Start making the chart then…
Now that everything is ready, go to Insert ribbon and add Key Influencer chart.
Of course I’m kidding. There is no such button. But you can insert a 3D donut chart. Or may be not.
So let’s move on then.
The Key Influencer chart demo’d at the start is actually a scatter plot. See below anatomy.
10 Steps for creating the chart
(1) Make a scatter plot from “more by” and “influence order” columns
Select columns 3 & 4 as shown in Picture A and insert scatter plot. We get something like this.
(2) Reverse the chart by changing Y (vertical) axis order
Just select the Y (vertical) axis and go to axis format settings (CTRL+1 shortcut). Now select “Values in reverse order” option.
We get this.
(3) Limit vertical axis from 0 to 8
The scatter plot is showing all influences. We don’t need to see everything. So let’s limit the chart to top 8 influences. To do that, simply enter axis limits as 0 and 8.5 (if you put 8, then the last point will be hugging bottom border of chart and makes it hard to read).
You will end up with this.
(4) Replace dots with bubble
Now draw a bubble shape in the spreadsheet. Copy it (Ctrl+C). Select the dots in the chart and hit paste (CTRL+V). We get nice bubbles instead of dots in the chart. See this quick tut to understand the concept.
(5) Add data labels
Select the bubbles and add data labels. Show either X value or calculated labels from cells. Center align and adjust font settings if needed. At this point our key influencer chart looks like this:
(6) Add a dummy series with values just 1 or 2% less than influence
Now that our bubbles are ready, we need to show an arrow from 0 to the influence amount. To do this, we will use error bars, specifically 100% negative x error bars. Try saying that three times in a row.
This is easy to get. Simply add a new column to the calculations area. Write =influence – 2% and you get “Dummy for arrows” column.
Add this column to the chart. Remember, the Y values will be same as “Influence order” (Column 4 in Picture A)
(7) Add 100% negative x-error bars to the dummy series and format them
Wait a second. You can’t simply do that. So just add error bars and then,
Remove vertical (Y) error bars
Select horizontal (X) error bars
Format them (CTRL+1 shortcut)
Set bar direction to “Minus”
Error amount to Percentage, 100%
And end style to “No cap”
We end up with this chart.
While you are at error bar formatting screen, also adjust the bar color and begin arrow type so we get this nice arrowed error bars.
(8) Add another dummy series at -20% for axis labels
We know that top influencer increases average salary by 8.8%, but we don’t know what that is. Time to fix the problem.
Let’s create our own axis labels. Start by adding a dummy column with =-20% in the calculations area.
Also, create the label we want (this can be column & ” is ” & criteria or something else).
You need data like this.
Once that is ready, add a new series to the chart (from Select Data screen) and set X as “Dummy x for axis label” and Y as Influence Order (column 4 in picture A).
At this stage, our key influencer visual looks like this:
(9) Add the labels
Select this new dummy series, add data labels to it. Change label settings so that you can get values from cells (works only Excel 2013 or above). Point to the cells with calculated axis labels.
When everything is set up and formatted, we will have this chart:
(10) Nearly there, just clean up and format the chart
Can you feel the rush of creating something beautiful, fun and interesting in Excel? We are almost done. Just clean up the chart. Remove markers from any dummy series that are not needed. Get rid of grid lines. Add background if you want. Color things and our key influencer chart in Excel is ready.
Final touches – Form control to see positive and negative influences
Of course, the chart is nearly done. But if you want, you can dynamically show either positive or negative influencers. To do that, simply multiply the “More by” column (Column 3 in Picture A) with +1 or -1. +1 for positive influence, -1 for negative. Everything else works just as expected. You can link this to a form control and you will have a dynamic influencers chart.
Key Influencer Chart in Excel – Video tutorial
As this is a fairly complex chart, I made a video tutorial explaining all the nuts and bolts. Watch it if you need a hand with the construction.
How to fake *AWESOME* key influencer chart in Excel - YouTube
This file contains detailed instructions, sample data and calculations. Use it to learn or modify for your needs.
What next?
If this is the first time you made a complex Excel chart, pat yourself on the back, go for an extra round of your favorite beverage and hug your loved ones.
And oh yeah, continue the journey with these other examples. You will be richly rewarded.
Budget vs. Actual Chart
Sixty sports in 6 charts
Dynamic chart in Excel
Commonwealth games in a chart
Interactive pareto chart
Salary increases in scatter plot
How do you like the Key Influencer Chart?
I love the original thing in Power BI. Faking (recreating) it in Excel was fun but not scalable for large or split out data sets. I enjoyed the process immensely and immediately wanted to share it with all.
What about you? How do you like the key influencers chart in Excel? Share your thoughts in the comments section.
Its Easter time. At Chandoo.org, I have tradition of publishing Easter Egg hunts since 2009. This is the first time our Easter egg hunt is on Power BI. Changing times, eh?
We all know that VLOOKUP can find first match and return the results. But what if you want to VLOOKUP multiple matches? Use this simple trick instead.
Multiple matches in Excel lookups
The problem is simple as illustrated below. Say you have a bunch of dates & locations. You want to find out corresponding date(s) for a location. If you use either VLOOKUP or INDEX+MATCH, you just get the first date, but you want them all.
In new version of Excel (2019), you can use awesome formulas like FILTER() to do exactly this, but most of us are still stuck in older version of Excel. So how to get all the matches?
The trick – Use TEXTJOIN…
TEXTJOIN is a text combining function available on Excel 365. We can it to solve our multiple matches problem.
Let’s say our data is in a table named plan. We want to lookup the location in cell G7 and return all matching dates.
We can use below TEXTJOIN formula (you must press Ctrl+Shift+Enter) to get result as this is an array formula.
IF(plan[Location]=G7 this part checks every location in the plan table and returns a bunch of TRUE or FALSE values. The end result would be an array like this. {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; TRUE;TRUE;TRUE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
TEXT(plan[Date], “dddd dd-mmm”) this part takes all the dates in plan table and converts them to dddd dd-mmm format (ie 18/05/2019 becomes Saturday 18-May). The end result would be another array of dates formatted.
IF(plan[Location]=G7, TEXT(plan[Date],”dddd dd-mmm”),””) this entire IF formula results in either a bunch of formatted dates or blanks, based on whether plan[Location] matches G7 or not. So we get something like this. {“”;””;””;””;””;””;””;””;”Saturday 18-May”;”Sunday 19-May”;”Monday 20-May“;””;””;””;””;””;””;””;””}
TEXTJOIN(“, “,TRUE, IF()) this is the final part of the puzzle. TEXTJOIN takes individual bits of the array in #3 and combines them with delimiter , (comma space) but ignores any blanks (TRUE as second parameter). The final result is one text that goes like – Saturday 18-May, Sunday 19-May, Monday 20-May
Why bother with TEXT(plan[Date], “dddd dd-mmm”)?
If you don’t use TEXT() to convert your dates, you get something like this instead – 43603, 43604, 43605. This is because Excel dates are really just numbers. So when you use them in any formula you just get the underlying number value. This is why TEXT() formula is so helpful.
As mentioned earlier, TEXTJOIN is available in Excel 365 or 2019 + only. So if you are using an older version of Excel, you should use one of the below techniques to get multiple matches.
Let’s say you manage a fund or charity. You get money from various places and you use that money for various reasons. How do you tell the story of source vs. uses of funds? In this post, let’s review 14 charting options.
Source vs. Use of funds – Sample Data for this problem
Let’s say you have 3 sources and 3 uses, as shown below. For all these charts, we will use the same data, but may add some simple calculations or change the layout to get the charts.
As I made quite a few alternatives, I broke them in to two kinds of charts.
Simple charts: These are easy to make, easy to read and scale well when you have lots of sources & uses.
Exotic charts: These are somewhat tricky but fun to make, look exotic and raise a few eyebrows in the meeting room.
Simple Charts for Source vs. Use of funds
#1 – Waterfall chart
Waterfall chart is perfect for this kind of data. Simply layout your data like below (you can use SUBTOTAL or SUM formulas to calculate intermediate totals), select everything and insert waterfall chart from Insert ribbon. Mark total columns “as total” and you are done.
#2 – Stacked column chart
Set up your data in two columns as shown aside and create stacked column chart. Format to your taste. Simple and effective.
#3 Couple of Pies
Use the same data format as “stacked column chart” but instead make two pie charts. If you have just a few options for sources and uses, Pie charts work great.
#4 – Mirrored bar chart
As you can see, sources and uses are two sides of the same coin, so we can go with a mirror metaphor like above. To create this chart, you need data in the format shown below. Once you have the data, just select it and insert a stacked bar chart. Format as you see fit and the mirror is ready.
#5 – Dollar background – stacked chart
This is same is option #2, but with a fancy dollar bill for plot area background and gap width for columns set to 0. When you make the fill colors for series semi-transparent, you get this effect.
#6 – Touching stacked bars…
Again, same format of data as “stacked chart #2”, but we make a stacked bar chart and then set gap width to 0 to get this effect. This works very well and makes it easy to compare.
#7 – Donuts
While I am not a fan of donuts or pies, they work great when you have small set of data like this. Set up your data like #2 stacked chart and instead make donut chart. Format and you get this.
#8 – Half a pie each…
This works very well if source total = use total. Just set up your data like this and make a normal pie chart. Format sources and uses in two colors like above and draw a line in the middle. You get above chart.
#9 – Sunburst chart
This is essentially similar to donuts (option #7) but we need data in a different structure (see aside). Sunburst charts work very well even when you have lots of items in each category or even sub-categories. Keep in mind though, sunburst charts are new to Excel 2016, so if you have older versions, you can’t get these.
#10 – Treemap chart
Same as sunburst charts, treemaps are a new chart in Excel 2016. They can work well when you have many items or even sub-categories.
Exotic charts for Source vs. Use of funds
#11 – Hourglass chart
The idea is Sources flow to Uses. So we make an hour glass shape with regular bar chart (with invisible series on left to push the items to center).
To create this, set up your data like below.
Now, simply create a regular stacked bar chart. Select Gap series, make it invisible (fill color > none), format source and uses series. You are done.
#12 – Butterfly chart
Sources and Uses as wings of butterfly. This is really an area chart, with an invisible series at the bottom to create the effect of butterfly wings. To make this chart, set up your data like below:
Now, Make an area chart. Move gap series to top (so it is really at the bottom when laid out), then make it invisible. See this GIF to understand how it all works to make the butterfly wings.
#13 – Waterfall lines
Instead of waterfall bars, we make this with lines. Unfortunately, this step chart is the trickiest one to create as we use XY plot with error bars to get the steps. The technique has too many steps, so I am not explaining them here, but if you are curious, see this tutorial.
#14 – Cube chart
Here the idea is Sources and Uses are two faces of a cube. Again, this is actually an area chart, but invisible gap series at bottom and a top layer. To make this, set up your data like below (remember, the horizontal axis has to be dates, else you will not get Cube effect).
To be honest, I am not a fan of cube chart, as it is tricky to label things and won’t work for more items. But I wanted to give it a try.
Download all the 14 charts…
I made a simple workbook with all 14 examples. Click here to download the file and play with it to learn. May be try making something on your own and post that in the comments section?
What is your favorite chart?
My favs are #1 (waterfall), #12 (butterly chart) and #8 (half pies).
What about you? What is your favorite chart among all these? Post in comments. Also feel free to make your own and share them so we can all learn.
Like charts & visual story telling? Check out these amazing examples
If you are in to data analytics & visualizations, you are going to love Chandoo.org. We got tons of examples and tips. Start with these…
Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.
Top 10 Excel Formulas – The list
#1 Table & Structural References
If you don’t know how to effectively talk to your data, then your formulas will suck. That is why, my #1 formula is not even a formula. Learn how to refer to data in the spreadsheet, especially in tables. You can use tablename[column name] notation to refer to entire columns of data. You can also use [@column] to refer to column value in the current row of table.
For example, you can write formulas like these:
SUM(mySales[no. of customers]) to find how many customers we had.
SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”
You might already know about IF formula. We use it to test logical conditions and output one of two possibilities. But what if you have a very long, complex scenario that requires multiple IF functions? Simple, use IFS() instead. It can take any number of condition, output combinations and works elegantly.
Example Nested IF formula: =IF(A1>20, “Very high”, IF(A1>15, “High”, IF(A1>10, “Medium”, IF(A1>5, “Low”, “Very Low”))))
Same formula as IFS() =IFS(A1>20,"Very high", A1>15,"High", A1>10,"Medium", A1>5,"Low", A1<=5,"Very low")
#3 SUMIFS / COUNTIFS
Almost all business analysis situations will involve questions like “what is the count / sum of things that meet conditions A,B…N”. And to answer them elegantly and swiftly, you need SUMIFS / COUNTIFS. The beauty of these functions is that they are easy to learn and use.
Example SUMIFS formula:
=SUMIFS(data[Purchase Amount], data[Lead Group],"Online", data[Quantity], ">3")
Sums up [Purchase Amount] column where [Lead Group] is online AND [Quantity] is more than 3.
SWITCH is one of the new functions introduced in Excel. This versatile function helps you select one of the many outcomes based on any type of conditions. In some ways, SWITCH is similar to IFS, but it also has default option, so if none of the SWITCH conditions are met, you get value in the default parameter.
Example SWITCH formula:
=SWITCH([@State],"CO","Other","WA","Other","TX","South","East")
What it does?
Looks at [@State] value and prints one of the outputs - "Other", "South" or "East"
#5 VLOOKUP – Always in style
The other day, I went to drinks with a few mates after work. We were sitting in a hip bar drinking best of Wellington beer on a sunny day. I overhear two ladies talking about, wait for it…. VLOOKUP. I turn around and look at them. They look like fresh graduates celebrating a busy week of work and they could be talking about almost anything, but VLOOKUP is trending.
So yeah, learn VLOOKUP you must. It is the quintessential Excel function for data analysis. You can answer questions about your data using VLOOKUP.
You know that SUM(), COUNT(), AVERAGE() etc. give you basic stats about your data. But what if you filtered out to look at data for “HR department” only or “people aged between 25 and 40”. Your SUM() doesn’t change.
This is where SUBTOTAL() comes in. By default, SUBTOTAL ignores anything that is filtered away. So what you see is what you get.
Example SUBTOTAL formula:
=SUBTOTAL(9,data[Purchase Amount])
Sums up (9) filtered values in data[Purchase Amount] column.
You may already know about MAX() and MIN() formulas. But what if you want to know the maximum value based on a few criteria? Simple, use MAXIFS(). This newly added function is simple, versatile and easy to learn (if you know SUMIFS, then you know this too).
Example MAXIFS() formula:
=MAXIFS(data[Purchase Amount], data[Lead Group],"Online")
Returns maximum data[Purchase Amount] where [Lead Group] is "Online"
#8 FIND / SEARCH
There are heaps of text formulas in Excel. But if you are just starting out, go with FIND(). It finds one text value inside another. If there is a match, FIND() returns the starting position of the match, else #VALUE error.
Keep in mind though, FIND() is case sensitive. Use SEARCH() if you don’t care about the case of data.
Examples of Excel FIND() and SEARCH() functions.
=FIND(“l”, “Hello people..”) => 3
=FIND(“P”, “Hello people..”) => #VALUE! error as P can’t be found.
=FIND(“p”, “Hello people..”) => 7
=SEARCH(“P”, “Hello people..”) => 7 as SEARCH doesn’t care for case
=FIND(“p”, “Hello people..”, 8) => 10 Finds p after 8th letter – i.e. second p
#9 TODAY / NOW
Almost all business data will have a component of dates. So learning how to work with date & time values in Excel can be a huge help. If you are new to this, start with TODAY(). As the name suggests, TODAY() tells you the current date. This is a dynamic formula, so if you write =TODAY() in a cell, the date changes every day.
You can use NOW() formula to see current date & time.
Example – Calculating employee tenure in days:
If you have start date of an employee in a cell (A1), you can calculate their tenure (service) using TODAY() formula like this.
=TODAY() - A1
The answer will be number of days between A1 (Start date) and today.
Errors happen, but use IFERROR so that your audience see soothing messages rather than confounding #VALUE!s. IFERROR looks at internal formula or expression and if there is an error, prints alternative result you want.
Example IFERROR():
=IFERROR(VLOOKUP("THIS", Customers, 2, false), "Customer not found")
Looks for "THIS" in Customers table and returns 2nd column value if found, else says "Customer not found".
I made a video about these top 10 formulas with an example data set. You will learn all the basics + some nifty tricks about these formulas in the video. Check it out below or watch it on my YouTube channel.
If you want to learn more about Excel formulas, you have come to the right place. Check out below three resources and be a formula master.
#1 – Read other formula articles
Formulas / functions are central to doing any kind of complex work in Excel. No wonder we have more than 400 pages on this on my site. Start with these examples and see how deep the rabbit hole goes.
Online video courses are easy and powerful way to learn everything you need from the comfort of your chair. I highly recommend either of these two for mastering Excel formulas & data analysis.