For a better understanding of data sometimes we show data in a graph. This is mostly used for financial calculations and office presentations. Excel has some built-in features to calculate and show the graph in the workbook. In this article, I will explain how to graph ratios in excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Simple Methods to Graph Ratios in Excel
Today I am going to share with you 2 simple methods to graph ratios in excel. Stay tuned.
1. Convert Ratios to Decimal Values to Graph Ratios in Excel
To graph a ratio you can convert ratios to decimal values and show them in a graph.
Suppose we have a dataset of some Student’s Name, their Height, Weight, and Ratio of height and weight. Now we are going to graph the ratio.
- Firstly we are going to calculate the decimal value from the dataset.
- Select a cell to get the decimal value. Here I have selected cell (F5).
- Apply the formula-
- Hit Enter to get the value.
- Pull the “fill handle” down to get the numbers in all cells.
- Thus we will get our decimal values to make a graph.
- Now selecting the decimal values from the column go to the “Insert” option from the home ribbon.
- Click on the “Bar column” icon and select the left one from the “2-D Column”.
- Here we have successfully made our graph. It’s time to edit the graph. Let’s do it.
- Select the graph to edit.
- Now from the filer option choose “Select Data”.
- A new window will appear named “Select Data Source”.
- From there hit the “Edit” option to edit data.
- A new window will pop up named “Axis Labels”.
- From the dataset select the range of names and press OK.
- Thus we will get our desired graph which shows ratios from the dataset.
2. Alter Constant Ratios to Decimal Values to Graph Ratios
While working with a dataset sometimes you get constant ratios. At that time you might need to calculate the graph in a different way. In this method, I am explaining how you can make a graph with constant ratios.
Here we have a dataset of a shop. In this dataset, we have the Total Number of Sold products and their Ratio. To plot the graph firstly we will calculate the products sold by A & B.
- Choose a cell (D5) to apply the formula.
- Put the formula in the cell-
- Press Enter and then drag down the “fill handle” to get the output.
- Here we have our values which were sold by A.
Now let’s calculate the product sold by B.
- Select a cell. Here I have selected cell (E5).
- Apply the formula-
- Hit Enter and drag down the “fill handle”.
- Thus we will get the output in the cell (E5:E11) showing products sold by B.
- Select cells (D4:E11) to plot the graph.
- Selecting the cells click on the “Insert” option.
- Now select a linear chart from the options.
- Thus we can graph ratios easily in a linear chart.
Things to Remember
- You can alter graph data in vertical and horizontal directions too. Follow the steps. After plotting the graph go to the “Filter” option. From there click “Switch Row/Column” to change the direction to a different axis.
In this article, I have tried to cover the simple and quickest ways to graph ratios in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
- Calculate Current Ratio in Excel (2 Suitable Examples)
- How to Calculate Compa Ratio in Excel (3 Suitable Examples)
- Calculate Average Ratio in Excel (2 Simple Ways)
- Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)
- How to Calculate Sharpe Ratio in Excel (2 Common Cases)
- Debt Service Coverage Ratio Formula in Excel
- Calculate Sortino Ratio in Excel (2 Methods)