Create A Custom Cell Format In Numbers On Mac - Apple Support (SG)

• Apple
• • Store
  • Mac
  • iPad
  • iPhone
  • Watch
  • Vision
  • AirPods
  • TV & Home
  • Entertainment
  • Accessories
  • Support
• 0+

Numbers User Guide for Mac Open Menu Close Menu

• Communities

Numbers Search this guide Clear Search Table of Contents

Numbers User Guide for Mac

• Welcome
• What’s new
• Numbers basics
  • Get started with Numbers
  • Intro to images, charts and other objects
  • Create a spreadsheet
  • Open or close spreadsheets
  • Personalise templates
  • Use sheets
  • Undo or redo changes
  • Save your spreadsheet
  • Find a spreadsheet
  • Delete a spreadsheet
  • Print a spreadsheet
  • Change the sheet background
  • Customise your app
    • Change the spreadsheet view
    • Customise the toolbar
    • Show or hide the sidebar
    • Change settings
    • Set a default template
  • Touch Bar for Numbers
  • Use VoiceOver with Numbers
    • Use VoiceOver to create a spreadsheet
    • Use VoiceOver to create formulas and autofill cells
• Add and style tables
  • Add or delete a table
  • Select cells, rows and columns
  • Add or change rows and columns
    • Add or remove rows and columns
    • Move rows and columns
    • Resize rows and columns
  • Merge or unmerge cells
  • Change how a table looks
    • Change how table text looks
    • Show, hide or edit a table title
    • Change table grid colours
    • Use table styles
  • Resize, move or lock a table
• Add and organise table content
  • Add text and objects to cells
    • Add text to tables
    • Add objects to cells
    • Add stock information
    • Autofill cells
    • Copy, move or delete cell content
    • Create a snapshot of a table
  • Work with different types of data
    • Format dates, currency and more
    • Create a custom cell format
    • Add tickboxes and other controls to cells
    • Format tables for bidirectional text
  • Wrap text to fit in a cell
  • Highlight cells
  • Filter data
  • Alphabetise or sort data in a table
  • Organise data into categories
    • Intro to categories
    • Add, edit or delete categories
    • Change category groups
    • Add calculations to summarise group data
  • Create pivot tables
    • Intro to pivot tables
    • Create a pivot table
    • Add and arrange pivot table data
    • Change how pivot table data is sorted, grouped and more
    • Refresh a pivot table
    • View the source data for a pivot table value
• Enter formulas and functions
  • Quickly calculate a sum, average and more
  • Calculate values using data in table cells
  • Use the Formulas and Functions Help
• Create and modify charts
  • Add or delete a chart
    • Select data to make a chart
    • Add column, bar, line, area, pie, doughnut and radar charts
    • Add scatter and bubble charts
    • Interactive charts
    • Delete a chart
    • Change a chart’s type
  • Modify chart data
  • Move and resize charts
  • Change the look of a chart
    • Change the look of data series
    • Add a legend, gridlines and other markings
    • Change the look of chart text and labels
    • Add a chart border and background
    • Use chart styles
• Add, edit and format text
  • Select text
  • Add and edit text
    • Add text
    • Copy and paste text
    • Use dictation to enter text
    • Use accents and special characters
    • Enter text in another language
      • Format a spreadsheet for another language
      • Use phonetic guides
      • Use bidirectional text
      • Use vertical text
  • Change the look of text
    • Change the font or font size
    • Bold, italic, underline and strikethrough
    • Change the colour of text
    • Change text capitalisation
    • Add a shadow or outline to text
  • Use paragraph styles
    • Intro to paragraph styles
    • Apply a paragraph style
    • Create, rename or delete paragraph styles
    • Update or revert a paragraph style
  • Use a keyboard shortcut to apply a style
  • Format characters and punctuation
    • Adjust character spacing
    • Add drop caps
    • Raise and lower characters and text
    • Format fractions automatically
    • Create and use character styles
    • Format dashes and quotation marks
    • Format Chinese, Japanese or Korean text
  • Align and space text
    • Set tab stops
    • Align text
    • Format text into columns
    • Adjust line spacing
  • Format lists
  • Add and edit equations
  • Add a highlight effect to text
  • Add links
  • Add borders and rules (lines) to separate text
• Add images, shapes and media
  • Images
    • Add an image
    • Add an image gallery
    • Edit an image
  • Shapes
    • Add and edit a shape
    • Combine or break apart shapes
    • Draw a shape
    • Save a shape to the shapes library
    • Add and align text inside a shape
  • Add 3D objects
  • Add lines and arrows
  • Animate, share or save drawings
  • Video and audio
    • Add video and audio
    • Record audio
    • Edit video and audio
  • Set movie and image formats
• Position and style objects
  • Position objects on a sheet
    • Use rulers
    • Position and align objects
    • Use alignment guides
    • Place objects inside a text box or shape
    • Layer, group and lock objects
  • Change object transparency
  • Fill shapes and text boxes with colour or an image
  • Add a border to an object or sheet
  • Add a caption or title
  • Add a reflection or shadow
  • Use object styles
  • Resize, rotate and flip objects
