Excel formulas can always be very handy, especially when you are stuck with data and need to get something done fast. But how well do you know the spreadsheet formulas?
Discover these 15 extremely powerful excel formulas and save a ton of time next time you open that spreadsheet.
1. Change the case of cell contents – to UPPER, lower, Proper
Boss wants a report of top 100 customers, thankfully you have the data, but the customer names are all in lower cases. Fret not, you can Proper Case cell contents with proper() formula.
Example: Use
proper("pointy haired dilbert")to get Pointy Haired Dilbert
Also try lower() and upper() as well to change excel cell value to lower and UPPER case
2. Clean up textual data with trim, remove trailing spaces
Often when you copy data from other sources, you are bound to get lots of empty spaces next to each cell value. You can clean up cell contents with trim() spreadsheet function.
Example: Use
trim(" copied data ")to get copied data
3. Extract characters from left, right or center of a given text
Need the first 5 letters of that SSN or area code from that phone number? You can command excel to do that with left() function.
Example: Use
left("Hi Beautiful!",2)to get Hi
Also try right(text, no. of chars) and mid(text, start, no. of chars) to get rightmost or middle characters. You can use right(filename,3) to get the extension of a file name 😉
4. Find second, third, fourth element in a list without sorting
We all know that you can use min(), max() to find the smallest and largest numbers in a list. But what if you needed the second smallest number or 3rd largest number in the list? You are right, there is a spreadsheet function to exactly that.
Example: Use
SMALL({10,9,12,14,26,13,4,6,8},3)to get 8
Also try large(list, n) to get the nth largest number in a list.
5. Find out current date, time with a snap
You have a list of customer orders and you want to findout which ones are due for shipping after today. The funny thing is you do this everyday. So instead of entering the date every single day you can use today()
Example: Use
today()to get 08/13/2008 or whatever is today’s date
Also try now() to get current time in date time format. Remember, you can always format these date and times to see them the way you like (for eg. Aug-13, August 13, 2008 instead of 08/13/2008)
6. Convert those lengthy nested if functions to one simple formula with Choose()
Planning to create a gradebook or something using excel, you are bound to write some if() functions, but do you know that you can use choose() when you have more than 2 outcomes for a given condition? As you all know, if(condition, fetch this, or this) returns “fetch this” if the condition is TRUE or “or this” if the condition is FALSE. Learn more about spreadsheet if functions like countif, sumif etc.
Where as choose(m, value1, value2, value3, value4 ...) can return any of the value1,2.., based on the parameter m.
Example: Use
CHOOSE(3,"when","in","doubt","just","choose")to get doubt
Remember, you can always write another formula for each of the n parameters of choose() so that based on input condition (in this case 3), another formula is evaluated.
7. Repetitively print a character in a cell n number of times
You have the ZIP codes of all your customers in a list and planning to upload it to an address label generation tool. The sad part is for some reason, excel thinks zip codes are numbers, so it removed all the trailing zeros on the leftside of the zip code, thus making the 01001 as 1001. Worry not, you can use rept() the extra needed zeros. You can also custom format cell contents to display zip codes, phone numbers, ssn etc.
Example: Use
zipcode & REPT("0",5-LEN(zipcode))to convert zipcode 1001 to 01001
You can use REPT("|",n) to generate micro bar charts in your sheet. Learn more about incell charting.
8. Find out the data type of cell contents
This can be handy when you are working off the data that someone else has created. For example you may want to capitalize if the contents are text, make it 5 characters if its a number and leave it as it is otherwise for certain cell value. Type() does just that, it tells what type of data a cell is containing.
Example: Use
TYPE("Chandoo")to get 2
See the various type return values in the diagram shown right.
9. Round a number to nearest even, odd number
When you are working with data that has fractions / decimals, often you may need to find the nearest integer, even or odd number to the given decimal number. Thankfully excel has the right function for this.
Example: Use
ODD(63.4)to get 65
Also try even() to nearest even number and int() to round given fraction to integer just below it.
Example: Use
EVEN(62.4)to get 64
UseINT(62.99)to get 62
If you need to round off a given fraction to nearest integer you can use round(62.65,0) to get 63.
10. Generate random number between any 2 given numbers
When you need a random number between any two numbers, try randbetween(), it is very useful in cases where you may need random numbers to simulate some behavior in your spreadsheets.
Example: Use
RANDBETWEEN(10,100)may return 47 if you keep trying 😉
11. Convert pounds to KGs, meters to yards and tsps to table spoons
You need not ask Google if you need to convert 156 lbs to kilograms or find out how much 12 tea spoons of olive oil actually means. The hidden convert() function is really versatile and can convert many things to so many other things, except one currency to another, of course.
Example: Use
CONVERT(150,"lbm","kg")to convert 150 lbs to 68.03 kgs.
UseCONVERT(12,"tsp","oz")to findout that 12 tsps is actually 2 ounces.
12. Instantly calculate loan installments using spreadsheet formula
You have your eyes on that beautiful car or beach property, but before visiting the seller / banker to findout of the monthly payment details, you would like to see how much your monthly / biweekly loan payments would be. Thankfully excel has the right formula to divide an amount to equal payment installments over given time period, the pmt() function.
If your loan amount is $125,000,
APR (interest rate per year) is 6%,
loan tenure is 5 years and
payments are made every month, then,Use
PMT(6%/12,5*12,-125000)which tells us that monthly payment is $ 2,416 if you keep trying 😉
Also, if you want to find out how much of each payment is going for principle and how much for the interest component, try using ppmt() and ipmt() functions. As you can guess, even though EMIs or loan installments remain constant, the amount contributed to principle and interest vary each month.
13. What is this week’s number in the current year ?
Often you may need to find out if the current week is 25th week of this year. This is not so difficult to find as it may seem. Again, excel has the right function to do just that.
Example: Use
WEEKNUM(TODAY())will get 33
14. Find out what is the date after 30 working days from today ?
Finding out a future date after 30 days from today is easy, just change the month. But what if you need to know the date thirty working days from now. Don’t use your fingers to do that counting, save them for typing a comment here and use the workday() excel funtion instead. 🙂
Example: Use
WORKDAY(TODAY(),30)tells that Sep 24, 2008 is 30 working days away from today.
If you want to find out number of working days between 2 dates you can use networkdays() function, find out this and a 14 other fun things you can do with excel.
15. With so many functions, how to handle errors
Once you get to the powerful domain of excel functions to simplify your work, you are bound to have incorrect data, missing cells etc. that can make your formulas go kaput. If only there is a way to find out when a formula throws up error, you can handle it. Well, you know what, there is a way to find out if a cell has an error or a proper value. iserror() MS Excel function tells you when a cell has error.
Example: Use
ISERROR(43/0)returns TRUE since 43 divided by zero throws divide by zero error.
Also try ISNA() to findout if a cell has NA error (Not applicable).
Give these functions a try, simplify your work and enjoy 🙂
31 Responses to “Beautiful Budget vs. Actual chart to make your boss love you”
Would be considerably easier just to have a table with the variance shown.
On Step 3, how do you "Add budget and actual values to the chart again"?
There are a few ways to do it.
Easy:
1) Copy just the numbers from both columns (Select, CTRL+C)
2) Select the chart and hit CTRL+V to paste. This adds them to chart.
Traditional:
1) Right click on chart and go to "select data..."
2) From the dialog, click on "Add" button and add one series at a time.
One more way to accomplish it is just select the columns into chart. Press Ctrl+C and then press Ctrl+V
Regards
Neeraj Kumar Agarwal
Unfortunately, this doesn't seem to work for me in Excel 2010. The "Var 1" and "Var 2" columns cannot combine two fonts to display the symbol and the figure side-by-side.
Secondly, there is no option to Click on “Value from cells” option when formatting the label options. The only options provided are Series Name, Category Name or Value.
@TheQ47... the emoji font also has normal English letters, so if you use that font, then you should be ok. I am assuming your computer doesn't have that font or hasn't been upgraded for emoji support.
Reg. Excel 2010, you can manually link each label to a cell value. Just select one label at a time (click on labels, wait a second, click on an individual label) and press = and link it to the label var 1 or var 2.
I am using excel 2010, please explain how to apply Step 12
Regards
Neeraj Kumar Agarwal
Hi Neeraj,
"Value from cells" option is only available in Excel 2013 or above. In older versions, you have to manually adjust the label value by linking each label seperately.
Read this please: https://chandoo.org/wp/change-data-labels-in-charts/
Sir, you are just awesome.
Your creativity has no limit.
Regards
Neeraj Kumar Agarwal
Hi Chandoo,
I just found your website, and really love it. It helps me a lot to be an Excel expert 😉
Currently I am facing with a problem at step 11:
Var1 Var2
D30%
A5%
B0%
B4%
B7%
C10%
C13%
D27%
I42%
Though at mapping table, I used windings, here formula uses calibra. How I can change it? I am able to change only the whole cell. In this case numbers will be Windings too.
Thanks for your help!
Hi Mariann... Welcome to Chandoo.org and thanks for your comment.
If you wanted to use symbols from wingdings and combine them with % numbers, then you need to setup two labels. One with symbol, in wingdings font and another with value in normal font. Just add the same series again to the chart, make it invisible, add labels. You may need to adjust the alignment / position of label so everything is visible.
[…] firs article explains how you can enhance your charts with symbols. You can simply insert any supported symbol into your data and charts. To some extend you can […]
You're a good person, thank you to share your knowledge with us, I will try to do in my work
Great visualization of variance. My question is that is this possible in powerbi?
How would you go about it?
HELLO, WHY CANT I FIND VALUES FOR LABELS IN EXCEL 2013
Dear chanddo sir,
What to do if we have dynamic range for Chart. How this will work. can you able to make the same thing works on dynamic range.
Sir Chandoo,
Good Day!
First, I'd like to say that I am very grateful for your work and for sharing all these things with us.
I tried to do this chart but it seems that the symbols don't work with text (abs(var%),"0%") unless we keep the Windings font style.
The problem is, it converts the text into symbol as well and you wont see the 0% anymore. I'm using Windows 7.
WOW - Segoe UI Emoji
This is the greatest discovery for me this month 🙂 Thanks for sharing.
Here's my two-cents:
https://wmfexcel.com/2019/02/17/a-compelling-chart-in-three-minutes/
Sir This is awesome chart, and very easy to made because of your way to explain is very simple , everyone can do. Thank you
one problem i am facing, I hv made this chart , but when i am inserting data table to chart it is showing two times , how can i resolve this
in this chart when i am adding new month data for example first i made this chart jan to mar but when i add data for the apr month graphs updated automatically but labels are missing for that new month
Hi Renuka,
Please make sure the formulas for labels are also calculated for extra months. Just drag down the series and set label range to appropriate address.
So I am playing with the Actual chart here - but amounts are bigger than your - you have 600 as Budget - my budget is 104,000 - is there a way to shorten that I am unaware of
thank you - I LOVE YOUR SITE
Thanks for the tips and tricks on Excel. In the Planned versus Actual chart examples, you use multiple values (ex. multiple Categories in above). How can this be done when we have only 1 set of values? For example if I have only this:
Planned Actual
SOW Budget 417480 367551
How can I create a single bar chart like the one above?
Thank you Chandoo.
This one is just perfect for my Quarterly Review presentation on Operational Budget against Actual Performance for the Hospital I'm currently working with.
Just Subscribed today (10 minutes ago)
Is there a way to make the table of data into a pivot table to be able to add a slicer for the graph due to many different categories and months?
Hi, I tried to modify you template with something appropriate for me, and I found a problem. this template was modified by me started with excel 2010, then 2016 and finally 2019. Same thing - somehow appear an error - or didn't show the emoticons for positive percentage or doubled the emoticons for some rows. I suspect to be from excel. if is need it I can sand you my xlsx for study. Please help if you can.
Hi Chandoo,
Could you please check the Var Formula in Step1. You have mentioned budget-actual and when i did this i got different values but when reversed like actual-budget i got the actual value what you have demonstrated in step1.
Please share your view.
This is a great chart (budget vs. actual). However, in trying recreate it, I cannot color in the UP Down bars individually, and they all become formatted with the same color. I'm using Office 365. Look forward to the feedback.
Thanks.
Dan
pls explain in detail step 7
While in the Excel sheet you have used following formula for Var
Var = Actual - Budget
But
in the note, you have written
Var = Budget - Actual
Good Presentation and Data information.thank you so much chandoo.