Excel 2007-Column Chart

Posted on 2011-10-10
Last Modified: 2012-05-12
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.
Question by:JCJG
    LVL 50

    Expert Comment


    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:

    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

    Author Comment

    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.
    LVL 50

    Accepted Solution


    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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now