Solved

Excel chart - show year over year changes as data labels

Posted on 2014-03-17
2
1,076 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
[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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

PowerPoint is the go-to presentation software for millions of users around the world. Many presentations use basic text features but you can really make special text jump out of your slide by applying this bubble text design process. This article ha…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

717 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