**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007. If you are using an earlier version (Excel 2003 or earlier), *this tip may not work for you*. For a version of this tip written specifically for earlier versions of Excel, click here: Numeric Value and Percentage Value in a Graph Column.

Harrie wants to create a column chart that displays two values for each column in the chart. One value to be displayed would be a percentage (such as 46%) and the other an absolute value (such as 359,000). One value would appear on the column in the chart, and the other just above the column.

There are many ways that this can be accomplished, depending on the nature of your data. This tip will examine a couple of the many ways you can proceed.

A relatively simple approach is to assume that your data is in three columns. The first column is the category (what will appear along the X-axis), the second is the percentage that you want to plot, and the third is the absolute value to be displayed. Follow these steps:

- Select all three columns of data.
- Display the Insert tab of the ribbon.
- Click the Column tool and then choose the clustered column chart type. Excel creates a chart based upon this selection. It displays the percentage on the Y-axis, but there are two sets of Y-coordinate data that are plotted.
- Right-click the second data series (the "exact amount" values) and choose Add Data Labels from the resulting Context menu. Excel displays the data labels above each column of the data series.
- Right-click the second data series again and choose Format Data Series from the resulting Context menu. Excel displays the Format Data Series dialog box. (See Figure 1.)
- Click Secondary Axis.
- Click Fill at the left of the dialog box.
- Click No Fill.
- Click Border Color at the left of the dialog box.
- Make sure No Line is selected.
- Click Close. You now have the absolute values displayed, yet you've hidden the graph column in which they would normally be displayed.
- Right-click the second Y-axis (the one on the right) and choose Format Axis from the resulting Context menu. Excel displays the Format Axis dialog box.
- Set the Major Tick Mark Type drop-down list to None.
- Set the Minor Tick Mark Type drop-down list to None.
- Set the Axis Labels drop-down list to None.
- Click Close. The secondary axis should now be gone from the graph.
- Right-click one of the data labels (the absolute values) and choose Format Data Labels from the resulting Context menu. Excel displays the Format Data Labels dialog box.
- Select the Center radio button.
- Click Close. The data labels should now be vertically centered within each column.
- Right-click the remaining data series (the percentage values) and choose Add Data Labels from the resulting Context menu. The percentage value should now appear above each column.

** Figure 1.** The Series Options of the Format Data Series dialog box.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (7888) applies to Microsoft Excel 2007. You can find a version of this tip for the older menu interface of Excel here: **Numeric Value and Percentage Value in a Graph Column**.

**Solve Real Business Problems** Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out *Microsoft Excel 2013 Data Analysis and Business Modeling* today!

Pie charts are a great way to graphically display some types of data. Displaying negative values is not so great in pie ...

Discover MoreWhen creating a line cart, the line can show values both positive and negative values. This tip explains how you can use ...

Discover MoreIf you chart data that includes dates along one of the axes, you might be surprised to find out that the chart includes ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2017-12-28 12:00:28

Fred Goodman

It would be helpful to see an example of the chart discussed in the tip.

2017-03-21 13:12:12

Becky OB

2015-03-18 17:30:12

Matthieu

I am dealing with two crosstabs that are identical except one has %'s and the other has numbers. I want to graph the columns based on the percentages, and add the numbers as data labels for each column.

The two datasets are based on percentage of traffic and a score given, so they wouldn't have the same bar but that's ok.

2015-03-17 00:09:50

Liz

2015-01-16 10:12:25

2014-06-19 09:05:43

Jomili

Wow! That was tough! Any idea why my two series were overlapping? I set up my three column table just the way Allen described.

2014-06-19 08:57:49

jomili

I tried that, but it doesn't help. The problem is, after following all the instructions, I'm left with only the "exact amount" values (data lables) and the Percent columns. The percent columns rise to the correct percent levels, but when I click on a percent column, the Series is "exact amount", whose value is displayed in the bar.

2014-06-13 18:13:20

Neil

2014-06-12 12:16:06

Jomili

The only visible columns in my graph are the the Bars, which have the fill color for Percent, and rise to the level of Percent, but which have the Values for Exact Amount. When I right click on one of the bars, I can format the data label, but can't Add a data label.

In Allen's "Format Data Series" pic above, the top option is for "Gap Depth". My corresponding option is "Series Overlap", with "Separated" and "Overlapped" as the left and right sides.

2014-06-12 12:05:53

Jomili

Curt,

I'd appreciate it if you could share your code when you complete it.

2014-06-12 12:04:19

Neil

What Allen means by "right-click on the remaining data series" is right-click on one of the visible columns in the graph.

2014-06-12 11:09:53

Jeff

2014-06-12 10:51:48

Chuck Trese

I think you are looking for this:

To give a series no fill, you can use

Activechart.SeriesCollection("on-time").Format.Fill.Visible=False

where you change "on-time" to the name of your series, of course.

2014-06-12 10:21:09

Jomili

2014-06-12 10:14:15

Frank

Regards

2013-03-07 17:31:18

Curt Weller

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments