Solved

Excel chart - show year over year changes as data labels

Posted on 2014-03-17
2
911 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:
teylyn 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

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

Introduction In all recent versions of PowerPoint it is possible to trigger animations. This means the animation takes place when a certain shape is clicked. This allows you to run animation “on demand” and outwith the normal sequence of mouse cl…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 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

11 Experts available now in Live!

Get 1:1 Help Now