Rubayed Razib Suprov
Log-Log graph is used primarily in skewed and clustered datasets, where linear graphs could not deliver clear insight. Creating both linear and logarithmic graphs in Excel is quite straightforward. But if are facing difficulty while creating a log-log graph or even a semi-logarithmic in Excel, this article can help you out. In this article, we are going to discuss how we can create and plot a log-log graph using Excel with elaborate explanations.
Download Practice Workbook
Download this practice workbook below.
Overview of Logarithmic Scale
The main inspiration behind the idea of the logarithm is to extract insight from large numbers. Another reason is to gain a clear idea about closely stacked data points in the graph. In other words, If there is more than one data point skewed in short spaces. For example, in the figure below, you can see that the population is increasing at a steeper rate from 1900-to 2000 in the world. That’s why the graph more stretched in the vertical portion and made it difficult to make any kind of deduction or insight.
One of the most useful benefits of using a logarithmic chart is that it gives information about rates pretty efficiently. User, in that case, need to be very careful in choosing the base of that logarithm.
We created a logarithmic scale version of the above graph below.
From the logarithmic graph, we can infer that the rate at which the population increasing rate in the 700-900 ad was almost stagnated. But then again it was starting to increase, from the 1600 ad. The increasing rate continued to increase till 2000.
Read More: Excel Logarithmic Scale Start at 0 (A Detailed Analysis)
Fundamentals of Log Log Graph
We can plot a log-log graph using Excel quite easily by tweaking some axis format options.in the log-log graph, both of the axes are actually on a logarithmic scale. This graph demonstrates whether the variables are in a constant power relationship, just like the equation Y = mX^n. Here the X is in the power of n relation with the Y. if we have created a dataset from this equation and then plot the data in logarithmic scale, then the line should be straight.
2 Suitable Examples to Plot Log Log Graph in Excel
We are going to provide a sample log-log graph with the data from covid-19 case information in the Lousiana state in the USA. we will how the weekly count of covid cases changes in relation to the week count. And whether adopting a logarithmic scale could help us in inferring information.
1. Log Log Graph of Weekly Covid-19 Cases in Lousiana State
We extracted the weekly covid cases from the parent Excel database. And now we will plot a Log-Log graph of the weekly cases with respect to the passing weeks.
Steps
- First, prepare the dataset. We collected the covid death data online in Lousiana state in the United States.
- From the Insert tab, we go to the Charts group and then click on the Scatter Chart command.
- Then there will be a new empty chart.
- Then you need to right-click on the chart, and from the context menu select the Select data command.
- There will be a new window named Select Data Source. From that window, click on the Add command icon.
- In the next window, you need to select the range of cells that will be taken as data for the X-axis and the Y-axis.
- To put the title, select the cell address that holds the cell name at this moment.
- In the second range box, select the range of cells D5:D24.
- And then in the third range box, enter the range of cells B5:B24 and then click OK.
- After selecting the address, the Scattered chart will form. But the chart will be difficult to read and there will be no format on the axis with no axis option title.
- To ensure a better understanding of this log graph, we need to enable the logarithmic scale in the Format axis option.
- To the Chart Elements icon on the corner of the chart, tick the necessary boxes like Axis Title, Chart Title, and Legends
- Now to create the logarithmic graph, click on the Horizontal Axis labels and then right-click on the mouse.
- From the context menu, click on Format Axis.
- A new side panel will open. Then from the Format Axis side panel, tick the Logarithmic scale box under the Axis Options.
- And also set Vertical Axis crosses to Automatic.
- Repeat the whole process of turning the logarithmic scale box for the vertical axis.
- Doing the above will turn the chart into a logarithmic graph.
- After some modifications, the log log graph will look like the one below.
Read More: How to Plot Log Scale in Excel (2 Easy Methods)
2. Plotting Log Log Graph of Male and Female Casualties in Covid-19
Next, we are going to use a different dataset. We will use the weekly casualties of males and females for covid cases with respect to passing weeks to create a log log graph.
Steps
- First, prepare the dataset. We collected the covid death data for both males and females in Lousiana state in the United States.
- We are going to make a Scatter chart.
- In order to do that, from the Insert tab, we go to the Charts group and then click on the Scatter Chart command.
- Then there will be a new blank chart.
- Next, you need to right-click on the chart, and from the context menu select the Select data command.
- There will be a new window named Select Data Source. From that window, click on the Add command icon.
- In the next window, you need to select the range of cells that will be taken as data for the X-axis and the Y-axis.
- To put the title, select the cell address that holds the cell name at this moment.
- In the second range box, select the range of cells D5:D44.
- And then in the third range box, enter the range of cells C5:C44 and then click OK.
- Similarly, we need to select the female column data into the chart.
- In the second range box, select the range of cells D5:D44.
- And then in the third range box, enter the range of cells B5:B44 and then click OK.
- After selecting the address, the scatter chart will form. But the chart will be difficult to read and there will be no format in the axis with no axis title.
- To ensure a better understanding of this log graph, we need to enable the Logarithmic scale in the Format axis option.
- To the Chart Elements icon on the corner of the chart, tick the necessary boxes like Axis Title, Chart Title, and Legends.
- Now to create the logarithmic graph, click on the Horizontal Axis labels and then right-click on the mouse
- From the context menu, click on Format Axis.
- A new side panel will open. Then from the Format Axis side panel, tick the Logarithmic scale box under the Axis Options.
- And also set Vertical Axis crosses to Automatic.
- After some modification, the log log graph will somewhat look like this.
Read More: How to Log Transform Data in Excel (4 Easy Methods)
How to Plot Semi-Log Graph in Excel
Next, we will plot a semi-logarithmic graph in Excel to infer how the world population has changed over the course of the last 1300 years. As things stand, the population is actually exploded in the last few centuries. So it’s better to create a Semi logarithmic graph compared to a log-log graph. As we need the year axis part to be in a linear format.
Semi logarithmic graph is the same thing but there is only one logarithmic scale applied on one axis. And in most cases, it actually applied to the vertical axis. This semi-logarithmic graph will come in handy in the case of data being skewed in one direction or two data points are way bigger than the rest of the data points, as shown in the example below.
Steps
- First, prepare the dataset. We collected the Populations of the earth rising from 700 AD to 2000 AD. in other words we have the population census of the earth from 700 to 2000.
- And we can also see that the population increases at almost an exponential rate.
- To get an idea of how the population of the world changed over the years, we need to create a graph.
- From the Insert tab, we go to the Charts group and then click on the Scatter Chart command.
- Then there will be a new empty chart.
- Then you need to right-click on the chart, and from the context menu select the Select data command.
- There will be a new window named Select Data Source. From that window, click on the Add command icon.
- In the next window, you need to select the range of cells that will be taken as data for the X-axis and the Y-axis.
- To put the title, select the cell address that holds the cell name at this moment.
- In the second range box, select the range of cells D5:D44.
- And then in the third range box, enter the range of cells C5:C44 and then click OK.
- After selecting the address, the Scatter chart will form. But the chart will be difficult to read and there will be no format on the axis alongside no axis title.
- To the Chart Elements icon on the corner of the chart, tick the necessary boxes like Axis Title, Chart Title, and Legends.
- Now to create the logarithmic graph, click on the Horizontal Axis labels and then right-click on the mouse.
- From the context menu, click on Format Axis.
- A new side panel will open. Then from the Format Axis side panel, tick the Logarithmic scale box under the Axis Options.
- And also set Vertical Axis crosses to Automatic.
- Doing the above will turn the chart into a logarithmic graph.
- After some modifications, the Semi logarithmic graph will look like the one below.
Read More: How to Do Inverse Log in Excel (3 Simple Methods)
Conclusion
To sum it up, the question “how to plot log-log graph within Excel ” is answered here with 2 different examples. Starting from using the weekly covid cases data set, then using the male-female death count in covid-19. And lastly using the population census from 700ad to 2000ad to demonstrate the semi-log graph.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.
