Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

This article illustrates how to plot a semi-log graph in excel. Assume 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.


Download Practice Workbook

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


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.

semi log transformation

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.

semi log graph vs linear graph


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.

create 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.
=5^B5

create data points

Read More: How to Create a Chart from Selected Range of Cells 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.

insert scatter chart

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

scatter chart

Read More: How to Plot Multiple Lines in One 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.

format axis to convert to semi log graph

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

check the logarithmic scale

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

semi log graph

Read More: How to Plot Graph in Excel with Multiple Y Axis (3 Handy Ways)


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.

add minor gidlines to the semi log graph

Read More: Plotting Row Number Instead of Value in Excel (with Easy Steps)


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.

how to read a semi log graph


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.

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. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.


Related Articles

 

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo