Software

An easier way to enter fractions in Excel

Enter fractions into Excel without formatting

Not too long ago, Mary Ann Richards told you to enter a fraction in Excel by formatting the cell first. Why should you bother? Well, if you don’t, Excel interprets a fraction as a date. For example, if you enter ½, Excel returns 2-Jan. Now, Mary Ann suggested that you format the cell first, which definitely works and might be the most appropriate way to go. However, for a quick fix, simply enter a 0 before the fraction. For instance, to enter the literal fraction ½, enter 0 ½ instead (there’s a space between 0 and ½). It works like a charm!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
Dave
Dave

Try entering 0 1/25. Doesn't work!

ralph
ralph

Cool! It never ceases to amaze me how many little tricks are available in MS Office programs. Ralph

mk3ps2
mk3ps2

Great shortcut. After learning Excel (2.0) using only keyboard and no mouse, I thought I had seen all the data entry shortcuts. Thanks.

cmr_exec
cmr_exec

You're are correct. When keying it fractions, it is easier to enter them this way as long as the denominator is one or two digits. If the denominator is more than two digits, you will have to reformat the cell to Fractions-Up to three digits.

LocoLobo
LocoLobo

QA here used to demand different significant figures in different places in a spreadsheet. One particular place was in a dilution factor where we were supposed to place 2 sig figs. That is fine if your dilution factor is 1/2 or 1/4 but numbers like 1/3 entered as 0.33 cause problems. Put it in as the fraction 1/3 (look ma only 1 sig fig!) and the number is better.

ssharkins
ssharkins

I tried it and you're right -- that's definitely good to know. Thank you!

Editor's Picks