• Use writing and editing tools
  • Look up words
  • Find and replace text
  • Replace text automatically
  • Check spelling
  • Set author name and comment colour
  • Highlight text
  • Add and print comments
• Share and collaborate
  • Send a spreadsheet
  • Intro to collaboration
  • Invite others to collaborate
  • Collaborate on a shared spreadsheet
  • See the latest activity in a shared spreadsheet
  • Change a shared spreadsheet’s settings
  • Stop sharing a spreadsheet
  • Shared folders and collaboration
  • Use Box to collaborate
• Manage and organise spreadsheet files
  • Use iCloud with Numbers
  • Import an Excel or text file
  • Export to Excel or another file format
  • Reduce the spreadsheet file size
  • Save a large spreadsheet as a package file
  • Restore an earlier version of a spreadsheet
  • Move a spreadsheet
  • Lock a spreadsheet
  • Password-protect a spreadsheet
  • Create and manage custom templates
  • Transfer spreadsheets between devices
    • Transfer spreadsheets with AirDrop
    • Transfer spreadsheets with Handoff
    • Transfer spreadsheets with the Finder
• Keyboard shortcuts
  • Keyboard shortcuts
  • Keyboard shortcut symbols
• Copyright

You can create your own cell formats for displaying numbers, text, and date and time values. Your custom formats are saved automatically and listed in the Data Format pop-up menu so you can use them again.

You might need to create a custom text format for a numeral so that you can use it as a text label. For example, if you enter a phone number with a leading 0 (zero), Numbers would normally treat it as a number and remove the leading 0 automatically. If you format the phone number as text, that doesn’t happen.

Note: To create a custom format for numerals to be used as text labels, see “Create a custom text format” below.

Create a custom number format

1. Go to the Numbers app on your Mac.

2. Open a spreadsheet, then select the cells you want to format.

3. In the Format  sidebar, click the Cell tab.

4. Click the Data Format pop-up menu, then choose Create Custom Format.

5. Type a name for your format, then click the Type pop-up menu and choose Number.

6. Do any of the following to specify the format:

   • Type text in the field: The text you type appears in the cell.

   • Use tokens to represent the type of information you will enter in a cell: Drag tokens to the custom format field. For example, if you’re entering phone numbers into a custom format, you can use an integer token with 11 digits.

     You can also drag tokens within the field to rearrange them.

   • Customise a token element: Click the arrow on a token to show formatting options, such as the type of currency symbol or the number of digits to display.

     

   • Specify rules to accommodate multiple conditions: Add up to three rules to change the formatting of a cell based on specific conditions you define.

     In the example below, all positive numbers you enter are displayed as a phone number with the US area code (952). When you type “5555103” in a cell, it’s displayed as “(952) 555-5103”. When you type “0” in a cell, the cell shows “No number on file”.

     

Create a custom date and time format

1. Go to the Numbers app on your Mac.

2. Open a spreadsheet, then select the cells you want to format.

3. In the Format  sidebar, click the Cell tab.

4. Click the Data Format pop-up menu, then choose Create Custom Format.

5. Type a name for your format, then click the Type pop-up menu and choose Date & Time.

6. Define your custom format by doing any of the following:

   • Type text in the field: The text you type appears in the cell.

   • Use tokens to represent the type of information you will enter in a cell: Drag tokens to the custom format field. You can also drag tokens within the field to rearrange them.

   • Customise a token element: Click the arrow on a token to show formatting options for the data.

     The example below uses the day of the year token (which is set to “5” here) and custom text so that when you type “25/02/2014” in a cell, the cell displays “56 days into the year”.

     

     If you enter a value that contains a hyphen (-) or a slash (/), such as “4/1/2014”, Numbers assumes it’s a date and assigns it a date and time format automatically.

Create a custom text format

1. Go to the Numbers app on your Mac.

2. Open a spreadsheet, then select the cells you want to format.

3. In the Format  sidebar, click the Cell tab.

4. Click the Data Format pop-up menu, then choose Create Custom Format.

5. Type a name for your format, then click the Type pop-up menu and choose Text.

6. In the text field, type text that you want to appear automatically in each cell that uses this format.

   The blue “text” token that appears by default in the Custom Format field represents the text that you type in the cell. In this example, the text “Notify customer:” appears before whatever you type in a cell. If you type “Need to update address”, it displays “Notify customer: Need to update address”.

   

Edit or delete a custom cell format

1. Go to the Numbers app on your Mac.

2. Open a spreadsheet, then select the cells with the custom cell format you want to edit or delete.

3. In the Format  sidebar, click the Cell tab.

4. Click the Data Format pop-up menu, then choose the custom cell format you want to edit or delete.

5. Click Edit Custom Format, then do one of the following:

   • Edit the cell format: Make your changes, then click OK.

   • Delete the cell format: Click Delete Format.

     Any cells in your table that use the deleted format revert to Automatic formatting.

See alsoFormat dates, currency and more in Numbers on MacAlphabetise or sort table data in Numbers on Mac Previous Format dates, currency and more Next Add tickboxes and other controls to cells Helpful? Yes No Character limit: 250 Please don’t include any personal information in your comment. Maximum character limit is 250. Submit Thanks for your feedback.