If you’ve ever worked in Excel, chances are you’ve used it to store and perform calculations on various types of data, such as integers, decimals, and percentages. However, it may happen that you need to work in Excel with values in the form common fractionsSuch as 1/2 (one second) or 2/3 (two-thirds), without converting to decimal fractions.
For example, we have a recipe for chocolate chip cookies and we want to format it in Microsoft Excel. The recipe requires an ingredient – 1 / 4 teaspoon salt, it must be written in column B, as an ordinary fraction.
Before we start entering the ingredients, we need to change something in our table. As you probably remember (including from our lessons), you can apply special formatting to any cell in Excel, i.e. number format. Excel has a fractional number format that allows you to enter values as fractions. To do this, we highlight column B and then on the tab Home (Home) in the drop down list Number Format (Number format) select item Fraction (Minor).
Please note that we are working in Excel 2013 in this example, but this method will work in Excel 2010 and 2007 in the same way. For Excel 2003 and earlier, select the desired cells and press the keyboard shortcut Ctrl + 1to set the number format. Please note that this option is not available in Google Sheets.
Now that the number format is set up, we are ready to enter fractions in column B.
Note that numbers can be displayed as mixed fractions, in the form 2 3/4 (two and three quarters). If you select one of these cells, you’ll see in the formula bar that Excel actually treats those values as decimals – the fraction format only changes how the number is displayed in the cell. For example, 2 3/4 it’s the same as 2.75.
You can use common fractions in formulas and functions. Imagine that this recipe is for two servings of cookies. If you need to make four servings of cookies, you can double the recipe using Excel. If we need to double the amount of salt in a recipe, we must multiply the value of cell B2 by 2; the formula will be like this: = B2 * 2. And then we can copy the formula to other cells in column C by selecting the cell and dragging the autofill handle.
We’ve got new fractional values for our doubled recipe! As you can see, using such a number format in Excel makes it much easier to work with fractions, especially if you do not want to convert ordinary fractions to decimals.