Home »

Custom Cell Formats in Numbers Spreadsheets

How to create custom cell formats in Apple Numbers.

graphic of Numbers logo

I really like spreadsheets. Lately, I’ve been building spreadsheets about electronics stuff in Numbers, which is Apple’s version of Excel. I was curious about how to use custom cell formatting to display the correct unit abbreviations on values. Here’s how I did it for Ohms, the SI derived unit for electrical resistance.

  1. Click in the cell you want to format.
  2. Hitting Option (⌥) + Command (⌘) + I opens the Inspector window. It’s also available in the menu bar under View > Show Inspector.
    screen grab of Inspector window in Numbers
  3. Click the Cells tab (it looks like a little 42 in a box).
  4. Under Cell Format heading choose Custom… from the drop down menu.
    screen grab of Ohms listed in Cell Format drop down menu
  5. In the Name field type “Ohms” (without the quotes).
  6. Make sure “Number & Text” is selected in the Type drop down menu.
  7. From the Number & Text Elements field drag the Decimals (.##) element into the field with the existing Integers (#,###) element in it.
  8. Add a space after the Decimals element, then type the symbol for Ohms, which is the omega character (Ω). The keyboard shortcut for this is Option (⌥) + Z.
  9. Click the plus (+) button on the right twice to add two more conditions.
  10. In the first added condition, select “If greater than or equal to …” from the drop down menu.
  11. In the field to the right of that drop down type “1000000” (one million without the quotes).
  12. In the element field below that, make sure there’s an Integers element, a Decimals element, a space, a Scale element set to Millions (M), and finally an omega.
  13. For the second condition you added do the exact same thing as above, but enter “1000” (one thousand without quotes) and set the Scale element to Thousands (K).
  14. If everything looks like the screenshot below, hit OK.

screen grab of Cell Format window

Now when you type a value into that field, it should automatically format into Ω, KΩ, and MΩ. If not, go to step 4 and double check that everything was entered correctly.

Your newly created formatting will be added to the Cell Format drop down. You can now select other cells and apply this custom formatting to them. The custom format will be saved in this Numbers file.

screen grab of Cell Format drop down menu

Try creating custom cell formats for other SI Units too.

No Comments >

Leave a Reply