How to Plot Log Log Graph in Excel (2 Suitable Examples)

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.

Plot Log Log Graph in Excel

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.

Plot Log Log Graph in Excel

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.

Plot Log Log Graph in Excel


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.

Log-Log Graph Plot of Weekly Covid Case in Excel

  • 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.

Log-Log Graph Plot of Weekly Covid Case in Excel

  • There will be a new window named Select Data Source. From that window, click on the Add command icon.

Log-Log Graph Plot of Weekly Covid Case in Excel

  • 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

Log-Log Graph Plot of Weekly Covid Case in Excel

  • 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.

Log-Log Graph Plot of Weekly Covid Case in Excel

  • 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.

Log-Log Graph Plot of Weekly Covid Case in Excel

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.

Log-Log Graph Plot of Weekly Covid Case in Excel

  • 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.

Plotting Log-Log Graph of Male and Female Casualties in Covid-19 in Excel

  • 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.

Plotting Log-Log Graph of Male and Female Casualties in Covid-19

  • There will be a new window named Select Data Source. From that window, click on the Add command icon.

Plotting Log-Log Graph of Male and Female Casualties in Covid-19

  • 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.

Plotting Log-Log Graph of Male and Female Casualties in Covid-19

  • 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.

Log-Log Graph Plot of Weekly Covid Case in Excel

  • 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.

Log-Log Graph Plot in Excel

  • 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.

Plotting Log-Log Graph of Male and Female Casualties in Covid-19

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.

Plot Excel Semi-Log Graph of Population

  • 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.

Plot Excel Semi-Log Graph

  • 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.

Plot Excel Semi-Log Graph of population

  • 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.

Plot Log Log Graph in Excel

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.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo