When we talk about a tool with which to create spreadsheets, it is undeniable to think of Excel, belonging to the Office or Microsoft 365 office suite, as the most used option worldwide. The application, by default, is configured so that data that we are not going to use is not displayed, such as the case of leading zeros, because if you write them in the cells, they will not appear.
While it is true that the leading zeros may not be useful for anything, it is likely to be annoying if when writing them, we see how Excel is in charge of eliminating the leading zeros automatically in our data. And it is that either for aesthetics or for any other reason, if we need to see these units we show you how we can activate it.
Show leading zeros in Excel
By default, Excel cells do not have any type of special format, but it is the application itself that is in charge of choosing which one is the best based on the data that we indicate. Despite this, it is possible to configure the program to our liking so that the data is displayed as best suits us.
Add custom formatting
One way is to select the cells on which we want to change the format and then click with the right mouse button on them and choose the Format Cells option.
This will automatically open a window from which we can choose the format we want to use. In this way, Excel shows us several predefined data formats. To display leading zeros, we will select “Custom”, and in the ” Type” box we will enter the format we want to display.
For example, we can enter ” 00000,000″ . This means that by default it will show us four leading zeros and three decimal zeros. Now, when we enter the data in our Excel we can see that they appear as follows.
If instead of four zeros to the left we want more or less to be displayed, we simply have to change the value that we have indicated before for the one we want. In the same way we can modify the format so that units are shown at the end, or a greater or lesser number of decimals, thus being able to customize the tables according to our needs at all times.
Format cells as text
Another option that we can make use of is to change the format of a range to text. In this way, any data we enter will be treated as text values, even if they are numbers, causing Excel to keep the leading zeros in the numbers.
To do this we are going to select the range of cells in which we want to enter zeros to the left. Click on the Home tab and in the Number section, click on the Number format drop-down section, which by default appears as General and which we are going to change to the Text option.
When doing this, if we enter a figure with numbers with leading zeros they will not disappear because they are added as text values instead of as numbers.
Use a leading apostrophe
Another way we have available to display leading zeros in a number is to force Excel to append the number as text by using a leading apostrophe. In this way it is possible to keep those zeros while we add our data, which makes it a fast and easy method to use. We simply have to write an apostrophe before writing any number, which will take care of indicating to Excel that the data should be as text instead of as numbers.
As soon as we press Enter, the leading zeros will remain visible in our worksheet. Although the apostrophe will not be displayed, it is visible in the formula bar at the top, at the moment we select the active cell with the cursor.
With the TEXT function
Another way we have to show the leading zeros in Excel is through the TEXT function which will allow us to apply a custom format to any numerical data found in our spreadsheet
To do this, in the formula box we must enter the following command:
= TEXTO (Valor;Formato)
In this formula, we need to enter the value that we want to convert to text and apply the format that we want.
For example, if we want to add zeros to a number for cell B1, using the data in cell A1 so that the total number of digits is 6, we write:
=TEXTO (A1;"00000")
How to remove them
There are occasions where we can find numerical data that contains leading zeros. In the event that we do not want to see them, we will see a couple of alternatives to eliminate those additional digits and thus be able to obtain the numerical value of the data.
with special glue
One way to remove leading zeros is through Paste Special. To do this, it will be necessary to fill a column with 1 numbers and copy the original values.
Later we click with the right mouse button on cell B1 and choose the Paste special option, which will show us a new window. Here we must select the Multiply option and then click OK.
Now we check how we have removed the zeros, but Excel keeps the values of the cells aligned to the left. To remedy this, simply change the cell format to General or Number.
Clear zeros with the VALUE function
Another way that we have to be able to eliminate the leading zeros is to use the VALUE function, which is in charge of obtaining a numerical value represented as text. In this way, Excel downloads the zeros to the left of the numbers, so using this function we are going to eliminate the zeros to the left. If, for example, we select box B1 and write the formula:
VALOR=(A1)
Next, we will check how the leading zeros in column B1 have been removed.
