Summary

Almost every strange thing Excel does with numbers comes back to one idea: a cell holds a value, and the number format only decides how that value is drawn on screen. The two are separate, and once you can see the join, #####, a total that is a penny out, a date where you expected a number, and a column of figures that refuses to add up all stop being mysteries and start being the same mystery.

In this guide

• 1. A Cell Holds a Value; the Format Decides How It Looks
• 2. Where the Formats Live
• 3. The Built-In Categories and What Each Is For
• 4. Currency and Accounting Are Not the Same Thing
• 5. Percentage — Why 0.5 Shows as 50%
• 6. Dates and Times Are Numbers Wearing a Costume
• 7. Text — the Escape Hatch, and What It Costs
• 8. Why a Cell Fills with #####
• 9. Why a Rounded Column Does Not Add Up
• 10. How to Read a Custom Format String

Before You Start

• None required. Number formats work the same way in every version of Excel, on Windows, Mac and the web.

Instructions

1. A Cell Holds a Value; the Format Decides How It Looks

This is the whole article in one sentence, and it is worth proving to yourself once.

Type 3.4 into a cell. Set the format to Number with zero decimal places. The cell now reads 3.

Click the cell and look at the formula bar above the column headers. It still reads 3.4. Nothing was rounded, nothing was lost, and nothing was changed — Excel drew the number 3.4 using a format that has no room for a decimal, so it showed you the nearest whole number.

Now put =A1*10 in the cell beside it. The answer is 34, not 30. Every calculation in the workbook uses the value, and the value never noticed the format.

Three consequences follow from that one fact, and each is a section below:

• Widening a column changes nothing about the number in it. It only gives the format more room.
• A number format cannot fix data. It cannot turn text into a number, and it cannot un-round a value that was genuinely rounded.
• ROUND() changes the value. Formatting does not. When a figure has to be genuinely two decimal places — because it is going into an invoice or a bank file — you need the function, not the format.

The formula bar is the truth. Whenever a cell surprises you, click it and read what the formula bar says before you touch anything else.

2. Where the Formats Live

Everything in this article is reached from two places.

The quick route. On the Home tab, the Number group holds a dropdown that normally reads General, plus buttons for currency, percent, comma style, and increasing or decreasing the decimal places shown. The dropdown covers the eleven built-in categories.

The full dialog. Press Ctrl + 1 — Command + 1 on a Mac — to open Format Cells. The Number tab lists every Category down the left with its options on the right, including Custom. Everything Excel can do to the appearance of a number is on that one tab.

One detail that catches people: the format lives on the cell, not on the value. Delete a cell's contents and the format stays behind, which is why a cell that once held a date turns your next entry into a date too. To strip it, select the cells and use Home > Clear > Clear Formats. That resets the appearance and leaves every value in the sheet alone.

Note: Excel for the web offers the same Number Format dropdown on the Home tab. A few of the specialised categories are available only in the desktop app.

3. The Built-In Categories and What Each Is For

Eleven categories, and the working difference between them is narrower than the list suggests.

Category	What it is for	How it draws 1234.5
General	The default. No fixed rules; shows roughly what you typed and drops trailing zeros.	1234.5
Number	Plain figures. You choose the decimal places, whether to use a thousands separator, and how negatives look.	1,234.50
Currency	Money in a single cell or a sentence. The symbol sits immediately in front of the digits.	$1,234.50
Accounting	Money in a column that someone reads down.	$ 1,234.50
Percentage	Proportions. Multiplies the value by 100 for display and adds a % sign.	123450.00%
Date	Calendar values, drawn from the number underneath.	A date in 1903
Time	Clock values, drawn from the fractional part of the number.	Midday on that date
Fraction	Measurements in halves, quarters, eighths.	1234 1/2
Scientific	Very large or very small numbers.	1.23E+03
Text	Anything that must keep its exact characters. Stops the cell being a number at all.	1234.5, left-aligned
Special	ZIP codes, phone numbers and similar, keyed to your region.	Varies by region

Custom sits at the bottom of the same list and is covered in Step 10.

General is not "no format". It is a format with its own rules — it drops trailing zeros, switches to scientific notation for very long numbers, and adjusts what it shows as the column narrows. That last behaviour is why a General cell can quietly show you fewer decimals than it holds.

One kind of value sits outside this list entirely. Alongside numbers, text and error values, Excel has the logical values TRUE and FALSE. They have no number-format category of their own, and the clearest demonstration of the value-versus-display idea in the whole application is what Excel does with them: a cell holding TRUE can be drawn as a ticked checkbox. Same value, completely different picture — see How to Insert Checkboxes in Excel.

4. Currency and Accounting Are Not the Same Thing

They produce almost the same result and are built for different jobs.

Currency puts the symbol hard against the first digit, so $42.00 and $1,234.50 have their symbols in different horizontal positions. You choose how negatives appear: a minus sign, red text, brackets, or red brackets.

Accounting pins the symbol to the left edge of the cell and the digits to the right, so a whole column lines up: symbols in one vertical line, decimal points in another. Zero values show as a dash rather than 0.00, and negatives always appear in brackets. Those are not preferences — they are the conventions of a financial statement, which is what the format was built to produce.

The rule of thumb: a single figure quoted in a report gets Currency. A column someone will scan down for the odd one out gets Accounting.

Note: Neither format changes the number. $1,234.50 and 1234.5 are the same value, and a cell displaying - under Accounting still holds a zero that =SUM() will add.

5. Percentage — Why 0.5 Shows as 50%

Excel stores a percentage as its decimal equivalent. Fifty per cent is the number 0.5, and the format is what puts the % on the end.

Apply Percentage to a cell already holding 0.5 and it displays 50%. The value did not change — =A1*2 still returns 1. The format multiplied the display by 100 and appended a sign.

This is also why =B2/C2 for a share of a total returns something like 0.375 rather than 37.5. The formula is right; the cell needs the Percentage format, not a *100 bolted onto the end. Adding both gives you 3750%.

Typing into a percentage-formatted cell has one rule worth knowing. Excel has a setting called Enable automatic percent entry, on by default, under File > Options > Advanced in the Editing options group. With it on, numbers less than 1 that you type into a percentage cell are multiplied by 100, so 0.5 becomes 50% and 50 stays 50%. Clear the checkbox and Excel multiplies everything you type, so 50 becomes 5000%. If a colleague's sheet behaves the opposite way to yours, that setting is why.

Working with the numbers themselves — increases, decreases, and shares of a total — is covered in How to Calculate Percentages in Excel.

6. Dates and Times Are Numbers Wearing a Costume

A date is not a special kind of data in Excel. It is an ordinary number with a date format on it.

Excel counts days from 1 January 1900, which it calls day 1. That makes 46000 a date in December 2025 and today's date a five-digit number in the same range. Take any date cell, set it to General, and the number appears.

Time is the fractional part of the same number. Half a day is 0.5, so 44927.5 is midday on a particular date. Six hours is 0.25.

Two everyday behaviours fall straight out of that:

• Subtracting dates works. =B2-A2 returns the number of days between them, because it is subtracting two ordinary numbers. The full set of date calculations is in How to Calculate the Difference Between Two Dates in Excel.
• The answer often arrives wearing a date format. Excel copies the format from the cells you subtracted, so 14 gets drawn as the fourteenth day of January 1900. Set the result cell to General and the number appears.

The same mechanism runs in reverse when you type. Excel sees 1-2 or 3/5, decides it is looking at a date, converts it, and keeps only the serial number — the characters you typed are gone. How to Stop Excel Changing Numbers into Dates covers how to head that off and what can be recovered afterwards.

7. Text — the Escape Hatch, and What It Costs

The Text format tells Excel to keep the exact characters and do no arithmetic on them, which is exactly right for some columns and quietly ruinous for others.

Use it for values that only look like numbers: ZIP codes with a leading zero, account numbers longer than fifteen digits, part codes such as 03-05 that would otherwise become dates, and anything with a leading + or =.

What it costs, in every case:

• =SUM() over the column returns 0. Text is skipped, not added.
• Values sit against the left edge of their cells instead of the right.
• A small green triangle appears in the corner of each cell — Excel flagging that a number is stored as text.
• VLOOKUP and XLOOKUP return #N/A when one side is text and the other is a real number, even though they look identical on screen.
• Sorting is alphabetical, so 10 comes before 2.

And the one that wastes the most time: changing a Text-formatted cell to Number does not convert what is already in it. The format governs how new entries are read and how values are drawn; it has no power over characters already sitting in the cell. You have to force Excel to re-read each one, which is what Data > Text to Columns and the other methods in How to Fix Numbers Stored as Text in Excel all do.

8. Why a Cell Fills with

A row of hashes is not an error and nothing has been lost. The column is too narrow to draw the formatted number.

Excel will happily let text spill sideways into the next cell, because a truncated word is still readable. A truncated number is not — 1,234,567 cut in half could read as 1,234, and that is worth more than an inconvenience. So Excel refuses to show part of a number and shows hashes instead.

Widen the column and the number reappears untouched. Double-clicking the boundary line between two column headers resizes to fit the widest entry.

