As you can see in this example below, I would like to be able to put in red the negative numbers that are in the table below the graph. I would like to apply a conditional formatting on this table (that is generated from the graph). How to do that?

enter image description here

NB: I know I could do it manually, by creating the table myself below the graph and applying conditional formatting. However I cannot do it because the real data I have (not the example) are too broad and I cannot put them all under the graph without reducing the size of the cells which will impact other stuff on the sheet.

  • FWIW, by accepting an answer before the end of the bounty period, you missed out on at least one (mine, and maybe other) alternative answers. – dav Jan 13 '16 at 17:09
  • Hi dav. You certainly are right but I didn't think anyone else would reply the question. I won't do the same mistake anymore (event though the answer that is given seems perfect to me) – Pierre Jan 15 '16 at 12:45
up vote 4 down vote accepted
+50

I don't believe it's possible to apply a colour to numbers in a chart data table.

However, I may have a workaround for you that means you can use a formatted table stored on a different worksheet, so that it won't affect the existing row and column heights on your worksheet. It's a bit of a hack, but will get the job done.

Use Excel's Camera Tool

To use the Camera tool, you need to customise your toolbars.

In Excel 2010 you can add it to your Quick Access Toolbar:

enter image description here

Create a new worksheet and create your table with your conditional formatting as you want it laid out. Then select the entire table and click the camera tool button. Go to your chart, and click just below the chart where you want the table to appear.

You should get something like this. Don't worry, we're not finished :)

enter image description here

We have three problems to address here: the width isn't right, the text is a bit grainy and there's a border we don't want.

First let's deal with the grainy text. I've recently faced this problem and worked out how to deal with it. Firstly let's delete the image you've got - we need a fresh image, then go back to your table on the new tab.

Now select your formatted table but this time with around 20 blank rows below your table. Select the Camera tool again and click below your chart. Now, right-click and crop your image to only show the data you want.

Removing the border is easy - right-click the image, select Format Picture and set No line.

Now you should have something like this.

enter image description here

Finally we need to sort the width out. You can either mathematically calculate the widths you need to use, or just a bit of trial and error. Select the columns containing your table and widen the columns a bit.

Go back to your camera tool image and the text will look squashed. We need to adjust the width of the image. Right-click, Format picture then set the Scale width to be the same as Scale height (because you've changed the width). Repeat until you've got the width as you want it, something like this:

enter image description here

What makes the Camera Tool so Great?

The wonderful thing about the camera image is that it's not static - if your data changes, the camera image will also change, automatically. It's a great tool!

In nearly all places in Excel, you don't need a Conditional formatting; you can use standard Excel format codes for this. Select the whole table, and format the cells with

0;[Red]-0

This will give you the format 0 for positive numbers, and the format -0 in red for negative numbers. You could add a third format for zeros, if you care, like 0;[Red]-0;[Blue]0 to get a blue zero.

You can also replace the 0 with whatever other format you want to use in your case too, for example if you want two digits behind the decimal separator, you could do: 0.00;[Red]-0.00, etc.

Unfortunately, this specific case of a table under a graph, this is not working; it is a limitation (bug?) in Excel to not transfer the coloring

  • How are you selecting the whole table? When I try and replicate this on a chart data table I can't select the table and apply a number format. Can you elaborate please? – Andi Mohr Jan 6 '16 at 17:15
  • I tried around a bit, and the concept is that you apply that format either to the original data table (and 'link' the formats), or apply it to the axis (along which the table sits). Both ways work for any format you chose - except just the coloring you want. It seems Excel has a limitation there, sorry. – Aganju Jan 6 '16 at 17:44
  • Thanks Aganju. Your answer is not replying to the problem if I understand correctly? – Pierre Jan 7 '16 at 10:08
  • Yes, unfortunately. I'm not sure what to do, should I delete the answer? – Aganju Jan 7 '16 at 12:27
  • Maybe just editing it so to explain that this would be the case in a normal table, but not a table from a graph? – Pierre Jan 7 '16 at 15:39

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.