Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Stopping Fractions from Reducing

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Stopping Fractions from Reducing.

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 Word 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. Choose Format | Cells. Excel displays the Format Cells dialog box.
  3. Make sure the Number tab is displayed. (See Figure 1.)
  4. Figure 1. The Number tab of the Format Cells dialog box.

  5. In the Category list, choose Custom. The Type box should contain the characters "# ??/??" (without the quotes).
  6. Change the contents of the Type box to "# ??/28" (again without the quotes).
  7. 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 (2760) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Stopping Fractions from Reducing.

Related Tips:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Ms. R    07 Sep 2016, 10:20
I have tried these tips to enter 32/32 into a cell and it continues to reduce to 1 and that is going to mess up my calculations in a seperate formula. Please help..
Jim    25 Jul 2016, 13:54
Thanks. I've been experimenting, but could never get anything to work. Thanks for your help.
Bob     02 Jun 2016, 07:07
I have always wanted to be an Excel expert but could never find the time because it was not part of my job. I now have more time, recently became a consultant for trades people so Excel is needed from time to time and I am learning. I am drive to learn more.

While I cannot do calculations on the numbers your tip allows me to put the pitch of roofs into my data validation.
jeremy    30 May 2016, 16:56
now how do you auto fill that? Eg.
2/4
2/5
2/6

blank
blank etc.
Jhnell Reid    11 Sep 2014, 17:19
Thank you so much for your help. Today I learnt something new:)
Aaron    02 Jun 2014, 13:14
It's not quite true that you can't force excel to remember how you entered a fraction. If you enter it as text (i.e. use a leading apostrophe, or change the type to Text before you type it), you can!

And you can then do fun things like add numerators together and denominators together!
If your fraction is in cell A1, then your numerator becomes:
=LEFT(A1,FIND("/",A1)-1)
and denominator is:
=RIGHT(A1,LEN(A1)-FIND("/",A1))
swetha     03 Sep 2013, 08:42
It was really useful..ive checked many sites but this was really helpful...thnx
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.