Quick Links
In a program like Excel, which is designed to handle numbers, you’d expect to be able to use fractions without any hiccups. However, because Excel is programmed to automate and simplify, it often converts your fractions into something other than what you typed. Here’s how to stop it.
Stopping Fractions From Turning Into Dates
By default, Excel converts fractions that look like the month/day format into a date. For example, typing1/2into an unformatted cell results in the value changing to “02-Jan” (or “01-Feb” in the UK). This is because people often use dates in Excel tables, so the program thinks it’s being helpful by saving you the time it would take tochange the number format to Datemanually.
There are two ways to prevent this from happening. The first method is to type a zero and a space before the fraction. For example, if I wanted a cell to display “2/3”, I would type0 2/3and press Enter.
As well as keeping your number as a fraction, this method automatically switches the cell’s number format from General to Fraction, so the value you typed can be used in other calculations or Excel operations. The formula bar also shows the value as a decimal.
Even though you don’tneedto change the number format to “Fraction” if what you type isn’t convertible to a date, it’s good practice to apply the correct number format to every cell you use in your spreadsheet.
If you plan to type lots of fractions into your sheet, select the relevant cell or cells where your fraction will go, or click the column or row header if you want to affect every cell in that range. Then, expand the drop-down menu in the Number group of the Home tab, and click “Fraction.”
Changing the number format from Date to Fraction doesn’t work if the cell already contains a date. You must make the formatting changesbeforeyou add the values.
While these techniques work well most of the time, Excel will simplify fractions where possible, including converting top-heavy fractions into mixed or whole numbers. In this case, you need to prevent Excel from simplifying your fractions. Keep reading to find out how!
Preventing Fractions From Simplifying
Excel fractions simplify automatically by default. For example, if you type4/6into a cell formatted to the Fraction number format, it’ll change to “2/3”. Likewise, if you type a top-heavy fraction, such as16/3, it will convert to an improper fraction (“5 1/3”) or an integer.
To stop this automatic simplification, you need to tell Excel what denominator to use. In other words, to keep “4/6” as is, you must force Excel to stick with “6” as the bottom number in the fraction.
With the relevant cell or cells selected, click the Number Format dialog box launcher in the corner of the Number group on the Home tab.
Then, click “Custom” in the left-hand menu.
Clear any existing text from the field box on the right, and type a question mark (?), followed by a forward slash (/), followed by the denominator you want to fix (in the example above, the number6). Now, click “OK.”
Excel will now always convert any fractions you type into these cells to a fraction with 6—or whichever number you specify—as the denominator.
To force Excel to convert top-heavy fractions (15/7) to mixed numbers (2 1/7), the custom number format to type is# ?/?(make sure you add a space between the octothorpe and the first question mark). To lock the denominator, you would specify this in the custom number format (for example,# ?/7).
Turning Fractions Into Text (And Why You Shouldn’t)
One way to make Excel keep exactly what you type is to add an apostrophe before the fraction, which has a similar effect to changing the cell’s number format to Text before you type the fraction.
However, while these approaches might seem like quick, convenient fixes, after you press Enter, the fraction’s numerical value will not be recognized for use in formulas, calculations, and other Excel operations.
As well as allowing you to change a cell’s number format to Date, Fraction, or Text,Excel lets you choose from nine other number formats, such as Currency, Time, and Scientific, so you’re able to make sure the program reads your data accurately.
