• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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
0
JCJG
Asked:
JCJG
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
 
JCJGAuthor Commented:
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.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

1. the text box will pick up the number format from the source cell. Format the source cell and see the text box change. Anything other than the number format, e.g. bold or font size, will need to be applied to the text box.

2. It's an absolute Must-Have for anyone working with charts.

3. Remove the text in A2, then click the chart > Design > Select Data > Chart data range and select =Sheet1!$A$2:$C$9 again. Hit OK. Now the sort works fine.

See attached.

cheers, teylyn
Column-Chart.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now