How to Calculate Ratio in Excel
Ratios are commonly used in Excel to compare two or more values in finance, accounting, business analysis, and everyday calculations. Excel allows you to calculate ratios using simple formulas and display the results in an easy-to-read format. Whether you want to compare sales, expenses, inventory, or any other values, learning how to calculate a ratio in Excel helps you analyse data accurately and efficiently.
-
Enter Your Data into Excel
Begin by entering the values you want to compare into separate cells.
For example, enter the first value in Cell A2 and the second value in Cell B2.
- Enter the first value
- Enter the second value
- Check that the data is correct
-
Use the Division Formula
A ratio is calculated by dividing one value by another.
In Excel, click on an empty cell and type:
=A2/B2
Then press Enter.
For example, if A2 = 100 and B2 = 20, Excel returns 5, meaning the ratio is 5:1.
- Click an empty cell
- Enter the division formula
- Press Enter
-
Display the Ratio as Text
If you want Excel to display the ratio in the format 5:1, use the following formula:
=A2/GCD(A2,B2)&”:”&B2/GCD(A2,B2)
This formula simplifies the ratio automatically using the Greatest Common Divisor (GCD).
For example, if A2 = 100 and B2 = 20, the result displayed will be:
5:1
- Use the GCD function
- Display the simplified ratio
- Verify the result
-
Copy the Formula to Other Cells
If you have multiple rows of data, you do not need to type the formula repeatedly.
Simply drag the fill handle from the bottom-right corner of the formula cell down the column to apply the formula to all remaining rows.
- Select the formula cell
- Drag the fill handle downward
- Apply the formula to all rows
-
Verify Your Results
Review your calculated ratios to ensure they accurately represent the values entered.
Double-check the cell references and confirm that the original data contains no errors.
- Review the formulas
- Check the cell references
- Correct any data entry mistakes
Also Read: How to Calculate Pips on Forex