How to convert between fraction and decimal in Excel?

When dealing with fraction numbers in Excel—such as those entered or imported as 22/3—you may often need to transform them into standard decimal numbers for easier calculation or further data processing. This method is suitable when your data is entered as a recognized fraction cell format. Here’s how you can perform this conversion directly using Excel's built-in format options:

1. Select the cells containing the fraction values you wish to convert. Next, right-click on the selection and choose Format Cells from the context menu. See the screenshot below:

2. In the Format Cells dialog box, go to the Number tab, then select General from the Category list. Click the OK button to confirm.

After clicking OK, all selected fractions will be displayed as decimal numbers. For example, 22/3 will be shown as 2.66666667. See screenshot:

Note: Alternatively, you can perform the same conversion by selecting the cells, then clicking Home > Number Format dropdown > General on the Ribbon. This is particularly convenient for quickly switching formats for multiple cells.

Applicable scenarios: This approach works best when the fractions are already properly recognized by Excel as number-type fractions, not as text.

Tip: If your fraction data is stored as text (e.g., entered as "22/3" but aligned left in the cell), this method will not convert it. 

Common issues and troubleshooting: If a cell remains unchanged or you see an error after formatting, confirm whether the original fraction is truly in Excel’s number format and not entered as text. Also, merged cells or protected worksheets can prevent format changes. If needed, unmerge cells or unprotect the worksheet before re-trying the format change.

To display standard decimal numbers as fractions in Excel, you can use format settings to change the appearance of the numbers. This is particularly useful when you want to present data in a more familiar or traditional fractional format (for example, for recipes, measurements, or engineering data). Here’s how to perform this conversion:

1. Select the decimal numbers in your worksheet that you wish to convert. Right-click the selection and choose Format Cells from the context menu. See this screenshot:

2. In the Format Cells dialog box under the Number tab, click to highlight Fraction in the Category list. Choose a fraction type in the Type list box based on your needs, then click the OK button.

Once completed, Excel will format your decimals as fraction numbers according to your chosen style. See screenshot:

Tip: As an alternative, you can use Home > Number Format dropdown > Fraction for quick conversion without opening a dialog.

To programmatically display a decimal as a fraction in a new cell, you can use the TEXT function with a custom number format, such as "# ?/?". This is helpful if you want to convert values dynamically with formulas for reporting or data processing purposes, rather than changing the original cell’s number format.

1. Suppose your decimal value is in cell A1. Enter the following formula into your target cell (for example, B1):

=TEXT(A1,"# ?/?")

2. Press Enter to show the decimal in fractional form. If you want more denominator precision, you can use formats such as "# ??/??" for up to two-digit denominators (e.g., twelfths or hundredths).

Copy down or across as needed to apply to additional data. This formula leaves the original decimal untouched, displaying the fraction separately. The result is always a text string, suitable for visual presentation but not direct calculation.

Applicable scenarios: Use this approach when you need fractional presentation next to the decimal value, or in reports and exports where number format alteration is not practical.

Precautions and tips: Since the output is text, further numeric calculations with this cell require reconversion or referencing the original numeric value. Adjust the format code to control the fraction’s precision based on your needs.

Troubleshooting: If the result seems overly simplified or odd (e.g., "1/1"), try increasing denominator places in the format (“# ???/???”) or checking the input decimal’s value.

How to add / subtract days / months / years to date in Excel?