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.
- Rider on a hill – Dynamic target tracking chart
- 6 charts to compare % progress
- Thermometer chart with last year markers
- Gauge chart in Excel
- 14 charts for budget vs. actual
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.
27 Responses to “Sum of Values Between 2 Dates [Excel Formulas]”
I would apply a filter and use function subtotal, with option 9. This way you can see multiple views based on the filter.
hey Chandoo, the solutions you proposed are very efficient, but if I wanted to be fancy I would do it this way .. the references are as your example workbook.
=SUM(INDIRECT("C"&(MATCH(F5,B5:B95)+4)):INDIRECT("C"&(MATCH(F6,B5:B95)+4)))
I like things simple:
=SUMIF(B5:B95,">="&F5,C5:C95)-SUMIF(B5:B95,">"&F6,C5:C95)
use something like: =SUM(OFFSET(B1,0,0,DATEDIF(A1,D1,"d")))
and have D1 be the date that I want to sum to.
In Excel 2003 (and earlier) I'd use an array formula to calculate either with nested if statements (as shown here) or with AND.
{=SUM(IF(B5:B95>F5,IF(B5:B95<F6,C5:C95,0),0))}
Note that I truly made this for BETWEEN the dates, not including the dates
I turned the data set into a table named Dailies.
I named the two limits StartDate and EndDate.
And used an array formula:
{=SUM((Dailies[Date]>=StartDate)*(Dailies[Date]<=EndDate)*Dailies[Sales])}
If I would still be using the old Excel I would do it as follows:
SUMIF($B$5:$B$95,"<="&H6,$C$5:$C$95)-SUMIF($B$5:$B$95,"<"&H5,$C$5:$C$95)
Works as simple as it is.
Regards
=sum(index(c:c,match(startdate,c:c,1)+1):index(c:c,match(enddate,c:c,1))
=sum(index(c:c,match(startdate,b:b,1)+1):index(c:c,match(enddate,b:b,1))
Great examples and thanks to Chandoo. You have simplified my work.
Hi! great tips I have found in your page, have you seen this
http://runakay.blogspot.com/2011/10/searching-in-multiple-excel-tabs.html
[...] I'm not sure I understand your question fully, but have a look at this: Sum of Values Between 2 Dates [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online [...]
Thank you! Thank you! Thank you!
=SUMIF(A2:A11;">="&B13;B2:B11)-SUMIF(A2:A11;"<"&A11;B2:B11)
awesome... thank yoo Chandoo!
which is most efficient and fast, if all are efficient ?
Thank you for this formula, I've just spent ages trying to find something to work on my data, I knew it would be possible! Don't care if others think there are easier/other ways to do it, you explained it so I understood it and could apply it to what I was doing so I'm happy!
The above said example is awesome for calculating values between dates,
can you pls let know how to calculate sale values if we have 10 sales boys for
ex: 1,rama
2,krishna
3,ashwin
4,naga
5,suresh
how much rama sale value between 1/jan/2015 to 10/jun/15
how much krishna sale value between 10/jan/2015 to 15/july/2015
i think you understood can you pls let me know the formula for how to calculate the sale between diffrent sale man sale value from master data file
Thanks,
Nagaraju
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,C2:C100,">="&E2,C2:C100,"<"&F2)
Col. A Col. B Col. C Col.D Col. E Col. F
Name Date Sales
ABC 28-Jun-11 1 MNO 28-Jun-11 25-Sep-11
XYZ 29-Jun-11 0.5
MNO 30-Jun-11 1
PQR 1-Jul-11 1
Typo ERROR / Correction in formula:
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,B2:B100,">="&E2,B2:B100,"<"&F2)
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Viv
@Viv
Can you please post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a file so that a specific answer can be delivered.
Thanks for this - it solved the problem that I was having. However can someone please explain to me why the "" needs to be around >= and <= as well as why we need to add & in order for the formula to work? Thanks in advance!
This formula works perfectly as well. Any ideas?: =SUM(INDEX(C5:C95,MATCH(H5,B5:B95,1)):INDEX(C5:C95,MATCH(H6,B5:B95,1)))
ikkeman had posted the same thing.
I am trying to sum total a range of cells between date ranges ie column n has $ amounts column d has the transaction dates ie 1/3/2015 or 25/3/2015 or 25/4/2015 column b has the text saying drp or distribution - reinv
In another cell I am trying to sum or total (in column n) with the value of a range of different dates (column d) that contain different text (column b) ie cell n48 is 50, n65 is 85, n165 is 36
with the dates ie cell d48 is 1/3/2015, d65 is 25/3/2015 and d165 is 25/4/2015
with different text that says drp or distribution - reinv ie cell b48 is drp, b65 is distribution - reinv, b165 is drp
If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp then the total would be 50. Also if I wanted to sum the amounts between 1/4/2015 to 30/4/2015 with drp the sum total would be 36 If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp and distribution - reinv the sum would be 115
What would the formula be for these different questions
hope you can help, it has been driving me nuts and cant work it out