Make Your Excel Area Charts Beautiful!

Excel is one of my favorite applications. So I was a little disappointed when Excel didn’t quite behave as I had expected when I created an Excel Area Chart.

Step One

Here is some sample data that I’ve loaded up in Excel to illustrate the point.

Step One

Step Two

Select the Chart Wizard and pick any of the Area Charts. For this example I’ve picked a 100% Stacked Area Chart.

Step Two

Step Three

We can see already from the preview that the Area Chart shows a “drop off” from 2007 to 2008. That’s not quite what I had expected.

Step Three

Step Four

Note the X-Axis setting – we’ll revisit this shortly.

Step Four

Step Five

We need to change the X-Axis to a date and insert a duplicate entry for the last bits of data.

Step Six

Step Six

By applying a custom format to the range of cells, we’ve changed the data back to the original four digit year. However, Excel clearly understands the data it’s working with.

Step 7

Step Seven

We now need to go back and edit some of the Chart Options. Notice in the screenshot below the duplicate entry is shown.

Step Eight

Step Eight

By changing the X-Axis to time-scale that ugly drop-off is removed.
Step Nine

Step Nine

Now admire the finished product – sans ugly drop off 🙂

Step Ten

Advertisements

2 comments so far

  1. thief on

    I only needed step 5, that’s the one that does the trick. Thanks man!

  2. Jim on

    I know this is an old thread, but I just ran across it today. As long as the axis is time scale (or date as they call it now) you can put your extra line of zero data anywhere in the data list. I dropped it at the bottom of a table and the area graph immediately looked pretty!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: