MrExcel's Learn Excel #885 - Ugly Time-Series Charts

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 5, 2009.
Chuck sends in a great question about charts that use a time-series along the x-axis.

Chuck has weekly data. The default chart in Excel 2003 is ugly. Excel makes the columns ultra-thin in the chart. Chuck has one workaround, but his manager doesn't approve.

In Episode 885, a workaround, then the real solution, and a bonus tip about widening the columns in a chart.
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Great question today from Chuck.
Chuck sent in this chart.
He's trying to create a chart of weekly data, and Excel is just, you know, making decisions for him that are causing problems.
Basically, because his data shows up on every Friday, down here in the category axis, we [ Inaudible 00:00:33 ], which, of course, drives Chuck's manager crazy.
So Chuck had a great idea, he said, "Alright, if i replace those dates with just the numbers 1 through 12, then the chart starts that look good." Of course, the manager, you know what the manager said; the manager said, "Chuck, what are all these numbers doing down here?" So let's talk about a couple of other ways to solve this.
My first approach was basically similar to exactly what Chuck did, I said, "Chuck, let's take these dates over here and copy them over to Column C, and then in Column A I'm going to convert the dates back to text using a really cool function called =TEXT." So I want the text of C1, and then in quotes, however I want to format it.
So maybe I just want m/d, copy that down.
(=TEXT(C1,"m/d").
And you see that as soon as those dates change, the text-- the chart-- starts to look right, instead of looking like it has a whole bunch of space for the extra days.
And if you want to spell this out, you can use any Custom Number format here you want.
You can even do "mmm d", which well elect to October 3rd.
Yeah, so whatever looks good, whatever the manager wants, is a great way to go.
Now, you know, that's a kludge; that's a workaround.
Let's talk about the real way to do this.
If we would just come back to the original chart, there's a setting in here which is very irritating to me because it's not where I think it should be.
I usually would right-click here and say Format Axis, and I go look for that setting.
But it's not there-- the setting is actually under Chart, Chart Options, and then go to the Axis category, where they say the difference between category and time scale is up to Excel.
It's automatic.
But we don't want to do that; we don't want this to be a time scale.
And if we come here and choose Category, then all of a sudden our chart starts to look right.
Now, just while I'm here talking about column width, a cool little trick not related to Chuck's question at all, but I'm here: So let's right-click, Format, Data Series, and go back to the Options tab.
There's a setting called Gap Width, and if you minimize that Gap Width-- make it smaller, like, 34-- you can see the chart, actually the columns, grow and the gap shrinks.
And the place where this comes up is, scientists and engineers love to make histograms where the columns actually touch.
And so this is where you would do that; change the Gap Width back to 0 and you end up with a chart where everything touches.
So great question from Chuck, loved his work around and I came up with a similar work around that actually shows days, which might convince the manager.
But, really, the better way to go is just to find that obscure setting under Chart, Chart Options, Axis, and turn off the Automatic and say that it's not Time Series; it's actually Category; and you're good to go.
Alright, well, I want to thank you for stopping by, we'll see you next time for another netcast from Mrxcel.
 

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top