asked on

Excel 2007-Column Chart

Hi, I have created a column chart (attached) and I want to add the following:

1. Total on the top of each column
2. % mix of each data label (I know I can do it with a pie chart but I can't find the option to do it here).
3. Sort the column and data table either base on alphabetical order or ascending value.

I appreciate your help. Thanks.
Column-Chart.xlsx

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

I've removed the gradient background and the 3D effect to make the chart easier to read.

1. Create two text boxes above the columns, select each text box in turn, enter a = sign into the formula bar and click the cell in the table that holds the total value for that column.

2. Only pie charts calculate percentages automatically. For all other charts, you need to provide the calculations in the sheet. I've created two column with formulas to do that. I've then used a free Add-in called XY Chart Labeler to add the percentages as data labels in the chart. The Add-in can be downloaded here: http://www.appspro.com/Utilities/ChartLabeler.htm

3. Separate the totals row by at least one empty row from the data table. Turn on autofilter and use the drop-downs in the table headers to sort whichever way you like.

See attached.

cheers, teylyn
Column-Chart.xlsx

JCJG

ASKER

Thanks, teylyn.

1. Got this one. But how can format the text in the text box? For example, the number is $100.9M instead of 100.9. I try using the TEXT formula but it didn't work.

2. I found this add-in on the web. It works perfectly

3. I try autofilter but the chart doesn't follow exactly the order displayed in the data source.

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial