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: 1268
  • Last Modified:

Excel chart - show year over year changes as data labels

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
Dale Logan
Asked:
Dale Logan
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Dale LoganConsultantAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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