Example blog

How to create log and semi-log charts in Excel

Semi-log and log-log plots are useful tools for analyzing and finding relationships of exponential and power functions. Logarithmic charts have the potential to give you perspective on a relationship between your data that isn’t obvious on a simple chart.

While a semi-log chart is logarithmic on only one axis, a log-log chart is logarithmic on both axes. Each of these graphics has its own uses. With Excel, you can create semi-log and log-log charts and graphs with just a few clicks to further analyze your data. Read on to know how you can create such charts in Excel.

How to create a semi-log chart in Excel

A semi-logarithmic chart is a chart that has one of two axes on a logarithmic scale. Usually the Y axis is logarithmic. This means that the Y-axis grid lines will be exponential, while the X-axis grid lines will remain equal.


Example worksheet for a semi-log chart in Excel.

As an example, in this spreadsheet we have the number of cells in a cell culture environment in an experiment on different days. The goal is to plot this data on a semi-logarithmic graph. If you are unfamiliar with creating charts in Excel, read our article on creating charts in Excel to fully understand the process.

  1. Select the entire table. They will be cells A1 for B7 in this example.

  2. Go to the Insert menu.

  3. Of Graphics section, click Insert a scatterplot (X, Y) or bubble chart.

  4. To select Scatter with smooth lines and markers.


Scatter plot in Excel.

You now have a scatterplot of your data. Now is the time to make a semi-log of it.

  1. Select the chart you just created.

  2. Double click on the Y axis.

  3. In the Format axis right panel, navigate to Axis Options tongue.

  4. In Axis OptionsCheck Logarithmic scale.


Semi-log chart in Excel.

Here is your semi-log graph! Note that the graph is now a straight line now.

You can also change the base from the default 10 to any other number that suits you better. This is because the number of cells in the data table was exponential, because they are actually powers of six. You can further improve the readability of your graph by changing the base of the logarithm from 10 to 6.

How to create a log-log chart in Excel

A log-log chart is a chart that has both axes on a logarithmic scale. Thus, the grid lines for the X and Y axes are exponential and unequal in this graph. Let’s see the log-log graph in action with an example.

In a similar example, we have cell counts on different days, but this time the days are also exponential. A table of data like this practically asks for a log-log chart.


A semi-logarithmic graph in the example.

Just like in the previous example, select the table and create a scatter plot for it. If you want to learn more about scatter charts, read our article on how to create a scatter plot in Excel to find out what exactly it is and when you should use it.

Once you’ve created the scatter plot, change the Y axis to log as you learned in the previous section. You can see that the graph is still not a straight line. This is because the X axis (day number in this example) is also exponential.

  1. Select the chart.

  2. Double click on the X axis.

  3. Of Format axis panel, click Axis Options.

  4. Under Axis Options, check Logarithmic scale.


A log-log chart in Excel.

Now both of your axes are on a logarithmic scale and you can see that your graph has also become a straight line. This is because the data on both axes is exponential.

Considering that the data on the X axis are all powers of two, changing the base of the logarithm to two will give you a better picture of this graph.

Logarithmic charts in Excel

Semi-log and log-log charts are useful assets that can help you discover new relationships between your data. These tools are widely used in different fields, and although they are not immediately visible in the chart settings, Excel is also capable of creating semi-log and log-log charts.

Now that you know how to create semi-log and log-log charts in Excel, it’s time to use these charts and see your data in a new light.


Excel tables and charts

8 types of Excel tables and charts and when to use them

Read more


About the Author


Source link