how to cause negative numbers in a chart to show in a different color

use conditional formatting.

select the cell(s) you want to format

open the cell formatter

click the "Conditional Highlighting..." button

now click the "Add a Rule..." button to add a new rule:

for each rule you should add rules that apply to numbers and setup as shown.

Then click "Done"

Add two columns to your table where you have one column that only includes positive data and another that includes negative data.

for positive data the formula would be something like (assuming original data is in column B)

=if(B>=0, B, "")

for negative data the formula would be:

=if(B<0, B, "")

then plot the columns two columns. set the negative series to be red.

I reordered the columns so that original data is last (to make crating the chart easier)

you can use the method I proposed using a separate table that takes input from your original data:

here

A2=Original Data::A2

B2=IF(Original Data::B2≥0,Original Data::B2, "")

C2=IF(Original Data::B2<0, Original Data::B2, "")

fill down as needed

you can move the "Chart Data" Table out of the way too

If you are doing only one series of data, use formulas to split it into columns of positive and negative values. Plot those two columns as a 2D stacked chart. The spacing will be equal between all bars. Positive can be one color and negative another. This only works if charting one series of data.

C = IF(B>0,B,0)

D = IF(B<0,B,0)

Plot those two columns as stacked bars. Choose the chart option to show hidden data Hide those two columns.

Hi Roger,

I wanted this type of plot for one series and a line graph for another series.

Numbers version 3 or 4.

Have a look at Template Chooser > Charting Basics > the 2-Axis Chart Tab.

Regards,

Ian.

Ian,

Thanks for the suggestion but I obviously wasn't clear enough about what I was trying to do. I want the mix of bar and line shown here, but I want the bars to be one colour for positive values and another for negative.

Using the "separate rows for positive and negative" can get this:

which is OK but the red and black columns are differently aligned (black x.01 to x.49, red x.51 to x.99)

What I want is something like:

which I've achieved by overlaying two charts

Jeff,

Curious about your use case. In a table a red font make sense as a visual cue for negative numbers. But in a chart you already have a prominent visual cue: the downward direction of the columns. Isn't color best reserved for showing other things in charts -- different series, etc.?

No doubt Excel charts can be "tortured" to do what you want. I find them amazingly customizable. But Numbers is pretty good on visual esthetics in charts, and Wayne has shown you an easy way to do what you want.

SG

Asking 'why would I want that' is a comment best left unsaid, i asked how to do it, not should I do it.

Ok, but it was a question, not a comment. Sorry you aren't satisfied with efforts of various people to help you here, and apologize for asking about your use case. Let that remain a mystery. 🙂

SG

Hi Jeff,

I have seen many cases when the key question is, "Why are you trying to do that?" Often times when that question is answered a solution can be offered that addresses the underlying problem. If you don't want to answer- no problem.

quinn

How do you do this in Excel? I am looking for the option but not finding it. I am not a power user of Excel, though.

I don't know why you are adverse to creating new columns. The typical way to do it for a single series in Numbers has been suggested already, use a formula to create a column of the positive values, another formula to create a column of the negative values, and plot both columns. I prefer a stacked bar chart because it preserves the correct bar spacing on the chart.

But you are plotting two columns of data already. For that, there is no good solution. You would have to create two stacked bar charts and overlay them to look like one. We showed that recently in another thread.

The best I get when I do that is white for negative values. Instructions online talk about a "right hand paint bucket" for choosing the other color but I don's see one.

EDIT: I found other instructions that use a 5% fill pattern with a foreground and background color to do it. That works.

It is too bad Numbers won't do what you need, at least not without jumping through hoops to do it. Excel is probably the better tool for your needs. There is no argument that is has more features and flexibility than Numbers.