CODEWITHSUNDEEP
excel
joegavin92
joegavin92

Reputation: 45

Chart doesn't keep data label number formattng

I am having a problem with my data label formatting within an excel chart. As can be seen in the image below, the chart isn't retaining the number formatting I have set in the columns B and C.

I have used conditional formatting to make it so that if a number is below 0.01 it is displayed as a scientific e.g. 5.E-05 to 2 DP. And I've also got another rule that keeps all the numbers to 2DP if they are over 0.01.

I've already tried to link the formatting back to the sources of the cells but it gives me the same results e.g. Format Data Labels >> Number >> Linked to source (ticked).

The reason I do not do this manually is because its part of a python automation project, and it would also defeat the object of my conditional formatting.

I've already seen the solution for this question but the answer was to manual change it and again wouldn't allow me to use my conditional formatting.

Excel Chart doesn't keep format

See link for image below.

https://www.dropbox.com/s/45gmvtjqgd0y8e0/thing.png

Thanks in advance

Upvotes: 1

Views: 3889

Answers (1)

guitarthrower
guitarthrower

Reputation: 5834

Instead of using Conditional Formatting you can use custom number formatting which should flow through to the chart.

  1. Select cells you want to format
  2. Right Click > Format Cells
  3. On the Number tab, select Custom

  4. In the Type: field enter the following

    [<0.01]0.E+00;0.00

This will format anything less than .01 as Scientific, and anything else by a number with 2 decimal points.

Upvotes: 1

Related Questions