Logarithmic graphs – commonly known as log graphs – are a useful function in Excel when dealing with, and visualising large ranges of data.
Log graphs are commonly used when one or several points on the graph are much higher in value than the majority of the data, or when a user wishes to show percentage change and multiplicative factors. They use logarithmic scales to account for any extreme values that may skew the graph unintentionally; eliminating the chance of unprofessional visualisations.
For example, data in one or both axes of an XY scatter graph may cover a range of a 1:10,000. In this situation, the default setting for a standard Excel log graph is to present these data points in a linear scale; however, this would most likely alter the appearance of the graph unintentionally; a common problem presented to data management specialists when it comes to presenting and sharing data.
Log graphs can seem a little tricky to create because they are not instantly accessible through Excel’s intuitive Chart Wizard. The good news is that with a little bit of knowhow it’s relatively simple to include log graphs in your spreadsheet or workbook.
Let’s dive straight in…
How to create a log graph
There are two main methods to create a log graph: editing an existing chart or manually working with Excel’s “LOG()” function and regression tool.
METHOD #1 – Edit an existing graph
1. Create a standard graph
The first step is to make a standard graph of your choosing. More often than not this will be a scatter graph or a similar variation. Once created, the graph can be edited to include a log plot.
2. Select the axis you wish to change
You can now edit on different parts of the graph – the plot area, the legend or either axis – by clicking into them. In this case, we want to edit one of the axis lines. From there, click the Format tab and choose Selected Axis to display the Format Axis option.
3. Change setting to Logarithmic Scale
In the Format Axis window, now click the tick box that says Logarithmic Scale and close the window. Now you have your log graph!
METHOD #2 – Use Excel’s “LOG()” function and regression tool
1. Prepare your data
Organise the data you wish to include into two columns, A and B. The A column values will correspond with your x axis markers, whereas the B column values will correspond with your y axis markers.
2. Insert LOG formula
Enter the formula “=LOG(A1,[base])” in cell B1, and remember to insert your logarithmic base number in replace of [base]. Another option is to replace “LOG” with “LN” if you’re looking to use the natural logarithm function. You can then copy this formula to the remaining cells in the B column that are linked with x values in the A column.
3. Insert scatter graph
Navigate to the Insert tab, click Charts and select Scatter with Only Markers.
4. Change setting to Logarithmic Scale
Once you’re in the Chart Wizard, click Layout followed by Trendline and select More Trendline Options. You should see a tick box for Logarithmic. Click this and then close. This will create a smooth graph that matches up with the data you have prepped.
Looking for log graph support?
If you’re still unsure or are having issues with creating a log graph in Excel, you can always save yourself the time and stress by getting in touch with an experienced Excel professional.
Are logarithmic graphs and log plots part of an Excel project that you’re working on? Get in touch with our team of expert Excel consultants today on 0161 883 2655