In a program like Excel, which is designed to deal with numbers, you’d count on to have the ability to use fractions with none hiccups. Nevertheless, as a result of Excel is programmed to automate and simplify, it usually converts your fractions into one thing apart from what you typed. Here is how you can cease it.
Stopping Fractions From Turning Into Dates
By default, Excel converts fractions that appear like the month/day format right into a date. For instance, typing 1/2 into an unformatted cell ends in the worth altering to “02-Jan” (or “01-Feb” within the UK). It is because individuals usually use dates in Excel tables, so this system thinks it is being useful by saving you the time it could take to change the number format to Date manually.
There are two methods to stop this from occurring. The primary technique is to sort a zero and an area earlier than the fraction. For instance, if I needed a cell to show “2/3”, I’d sort 0 2/3 and press Enter.
In addition to preserving your quantity as a fraction, this technique mechanically switches the cell’s quantity format from Common to Fraction, so the worth you typed can be utilized in different calculations or Excel operations. The components bar additionally exhibits the worth as a decimal.
Although you do not want to vary the quantity format to “Fraction” if what you sort is not convertible to a date, it is good follow to use the right quantity format to each cell you employ in your spreadsheet.
In the event you plan to sort a lot of fractions into your sheet, choose the related cell or cells the place your fraction will go, or click on the column or row header if you wish to have an effect on each cell in that vary. Then, increase the drop-down menu within the Quantity group of the Dwelling tab, and click on “Fraction.”
Altering the quantity format from Date to Fraction does not work if the cell already comprises a date. You need to make the formatting adjustments earlier than you add the values.
Whereas these methods work nicely more often than not, Excel will simplify fractions the place attainable, together with changing top-heavy fractions into combined or complete numbers. On this case, it’s good to forestall Excel from simplifying your fractions. Maintain studying to learn how!
Stopping Fractions From Simplifying
Excel fractions simplify mechanically by default. For instance, should you sort 4/6 right into a cell formatted to the Fraction quantity format, it will change to “2/3”. Likewise, should you sort a top-heavy fraction, similar to 16/3, it should convert to an improper fraction (“5 1/3”) or an integer.
To cease this automated simplification, it’s good to inform Excel what denominator to make use of. In different phrases, to maintain “4/6” as is, you have to drive Excel to stay with “6” as the underside quantity within the fraction.
With the related cell or cells chosen, click on the Quantity Format dialog field launcher within the nook of the Quantity group on the Dwelling tab.
Then, click on “Customized” within the left-hand menu.
Clear any present textual content from the sector field on the correct, and sort a query mark (?), adopted by a ahead slash (/), adopted by the denominator you wish to repair (within the instance above, the quantity 6). Now, click on “OK.”
Excel will now at all times convert any fractions you sort into these cells to a fraction with 6—or whichever quantity you specify—because the denominator.
To drive Excel to transform top-heavy fractions (15/7) to combined numbers (2 1/7), the customized quantity format to sort is # ?/? (be sure to add an area between the octothorpe and the primary query mark). To lock the denominator, you’d specify this within the customized quantity format (for instance, # ?/7).
Turning Fractions Into Textual content (And Why You Should not)
One technique to make Excel preserve precisely what you sort is so as to add an apostrophe earlier than the fraction, which has the same impact to altering the cell’s quantity format to Textual content earlier than you sort the fraction.
Nevertheless, whereas these approaches may look like fast, handy fixes, after you press Enter, the fraction’s numerical worth is not going to be acknowledged to be used in formulation, calculations, and different Excel operations.
In addition to permitting you to vary a cell’s quantity format to Date, Fraction, or Textual content, Excel lets you choose from nine other number formats, similar to Foreign money, Time, and Scientific, so you may make certain this system reads your information precisely.