BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andrew Gould on 03 October 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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:
|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 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:
|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.
A fraction with a fixed denominator.