Solved

Excel chart - show year over year changes as data labels

Posted on 2014-03-17
2
966 Views
Last Modified: 2014-03-19
I am trying to help someone that is having to do far too much manual editing of charts. Hundreds of Excel charts are being pasted into PowerPoint. These clustered column charts are showing 3 years of pricing for multiple products in each chart. After the chart is pasted into PowerPoint text boxes are created for each set of columns to show the year over year percentage change in prices.  I have just never done enough work with charts to know how to do this in Excel. However, I have used EE enough to know that you experts can do this in your sleep.

I'm just the Access data guy for this project and am trying to help a co-worker. Any help from you folks would be greatly appreciated.

Thanks, DaleChart-in-Microsoft-PowerPoint.xlsx
0
Comment
Question by:dlogan7
2 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 39935863
Hello,

first, download the XY Chart Labeler add-in, which is free and an indispensable tool for charting.

Next, add four columns of data to the source table. One for the low small Labels, one for the high small labels, one for large Labels and one for the large label text. For fine-tuning of the positioning, note the two tables with the settings for high and low values. See attached file.

Add the low small label data column to the chart, format it to be on the secondary axis and change its chart type to XY Scatter chart.

Add the high small label and the large label data to the chart. It should be on the secondary axis automatically.

Now use the XY Chart labeler to add labels to the three XY chart series. For the small labels use "Center" and for the large label series use "Right" as the label position. Use the Tool to move the large labels to the left, soo they align with the first small label.

When you set a gray fill for the label, you can leave the data markers for the data markers as they are. If you don't want to use a fill, format the data markers and set them to no fill and no border.

This sounds like a lot of work and it is. But now you have a highly dynamic chart. The idea is to work with that one chart and paste different data into the chart source table. Then tweak the vertical positions of the labels by changing the values in column O and mark additional large labels in column H.

The labels are generated and positioned dynamically. Then the chart can be copied and pasted into PowerPoint and the spreadsheet table can be populated with the data for the next chart.

Let me know if that works for you

cheers, teylyn
Chart-in-Microsoft-PowerPoint.xlsx
0
 

Author Closing Comment

by:dlogan7
ID: 39939472
teylyn,

Wow. That is awesome. As I've always said, the experts here make me look way smarter than I am. Thank you so much.

Dale
0

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Move data from one sheet to another 11 32
Countifs formula not counting data points 5 22
Excel Conditional Median Problem 1 15
vba autofilter in row 4 6 9
 Regular Expressions Microsoft Word has sophisticated search tools that can search for patterns. For example if you wanted to search for all UK phone numbers that followed a pattern of five digits, a space and then six digits you can easily do th…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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