Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • 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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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