Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.
While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.
If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.
A good analyst should be familiar with below 10 formulas to begin with.
1. SUMIFS Formula
If you listen very carefully, you can hear thousands of managers around the world screaming… “How many x we did in region A, product B, customer type C in month M?” right now.
To answer this question without the song and dance of excessive filtering & selecting, you must learn SUMIFS formula.
This magical formula can sum up a set of values that meet several conditions.
The syntax of SUMIFS is like this:
=SUMIFS( what you want to sumup, condition column 1, condition, condition column 2, condition….)
Example:
=SUMIFS(sales, regions, “A”, products, “B”, customer types, “C”, month, “M”)
Learn more about SUMIFS formula.
10 Advanced SUMIFS examples (video)
2. X/VLOOKUP Formula
Pop quiz time ….
Which of the below things would bring world to a grinding halt?
A. Stop digging earth for more oil
B. Let US jump off the fiscal cliff or hit debt ceiling
C. Suddenly VLOOKUP (or XLOOKUP) formula stops working in all computers, world-wide, forever
If you answered A or B, then its high time you removed your head from sand and saw the world.
The answer is C (Well, if all coffee machines in the world unite & miraculously malfunction that would make a mayhem. But thankfully that option is not there)
XLOOKUP or VLOOKUP formula lets you search for a value in a table and return a corresponding value. For example you can ask What is the name of the customer with ID=C00023 or How much is the product price for product code =p0089 and VLOOKUP would give you the answers.
The syntax for VLOOKUP is simple.
=VLOOKUP(what you want to lookup, table, column from which you want the output, is your table sorted? )
Example:
=VLOOKUP(“C00023”, customers, 2, false)
Lookup customer ID C00023 in the first column of customers table and return the value from 2nd column. Assume that customers table is not sorted.
Learn more about the new & improved XLOOKUP formula.
Click here to learn more about VLOOKUP Formula.
Bonus: Comprehensive guide to lookup formulas.
3. Dynamic Array Functions
Excel 365 introduced a new class of functions called DYNAMIC ARRAY FUNCTIONS. These will let you filter, sort, extract distinct values from your data with ease. It also added a special formula functionality called spill behavior. It means Excel formulas can now return multiple values as a result & spill them down as needed. See this quick GIF demo.
Learn more about the POWERFUL dynamic array functionality in Excel (video).
4. IF & IFS Formulas
Q: What do you call a business that does not make a single decision?
A: Government!
Jokes aside, every business needs to make decisions, even governments!!! So, how do we model these decisions in Excel.
Using IF formulas of course.
For example, lets say your company decides to give 10% pay hike to all people reading Chandoo.org & 5% hike to rest. Now, how would you express this in Excel?
Simple, we write =IF(employee reads Chandoo.org, “10% hike”, “5% hike”)
The syntax of IF formula is simple:
=IF (condition to test, output for TRUE, output for FALSE)
10 must know Advanced IF formulas.
5. Nesting Formulas
Unfortunately, businesses do not make simple decisions. They always complicate things. I mean, have you ever read income tax rules?!? Your head starts spinning by the time you reach 2nd paragraph.
To model such complex decisions & situations, you need to nest formulas.
Nesting refers to including one formula with in another formula.
An example situation: Give 12% hike to employees who read Chandoo.org at least 3 days a week, Give 10% hike to those who read Chandoo.org at least once a week, for the rest give 5% hike.
Excel Formula: =IF(number of times employee reads chandoo.org in a week >=3, “12% hike”, IF( number of times employee reads chandoo.org in a week >0, “10% hike”, “5% hike”))
You see what we did above? We used IF formula inside another IF formula. This is nothing but nesting.
You can nest any formula inside another formula almost any number of times.
Nesting formulas helps us express complex business logic & rules with ease. As an analyst, you must learn the art of nesting.
Lots of nested formula examples & explanations here.
6. Basic Arithmetic Expressions
=(((123+456)*(789+987)) > ((123-456)/(789-987)))^3 & " time I saw a tiger"
If you read the above expression and not had to scratch your head once, then you are on way to become an awesome analyst.
Most people jump in to Excel formulas without first learning various basic operators & expressions. Fortunately, learning these requires very little time. Most of us have gone thru basic arithmetic & expressions in school. Here is a summary if you were caught napping in Math 101.
| Operator | What it does | Example |
|---|---|---|
| + – * / | Basic arithmetic operators. Perform addition, subtraction, multiplication & division | 2+3, 7-2, 9*12, 108/3, 2+3*4-2 |
| ^ | Power of opetator. Raises something to the power of other value. | 2^3, 9^0.5, PI()^2, EXP(1)^0.5 |
| ( ) | To define precedence in calculations. Anything included in paranthesis is calcuated first. | (2+3)*(4+5) calcuates 2+3 first, then 4+5 and multiplies both results. |
| & | To combine 2 text values | “You are ” & “awesome” returns “You are awesome” |
| % | To divide with 100. | 2/4% will give 50 as result. Note: (2/4)% will give 0.5% as result. |
| : | Used to specify ranges | A1:B20 refers to the range from cell A1 to B20 |
| $ | To lock a reference column or row or both | $A$1 refers to cell A1 all the time. $A1 refers to column A, relative row based on where you use it. For more refer to absolute vs. relative references in Excel. |
| [ ] | Used to structurally refer to columns in table | ourSales[month] refers to the month column in the ourSales table. Works only in Excel 2007 or above. Know more about Excel Tables. |
| @ | Used to structurally refer to current row values in a table | ourSales[@month] refers to current row’s month value in oursales table. |
| # | Spill Operator (Excel 365) | Used to get spill range from a dynamic array formula |
| { } | To specify an inline array of values | {1,2,3,4,5} – refers to a the list of values 1,2,3,4,5 |
| < > <= >= | Comparison operators. Output will always be boolean – ie TRUE or FALSE. | 2>3 will be FALSE. 99<101 will be TRUE. |
| = <> | Equality operators. Check whether 2 values are equal or not equal. Output will TRUE or FALSE | 2=2, “hello”=”hello”, 4<>5 will all return TRUE. |
| * ? | Used as wild cards in certain formulas like COUNTIFS etc. | COUNTIFS(A1:A10, “a*”) counts the values in range A1:A10 starting with a. For more on this refer to COUNTIFS & SUMIFS in Excel |
| SPACE | Intersection operator. Returns the range at intersection of 2 ranges | A1:C4 B2:D5 refers to the intersection or range A1:C4 and B2:D5 and returns B2:C4. Caution: The output will be an array, so you must use it in another formula which takes arrays, like SUM, COUNT etc. |
7. Text formulas
While there are more than two dozen text formulas in Excel including the mysterious BHATTEXT (which is used to convert numbers to Thai Bhats, apparently designed by Excel team so that they could order Thai take out food #), you do not need to learn all of them. By learning few very useful TEXT formulas, you can save a ton of time when cleaning data or extracting portions from mountains of text.
As an aspiring analyst, at-least acquaint your self with below formulas:
- LEFT, RIGHT & MID – to extract portions of text from left, right & middle.
- TRIM – to remove un-necessary spaces from beginning, middle & end of a text.
- SUBSTITUTE – to replace portions of text with something else.
- LEN – to calculate the length of a text
- TEXT – to convert a value to TEXT formatting
- FIND – to find whether something is present in a text, if so at what position
Here are my top 6 TEXT formulas for data analysis.
8. NETWORKDAYS & WORKDAY Formulas
“There aren’t enough days in the weekend” – Somebody
Whether a weekend has enough days or not, as working analyst, you must cope with the working day calculations. For example, if a project takes 180 working days to complete and starts on 16th of January 2013, how would you find the end date?
Thankfully, we do not have to invent a formula for this. Excel has something exactly for this. WORKDAY formula takes a start date & working days and tells you what the end date would be.
Like wise NETWORKDAYS formula tells us how many working days are there between any 2 given dates.
Both these formulas accept a list of additional holidays to consider as well.
- NETWORKDAYS: calculate the number of working days between 2 dates (assuming Saturday, Sunday weekend)
- NETWORKDAYS.INTL: Same as NETWORKDAYS, but lets you use custom weekends [Excel 2010+ only]
- WORKDAY: Calculate the end date from a start date & number of working days
- WORKDAY.INTL: Same as WORKDAY, but lets you use custom weekends. [Excel 2010+ only]
More on working with Date & Time values in Excel.
9. SMALL & LARGE Formulas
Almost nobody asks about “Who was the second person to climb Mt. Everest, or walk on moon or finish 100 mtrs race the fastest?”.
And yet, all businesses ask questions like “Who is our 2nd most valuable customer?, third vendor from bottom on invoice delinquency? 4th famous coffee shop in Jamaica?”
So as analysts our job is to answer these questions with out wasting too much time. That is where SMALL, LARGE formulas come in handy.
- SMALL: Used to find nth smallest value from a list. Use it like =SMALL(range of values, n).
- LARGE: Used to find nth largest value from a list.
- MIN: Gives the minimum value of a list.
- MAX: Gives the maximum value of a list.
- RANK: Finds the rank of a value in a list. Use it like =RANK(value, in this list, order)
10. IFERROR Formula
Errors, lousy canteen food & dysfunctional coffee machines are eternal truths of corporate life. While you can always brown bag your lunch & bring a flask of finely brewed coffee to work, there is no escaping when your VLOOKUP #N/As. Or is there?
Well, you can always use the lovely IFERROR formula to handle errors in your formulas.
Syntax:
IFERROR(formula, what to do in case of error)
Use it like:
IFERROR(VLOOKUP(….), “Value not found!”)
Click here to learn more about IFERROR Formula.
3 Bonus Formulas
If you can master the above 10 formulas, you will be ahead of 80% of all Excel analysts. Here are 3 more important formulas that can come handy when doing some serious data analysis work.
- OFFSET formula: to generate dynamic ranges from a starting point and use them elsewhere (in charts, formulas etc.).
- SUMPRODUCT formula: Unleash the full power of Excel array processing by using SUMPRODUCT.
- SUBTOTAL formula: Calculate totals, counts & averages etc. on a range with filters.
Top 10 Excel Formulas – Video
If you like a video presentation of these formulas with some demos, check this out.
Sample file & more on the concepts shown in the video here.
What formulas do you think are important for analysts?
During my days as business analyst, not a single day went by without using Excel. It was an important tool in my journey to become an awesome analyst. I cannot stress the importance of formulas like SUMIFS, VLOOKUP, XLOOKUP, INDEX, MATCH enough. They play a vital role in analyzing data & presenting outputs.
What about you? What formulas do you think are important for analysts? Please share your ideas & tips using comments.
Want to become an Awesome Analyst? Consider our Excel School program
If you are a budding analyst or manager, adding Excel Skills can be a very valuable investment of your time. My Excel school program is designed to help people like you to learn various basic & advanced features of Excel & use them to create kick ass reports, trackers & analysis. This program has 24 hours of Excel training, 40 example workbooks & 6 month online access.
70 Responses to “10 Tips to Make Better and Boss-proof Excel Spreadsheets”
Proper print settings on each sheet helps your boss to print the reports quickly without hastling you after printing irrelevant stuff.
It is highly relevant that you print your reports once before circulating it to your boss or other people.
Knowing that what your boss actully look at in the entire report can be very usefull. You can build a good summary of what your boss wants and put that as separate tab in the form of dashbord report, so that your boss does not peep into rest of your work and start pocking you with irrelevant stuff.
You can also put that Dashboard into the email summary and not trouble your boss to open your workbook. This is ultimate boss proof tip and I have been using this for long time now.
Thank you Chandoo. Great checklist to follow before delivering an excel spreadsheet to someone else. Some points you mention are seemingly so simple that we might overlook them - like selecting cell#A1, but they make a difference to the impression the spreadsheet creates at the recipient's end.
Dear Chandoo,
Great tricks.
One trick I use (more and more) is to hide the sheet tabs and to hide the formulabar via the 'tools' 'options' and the 'view'-tab.
Another trick is to limiting the scrolling area to hide all columms (or rows) until the end of the sheet. Select the column, press CTRL+SHIFT+RIGHT, right-click on the column and hide (also possible via VBA).
I was wondering though if 'boss-proof' is related to 'excel-stupid-proof'?
Cheerio
Tom
Just wondering if the hiding formula bar really works when a recipient opens it whose "view-show-Formula Bar" is still checked...
It's saved to the sheet I believe.
Absolutely agree with this post !!!
on the past months, after reading this blog, PTS's and Debra's Contextures, one of the things I've beggining to do as a best practice is to create all my spreadsheets with 3 tabs: data, summary and control, and this last one generally xlveryhidden, and sometimes the data one hidden as well.
And this restrictions are also being applied as best practice, and with a lot of benefits as you well mentioned. Furthermore, if combined with dynamic named ranges, formulae is more readable to users, and the WOW effect is often achieved when the question "How did you do that?" arises.....
Keep on the good posts !!!
Rgds,
Martin
Would you mind sharing an example of this technique?
Is there a way to keep the data in a seperate file rather than the same excel. This way you could keep presentation and data separate. But not sure how you would link up the two excel files
Yes, there is a way but it is not prefered.
I used this a coulple of times, (You need to code).
mail me if you need assistance with some sort
It entirely is possible. The problem comes though, when you share the spreadsheet.
If the recipient doesn't have both files, or access to both, things break when the values try to refresh.
ey, why is the boss a she??
haha - welcome to the future. About time.
Chandoo, one more trick that we could use with the help of VBA, RT click on the View code of the particular sheet, in the properties table set the Visible status to 2-xlveryhidden, this ensures the sheet name does not show up even when the BOSS tries to unhide the sheet from the sheet >> unhide option. Dont forget to password protect the VBA (available under tools >> VBAProject properties.
Very good tips, although I have to say Chandoo, that your cats probably need to be spayed or neutered if they behave like that. =)
Good to see all these tips on a single "sheet", and giving the name *boss proof*, and Dilbert was a great welcome 😀
The best way to "Boss Proof" (and "Self Proof"!!) a spreadsheet is to keep back ups. I use a macro that saves the last 3 significant versions of the spreadsheet all with a date stamp included in the file name.
To quickly select cell A1 on all sheet, use CTRL-Page UP or CTRL-Page down to navigate between sheets and CTRL-Home to select cell A1 (if you have frozen pane, it will select the top left cell of the section below).
Great list. And I follow every single item... I also use a consistent background color for input cells in every report/dashboard. And I use a little VBA to identify the user and change the report accordingly (selecting the right market, for example).
Chandoo, Nice post. I like to use the hidden Paste Picture Link option. Keep the original report you want displayed on a hidden sheet and only show the boss the report picture. Also great to watch the confusion when boss trying to select cells is worth the effort!
I usually save as PDF if there's no interactivity in the report. That way nothing can go wrong 🙂
PDFs work a dream for me too and saves the boss's EA from telling me all the time that she can't print my work!!
@All.. thanks a ton for sharing your ideas. I am thinking of writing a part 2 of this post explaining some of your ideas in detail.
@Bazlina ... I will make sure the boss is a HE in the next post 🙂
"10 Tips to Make Better and Boss-proof Excel Spreadsheets"...
Unless of course your Boss reads PHD !
Great article with one glaring error.
If (like me) the majority of your spreadsheet errors are *caused* by cats, adding more cats is just going to increase the problem.
@Hui you always have a boss, even if you are boss. If you dont have a boss, then may be a cat or even a dog.
@Debra: hmm... Are you sure the cats are not after the mouse? Go learn some keyboard shortcuts.. now 😛
Great Web Site. I've done almost all the above in trying to build my application and it's taken me hours and hours reading my "dummies " book. Thank you for all this information.
Is there a formula I can use that will automatically return to "A1" cell should an associate use the 10 page spreadsheet I have?
Is there a way to set an expiration date on my workbook so that beynd that date no one will get beyond the cover page?
Paul, in all my "user facing" workbooks (those that I distribute) I create a named range called "Home" on the worksheet(s) that are most likely to be used. Then I write a little VBA that selects the Home range whenever that worksheet is activated or on other triggers depending on the context of the sheet. This is more appropriate for the dashboard tabs or summary tabs my job requires.
But I usually set this functionality up early on in the design process so I can take advantage of it as well. I will sometimes assign a keystroke to the GoHome macro.
I'm in the marketing department (aka the picture department) and have to say that the macros/Excel sheets from our controlling department are the worst! They come to me to sort out the mess!!
@Peter: You can try creating a table of contents and then place it on each and every sheet so that user can jump to anywhere from anywhere. Here is a tutorial to help you get started.
Also, You can prevent users from accessing the workbook after a certain date using macros. But users can certainly by pass it by disallowing macros on that workbook.
@Jimmy: Wow... (just kidding) Welcome 🙂
I was recently given a spreadsheet to improve upon.
One of the "boss-proof" actions that the previous author had used was to use data validation instead of protecting the sheet to ward off people changing formulas.
After entering a formula or value into a cell, use data validation to only allow, in this spreadsheet, whole numbers between 9999999 to 99999999.
It's a bit of a pain to actually correct stuff instead of just unprotecting a sheet, but for those that know how to unprotect a sheet, it's a definite way to keep them from fooling with formulas.
Puchu,
We would love to see "Print" in your links section.
It helps us taking prints as neat as your posts 🙂
Chandoo,
I've emailed you a couple of times looking for avenues I need to try to put my workbook on the Internet.
I notice you use PremiumThemes for your Web Site...You must feel good about their service. Do you think PremiumThemes might be an option for me?
Paul
Instead of :
Now Right click and select “Hide” option.
Shortcut can be used : Ctrl+0 (to hide)..
sir i wanted to know,how to hide cells or tab without hiding rows and columns? PLZ TELL ME
Hi Chandoo!
Great tips! Im researching on an excel project now that you can create to "lighten" the size without sacrificing the data inside..
We usually encounter problems with the data, excel file is shared, in a network folder.. and there are 11 people that enters their own productivity in each tab.. however, there comes a time (uncertain) where some of the data they enter either gets deleted or changes value.. could this be a file size problem? are there other ways to create this file that will decrease data inconsistencies?
thanks!
[...] Hide un-necessary rows to create clean looking workbooks (and 9 more tips) [...]
[...] Presentation format: all spreadsheets, should be designed so that it is easy to follow the process flow and result. Almost every spreadsheet should be presentable and understandable to senior management without additional formatting or explanation. (tips: how to design boss-proof excel sheets) [...]
[...] on Excel formatting here: How to make better excel sheets, Formatting [...]
[...] on Excel formatting here: How to make better excel sheets, Formatting [...]
[...] 10 Tips to make better & boss-proof spreadsheets in Excel [...]
You will find another quick and easy technique here:
http://www.onsitetrainingcourses.com.au/main/page_blog_hiding_most_excel_rows_and_columns.html
[...] tips: Learn how to make better Excel sheets Spread some love,It makes you awesome! [...]
Save what you want the boss to see as a PDF. Absolutely foolproof and no cats hurt in the process.
I really enjoyed allot of the tips on here, especially the one on comments on cells. That will come in handy on allot of our projects. I would also like to share on on my little tricks. I am constantly working on several different reports with several different systems and in doing so I am constantly running in problems and my way out of them is simply calling <a href"http://www.reportingguru.com/"> Reporting Guru </a> and telling exactly what I'm going through and they can tell me exactly how to get out.
One of the things I've found to boss proof my worksheets are a few simple VBA scripts to automatically protect the workbook/worksheets, and direct them to the "Quick Look" dashboard page, I hide all of the raw data sheets before saving. The script looks like this:
Private Sub Workbook_Open()
Sheets("Summary").Protect Password:="password"
Sheets("Labor Cost by Site").Protect Password:="password", AllowUsingPivotTables: =true
Sheets("Labor Cost by month").Protect Password:="password"
Sheets("Quick Look").Protect Password:="password"
Sheets("Quick look").Activate
ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False
End Sub
I also have a pivot that contains labor cost data which cannot be refreshed while the worksheet is locked.
Private Sub Worksheet_Activate()
Sheets("labor cost by site").Unprotect Password = "password"
Set pvttable = Worksheets("labor cost by site").Range("a1").PivotTable
pvttable.RefreshTable
Sheets("labor cost by site").Protect Password = "password", AllowUsingPivotTables:=True
End Sub
OPPAN GANGAM STYLE!
good
Your post are always with something creative , thanks for sharing this information , your post are worth reading and implementing 🙂 great job
Hi,
I will try to learn every point slowly !
Shokran Chandoo.
Best boss Proofing of sheets is useing indirect(address 😛 this prevents most smartass bossess from doing any actual changes cus the formula will be long and hard to understand for any bystanders..
Also putting the actual calculations on a different sheet can make a sheet bulletproof from bosses.. especialy if you put them in the Very hidden so when the boss learns how to unhide sheets he wont simply find them.
One thing iv also learned is that most bosses is scared of macros that gives "virus" warnings before beeing run 😛 That include the default warning from Excel...
Long formulas or work arounds is best way to go.
What's the best way to amalgamate two existing excel spreadsheets into one?
Two teams use the same format spreadsheets with individual data split into calendar months and I want to make them one without manually entering the data.
Alt + D + D + N
Write a query and viola, Two sheets into one.
Changing the properties of the file to read-only . (While the file is closed, right click on the file and check the read-only box.)
This allows my boss(es) to access the file -- even change it -- without being able to save their changes. If a boss likes his 'new' version, he can save it with a different file name.
But now -- how to prevent the boss from deleting the file altogether? Or deleting the whole network?
Hey man.
Think you can go as easy as to make a shortcut that links to your read only document. Then the boss wont know of the root document. He can figure it out but lets face it. He is a boss and 70% if them wont know squat
Instead of "Hiding" rows & columns, I find "Grouping" works best as its very easy to quickly see if a worksheet has hidden rows/columns. Sometimes hiding a random row/column is not easily noticed and can create issues.
I have one xl sheet with different dates in many columns and one raw's. I want to send this data to another xl sheets for each date. if somebody can help me will be great.
Dear Samantha,
Check out the website of Ron de Bruin. He has a great set of macro's and free add-in that can help you with this issue.
http://rondebruin.nl/win/s3/win006.htm
Tom
Hello, I have just found out that I made a mistake in my spreadsheet: I had a column of negative numbers, but one of them was positive (while it should have been negative). Is there a formula/system to avoid this?
Thanks.
Mariateresa
Yes, data validation. Values you denote would be between -1 and -999,999,999.
Hi,
Hiding any worksheet can be unhidden and messed around easily. I change the visibility in visual basic from -xlSheetVisible to -xlSheetVeryHidden. By this, even if you right click on sheets, you will be unable to find the hidden sheets.
Cool? I think so...
Very informative, Thanks
Is there a way to lock cells in an already protected worksheet.
(Thus the entire worksheet is protected, then the entire office can open it as read only but only a few users have the password to edit the file)
I would like an additional password or prompt box so these few users don't accidentally change formulas.
Itss such as you learn my thoughts! You appear too understand
a lot abnout this, like you wrote thee e-book in it
or something. I fel that you just could do with some percent to presseure the message house a little bit,
but insatead off that, this iis wondeerful blog.
An excellent read. I'll definitely be back.
It is in reality a nice and helpful piece of info.
I am happy that you just shared this useful info with
us. Please keep us up to date like this. Thank
you for sharing.
I laughed out loud reading the 2nd solution about moving to marketing department and making ppts.
I've been using "technical" sheets for a long time already and depending on the audience it is hidden or not. I'm currently in my NO VBA mindset, so the very hidden option is no longer. Using sheets names like: TechnicalCodes; ExplicitVariables;SetUp; HeavyCalc seem to work to my experience as they send along a message "Don' t you mess-up here, you fool!". A "Read This" section or sheet however does not work!
Reading stuff on this site has helped me develop a good habit of using colors and themes to assist the end user in being well-behaved. In my book the best advise here, because it is about the user experience and not only about protection your own work.
For dashboards I get rid of tabs and scroll bars. Besides 2 exceptions, I need to come across a manager who can turn them on again without my help.
Seems that I forgot about protecting cells, sheets and workbooks altogether. Damn!
Thanks for the informative article Chandoo, I've been struggling with Excel lately. It's a powerful tool, but hard to learn for me.
Thanks Chandoo for sharing these excel sheet tips it helps me a lot to understand excel more.
Nice roundup, Chandoo! Here's one more I thought would be relevant:
For Excel 2013+, you can hide the ribbon, as shown in this animated gif: https://gridmaster.io/tips/hide-ribbon-excel-space
This will simplify the interface, making it less likely for people to accidentally make changes. 🙂
THANK YOU SIR
I'm better at Power BI thanks to you!