Custom cell formatting in Excel

Through the custom format we can give the appearance we need to any numerical value of a cell in Excel without the need to edit it.

Just by indicating a custom format code we can manipulate the way Excel displays that value.

Formatting cells in Excel

By default, Excel has a number of cell formats that we can apply to our data. One way to access these formats is through the commands found in the group inside the tab .

Cell formatting options in Excel

In the drop-down list you will find the cell formats commonly used in Excel such as General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific and Text format.

Below the drop-down list are command buttons for choosing the currency symbol, percent symbol, thousands style, and buttons for increasing or decreasing decimals. If none of these commands can help you display the cell value as desired, then you can fall back on custom cell formatting.

For example, if in your Excel sheet you have a column that will contain a folio number and you need that number to always show 5 digits, then you need Excel to show the value “1” as “00001”, and the value “2 “Such as” 00002 “and so on.

If you don’t want to enter all leading zeros for each of the folios, then you will need to use a custom cell format.

Custom cell format

To start our example, I will enter the value “1” in cell A1, and right click on the cell to select the option .

Menu option

The above command will open the dialog and you should make sure to select the tab and inside the list you must select the option as shown in the following image.

Dialog box

You must immediately activate the text box , which is where the word is currently displayed , and you must delete the text and instead you must enter the value It should contain five zero numbers.

Custom cell formatting in Excel

Finally you will have to click on the OK button and cell A1, which had the value 1, will be shown on the screen as 00001.

Example of custom format in Excel

From now on, cell A1 will automatically display the leading zeros to complete the 5 digits. You can enter a number other than 1 and you will see that Excel will apply the custom format automatically.

If you want to copy the custom format from cell A1 to the entire column, you can follow the steps below.

  1. Activate cell A1
  2. Push the button found in the tab Within the group .
  3. Click on the column A heading and the custom formatting will be applied to the entire column.

Once you have copied the custom format to the entire column, you can enter any number and Excel will fill in the leading zeros needed to display the 5-digit number.

Copy custom format from one cell to entire column

In this way we have created a custom format in Excel and we have applied it to the entire column. If you want to stop using this format, you must select all the cells in the column and choose the format from the list of tab formats .

Custom format codes

We have already seen that custom cell formatting allows us to give our data a unique appearance. The following table shows other custom format examples that we can use.

The spine shows the original value of the cell, that is, when the format is being used . The spine is the value that has been entered into the dialog box and finally the column is the value that is displayed in the cell after applying the custom format.

Value Custom format Presentation
1 000 001
1 “N” 000 N001
11.1 000.00 011.10
55385040 00-00-00-00 55-38-50-40
01/28/2019 dddd-dd-mmmm-yyyy Friday-January-28-2019

You should consider that, although Excel changes the format of the data on the screen, the value of the cell is not affected. For example, if we have the value 1.2345 in cell B1 and we apply a custom format to display it as 1.23, when making a calculation with cell B1, the value 1.2345 will be considered even though it is not the value displayed on the screen.

It is very important to mention that the was primarily intended to change the appearance of numeric values, and it is not possible to create custom formats to manipulate the appearance of text strings.

The only thing we can do in terms of text strings, is add individual characters as shown by the second example in the table above.

If you want to know a little more about custom format codes, I recommend you read the article Custom format codes in Excel.

Leave a Comment