The improvement that makes this technique possible was introduced in Excel 2013. Cells B2:F4 show the monthly revenues for each region. Cells B7:F9 calculate the percentage of the month for each of the regions. The formula in B7 is =B2/SUM(B$2:B$4). Notice that the formula uses dollar signs before the 2 and 4 in the denominator, but not before the B. That way, the formula can be copied to columns C through F and the range will adjust for each month.
CREATE THE CHART
Select A1:F4 and press Alt+F1 to create the default chart. Unless you have changed the chart default, you’ll get a clustered column chart with the legend at the bottom, no data labels, and a chart title with the words “Chart Title.”
Delete the title. Click on the legend. Press Ctrl+1 to open the Format panel at the right side of the screen. This panel offers a confusing hierarchy of choices. There’s a line with the words “Legend Options” and “Text Options.” With Legend Options selected, the next row offers three icons: a paint bucket, a pentagon, and a column chart. In most cases, the most-used choices can be found by choosing the chart icon at the end of this row.
Move the legend to the top by choosing Top as the Legend Position.
Once the Format panel is displayed, you can change its contents by clicking on another element in the chart. For example, click on any column in the chart, and the panel will change to offer choices for “Format Data Series.” Drag the Gap Width slider to the left to make the gaps narrower, which makes the columns wider.
Click on the numbers along the left axis of the chart, and the panel changes to “Format Axis.” Near the bottom of the panel, open the Display Units drop-down menu and choose Thousands. While this changes the numbers along the vertical axis, it will also change the data labels to appear in thousands as well.
DATA LABELS
When you select the chart, three icons will appear to the right of it. Click the Plus icon and choose Data Labels. This will add a General-format label above each column. With the Plus icon’s panel still open, hover over “Data Labels.” A triangle will appear at the right end. Click on the triangle, then More Options.
The next step seems a little buggy. For some reason, when you choose More Options, Excel changes the focus to the data labels for the first region. The Format panel says “Format Data Labels,” but you’re only changing the data labels for the first column in each month.
In the Format Data Labels panel, choose the Column Chart icon. Near the bottom is a Number menu. Click on Number to reveal choices. Change the category from General to Custom. In the Format Code field, enter $0K and click Add. Once you’ve added this type for the East region, you will be able to choose $0K from the Type drop-down menu when you repeat this step for the other two regions.
Near the top of the Format Data Labels panel are choices of what to show in the label. In this panel, Value is already selected. Leave that selected and also choose Value From Cells. A Data Label Range dialog box will appear. Choose B7:F7 as the range and click OK.
At this point, the January column in the East region will say 43%, $10K. This is too wide to fit above the column. Open the Separator drop-down label and choose “(New Line).”
Repeat this process for each of the other regions: Click on one of the data labels for the region. Change the number format to $0K. Select Values from Cells and choose the respective row (B8:F8 for Central and B9:F9 for West). Change the Separator to (New Line).
WHAT ABOUT A TEMPLATE?
The steps to show both the percentage and value are fairly complex. Can this process be automated using a template? At the Excel seminar in Dallas, Texas, I attempted to save the final chart as a template and use that template to create a new chart. This partially works, but the data labels appear as “Add Text,” and you’ll have to specify a range for the percentages. After specifying the range, click the Reset Text button and the labels will display correctly.
SF SAYS
You could also use the Values From Cells to identify the best or worst columns in a chart.
June 2019
