How to Remove the Leading Zero Before a Decimal in Excel
By Irene A. Blake
Microsoft automatically adds a leading zero before a decimal in Excel to assist with visual recognition of data. A business owner might remove the leading zero for a variety of reasons, such as when the data is part of a code that would display incorrectly with a zero before the decimal or the data looks better for presentation purposes without it. To remove the zero, you will need to customize the number formatting for the cell or cells containing the data.
Highlight the cell or cells you need to format without a leading zero in an open Microsoft Excel spreadsheet. If you need to format the entire spreadsheet, click the button under the top spreadsheet tab marked with a white arrow pointing at the spreadsheet.
Click the “Home” tab, locate the Number section and then click the diagonal arrow at the bottom right corner of the section to launch the Format Cells dialog box.
Select the “Number” tab, if it’s not already selected, and then select “Custom” in the Category panel.
Highlight the characters or numbers in the “Type” field and then press the “Delete” or “Backspace” key on your computer keyboard.
Enter a decimal point in the field followed with enough zeroes to match the number of decimal places you need. For example, type .00000 if you need five decimal places after the decimal point.
Click the “OK” button to update your spreadsheet with the change. If the spreadsheet already contained number data, check that the highlighted cells no longer display a zero before the decimal point.
You can also open the Format Cells box by right-clicking the selected cell or cells and selecting “Format Cells…” on the menu or by clicking the down arrow next to the field in the Number section and selecting the “More Number Formats…” option. If you need this cell formatting for future spreadsheets, save the workbook that contains the spreadsheet as a template. Click “File” and select “Save As” to open the Save As dialog box, enter a template name in the File Name field, select “Excel Template” on the Save as Type menu and then click the “Save” button.
Excel by default truncates a number in a customized cell if you don’t have enough zeroes in the decimal places. It also rounds up the truncated number if the number to the right of the truncation spot is higher than five. For example, Excel truncates the number .123456 to .1235 if you use .0000 in the “Type” field. Removing the leading zero before a decimal might lead to visual reading errors in spreadsheets that display numbers in different formats, as without it, your eyes might mistake a number with a decimal for one without one.
- Microsoft: Office -- Basic Tasks In Excel 2010
- Microsoft: Office -- Create a Custom Number Format
- Microsoft: Office -- Select Cells, Ranges, Rows, Or Columns On A Worksheet
- GCFLearnFree.org: Excel 2010 Formatting Cells -- Formatting Numbers and Dates
- Microsoft: Office -- Save a Workbook or A Spreadsheet As a Template
Based in Southern Pennsylvania, Irene A. Blake has been writing on a wide range of topics for over a decade. Her work has appeared in projects by The National Network for Artist Placement, the-phone-book Limited and GateHouse Media. She holds a Bachelor of Arts in English from Shippensburg University.