How to Make a Chart Sheet in Excel

This technique lets you build an Excel chart that shares supportive information in series form.

Most of us know how to create a chart in Excel, but it's the chart with a little something extra that often does the best job. For instance, suppose you're charting product favorites by age group and you want to highlight a specific group's preference for each product. You can do so by charting a formula that expresses that preference. The percentage adds to the story: The percentage of those who prefer a specific project and are adults (the formula in B5 is =B4/(B2+B3+B4, copied to C5 and D5).

The trick to displaying the adult-preference percentage is to treat the value as a series. But first, create the chart as you normally would (in Excel 2003):

1. Select the data labels and data. In this case, you'd select A1:D5.
2. Then, click the Chart Wizard on the Standard toolbar.
3. Choose Bar as the Chart Type, click the Stacked Bar subtype, and click Next.
4. For this chart, choose Rows as the series source and click Finish.

The resulting chart needs a bit of work. Let's adjust some of the formats to make the chart a bit more readable:

1. Right-click the X axis (the flavors), choose Format Axis from the resulting context menu, and then click the Font tab. Choose 8 in the Size control and click OK.
2. Right-click any of the values on the Y axis, choose Format Axis, and click the Font tab. Choose 8 in the Size control and click OK. (Depending on your settings, you might need to choose a smaller or larger font size.)
3. Right-click the legend, choose Format legend, click the Font tab, and enter 8 as the Font size. Doing so should display the % Adults series, if you can't see it in the legend.

The next step is to expose the % Adults series, as follows:

1. Right-click the chart's background and choose Chart Options.
2. Click the Data Labels tab.
3. Check Value in the Label Contains section and click OK. Doing so displays all values for all four series, but you want to display only the values for the % Adults series.
4. You must hide the values for each series individually. Right-click the Children series and choose Format Data Series. (If you get a different context menu, try again. If you can't get the right menu, move a data label from that series to expose the actual series and try again.)
5. Click the Data Labels tab.
6. Uncheck the Values option in the Label Contains section and click OK.
7. Repeats steps 4 through 6 for the Teens and Adults series (but not the % Adults series).
8. At this point, you're nearly done, but the remaining data labels -- the % Adults percentage -- probably need to be repositioned. Right-click one of the remaining data labels and choose Format Data Labels.
9. Click the Alignment tab.
10. Choose Inside Base in the Label Position section.
11. If you want, click the Font tab and reduce the size of the data label.
12. Click OK.

The addition of the % Adults value tells an important story, at just a glance. Visually (see the complete chart above) you can see that the adults make up the larger portion of chocolate lovers, but you can quickly discern that 51% of chocolate lovers are adults, 26% of strawberry lovers are adults, and 14% of vanilla lovers are adults. The legend continues to display the % Adults series, which is a bit awkward, but not a truly horrible offense. If the color scheme is right, you can display the data labels in a matching color, making the relationship between the legend item and the values obvious. Or display the legend key in the series. Another solution is to just delete the legend. This technique is a combination of charting the right expression to support the chart's purpose and exploiting a few chart properties to display that supportive information in a meaningful way.

How to Make a Chart Sheet in Excel

Source: https://www.techrepublic.com/blog/microsoft-office/displaying-percentages-as-a-series-in-an-excel-chart/

Share this post