How to Enter and Display Fractions in Excel
By ExcelReady TeamUpdated 5 min read
The short answer
To enter fractions in Excel, type a whole number, a space and the fraction, such as 0 1/4 or 3 1/2. Excel stores the decimal (0.25 or 3.5) and shows it as a fraction. Typing 1/4 alone becomes a date. To show existing decimals as fractions, press Ctrl+1 (Mac: ⌘1) and choose Fraction.
Key takeaways
- Type 0 1/4, not 1/4. On its own, 1/4 is read as a date.
- Excel stores fractions as decimals. The Fraction format only changes how they look.
- Up to one digit shows the nearest simple fraction, so 0.3 appears as 2/7. Use As tenths to show 3/10.
- For a fixed denominator, choose As eighths or As sixteenths, or a custom format like # ?/16.
- To round a measurement to the nearest 1/8, use =ROUND(A2*8,0)/8.
On this page+
Fractions in Excel are decimals with a fraction format on top. To type one, enter a whole number, a space and the fraction, such as 0 1/4 or 3 1/2, and Excel stores 0.25 or 3.5 while showing the fraction. To show numbers you already have as fractions, apply the Fraction number format. This guide is for anyone working with recipes, measurements, inches or stock prices that use halves, quarters and eighths.
How to type a fraction in Excel#
Type a whole number, then a space, then the fraction. For fractions smaller than 1, the whole number is 0.
| You type | Excel stores | Excel shows |
|---|---|---|
0 1/4 | 0.25 | 1/4 |
0 3/8 | 0.375 | 3/8 |
3 1/2 | 3.5 | 3 1/2 |
1/4 | A date (4 January or 1 April, depending on your region) | A date, such as 4-Jan |
The last row is the trap. Excel reads 1/4 as a day and month, so it becomes a date. The leading 0 tells Excel you mean a number. The article on stopping numbers changing to dates covers this behaviour in more detail.
When you type a mixed number like 3 1/2, Excel applies a fraction format to the cell for you. Click the cell and the formula bar shows 3.5. That is the real value.
Format cells as fractions first#
If you'll type many fractions, format the cells before you type. Then 1/4 stays a fraction and doesn't turn into a date.
- Select the cells.
- Press Ctrl+1 on Windows or ⌘1 on a Mac to open Format Cells.
- On the Number tab, choose Fraction.
- Pick a type, such as Up to one digit (1/4), and click OK.
You can also use Home > Number Format and choose Fraction from the list. Changing a format doesn't change the value, only how it looks. The guide to formatting numbers as currency and percent explains this idea for other formats too.
Which Fraction format to choose#
The Fraction types fall into two groups: ones that find the closest fraction, and ones with a fixed denominator. The denominator is the bottom number of a fraction.
| Fraction type | 0.3 shows as | 0.6 shows as | Best for |
|---|---|---|---|
| Up to one digit | 2/7 | 3/5 | Simple fractions like 1/2, 1/4 |
| Up to two digits | 3/10 | 3/5 | Most everyday values |
| Up to three digits | 3/10 | 3/5 | Precise values |
| As halves | 1/2 | 1/2 | Half units |
| As quarters | 1/4 | 2/4 | Quarter hours, quarter units |
| As eighths | 2/8 | 5/8 | Inches, stock fractions |
| As sixteenths | 5/16 | 10/16 | Fine measurements |
| As tenths | 3/10 | 6/10 | Decimal-style fractions |
Two things stand out. First, Up to one digit shows 0.3 as 2/7, because 3/10 needs a two-digit denominator and 2/7 is the closest one-digit fraction. Second, fixed types don't simplify: As tenths shows 0.6 as 6/10, not 3/5. Both are display choices. The stored values stay 0.3 and 0.6.
Custom fraction formats#
A custom number format gives you more control than the built-in list. In Format Cells, choose Custom and type a code in Type:
| Custom format | 1.75 shows as | What it does |
|---|---|---|
# ?/? | 1 3/4 | Whole number plus a one-digit fraction |
# ??/?? | 1 3/4 | Whole number plus up to two digits |
# ?/16 | 1 12/16 | Always sixteenths |
?/? | 7/4 | Improper fraction, no whole number |
# ?/4 "in" | 1 3/4 in | Quarters with a unit label |
The # is the whole number, ? makes a space for each digit so fractions line up in a column, and a number after the slash fixes the denominator. Text in double quotes, such as "in", is shown as typed. The guide to custom number formats in Excel explains the codes in full.
Round to the nearest 1/8 or 1/16#
A fraction format only rounds the display. The stored value stays the same, so totals can differ from what you see. If the value itself must be an exact eighth, round it with a formula:
=ROUND(B2*8,0)/8This multiplies by 8, rounds to a whole number, then divides by 8. For 2.37 the steps are 2.37 × 8 = 18.96, rounded to 19, and 19 ÷ 8 = 2.375. Format the result as As eighths and it shows 2 3/8.
For sixteenths, use 16 in both places: =ROUND(B2*16,0)/16. To always round up, use ROUNDUP instead of ROUND. Excel also has MROUND, which rounds to a multiple: =MROUND(B2,1/8) gives the same 2.375. The guide on rounding to the nearest 5, 10 or fraction covers these options side by side, and the difference between rounding and decimal places explains why display rounding can mislead you.
Calculate with fractions#
Excel calculates with the stored decimals, so fractions work in any formula. If A2 shows 3 1/2 and B2 shows 1/4, then =A2+B2 returns 3.75. Format the answer cell as a fraction to show 3 3/4.
You can also write fractions straight into a formula as a division: =1/4+1/8 returns 0.375. Inside a formula, 1/4 is just 1 divided by 4, not a date.
To turn a fraction into text, for a label or a report, use TEXT with a fraction format code: =TEXT(B2,"# ?/8"). The result is text, so you can't do sums with it.
Fix fractions imported as text#
Fractions copied from a website or another system often arrive as text, sitting on the left of the cell. A text value like 3/4 won't add up, and converting it with VALUE can turn it into a date.
For simple text fractions such as 3/4, split at the slash and divide, in Microsoft 365 or Excel 2024:
=TEXTBEFORE(A2,"/")/TEXTAFTER(A2,"/")This returns 0.75. In older versions, use =LEFT(A2,FIND("/",A2)-1)/MID(A2,FIND("/",A2)+1,9), which gives the same result.
Practise it#
Formatting and rounding are separate skills, and job tests check both. Practise number formats in the More formatting lesson, then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you type each formula and it checks the answer instantly. Or try rounding to the nearest eighth in the exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Round the measured length in B2 to the nearest eighth of an inch. In C2, multiply by 8, round to a whole number, then divide by 8.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Nearest 1/8 | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 |
Tip: 2.37 × 8 = 18.96, which rounds to 19. Then 19 ÷ 8 = 2.375, which is 2 3/8.
Practise this in a lesson
Frequently asked questions
How do I type a fraction in Excel without it becoming a date?
Type a zero, a space and then the fraction, for example 0 3/4. Excel stores 0.75 and shows 3/4. You can also format the cells as Fraction first and then type 3/4.
How do I convert a decimal to a fraction in Excel?
Select the cells, press Ctrl+1 on Windows or Command+1 on a Mac, choose Number > Fraction and pick a type, such as Up to two digits or As sixteenths. The value doesn't change, only the display.
Why does Excel show 2/7 instead of 3/10?
The Up to one digit format can only use denominators from 1 to 9, so it shows the closest one. Choose As tenths, Up to two digits or a custom format like # ?/10 to show 3/10.
How do I show fractions in sixteenths in Excel?
Apply the Fraction type As sixteenths, or the custom format # ?/16. If the value isn't an exact sixteenth, Excel shows the nearest one, so round it first with =ROUND(A2*16,0)/16 if you need the stored value to match.
Can Excel show improper fractions like 7/4?
Yes. Use a custom number format without the whole-number part, such as ?/? or ?/4. Then 1.75 displays as 7/4 instead of 1 3/4.