Phone (01457) 858877 or email
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.
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 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.
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:
Formatting cells to contain fractions.
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.
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:
You can choose one of the options shown on the right to fix the denominator to one of several predetermined values.
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.
Comments on this blog
This blog has one comment: