Sheets AcademySheets Academy
Premium
First steps

Format is not the value

What's 0.33 + 0.33 + 0.33? Obviously 0.99. But in the table below, three such profit shares add up to 1.00:

fx
A
B
C
1
2
3
4
5
6
7
Partner
Share
Emma
0,3333333
Ben
0,3333333
Olivia
0,3333333
Total
Sheet1
Click Emma's share in B2 and look at the formula bar above the sheet: what's stored there isn't 0.33.

Excel didn't make a mistake. What you see on the screen isn't the number itself but the way someone chose to display it.

Value and format

A cell stores a value, and on the screen you see it in some format. A format changes only the look: 0.15 and 15% are the same number, displayed in different ways.

You choose a format on the Home tab in the Number Format list. The main formats are:

  • General shows the number as is;
  • Number shows two decimal places: 1234.50;
  • Currency adds a currency sign: $1,234.50;
  • Percentage multiplies the number by 100 and adds a percent sign: 0.15 becomes 15%;
  • Short Date shows the number as a month, day and year.

Try it yourself. Below the sheet you can see what's stored in the selected cell and what's shown on the screen.

Select the price in B2 and choose the Currency format
fx
A
B
C
1
2
3
4
5
6
Item
Value
Price
1234,5
Discount
0,15
Mystery
46054
Sheet1
Click B2, open the Number Format list on the Home tab and choose Currency.
Stored in the cellempty
Shown on the screenempty

Percentages

Since 15% and 0.15 are the same number to Excel, there are two ways to enter a percentage:

  • type 15% with the percent sign, and Excel turns on the Percentage format by itself;
  • type 0.15 and choose the Percentage format.

If a cell already has the Percentage format, Excel helps out: it reads the number 15 as 15%, not as 15 whole units. Try it in B2:

fx
A
B
C
1
2
3
4
Product
Discount
Jacket
Sheet1
Type the number 15 in B2: the cell shows 15%, not 1500%.

In a cell without the Percentage format, the same 15 stays the number 15. That's why the safest way is to type percentages with the percent sign: then the result doesn't depend on the cell's format.

How many decimal places you see

A format doesn't round the number, it only hides the extra digits. The Increase Decimal and Decrease Decimal buttons next to the format list change how many decimal places you see.

That's what happened with the profit shares. Each cell stores 0.3333333, but the format shows only two decimal places, 0.33. Excel added up the real values and got 0.9999999, and with two decimal places that's exactly 1.00:

fx
A
B
C
1
2
3
4
5
6
7
Partner
Share
Emma
0,3333333
Ben
0,3333333
Olivia
0,3333333
Total
Sheet1
Select B2:B5 and click Increase Decimal on the Home tab a few times: the hidden digits appear.

If a total is off from what you see on the screen by a cent, check the value in the formula bar first. Most likely, Excel calculated correctly, and the format just didn't show all the digits.