How to use fractions in Excel

If you've ever worked with Excel, chances are you've used it to store and calculate different types of values, such as whole numbers, decimals, and percentages. However, there may also be times when you want to work with fractions in Excel, such as 1/2 (one-half) or 2/3 (two-thirds), rather than use the decimal value.

For example, let's say you have a recipe for chocolate chip cookies and you'd like to enter it into Excel. The recipe calls for things like 1/4 teaspoon of salt , so you'll want to enter these values as fractions in column B.

Before we enter the ingredients, we'll need to make a small change to the spreadsheet. As you may have learned in our Understanding Number Formats lesson from our Excel Formulas tutorial, you can apply a special kind of formatting—known as number formatting —to any cell. Excel has a fraction number format that will allow us to enter our values as fractions, so we'll select column B and then use the Number Format command on the Home tab apply the fraction number format.

Note that we're using Excel 2013 in this example, but this will work the same way in Excel 2010 and Excel 2007. If you're using Excel 2003 or earlier, select the desired cells and press Ctrl+1 on your keyboard to apply number formatting. Please also note that this feature is not available in Google Sheets.

Now that we've applied number formatting, we're ready to enter the fractions in column B.

Notice that it can also display mixed fractions , such as 2 3/4 (two and three-fourths) . If you select any these cells, you'll see that Excel is actually treating the value like a decimal number in the formula bar—the fraction number format just changes the way the value is displayed in the spreadsheet. For example, 2 3/4 is the same as 2.75.

You can even use fractions in formulas and functions. For example, let's say this recipe yields about two dozen cookies. If you wanted to make four dozen cookies, you could use Excel to double the recipe . If we wanted to double the amount of salt in our recipe, we would multiply the value in cell B2 by 2; the formula for this would be =B2*2 . We can then use the fill handle to add the formula to the other cells in column C.

Here are the new fractions for our doubled recipe! As you can see, this number format makes it easy to work with fractions in Excel, especially if you don't want to convert your fractions into decimals.