Contextures

Home > Formulas > Text > Ratio

Calculate Ratio in Excel

How to use Excel functions to show a ratio, such as 4:3 or 8:5. To calculate ratio in Excel, you can use the GCD function. Or use TEXT and SUBSTITUTE functions to calculate proportion in Excel.

GCD formula for ratio

Video: Calculate Ratio in Excel

To calculate a ratio between 2 numbers in Excel, you can use the GCD function (Greatest Common Divisor) or use the TEXT and SUBSTITUTE functions. Watch this video to see how to use these formulas to calculate proportion in Excel.

Written instructions are below the video, as well as the full video transcript.

Video Timeline

  • 0:16 Ratio introduction
  • 1:20 GCD formula
  • 4:03 TEXT and SUBSTITUTE formula

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Video Transcript: Calculate Ratio

When you want to compare numbers, one way to do that is by using a ratio such as four to three or five to four. There's no function in Excel that does this, but we can use other functions to create a ratio.

Now here we have numbers 400 and 300. To show those as a ratio, we don't want to show 400 to 300. We want to break that down to the smallest numbers possible.

If we look at these two numbers, the first step is deciding what is the biggest number that could go into each of these two numbers and divide evenly.

Just by looking at them, they're both hundreds. I would guess that a hundred would go into both of these numbers evenly. And that's probably the biggest number that we can use. So that is the Greatest Common Divisor.

  • I'm just going to type that here.
  • If I look at 400, I could divide 100 into 400 four times and into 300 three times.
  • So our ratio would be four to three.

So it's easy to look at that one and figure it out without any formulas in Excel. But these are some screen dimensions and it's not as easy to look at those numbers and decide.So we'd need formulas to calculate those. So I'm going to remove these.

Use GCD Function

There is a function in Excel that will calculate the Greatest Common Divsor.

  • So in this cell, I'm going to type equals GCD
  • and then click on the first number, type A comma,
  • and the second number and close the bracket.

When I press Enter, it shows that yes, I was correct in my guess that 100 is the greatest common divisor for those two numbers.

  • And I'll double click on this fill handle.

And that shows me the greatest common advisor for each of these other screen dimensions.

Divide the Results

So now that we have this number, we can do the next step, which is how many times can we divide this number into this one? And to do that, we can use the division operator.

  • So I'll type here equals the width divided by our greatest common divisor,
  • and that's a four,
  • double click to copy that down.
  • And the same thing in this cell. We're going to divide the height by our greatest common divisor.
  • And so that's three and there are the rest.

And now to get the ratio, I just want to show this number and this one with a colon between them.

  • Start with an equal sign.
  • Click on the first number to join characters in a string.
  • I'm going to use the ampersand character.
  • Then in double quotes, I'll type a colon, another ampersand, and the second number.

So we can now see the width and height as a ratio and just fill that down.

Create a Single Formula

Now instead of showing that in four separate cells, I could do it all in one formula.

And if we look at the final number here, we're using E4 colon F4. So our first number is the width divided by the greatest common divisor. The second number is the height divided by the greatest common divisor.

  • So in this cell type, an equal sign,
  • then the width divided by,
  • and then the formula for the greatest common divisor,
  • and then our colon,
  • and then the height divided by the greatest common divisor
  • and press enter.

And we get the same ratio all in one cell instead of the four separate cells.

  • I'll double click to fill that down, and you can see the results are all the same.

So you can either break it down into separate steps or do it all in one formula.

Use TEXT and SUBSTITUTE Functions

Another way we can show ratio is by using the TEXT and SUBSTITUTE functions.

So here I've got the same numbers, and for this technique, we're going to be dividing the numbers and then showing them as a fraction, and finally replacing the slash in the fraction with a colon.

So the first step will be division.

  • I'll click here equals the width, divided by the height,
  • and we can see that as a number with decimal places.
  • To get that as a fraction, I can just refer to that division cell.

Now to show this as a fraction, I'm going to format the cell.

  • So on the Home tab, I'll click the Number Format button.
  • If I look down the list of categories, here's fraction, and I can show up to two digits.
  • I'll select that one.

So that shows it as one and a third. So that's really three thirds and one third, which would be four thirds. And that's the ratio that we want, but we want it to show four thirds instead of one and a third.

  • So I'm going back into the number format and go down to custom.
  • And it's going to show us here how it has formatted that.
  • So I don't want a number and then the fraction, so I'm going to delete that part of it.
  • Click OK.

And now it shows what I want, which is that four thirds.

  • And I'll double click here to fill it down.

Format with TEXT and SUBSTITUTE

In the custom formatting, we can't replace that slash with a colon because the colon is used for times, so we won't get a correct result. But we can use the TEXT function to create a custom custom format, and then SUBSTITUTE the slash with a colon in this cell.

I'm going to use the TEXT function to create that custom format type.

  • An equal sign TEXT,
  • And we want to divide the width by the height
  • and then a comma.
  • And now we have to show how we want to format that.
  • And we're going to use something similar to that custom format that we saw.
  • So in double quotes, number sign slash,
  • then I'll type five or six number signs as the lower part of the fraction just
  • so it can accommodate any fractions that we create.
  • Double quote, close the bracket.

And there's our fraction,

