Let’s take a look. In column B of our table we have a set of numbers in General format that can be expressed as fractions. Let’s first copy these numbers to the rest of our table. Now let’s apply the Fraction format in columns C through G using the Number Format menu on the ribbon. As we can see, Excel converts most of the values to fractions. However, some numbers aren’t converted, while others are just a close match. If we check the options available for the fraction format in the Format Cells dialog box, we can see a number of built-in combinations, and the current selection is for fractions “up to one digit.” That’s the correct setting for column C. For column D, we want fractions up to two digits. In the same way we can set fractions up to three digits for Column E. Looking at the table, we can see that some fractions are more accurate than others. For example, .97 was first expressed as 1, then as 32/33rds, then as 97/100ths. Excel will display the best match available for each fraction format. Let’s try quarters and eighths for the last two columns. We’ll use the keyboard shortcut Ctrl-1 to get to Format Cells. Again, we can see many fractions are estimates, while others are exact. You can enter fractions directly in cells pre-formatted with the Fraction format. You may want to adjust the fraction type in some cases. For cells using General format, you might have trouble typing in a fraction, because Excel may interpret the fraction as a date. You can work around this problem by entering a zero and a space first before the fraction. For example, to enter four fifths, enter “zero space 4 / 5”
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
