We know that column charts are excellent for presenting information. But what if some of the columns are too tall and hijacking the rest. In a previous article, we discussed few of the approaches. Today let’s learn how to build a cropped chart (broken y-axis chart) using Excel, something like this:
Looks interesting? Read on.
Tutorial to create a cropped chart in Excel
Before we begin: Is this the best chart for this data?
Cropped charts or broken Y-axis charts can be misleading and confusing. That said, in some very rare cases, you may need to use them. My suggestion is simple:
- See if you can use a regular column chart
- See if you can use a regular column chart, crop the tall columns at a certain point and fade them using gradient fills. Then apply labels to them so people know which ones are too tall to show on the chart.
- See if there is any other alternative representation for this data (may be just the numbers in a table?!?)
- If your boss / client / spouse is adamant about broken y-axis chart / cropped chart, then make one.
Step 1: Arrange your data
Let’s say we have the numbers as shown aside.
We will have to set up some extra calculations to make this chart. We need to split each column in to 2 portions.
- Below crop
- Above crop
But we can do this only for columns that are too tall. Also, we need to know 2 things:
- At what point we should crop the value – let’s call this crop
- What is the size of crop – let’s call this size
We also need to print a cropped symbol (2 zigzag or slant lines) at the location of crop, if we crop a column.
First, take a look the the calculation setup.
The formulas for 3 extra columns are:
Remember, our data starts from cell B10.
- Crop: =IF(B10>crop+size,crop,B10)
- Above: =IF(B10>crop+size,B10-crop-size,0)
- Marker: =IF(B10>crop+size,crop,NA())
Step 2: Create a stacked column chart
Select both Crop & Above columns and create a regular stacked column chart. We should get something like this:
Step 3: Add marker series as a line to the chart
Add the marker series (select all the values, copy and paste in to the chart – or use Chart > Select Data > Add option).
Marker series will be added as a stacked column by default.
Right click on it and select change series chart type option.
Change the series to line with markers.
Now, set the line properties to no line so that only markers show up.
At this stage, our cropped chart looks like this:
Step 4: Replace markers with crop symbol
Draw a crop symbol. Here is one I used:
- Draw a box. Fill it with pale white color and remove borders.
- Draw 2 horizontal lines and align them to top & bottom edges respectively.
- Select all three shapes (2 lines and one box) and group them (right click and group).
- Rotate this grouped object a bit.
Copy this object / symbol.
Select the markers on the chart. Press CTRL+V.
Excel replaces the markers with your symbol. (more: use shapes to enhance your charts)
At this stage, our chart looks like this:
Step 5: Format the chart
This is easy. Set both crop & above portions to same color. Adjust gap width between columns if necessary.
Play with both crop and size values until you get the perfect chart.
Step 6: Add labels to your chart
As you have cropped the columns, the axis is no longer relevant. We either need to replace the axis labels with two sets of values (before crop & after crop) or remove the axis & set data labels.
Setting different axis labels requires a bit more tweaking of the chart.
So, let’s go with data label route.
First remove the vertical axis. To set the labels:
- Select the bottom series of the column chart. Right click and choose data labels option.(Click here for a screenshot of this step)
- This adds default labels.
- Select the labels and press CTRL+1 to format them
- From label options pane, select “Value From Cells” as the source for labels. Note: This is available only in Excel 2013 or above. For older versions use XY Labeler add-in by Rob Bevey.
- Select the original data (in B10 cell onwards) as the source.
- Set up label properties (location, font, font size, color as you see fit)
- Done!
That is all. Your cropped chart is ready.
Download cropped chart template
Click here to download cropped chart example workbook. The workbook contains all the calculations, full chart and all intermediate steps so that you can learn more.
Awesome resources on charts
Raise above the rest with these awesome resources on charting:
- 5 Simple rules to make awesome column charts – podcast
- 6 Best charts to show progress against a goal
- Column / bar chart with lower and upper boundary
- Interactive sales chart using Excel
Struggle with charting? Excel School is for you:
If you are mystified by the Excel charts and spend way more time on them, then consider enrolling in our Excel School program. This will help you learn how to create awesome charts, interactive workbooks, complex dashboards in a structured way.
Visit Excel School to know more about this program and enroll.
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