data labels

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Train Strikes - 7th, 12th Nov 2024
Hide
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data Labels

Data Labels

resolvedResolved · Medium Priority · Version 2010

Michail has attended:
Excel Intermediate course
Excel Advanced course

Data Labels


Hi,

I'm trying to add data labels only for the last observation of each of the series on a line chart. I want to show the value, as well as the percentage change from the last obervation.

Any ideas?

Thanks

RE: Data Labels

Hi Michail

Thanks for getting in touch. On you rchart, click the line once. On the last data point, click again. Then right-click and choose Add Data Label. Right-click the data label that appears and choose Format Data Label. This menu will have a list of all the options you can have, including Value and Percentage (if applicable).

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Data Labels


Hi Gary,

I tried the above, but the chart has too many data points and even when I zoom-in I can't select the last data point, but one of the previous ones. Any alternative way of selecting the last data point?

Regarding the percentage, I actually want to display both the numerical value and the percentage change from the last data point. Any way of having both values?

(my data is the performance of a stock in the past 12 months - I want to show today's closing price and the percentage change from yesterday's closing price)

Thanks and best regards

RE: Data Labels

Hi Michail

Thanks for your reply. Once you have one data point selected, you can use the right arrow key to move along another data point. You can then use the Format Selection button on the Format menu if right-clicking is too fiddly.

If you need percentages to be shown then you will have build in the source data to do it (i.e. the calculation will need to be in a cell somewhere). This link shows the process:

http://chandoo.org/wp/2010/05/05/change-data-labels-in-charts/

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Data Labels


Hi Gary,

The first step with the nemerical values worked fine.

On the second step with the percentage changes - I have included the information to be included on the data labels on a seperate table, but excel for some reason doesn't allow me to click on the formula bar, as on the instructions on chandoo.

Thanks
M

RE: Data Labels

Hi Michail

Try clicking on the label once, then again to start editing the text. You should now be able to edit the formula bar.



Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Data Labels


Thanks Gary, this worked fine.

I now have both today's price and the percentage change from yesterday's price (the result of a formula) linked to the chart and they both appear as the last data label.

Is there a way of formatting the percentage value to be placed under the numerical value (at the moment it's next to it) and also in a parenthesis and perhaps in red if the percentage value has a minus sign or green if it's positive?

Thanks
M

RE: Data Labels

Hi Michail

The percentages are linked to the table you made. Therefore however you format those cells will display on the chart.

To move the percentages to the next line, use ALT ENTER when editing a cell.

To modify the formatting, apply a custom format (right click and Format Cells, choose Custom and one of the formats there such as:

[Green]##%;[Red]-##%

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Data Labels


thanks Gary.

The numerical and percentage values on the table that I created, which I've linked with the chart, are the results of a simple formula and are changing on a daily basis.

When I apply the step you described earlier, the data label on the chart changes to today's values on the table, but when the latest data (i.e. next day's data inserted the following day) is inserted on the formula/table, the data label on the chart is not updated automatically.

Have also applied conditional formatting/highlighting the cells with the percentage values on the table, with red for negative and green for positive values, but this doesn't seem to be linked with the chart.

So basically, my problem is that the formulas and conditional formatting are not linked with the chart, but only the values of the formulas.

Any advise?

RE: Data Labels

Hi Michail

I'm not sure what's happening with the data label not updating. In my tests it seems to update automatically. Is the workbook set to manual calculation?

It appears that there's no way to automatically pull the formatting across. A VBA solution, which detects changes in the chart, would be able to modify this however.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Data Labels


Hi Gary

Have figured out why the data label is not updating.

When the numerical value and the percentage value are kept on the same line, they are updated, but when I move the percentage value below the numerical value by clicking on the data label and editing it (I press Enter just before the percentage value and move it on the line below)), it seems that the format of the data label changes and stops being linked to the cells.

Any ideas on how to have the two values in two lines, while allowing them to automatically update everytime I input new data?

RE: Data Labels

Hi Michail

Have you tried ALT + ENTER to force a line break?


Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Data Labels


Yes, but for some reason ALT+ENTER doesn't work on the data label.

RE: Data Labels

Hi Michail

I don't seem to be able to find a solution for this using Excel's normal tools.

A VBA code solution would probably work, detecting changes and formatting as it goes.

I'm sorry I wasn't able to find a solution at this time.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Sun 24 Mar 2013: Automatically marked as resolved.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel-training-advanced courses and other training courses.

Excel tip:

Screen Splitters in Excel

Screen splitter icons can be set from the ribbon bar, or dragged from the scroll bars. The icon just above the up arrow on the right scroll bar controls the horizontal splitter; the icon to the right of the right arrow on the bottom scroll bar controls the vertical splitter.

These icons can be double-clicked to split the screen at the top left of the cell currently in use.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.