?
Solved

Excel chart - show year over year changes as data labels

Posted on 2014-03-17
2
Medium Priority
?
1,135 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:Dale Logan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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:Dale Logan
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

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