One variant surprises people: a date or time cell holding a negative value shows hashes no matter how wide the column gets, because there is no such thing as a date before Excel's calendar begins. That usually means a subtraction ran in the wrong order. The full set of causes and fixes is in How to Fix "######" Errors in Excel Cells.

9. Why a Rounded Column Does Not Add Up

Your figures each show two decimal places, they visibly add to 30.00, and the total cell insists on 30.01. Nothing is broken, and the reason is the first section of this article.

Say three cells hold 10.004, 10.004 and 10.004, formatted to two decimal places. Each one displays 10.00. =SUM() adds the values, not the pictures, so it adds 30.012 and displays 30.01.

There are two honest ways out, and they are genuinely different:

• Change the values with ROUND(). =ROUND(A2,2) produces a new value that really is two decimal places. Build the column with it, or wrap it round the calculation that feeds the column, and the displayed figures and the total agree because there is nothing hidden left. This is the right answer for anything going into an invoice, a payment file, or a set of accounts.
• Change the whole workbook with Set precision as displayed. Under File > Options > Advanced, in the When calculating this workbook group, this setting permanently rewrites every stored value in the workbook to match what is displayed. Permanently is the operative word — the hidden decimals are discarded from the file and closing without saving is the only way back. It is a blunt instrument, and ROUND() is almost always the better choice.

10. How to Read a Custom Format String

You do not need to write these. You do need to recognise one, because a custom format is the usual explanation for a cell that displays something the formula bar does not.

Select the cell, press Ctrl + 1, and click Custom at the bottom of the Category list. The Type box shows the string in force.

The pieces that account for almost everything you will meet:

Symbol	What it does	Example
0	A digit position that always shows, padded with a zero if needed	00000 draws 2134 as 02134
#	A digit position that shows only when there is a digit for it	#,##0 draws 1234 as 1,234
.	The decimal point	0.00 forces two decimal places
,	Thousands separator; a trailing one divides the display by 1,000	0, draws 12500 as 13
"text"	Literal characters, shown as typed	0" kg" draws 45 as 45 kg
@	Wherever the cell's text goes	"Ref: "@ draws A19 as Ref: A19
;	Splits the string into sections	See below

The semicolons are the part worth knowing. A format string can carry up to four sections, in this order: positive numbers; negative numbers; zero; text. So this string —

#,##0.00;[Red](#,##0.00);"-";@

— means: positives with thousands separators and two decimals, negatives in red brackets, zeros as a single dash, and text shown as typed. That is roughly what the Accounting format does under the bonnet.

And the point that ties back to the top of the page: a custom format never changes the value. A cell displaying 45 kg holds the number 45, multiplies like 45, and exports to CSV as 45. If you need the unit to be part of the data rather than part of the picture, it has to go into the value itself.

Troubleshooting

TIP

When a cell does something you did not expect, click it once and read the formula bar before changing anything. The formula bar shows the value; the grid shows the format's opinion of it. Nine times out of ten they disagree and that disagreement is the whole problem — which means the data is fine and only the appearance needs attention.

Symptom / Error	Potential Cause	Solution
A cell fills with #####	Column too narrow for the formatted number	Widen the column, or double-click the boundary between the column headers to autofit — Step 8.
The column total is a penny out	The display is rounded; the values are not	Round the values themselves with =ROUND(A2,2) — Step 9. Formatting alone never changes a number.
Setting a Text column to Number changes nothing	Format governs display and new entries, not existing text	Force a re-read with Data > Text to Columns — see How to Fix Numbers Stored as Text in Excel.
Typing 1-2 produces 2-Jan	Excel reads a dash between numbers as a date	Format the cells as Text before typing — see How to Stop Excel Changing Numbers into Dates.
A percentage reads 100 times too large	The cell holds 50 where it needs 0.5	Percentage multiplies the display by 100. Store the decimal, or check Enable automatic percent entry under File > Options > Advanced — Step 5.
A date subtraction returns a date	The result cell inherited the date format	Set it to General — see How to Calculate the Difference Between Two Dates in Excel.
An emptied cell still formats new entries strangely	Deleting contents leaves the format behind	Select the cells and use Home > Clear > Clear Formats. Only the appearance is reset; every value in the sheet is untouched — Step 2.
A cell displays units or words the formula bar does not show	A custom format string is adding literal text	Press Ctrl + 1 and read the Type box under Custom — Step 10.

Related Articles

• How to Stop Excel Changing Numbers into Dates
• How to Fix Numbers Stored as Text in Excel
• How to Calculate Percentages in Excel
• How to Calculate the Difference Between Two Dates in Excel

Did this solve your problem?