Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Stopping Fractions from Reducing.

Stopping Fractions from Reducing

Written by Allen Wyatt (last updated November 13, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


Excel allows you to enter fractions into cells, provided you preface the entry with a zero and a space. Thus, if you wanted to enter a fraction such as 18/28, you would enter 0 18/28 into the cell. (If you just enter 18/28, then Excel assumes you are entering either a date or text.)

When you enter your fraction, Excel does two things during the parsing process. First, it formats the cell as a fraction, based on the number of digits in the denominator of the fraction. In the case of the fraction 18/28, there are two digits in the denominator, so the cell is automatically formatted as a fraction of up to two digits. (See the Number tab of the Format Cells dialog box.)

The second thing that happens is that Excel converts the number into its decimal equivalent. In other words, Excel stores the number internally as 0.642857142857143, which is what you get when you divide 18 by 28. At this point, the fraction no longer exists; the number is a decimal value.

After you enter any value into Excel, it automatically recalculates your worksheet. With the parsing done, and the new value entered, Excel recalculates and redisplays values. Remember—the value in the cell is now 0.642857142857143, and to redisplay the value, Excel sees that it is supposed to use a fraction of up to two digits. The smallest fraction it can do this with is 9/14. Thus, this is what Excel displays in the cell—9/14 instead of 18/28.

Because of the parsing process that Excel follows, there is no way that you can force Excel to remember your fractions exactly as you entered them. After all, Excel doesn't store fractions, it stores decimal values. You can, however, change the format used by Excel to display the value in a particular cell. For instance, if you wanted the cell into which you entered 18/28 to display the fraction with 28 as the denominator, then you could follow these steps:

  1. Select the cell you want to format.
  2. Display the Home tab of the ribbon.
  3. Click the small icon at the bottom-right of the Number group. Excel displays the Format Cells dialog box.
  4. Make sure the Number tab is displayed. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. In the Category list, choose Custom. The Type box should contain the characters "# ??/??" (without the quotes).
  7. Change the contents of the Type box to "# ??/28" (again, without the quotes).
  8. Click on OK.

Again, remember that this only changes the display of the values in Excel, not the actual values themselves—they are still decimal values. The only way to have Excel remember exactly what you entered is to enter the fraction as text (format the cell as Text before making your entry), but there is a drawback to this. Once entered as text, you cannot use the fraction in any calculations.

If this is a problem for your needs, then you may want to consider putting the numerator in one cell and the denominator in another cell. This provides a way to remember what you entered, but still be able to use the numerator and denominator in a formula.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12043) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Stopping Fractions from Reducing.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding Graphic Linking

Word provides a couple of different ways that graphics can be linked to your document. How you control the method used ...

Discover More

Applying Borders to Tables

Want to change the borders that Word adds to your tables? You have complete control over the way your borders appear, ...

Discover More

Changing Currency Formatting for a Single Workbook

Currency is formatted differently in different corners of the world. Most formatting uses periods and commas to indicate ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Shrinking Cell Contents

Need to cram a bunch of text all on a single line in a cell? You can do it with one of the lesser-known settings in Excel.

Discover More

Creating 3-D Formatting for a Cell

The formatting capabilities provided by Excel are quite diverse. This tip examines how you can use those capabilities to ...

Discover More

Setting Orientation of Cell Values

Need the contents of a cell to be shown in a direction different than normal? Excel makes it easy to have your content ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.