Working with fractions and mixed numbers in Excel

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Site Admin
Posts: 312
Joined: Tue Sep 25, 2007 12:41 pm

Working with fractions and mixed numbers in Excel

Post by Admin » Fri Dec 16, 2016 7:19 pm

It's just little bit tricky to enter fractions and mixed numbers in Excel. Just that. Let's make it easy.


When you enter any fraction like 5/6 or 7/8 in your cell, Excel interprets these fractions as date and automatically format the cell as a date.

To enter a simple fraction:

first enter 0 and a space (after entering 0 hit the spacebar)

now enter your fraction number – so it will be like 0 5/6 (as an example)

hit Enter. It's done.


The decimal value of the fraction is shown in the formula bar.


Fractions with more than one digit

In the above example we were dealing with fractions up to one digit, so what about fractions with two or more digits like 11/14 or 217/867?

To enter these fraction numbers you have to go to Format Cells, there under Fraction you can select the type – like Up to two digits for 11/14 or Up to three digits for 217/867.


However, if you enter 33/51 in your cell even you have selected the fraction up to two digits, Excel will round the fraction to 11/17 but will give you alwasys the right decimal values (you can check it on the formula bar)

To fix this problem, select the cell where you want to enter this fraction, right click > go to Format Cells.

Here, under category Custom, change # ??/?? to # 33/51. Now your cell will show exactly this value.


So while working with fractions, you will often need to play with cell formatting, if necessary customising it. That's all.

Mixed numbers

To enter any mixed number like 2 5/8:

enter the first number (here 2),

then hit the Spacebar, now enter the fraction (here 5/8 ),

Press Enter. Done!


Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.

For comments of suggestions, please contact us:

Post Reply