This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using Fractions in Microsoft Excel
If you've ever tried to enter fractions in Microsoft Excel you'll probably have become frustrated when Excel insists on converting them into dates! Read this blog to see how to prevent this from happening.

Posted by Andrew Gould on 03 October 2011

In this blog

The Problem With AutoFormat
Applying a Specific Format to Cells
Fixing the Denominator

Using Fractions in Microsoft Excel

Entering fractions into Microsoft Excel can be tricky due to the AutoFormat feature. This article explains how to get around this issue and ensure that when you enter a fraction into a cell, it stays as a fraction!

The Problem With AutoFormat

The AutoFormat feature in Microsoft Excel is designed to recognise certain patterns of inputs and to apply specific formatting to the value entered into a cell. Some examples of what the AutoFormat feature does can be seen below:


Entering this value into a cell...	..results in this date format.


If you enter a currency symbol followed by a number with a single decimal place...	...the AutoFormat feature will automatically apply a second decimal place.

In many cases the AutoFormat feature is quite helpful, but not when it comes to entering fractions in a cell:

$Entering a fraction$
Trying to enter a fraction of five eighths...	...results in AutoFormat changing the value to a date.

This is annoying to say the least! Fortunately, there is a simple way around this issue.

Applying a Specific Format to Cells

The trick to making sure that when you enter a fraction into a cell it stays as a fraction is to format the cell before you enter the value. To do this:

Select the cell, or cells, you want to enter fractions into.
Right-click on the cell(s) and choose Format Cells... (or press CTRL + 1 on the keyboard).
On the dialog box that appears, click the Number tab.

$Formatting as fractions$

Formatting cells to contain fractions.

From the list on the left of the dialog select Fraction.
Choose how many digits you want to display in the fraction.
Click OK.

You should now find that when you enter a fraction into a cell, it remains as a fraction:

You can see that the cell is formatted to show a fraction, but the formula bar shows the true, decimal value that is contained in the cell.

You must make sure that you format the cell as a fraction before you enter the value into it. Entering the value 5/8 into a cell with the General format means that Excel will convert the value into a date in the current year, e.g. 5/8/2011. When you try to format this as a fraction you'll see the serial number of the date, e.g. 40760, rather than the original value you typed in.

Fixing the Denominator

One final potential problem with entering fractions into a cell is Excel's determination to always use the lowest common denominator:

$Entering fraction in cell$	$Excel simplifying fraction$
If we enter the fraction four eighths into a cell...	...Excel will automatically convert it into one half.

You can fix the value of the denominator in one of two ways:

Use one of the additional options of the Fraction number format in the Format Cells dialog box.

You can choose one of the options shown on the right to fix the denominator to one of several predetermined values.

Create a custom number format to fix the denominator to a value of your choice.

Choose the Custom option on the left hand side and then type your specific format into the Type: textbox. The format shown here is # ??/18 and will format fractions as eighteenths.

The end result is that the fractions you enter into a cell will remain as you've typed them.