fbpx
Search
Close this search box.

Beautiful Budget vs. Actual chart to make your boss love you

Share

Facebook
Twitter
LinkedIn

Call them by any name – Budget vs. Actual, Target vs. Actual, Goal vs. Progress, KPIs, Performance charts, but they are the bread and butter of business charting. So how about a drop dead gorgeous and insightful chart for your next meeting with the folks upstairs? Something like this:

Create Budget vs Actual chart with smart labels in Excel – Tutorial

If you are in a hurry to make such a chart, download the template, plug in your values and you are good to go. For instructions on how to create them in Excel, read along.

Step 1: Getting the data

Set up your data. Let’s say you have budgets and actual values for a bunch of categories (products, months, departments etc.) in this format. Calculate variance and variance % using simple formulas as shown below.

Step 2: Create a column chart

Simply select your category, budget and actual columns and insert a column chart (clustered). You will get this.

Step 3: Add Budget and Actual data again to the chart

It feels wrong, but trust me on this one. Add budget and actual values to the chart again. We now end up with a cluster of 4 columns per category, as shown below.

Step 4: Change the newly added columns to lines

Right click on either of the newly added columns, choose “Change series chart type” and convert both of them to lines.

This step looks different in older versions of Excel, where you have to do it for each column. In Excel 2013 or above, you will go to “Combination chart” screen and you can adjust the series types for all series from there.

Step 5: Add up / down bars to these lines

Select either of the lines and use the + icon to insert up/down bars. In earlier versions of Excel, you need to use either Insert ribbon or menu to do the same.

Step 6: Format up down bars and columns

Quickly adjust the colors of each bar (don’t touch the lines yet) as you see fit.

Step 7: Adjust gap width and series overlap

This is the tricky bit. Use below instructions.

  • Select the columns first. Go to format series (Ctrl+1)
  • Adjust series overlap to 0%
  • Set gap width to 150%
  • Now select the lines
  • Adjust the gap width to 300%
  • Feel free to adjust / experiment with various gap width combinations to see which works best for your eyes.

Your result should look like this:

Step 8: Make the lines invisible

Select the lines (one at time) and yell gently reducto

If you are muggle, simply set the outline color to no line and you are gold. We get this:

Step 9: Add a title to your chart and remove unnecessary legend items

Double click on the chart title and type something meaningful. Alternatively, you can also link it to a cell value. To do that, select the title, press = and point to a cell that has the title you want to use.

To remove legend entries, click on the chart legend, now click again on the series 3, hit DEL key. Repeat the process for series 4.

 

Step 10: Add data labels to both lines

Select the lines one at a time (remember, the lines are invisible, so just click where they are supposed to be or use the format box to select them). Now use the + button to add data labels. In older versions of Excel, you need to use either ribbon or menus to add labels. At this stage, your chart should look like this:

Step 11: Calculate new labels

This is the fun part. Start by setting up rules for what symbol+value you want to show. For example, you may want to show,

  • Thumbs down if the variance is below -5%
  • fingers crossed if the variance is between -5% and 0%
  • OK symbol if variance is positive and less 10%
  • Thumbs up if it is between 10% and 25%
  • Double thumbs up if it is more than 25%

Create a range where your symbol % mapping will go and fill up the symbols using Insert > Symbol option. Select Segoe UI Emoji font to insert cool emojis.

Your mapping table should look like this:

Note the first value. It means we will display thumbs down for all values between -5% and -100%.

Now, let’s calculate the labels. There are two sets of labels. Positive and Negative. This gives you finer control on formatting them. Our raw data area now looks like this:

Formulas for labels:

  • Symbol: =VLOOKUP(var%, mapping-table, 2) We are using the approximate lookup technique to get relevant symbol.
  • Var 1: =IF(var%<0, Symbol & TEXT(ABS(var%), “0%”),””)
  • Var 2: =IF(var%>=0, Symbol & TEXT(ABS(var%), “0%”),””)

Replace the words var%, mapping-table, Symbol with actual cell references in your workbook.

Step 12: Plug our smart labels in to the chart

Now that we have gorgeous labels, let’s replace the old ones with these.

  • Select first line (budget)’s labels and press CTRL+1 to go to format options.
  • Click on “Value from cells” option and point to Var 1 column.
  • Repeat the process for second line (actual) labels too.

We get this.

Step 13: Adjust label position

We are almost there. Click on the labels and choose position as “Above”.

Our kick ass budget vs. actual chart is ready.

Download FREE Budget vs. Actual Chart Template

Click here to download the chart template. Just type in your data and see the chart. If you want to learn how to make the chart, there are instructions in the workbook too. Scroll down to see them. Have a play and use it in your work to be a hero in front of your boss.

More such charts for you:

If you liked that chart, check out these additional resources for more inspiration and wow factor.

How do you make your budget vs. actual charts?

For simple data, I use either databars or thermometer charts. For something fancy, I use technique described in this post. It never ceases to amaze my audience.

What about you? What charts do you use to make budget vs. actual charts? Please share your thoughts in comments.

Problem re-creating this chart in Excel?

If you face difficulty making budget vs actual chart in Excel, check:

  • You have Emoji font installed. Windows should have added this by default long ago. The font name is Segoe UI Emoji.
  • Labels are set to Segoe UI Emoji font. In some versions of Excel, emojis are available only on few fonts. If you see funny symbols or boxes with ? inside them, select labels and set the font to Segoe UI Emoji.
  • Any other problem… post a comment so one of our readers or I can help you.
Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

31 Responses to “Beautiful Budget vs. Actual chart to make your boss love you”

  1. Harry says:

    Would be considerably easier just to have a table with the variance shown.

  2. Jomili says:

    On Step 3, how do you "Add budget and actual values to the chart again"?

    • Chandoo says:

      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.

      • Neeraj Agarwal says:

        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

  3. TheQ47 says:

    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.

    • Chandoo says:

      @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.

  4. Neeraj Agarwal says:

    I am using excel 2010, please explain how to apply Step 12

    Regards
    Neeraj Kumar Agarwal

  5. mariann says:

    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!

    • Chandoo says:

      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.

  6. […] 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 […]

  7. Franciele says:

    You're a good person, thank you to share your knowledge with us, I will try to do in my work

  8. Ali says:

    Great visualization of variance. My question is that is this possible in powerbi?

    How would you go about it?

  9. NARUTO says:

    HELLO, WHY CANT I FIND VALUES FOR LABELS IN EXCEL 2013

  10. Amol says:

    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.

  11. Ricardo says:

    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.

  12. MF says:

    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/

  13. Renuka says:

    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

  14. renuka says:

    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

    • Chandoo says:

      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.

  15. Justine says:

    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

  16. Arvind says:

    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?

  17. JEREMIAH KOOL says:

    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)

  18. Shawn says:

    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?

  19. Mihail says:

    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.

  20. Saidatta Pati says:

    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.

  21. Dan says:

    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

  22. sathik says:

    pls explain in detail step 7

  23. Arun says:

    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

  24. aye myat maw says:

    Good Presentation and Data information.thank you so much chandoo.

Leave a Reply