How to Plot Semi Log Graph in Excel (with Easy Steps)

In this Excel article, you will learn how to plot a semi-log graph in Excel. Assume that you have a dataset with two variables one of which is proportional to the exponent of the other. Then plotting the data in a linear graph may not be a good idea. For example, coronavirus cases increased exponentially every day. Now if you plot the dates on the x-axis and the number of cases on the y-axis, you may not get a satisfactory result as it will be difficult to read the graph. So what should you do? Well, you can plot the number of cases on a logarithmic scale and the dates on the linear scale. Follow the article to learn how to do that in Excel.

What Is a Semi Log Graph?

Semi-logarithmic or semi-log graphs have one axis on the logarithmic scale and the other on the linear scale. In other words, if the Y-axis is in logarithmic scale then the X-axis must be in linear scale and vice versa. You can use semi-log graphs to plot exponential functions. In other words, you should use the semi-log graph when one variable changes more abruptly than the other one.

Consider the transformation of the above equation. If you plot y vs. x, you will get an exponential trendline as y is exponentially proportional to x. But if you plot Y vs. X, you will get a straight trendline as the deduced equation indicates a straight-line equation. Here the graph will be a semi-log graph as you will actually plot log(y) vs x.

How to Plot Semi Log Graph in Excel

Follow the steps below to see how to plot a semi-log graph in Excel.

Step 1: Prepare Dataset

• First, we will prepare a dataset to plot the graph. If you want to apply this to an existing dataset, then go to Step 2. Otherwise, enter 0 in cell B5, hold CTRL and drag the Fill Handle icon below to create a data series.

• Then, enter the following formula in cell C5 and copy the formula down using the Fill Handle icon. After that, you will get the following dataset.

Read More: How to Plot Time Series Frequency in Excel

Step 2: Insert Scatter Chart

• Now you need to create a chart for the dataset. Click anywhere in the dataset and go to Insert >> Insert Scatter (X, Y) or Bubble Chart >> Scatter with Smooth Lines and Markers.

• After that, you will see the following chart. Notice that it seems impossible to extrapolate the y-axis values corresponding to the first few x-axis values. That’s why you need a semi-log graph. Go to the next step to convert this to a semi-log graph.

Read More: How to Make a Time Series Graph in Excel

Step 3: Format Axis

• Now right-click on the y-axis and select Format Axis. This will take you to the task pane.

• Then, check the Logarithmic scale checkbox and keep the Base to 10.

• After that, the graph should look as follows. Notice how the trendline has changed from exponential to straight-line.

Read More: How to Plot Sieve Analysis Graph in Excel

Step 4: Add Gridlines

• It is very important to show gridlines if you plot data on a logarithmic scale. So select the graph, click on the Chart Element icon, and check the Gridlines checkbox. If you keep the cursor on the Gridlines element, you will see options to add minor gridlines. You can also do this from the Chart Design tab.

Read More: How to Plot Michaelis Menten Graph in Excel

How to Read Semi-Log Graph in Excel

Now the question is how to read the semi-log graph. Well, it’s not that difficult, to be honest, if you observe attentively.

• Notice that the vertical minor gridlines are uniformly distributed. As each unit along the x-axis is divided into 5 parts, you can read the values corresponding to the minor vertical gridlines as 0, 0.2, 0.4, 0.6, 0.8, 1.0, 1.2, 1.4, 1.6, and so on.
• On the other hand, the horizontal minor gridlines get closer to each other when they approach the major gridline above them. Notice that each section along the y-axis is divided into 10 parts. So you must read the values corresponding to the horizontal gridlines as 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000,2000, 3000, and so on.

Read More: How to Make a Lineweaver Burk Plot in Excel

Things to Remember

• You can format the X-axis instead to plot log(x) vs y.
• You must add minor gridlines to the semi-log graph to avoid misrepresentation.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to plot a semi-log graph in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.

<< Go Back To How to Create a Chart in Excel | Excel Charts | Learn Excel