Calculate a Ratio in Excel

In Excel, if you divide 2 by 8, the result is 0.25. If you format the cell as a fraction, the cell might show 1/4 as the result. But how can you calculate a ratio in Excel? See the written steps and video below.

Here’s a worksheet with 2 divided by 8, with the result showing as a ratio, in cell D2, and as a decimal number in cell D4.

Video: Show a Ratio in Excel

Watch this short video to see how to show a ratio in Excel. It shows 2 different formulas that you can use. The written steps are below the video.

Show a Ratio in Excel

What if you want to show the result as a ratio? How can you get the cell to show 1:4 instead of 1/4?

There may be other ways, but here’s the formula that I used.

=B2/GCD(B2,C2) & ":" & C2/GCD(B2,C2) 
Show a Ratio in Excel
Show a Ratio in Excel

How the Ratio Formula Works

NOTE: This formula requires that the Analysis ToolPak be installed, in Excel 2003 and earlier versions.

The formula:

  • divides each cell by the greatest common divisor (GCD)
  • puts a colon between the two numbers — “:”

Another Ratio Formula

Another way to calculate ratio is with the TEXT and SUBSTITUTE functions — these functions work in all versions of Excel, without the Analysis Tookpak installed.

In this example, there is a list of video dimensions on the worksheet.

TEXT and SUBSTITUTE formula for ratio

To see the ratio, enter this formula in cell E4:

=SUBSTITUTE(TEXT(B4/C4,”#/######”),”/”,”:”)

The result is 4:3 — the ratio for those screen dimensions.

How This Ratio Formula Works

In this formula:

  • First, the width(B4)  is divided by the height (C4)
  • The TEXT function formats the result as a fraction — “#/######”
  • The SUBSTITUTE function replaces the slash with a colon — “/”,”:”

Get the Sample File

To see the sample file used in the video, with both Ratio formulas, go to the Excel Ratio Formula page on my Contextures website. The zipped file is in xlsx format, and does not contain any macros.