So that's very close to what we want, but we want to see a colon here instead of a slash. So in this cell, I'm going to use the SUBSTITUTE function, which will replace one thing with another

  • equal SUBSTITUTE open bracket.
  • And I'll click on this cell where we have our calculation.
  • And the old text is the slash.
  • So in double quotes, I'll type a slash comma
  • and the new character we want, the new text is a colon.
  • So in double quotes, I'll type a colon, close the brackets, and press Enter.

And there's our ratio.

  • Double click to fill that down.

And we get the same ratios we had on the other sheet.

Create Single Cell Formula

And again, we could do this all in one formula, instead of breaking it out into different cells.

  • Here, we'll start with an equal sign and SUBSTITUTE.
  • And then our TEXT formula dividing the width by the height,
  • our custom format that we want to use, which is the fraction.
  • And in the SUBSTITUTE, we want to replace the slash with a colon.

And there, all in one formula, is our ratio using TEXT and SUBSTITUTE.

  • And I'll just fill that down

Use Either Formula

So you can use either formula. The only note is that:

  • in older versions of Excel, Excel 2003 and older, you have to have the Analysis ToolPak installed in order to use the greatest common divisor.
  • And in any version of Excel, you can use SUBSTITUTE and TEXT

Calculate Ratio with GCD

In this example, there is a list of screen dimensions, with the first width -- 400 -- in cell B4 and the first height -- 300 --in cell C4.

How can you calculate a ratio in Excel, to see those screen dimension the way they're in an advertisement for computer monitors? Or the rations that are the options when you're cropping photos in your photo editing software?

For example, you might crop a photo to a 4:3 ratio, or save a video in 16:9 ratio.

Using GCD Function

To calculate the ratio for each screen, the formula will use the GCD function, which returns the Greatest Common Divisor, for two or more integers.

The result of the GCD function is the largest integer that divides both numbers (the greatest common denominator), without leaving a remainder.

NOTE: In Microsoft Excel 2003 and earlier, the Analysis Toolpak must be installed, to use the GCD function.

GCD Function Syntax

The GCD function is in the Math and trigonometry functions category. The GCD function has the following syntax for its arguments:

GCD(number1, [number2],...)

  • Number1: required- first integer for the common divisor calculation
  • Number2: optional - up to 255 numbers are allowed in the GCD arguments

Notes:

If any of the numbers are not integers, those numbers will be truncated to zero decimal places.

All numbers must be zero or above, or the result will be a #NUM! error

Test the GCD Function

In this simple example, width numbers are in column B and Height numbers are in column C.

  • The first number, 400, is in cell B4 on the Excel spreadsheet.
  • The second number, 300, is in cell C4.

In column D, to see the largest number which will divide equally into the numbers in B4 and C4, enter this formula in cell D4:

  • =GCD(B4,C4)

The GCD result is 100. That is the largest integer that divides both numbers (300 and 400), without leaving a remainder

Create the Ratio Formula

To calculate the ratio, the width will be divided by the GCD and the height will be divided by the GCD. A colon will be placed between those two numbers.

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

  • =B4/GCD(B4,C4)&":"&C4/GCD(B4,C4)

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

Cell References

In the above example ratio formula, I used a relative reference to each cell -- B4 and C4. That way, the row numbers in the references will adjust automatically, when the formula is copied down to the rows below.

If you use an absolute cell reference, like $B$4 and $B$4, the dollar sign locks the row numbers and column letters. Those won't change if you copy the formula down or across.

GCD formula for ratio

Video: Ratio Formula with TEXT and SUBSTITUTE

In this video, see how to create a ratio formula in 3 steps, using division, TEXT function and SUBSTITUTE function.

There is another example, below the video, with written steps.

Video Timeline:

  • 00:00 Introduction
  • 00:23 Step 1 - Divide
  • 00:37 Step 2 - Fraction
  • 01:17 Step 3 - Ratio

 

Ratio With TEXT and SUBSTITUTE

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

In this example, there is a list of screen dimensions, with the first width -- 400 -- in cell B4 and the first height -- 300 --in cell C4.

To calculate the ratio for each screen, the formula will divide the width by the height, and format the result as a fraction. Then, the slash will be replaced with a colon, to create the ratio.

Test the TEXT Function

To see the result as a fraction, enter this formula in cell D4:

=TEXT(B4/C4,"#/######")

The result is 4/3.

Create the Ratio Formula

To calculate the ratio, the width will be divided by the height, and formatted as a fraction. A colon will replace the slash.

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. go to top

TEXT and SUBSTITUTE formula for ratio

Video: Introduction to Ratios

For a short introduction to Ratios, you can watch this video from the Math with Mr. J channel on YouTube. It shows two examples for calculating a ratio, with a pencil, instead of Excel.

Understanding what a ratio is, and the different ways you can show it, might make it easier for you to understand how the GCD function helps create a ratio in Excel.

Get the Sample Files

Screen Dimensions: Download the zipped sample Excel Ratio Formulas file with the list of screen dimensions and ratio formulas

Ratio Formula 3 Step: Download the zipped sample Excel Ratio 3 Step Formulas file with ratio formula steps for Divide, Fraction and Ratio.

Excel Function Tutorials

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Functions

INDIRECT Function

 

 

Last updated: February 28, 2023 3:56 PM