We have received a chart for chart busters that required some fixing. I thought, this will be a fun exercise for you. So here it goes,
…column chart that shows daily, weekly or monthly data depending on the user’s choice. In daily the columns are displayed properly, but in weekly & monthly mode the columns are a fraction of the width they should be – why, and how can this be avoided? Bonus points if you can describe how to use an INDIRECT formula on the x-axis labels which is another problem I ran into whilst creating this mockup!
You can download the workbook from here.
Here is how it is looking:
Thanks Gordon for asking this question.
Featured Answers:
There were several people who answered this correctly. I am featuring two answers for this problem.
By Jeff Weir:
One way to fix this is to select the ‘axis options/axis type/text axis’ option in the axis dialogue box (it’s current setting is “Automatically select based on data”.
Then it would be good if you set the ‘interval between tick marks as one, as well as the ‘interval between labels’ as 1 also.
Unfortunately then you run into the problem that your dates are now too wide for the space allowed for them on the graph. Easiest way to do that is to firstly make the graph a little wider, and secondly have an intermediate formula that formats your dates so they have a character return between the month and year, like this:
1 Jan
2009
instead of this:
1 Jan 2009
You can accomplish that with a formula along the lines of this:
=DAY(B6)&CHOOSE(MONTH(B6),” Jan”, ” Feb”, ” Mar”, ” Apr”, ” May”, ” Jun”, ” Jul”, ” Aug”, ” Sep”,” Oct”,” Nov”,” Dec”)&CHAR(10)&YEAR(B6)
Also, the y axis could do with a custom number format. No point of displaying all those zeros if say $250k or 250k (assuming not a currency) will do.
You can see it here
By Gerald Higgins
Well, here goes with the simple solution (in 2003).
Right click the chart, and select Chart Options.
On the AXES tab, there are 3 options under “Category (X) axis”.
I think the option for Time scale was originally selected.
The option for “Automatic” also does not work.
But the option for “Category” does work.
All the commenters with an answer will receive their discount codes by this weekend. Enjoy.
Lear more about making better charts using these chart busters examples:
- Asset Allocation Charts – Done the right way
- Calorie chart – How much you should exercise for what you eat – fixed properly
22 Responses to “Fix this chart [excel homework #1]”
Hi Chandoo
I have a solution to the weekly problem. Can we post it here or do you want to keep readers in suspense for a while ? 🙂
Thanks for putting this up as question Chandoo.
It's worth noting that the problem as it exists in the workbook (Excel 2003) is slightly different from the description and animation shown (only weeks affected in my workbook), although I bet the cause is the same. Did you use 2007 for the animation which is why it also fails on Monthly? Perhaps this is another 2007 charting inconsistency for Jon to take note of?
This is a simplified dataset from a real-world problem I came across recently so I'm eager to see what can be done!
Hi Gordon, yes, the download file is a 2003 file and the problem only exists for the weekly version.
There is a simple fix in 2003 for this. Is it OK to post the solution ?
Incidentally, I don't understand how this chart has a practical purpose, at least not with the data currently shown . . .
In the real version data is pulled from an SQL Server database. The actual data here doesn't have any impact on the formatting issue so I just left it hard-coded.
This is a great learning challenge.
Looks like the problem is that in the format axis dialog box the 'major unit' setting is not on 'auto' but is set at 'fixed: 2 days'. Which is fine when you're just looking at weekly data, but screws things up when you go to longer timeframes, as excel scales the thickness of the series based on this setting (in days), but you're actually displaying weeks or months. It's not a bug...it's just Excel doing what the user asked it to.
If you put the settings back to 'auto' and select the weekly data, the axis dialogue box shows it used a major unit of 2 days (i.e. the text labels are 2 days apart) and a minor unit of 1 day (i.e. each series is plotted). This looks okay. When you select monthly, the dialogue box shows a major unit of 2 months, and a minor unit of 1 month, which looks fine.
Unfortunately for the weekly series, Excel incorrectly guesses that you want a major unit of 14 days, and a minor unit of 1 day, and so the graph thicknesses are rescaled to suit this.
But hey...2 outta three aint bad, as the song goes.
One way to fix this is to select the 'axis options/axis type/text axis' option in the axis dialogue box (it's current setting is "Automatically select based on data".
Then it would be good if you set the 'interval between tick marks as one, as well as the 'interval between labels' as 1 also.
Unfortunately then you run into the problem that your dates are now too wide for the space allowed for them on the graph. Easiest way to do that is to firstly make the graph a little wider, and secondly have an intermediate formula that formats your dates so they have a character return between the month and year, like this:
1 Jan
2009
instead of this:
1 Jan 2009
You can accomplish that with a formula along the lines of this:
=DAY(B6)&CHOOSE(MONTH(B6)," Jan", " Feb", " Mar", " Apr", " May", " Jun", " Jul", " Aug", " Sep"," Oct"," Nov"," Dec")&CHAR(10)&YEAR(B6)
Also, the y axis could do with a custom number format. No point of displaying all those zeros if say $250k or 250k (assuming not a currency) will do.
I'll whip up a refined graph tomorrow.
p.s. my observations based on excel 2007.
Well, here goes with the simple solution (in 2003).
Right click the chart, and select Chart Options.
On the AXES tab, there are 3 options under "Category (X) axis".
I think the option for Time scale was originally selected.
The option for "Automatic" also does not work.
But the option for "Category" does work.
@All.. one small clarification... the screencast was made in Excel 2007. Also, I have seen the file only in excel 2007, but I believe the excel 2003 version must have the months and days working fine.
Also, please post your solutions in the comments.. no need to email them.. I think it will be easy that way. Once we have a meaningful number of solutions, I will pick one or two and include in the main post.
Here is one solution which will keep the width constant by using the "TEXT" function to format the label column:
formula for Cell E6:
=TEXT(INDEX(INDIRECT(AxisPivot),A6,1),CHOOSE(MATCH(AxisPivot,$B$5:$D$5,FALSE),"d mmm yy", "d mmm yy","mmm yy"))
I would love to know how to have this chart reference (say) the Last 31 days, or Last 13 weeks, or Last 6 months data based user selection from Cell E3. I guess you would need to create a Defined Name range and use OFFSET, but I would want the chart to expand/contract based on 31 data points down to 6 - similar to how I have seen share prices on FT.com
Any assistance is greatly received
Gordon: I like how you used the indirect function. It had me scratching my head until I highlighted the INDIRECT(AxisPivot) part of the formula in the formula bar and pushed F9 (which expands this out to the array being evaluated). Nice.
When you say that the use of this indirect formula was 'another problem I ran into whilst creating this mockup' are you saying that you still are having some kind of issue with it? I don't quite follow.
Jialin: looks like some of your formula didn't wrap properly...at least it looks truncated on my PC using firefox. Can you repost? This is a much simpler approach to my formula, although my formula has the small advantage of putting the year on a seperate line - which could be handy if the graph gets resized as it might just stop the labels running into each other.
Just to throw another (complicated) formula into the mix: If you put the below formula into the Labels column (Paste into E6 and copy down) then it takes the date range and reconstitutes it on the fly so that day, month, and year are on seperate lines; and it displays the day as an ordinal (e.g. 1st, 2nd, etc). Adapted from Walkenbach:
=DAY(INDEX(INDIRECT(AxisPivot),A6,1))&IF(INT(MOD(DAY(INDEX(INDIRECT(AxisPivot),A6,1)),100)/10)=1, "th", IF(MOD(DAY(INDEX(INDIRECT(AxisPivot),A6,1)),10)=1, "st",IF(MOD(DAY(INDEX(INDIRECT(AxisPivot),A6,1)),10)=2,"nd", IF(MOD(DAY(INDEX(INDIRECT(AxisPivot),A6,1)),10)=3, "rd","th"))))&CHAR(10)&CHOOSE(MONTH(INDEX(INDIRECT
(AxisPivot),A6,1)),"Jan", "Feb","Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep","Oct","Nov","Dec")&CHAR(10)&RIGHT(YEAR(INDEX
(INDIRECT(AxisPivot),A6,1)),2)
Note: Sometimes when copying a formula from a web browser and pasting it into Excel, it uses the wrong kind of Quote mark "". These might need to be changed.
Have posted an example that uses this approach, and another that uses a dynamic named range at http://cid-f380a394764ef31f.skydrive.live.com/browse.aspx/.Public?uc=1
Tony: When you say [would want the chart to expand/contract based on 31 data points down to 6 - similar to how I have seen share prices on FT.com] do you mean that you want the actual chart width to expand or contract with the data, or rather that you want the chart dimensions to remain the same size, but more or less points to be plotted as appropriate?
Perhaps you could post a link to a 'before/after' screenshot from FT.com.
Hi Jeff - paste this link into your web browser
http://markets.ft.com/tearsheets/performance.asp?s=TSCO%3ALSE
Just below the x-axis is a list box where you can change the displayed data (width of chart remains the same) from days to weeks to months to years. This is the sort of thing I would like to achieve in Excel
Right - I get what you mean now...you want to be able to select different timeframes, and have the granurarity of the data reflect your selection. So If looking at a day's data, you want to see say datapoints plotted half hourly. If looking at monthly data, you want to see say daily averages plotted, and so forth.
No problem. I'll whip up an example that takes 2 different approaches to this and post link here.
Tony -
This example might give you some ideas:
Dynamic Charting By Dates
@ Jon
This is great. Much appreciated. I can incorporate the formulae posted earlier to format the time value and go from there.
@ Jeff
Look forward to your version(s)
@ Jon - yes, I found your page last night. Was having some trouble with the graph not accepting the dynamic formula though. Might be an excel 2007 thing, might be a tired operator. So I'm having another go now.
Jeff -
The dynamic chart method with dynamic ranges as series data works in 2007, but there are a few gotchas. Most important, until SP2, any named range that had a name beginning with "chart" would not be accepted in the series formula (though it could be accepted in the source data dialog). Replace "chart" with "cht" and it should work fine. Somewhat frustrating is that sometimes the chart doesn't update when the data in the named range changes. In some client projects I've had to incorporate VBA code to update these series. I'm not sure whether this has been fixed in SP2.
Ha - it was operator error...I forgot to change the Sheet name from Data! to the name of my sheet in the formula =Data!$A$5:INDEX(Data!$A:$A,MATCH(9.99999E+307,Data!$A:$A))
Doh. Although thanks for reminding me to upgrade to SP2.
Hopefully this fixes the bug where after hiding the ribbon via CTRL F1, if you then select the Home tab and double click on the paste format icon, it doesn't work as expected...or rather it doesn't work at all. I suspect Excel treats the automatic closing of the window as a user-performed action, and so cancels the 'repeat paste format' functionality .
I have fixed the chart and uploaded it at - http://www.box.net/shared/imqze3ryuo
Here are the reasons and solution to the 2 issues mentioned:
1. Issue: The width of the bars was not consistent for Daily, Weekly and Monthly data.
Reason: This was due to the data-type used for X Axis. The X axis was of ‘date’ type and hence the chart automatically used to calculate the distance of one date point to another, in all the 3 cases, i.e. – daily, weekly and monthly. So, the bars were thinner in daily and thinnest in monthly plots.
Solution: The solution is to right click on the X axis (for Excel 2007), click on Format Axis and then change the ‘Axis Type’ to ‘Text’.
2. Issue: Use Indirect() to make X Axis label dynamic
Alternative: There is a better way t do this. The labels (and not just the labels, even text boxes) can be used to have dynamic text.
How to do it: [I have attached the worked-on mockup spreadsheet]. For this, click on the borders of the label box, and with this box selected, enter this formula into formula bar: =$E$3. This will make the label box extract its text from the cell E3, which, in our case, is the cell where you have in-cell drop down list to select daily/weekly/monthly options.
But, I have gone a step ahead and made the text complete. I picked cell F3 instead of cell E3 and had cell F3 pick the daily/weekly/monthly term from E3 and added ‘Data’ word with it. So, now cell F3 shows ‘Daily Data/ Weekly Data/ Monthly Data’ as per the case.
You can download the excel file from the above given link.
Let me know if you have any questions
Regards,
Mohit
How we create cell that we Automatically select based on data
hai sir
please give details how to create filters automatically select data in cell