Situatie
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.
Solutie
By default, Excel converts fractions that look like the month/day format into a date. For example, typing 1/2 into 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 to change the number format date manually.
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 type 0 2/3 and 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.
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 changes before you 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.
Excel fractions simplify automatically by default. For example, if you type 4/6 into a cell formatted to the Fraction number format, it’ll change to “2/3”. Likewise, if you type a top-heavy fraction, such as 16/3, it will convert to an improper fraction (“5 1/3”) or an integer.
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 number 6). 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).
Leave A Comment?