MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Charting with 4 variables.

Posted by Deirdre Marie on December 04, 2001 8:50 AM


I have compiled some data and wish to present it in chart form. I have been able to create the chart but it's very busy and I hope there is an easier way. The information I am charting:

% Cancellations
% Deferrals

From July to June
FY 2000-2001 vs FY 2001-2002

Basically, I want to have a graphic representation showing the changes in % across the Fiscal Year (FY)July-June and COMPARE last FY to this FY.

I have been able to create this by changing Data Series colours and markers and rearranging the placement of series so like-ones are next to each other. For instance, % Cancellations are coloured in Navy Blue with last FY using a solid triangle and this FY using an open triangle. Fortunately, the 3 categories of series (Cancellation, Deferrals, QNS) are very different and do not jumble with each other.

Is there an easier way to accomplish this?

Thanks for your time.

Posted by Mark W. on December 04, 2001 9:12 AM

Of course this is largely a matter of personal
taste, but...

I'd create a combination Line-Column chart. I'd
let the lines represent the 3 series ("% Cancellations",
"% Deferrals", "% QNS") for the current year. And
use the columns to represent the difference between
the current and prior fiscal year. I'd settle
on a give color for each statistic, and use that
color for both the line and column representations
of that statistic.

This allows management to see the current year
trends and quickly see the differences from last