Number and percent format

A number inside, a mask outside

A cell can hold 0.2 and show 20 %. Or 50 and show 50.00 Kč. Format does not change the value — only how it looks.

That is the most important sentence of this chapter. When you get it, you stop being scared of percents.

Click a cell. In the formula bar at the top you see the value. In the cell you see the mask. Sometimes they are the same. With percents and korunas almost never.

Right-click a cell → More number formats (Sheets) or Format cells (Excel). The three most common:

• Number — 1.50 or 1.5
• Percent — 20 %
• Currency — 50.00 Kč

On an English account the decimal is a dot. 0.2 is two tenths. On a Czech account it is a comma: 0,2. If a formula shouts, check if you typed a comma into an English workbook.

A trap almost everyone makes

You want twenty percent. You type 20. You turn on percent format. The cell shows 2000 %.

Why: percent format takes the number and multiplies it by a hundred only on the screen. Inside 20 stayed. Twenty times a hundred is two thousand percent.

There are two right paths:

1. Type 0.2 and turn on percents. You will see 20 %. Inside it is still 0.2.
2. Type 20 % including the percent sign. The spreadsheet stores 0.2 and puts the mask on itself.

Do not type 20 and then click %.

Try it on one cell

1. Open sheet 备注s. In A1 type 0.2. Enter. You see 0.2. That is value and mask — still an ordinary number.

2. Leave A1 selected. Format → Percent. The cell must show 20 %. Click A1. In the formula bar 0.2 must still light up (or 20 % depending on the version — the next step decides).

3. In B1 type =A1*100. You must see 20. That is proof: inside A1 is 0.2. A hundred times 0.2 is 20. If in B1 you see 2000, in A1 there was 20, not 0.2.

4. In A2 type 20 and turn on percents. You will see 2000 %. That is the trap. Delete A2, so it does not mix a total.

If in B1 #VALUE! pops up, in A1 there is no number. There is the text “0.2” with a space, or you typed 0,2 where the account expects a dot. Rewrite the cell again.

Pocket money in korunas

Money without “Kč” looks like points in a game. Currency format says it is korunas.

Imagine sheet Pocket:

Inside B2 is still 50. The mask turns it into 50.00 Kč.

1. On Pocket select columns B, C and D — only numbers, not headings. For example B2:D8.

2. Format → Currency. If it offers dollars, open more formats and pick Kč / CZK. Suddenly from 50 you see 50.00 Kč.

3. Click B2. In the formula bar is 50, in the cell 50.00 Kč. Formula =B2+C2 still adds fifty and zero, not text.

4. On Grades leave grades as an ordinary number. A koruna by a grade is nonsense. Average 1.80 Kč looks like a joke and a teacher will know.

What often goes wrong

A percent from a twenty. You typed 20, turned on %. You see 2000 %. Put the format back to number, rewrite to 0.2, turn on % again.

Currency on the whole sheet. You select A1:Z100 and give Kč. Headings Subject and Day stay text, but the grade average gets a koruna. Format only columns that are money.

Text that looks like a number. 50 Kč typed by hand is text. =SUM skips it or throws #VALUE!. Type 50 and currency format. Let the spreadsheet paint the Kč sign.

Two decimal separators. 50.00.00 is not a number. The spreadsheet takes it as text. Delete and type 50 again.

Now you 💪

Two mini exercises. Both on 备注s, so you do not break grades.

First: A1 = 0.2 → percent format → you see 20 %. Next to it in B1 =A1*100. It must come out 20. That proves inside it is still 0.2.

Second: C1 = 50 → currency format → 50.00 Kč. In D1 type =C1*2. You must see 100.00 Kč, not the text 50*2. If you see #VALUE!, C1 is not a number.

Then go back to Pocket and turn on currency on the money columns. On Grades do nothing with a koruna.

What to take from this lesson

• Format = how it looks. The value stays. The formula bar does not lie.
• 20 % is written as 0.2 + format, or 20 %. A bare twenty + % is 2000 %.
• Currency on money. Leave grades as a number. Text 50 Kč SUM does not add.

© 2026 Ing. Martin Polak / AlgoRhino · 内容使用条款