You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Guys, is it possible to add conditional formatting to a simple chart so that negative results below the zero line change colour? I have got conditional formatting in the data but it doesn't transpose over to the chart.
Thanks
Ian
MacBook Air 13″, macOS 15.1
Separating the data is certainly the easy option here.
You don't necessarily need to change the source data, though. If your data is in Column B, then you could add (a hidden?) column C for positive values and column D for negative values (using simple IF() statements against column B).
In this way you continue to have a single column for all your ±values and Numbers automatically filters this into positive and negative value columns, which you can add to the chart.
The only other ideas I had was a 2-axis chart with min=0 on one side, and max=0 on the other, then add the series twice, but that doesn't work since it still shows values above/below the line.
That led to the last option I had, which is two separate charts.
One chart has a Min on the Y axis set to 0, so it surpasses negative values
The other chart has a Max on the Y axis set to 0, so it surpasses positive values.
Now you have two charts referencing the same column of data (so you avoid having to split the data), and creative positioning of the charts on screen should give you what you want:
(this is two separate charts with different Min and Max values for the Y axis)
Separating the data is certainly the easy option here.
You don't necessarily need to change the source data, though. If your data is in Column B, then you could add (a hidden?) column C for positive values and column D for negative values (using simple IF() statements against column B).
In this way you continue to have a single column for all your ±values and Numbers automatically filters this into positive and negative value columns, which you can add to the chart.
The only other ideas I had was a 2-axis chart with min=0 on one side, and max=0 on the other, then add the series twice, but that doesn't work since it still shows values above/below the line.
That led to the last option I had, which is two separate charts.
One chart has a Min on the Y axis set to 0, so it surpasses negative values
The other chart has a Max on the Y axis set to 0, so it surpasses positive values.
Now you have two charts referencing the same column of data (so you avoid having to split the data), and creative positioning of the charts on screen should give you what you want:
(this is two separate charts with different Min and Max values for the Y axis)
Excel has more options and features for charts, that's for sure.
Here is a way to do it in Numbers:
Formula in C2 =IF(B2>0,B2,0)
Formula in D2 =IF(B2<0,B2,0)
Fill down with both
The chart is a stacked bar chart using those two columns as the data.
You'll want to turn the legend off probably
If you select "hidden data" for the chart you can hide those two columns.
Note that this only works for one set of data.
Hi Ian,
Two series. Note the blank cells. The series can be formatted separately. Click on a series to see the Format > Style tab on the right.
Regards,
Another Ian.
That's an interesting work around Ian but it means I would need to change the host data by adding another column (series) as per this example. The spreadsheets I converted from Excel show the negative data in a red bar on the chart but I can't find a function to simply do this in Numbers.
Ian
Thank you, I will consider all these ideas and work out the best way of displaying the results.
Many thanks
Ian
Conditional formatting in charts
