To apply the default Excel number format for any of the middle sections, type General instead of the corresponding format code.įor example, to display zeros as dashes and show all other values with the default formatting, use this format code: General -General '-' General.

If a custom number format includes 2 sections, the first section is used for positive numbers and zeros, and the second section - for negative numbers.Ī custom format is applied to text values only if it contains all four sections. If a custom format contains just 1 section, that format will be applied to all number types - positive, negative and zeros.

Excel custom number format does not have to include all four sections.

The original number format cannot be changed or deleted.

When you are customizing a built-in Excel format, a copy of that format is created.

The underlying value stored in a cell is not changed.

A custom Excel number format changes only the visual representation, i.e.

When creating a custom number format in Excel, please remember these rules:

Format for text values (display text in magenta font color).

Format for zeros (display dashes instead of zeros).

Format for negative numbers (the same as for positive numbers, but enclosed in parentheses).

Format for positive numbers (display 2 decimal places and a thousands separator).

Here's an example of a custom Excel format code: To be able to create a custom format in Excel, it is important that you understand how Microsoft Excel sees the number format.Īn Excel number format consists of 4 sections of code, separated by semicolons, in this order: Wait, wait, but what do all those symbols in the Type box mean? And how do I put them in the right combination to display the numbers the way I want? Well, this is what the rest of this tutorial is all about :)

Instead of creating a custom number format from scratch, you choose a built-in Excel format close to your desired result, and customize it. Create a custom Scientific Notation format.Show the desired number of decimal places.

How to create an Excel custom number format.The aim of this tutorial is to explain the most essential aspects of Excel number format and set you on the right track to mastering custom number formatting. Number formatting in Excel is a very powerful tool, and once you learn how to use it property, your options are almost unlimited. If none of the inbuilt Excel formats meets your needs, you can create your own number format. But there are situations when you need something very specific. Microsoft Excel has a lot of built-in formats for number, currency, percentage, accounting, dates and times. You will learn how to show the required number of decimal places, change alignment or font color, display a currency symbol, round numbers by thousands, show leading zeros, and much more. To learn how to enable full keyboard access in a Mac, see Set up your device to work with accessibility in Microsoft 365.This tutorial explains the basics of the Excel number format and provides the detailed guidance to create custom formatting. *Move to the previous box, option, control, or command in a dialog *Move to the next box, option, control, or command in a dialog Paste Special (doesn't apply to all products) In this topicĬreate a new file or item (context dependent)Ĭreate a new file from a template or themeĬut the selection (and copy to the clipboard) If you have to press one key immediately after another, the keys are separated by a comma (,). Note: If a shortcut requires pressing two or more keys at the same time, this topic separates the keys with a plus sign (+).

Ctrl m excel