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.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

I would like to understand how this is implemented. I got an answer for ChatGP on how to simulate Data Bars like in Excel and I cant see how to make it work. Specifically when it comes to the "Apply" section of the answer.

In Apple Numbers, you can create a visual effect similar to Excel's Data Bars using Conditional Highlighting. Here's how you can achieve this:



  • Open your Numbers spreadsheet.


  • Select the cell or range of cells where you want to apply Data Bars or create a new column that contains the values you want to visualize with Data Bars.


  • Click on the "Format" button in the Format Panel on the right-hand side.


  • In the Format Panel, make sure you have the "Cell" tab selected.


  • Under the "Cell Fill" section, click on the "Add" button next to "Conditional Highlighting."


  • In the "Conditional Highlighting" dialog that appears, set up the rules for your Data Bars. For example, if you want to create Data Bars based on a range of values, you can set up rules like this:



  • Condition: Is greater than


  • Value: Enter the minimum value for your Data Bars.


  • Color: Choose a color for your Data Bars.


  • Apply: Choose "Left to Right Fill" or "Right to Left Fill" for horizontal Data Bars, or "Top to Bottom Fill" or "Bottom to Top Fill" for vertical Data Bars.


    • Stop If True: Check this option.


  • Repeat these steps to add multiple rules for different ranges of values with different colors if needed.


  • Click the "Done" button to apply the conditional highlighting.

Now, your selected cells or column will display Data Bars based on the rules you've set. The color and length of the Data Bars will vary depending on the values in the cells, with longer bars representing higher values according to your rules.


This method provides a way to achieve Data Bars-like visualizations in Apple Numbers, although the customization options may not be as extensive as in Excel.





Any help with this will be greatly appreciated, Frank




Mac mini (M2 Pro, 2023)

Posted on Oct 7, 2023 11:11 AM

Reply
12 replies
Sort By: 

Oct 7, 2023 8:22 PM in response to franks4business

Not quite the same as Data Bars but you can use the REPT function in another column to achieve a similar effect.




The character I use for the demo is this one:



You can copy-paste or use:


=CHAR(9608)


Go to Text Color in the Text panel to change the color of the bar.


For large values divide by a suitable number to get the desired number for second argument of the REPT function.


SG


Reply

Oct 8, 2023 3:28 AM in response to franks4business

Hi Frank,


SG's use of the REPT function seems the way to go.

But don't ignore Format > Cell > Data Format > Star Rating.



The downsides:

  • Star Rating allows only 0 to 5 stars.
  • Star Rating requires user input (clicks). It is not possible to use a formula to set the number of stars.


Regards,

Ian.


Reply

Oct 7, 2023 12:34 PM in response to franks4business

Just goes to show how unintelligent AI can be. If it doesn't know, it makes things up just like a know-it-all. Numbers does not have data bars and, to the best of my knowledge, does not have gradient fills for conditional highlighting, which is what it is suggesting you set up. Even if it did, the "bar" would be static (boolean, actually, either there or not there); the bar length would not be based on the value in the cell.


If it is a column or row of data, you can emulate data bars with a chart properly sized and aligned underneath the table. The result might be hard to maintain, though, because it will not be physically tied to the table (they move independently and can become misaligned).


This is the chart by itself:


All features of the chart other than the bars have been deleted. Make sure one of your cells is 100 (or 100%) so that you can resize and align it horizontally using that row. I resized and aligned it over the table then sent it to the back so the table was on top. It is a little hard to work with after that. To select it when it is behind the table, right click on the sheet tab and choose it from the list. To select the series on the chart, click on "edit data references" then click in the table on the colored area at the top of the series. It is best to have the format exactly as you want it and the table sized and aligned before sending it to the back.


My example was only those 4 rows because I only had those four filled in but you can make a chart that includes additional, blank rows below that will get "data bars" as you give them data.

Reply

Oct 8, 2023 6:39 AM in response to SGIII

That's a neat way to do it within the table. Expanding on that idea a little, here is a way to increase the precision by a factor of 8 by using partial blocks:



Formula in B2 =REPT(Characters::$B$2,A2)&XLOOKUP(MOD(MROUND(A2,0.125),1),Characters::C,Characters::B,"",−1)


Formula in Characters::B2 =CHAR(HEX2DEC(A2))


The font needs to be one that makes all the blocks the same height. Most do not. American Typewriter is the one I used. There used to be one called "Line Printer" or "Impact Printer" or something like that that might also work but I don't appear to have that one installed.


I'm sure this can be improved upon to eliminate the table of characters and make the formula simpler.

Reply

Oct 9, 2023 9:30 AM in response to Badunit


The lookup table version is =REPT(Characters::$B$2,A2)&XLOOKUP(MROUND(MOD(A2,1),0.125),Characters::C,Characters::B,"")


The single cell version is

=REPT(CHAR(9608),A2)&IFS(A2=0,"",A2<0.125,CHAR(9615),MROUND(MOD(A2,1),0.125)×8=0,"",TRUE,CHAR(9616−MROUND(MOD(A2,1),0.125)×8))


Both are a lot more complicated than the much shorter formula SGIII provided but they increase the precision by a factor of 8. Both formulas round up to the nearest 8th block. The "single cell" version will put a 1/8-wide block when 0<X<0.625 to indicate that X is not 0, the lookup version will have no blocks until X>=0.625 and it rounds it to 0.125.


The outlines on the blocks in the screenshot can be put on in the format sidebar where the text font is chosen. Click the gear and the settings for "more text options" are there. The outlines give another visual clue, if you like it.


Edit: I see the one formula is truncated on my screen. If you select that entire row and Copy it should give the entire formula.


Reply

I would like to understand how this is implemented. I got an answer for ChatGP on how to simulate Data Bars like in Excel and I cant see how to make it work. Specifically when it comes to the "Apply" section of the answer.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.