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