In this article, you will learn how to find the slope of a logarithmic graph in Excel. When linear graphs are unable to provide meaningful insight into skewed and clustered datasets, log-log or natural log-log graphs are typically employed. It’s simple to make a logarithmic graph with Excel. However, if you’re having trouble making a log-log or natural log-log graph or a semi-logarithmic and finding the slope of that graph in Excel, this article might be a useful session for you to solve these kinds of problems.
Download Practice Workbook
You can download the practice workbook from the following download button.
Steps to Find Slope of Logarithmic Graph in Excel
By adjusting a few axis format variables in Excel, we can simply plot a log-log graph. Both axes in the log-log or natural log-log graph are really on a logarithmic scale. Similar to the equation y = c. xa, this graph illustrates whether the variables are in a constant power relationship. In this case, the X and Y are related by a power of ‘a’ as in the image below.
- However, the line should be nearly straight if a dataset derived from this equation is shown using a logarithmic scale.
⦿ Step 1: Create a Logarithmic Graph by Using the Charts Tool of Excel
- We select the Scatter Chart from the Charts group under the Insert tab.
- A fresh, blank chart will appear after that.
- Next, you must do right-click on the chart and choose the command Select data from the context menu.
- A new window titled Select Data Source will appear. Click the Add command icon in that window.
- We must choose the range of cells that we will use as data for the X-axis and the Y-axis in the following window.
- Choose the cell address that currently has the cells and cell name in it to insert the title there.
- Now click Ok like the image below.
- Right-click the mouse after clicking on the labels for the Horizontal Axis to generate the logarithmic graph.
- Select Format Axis from the context menu.
- It will reveal a new side panel. Select the Logarithmic scale checkbox and enter Base 2.7 under the Axis Options in the Format Axis side panel.
- Set Vertical Axis Crosses to Automatic as well.
- Similar to the y-axis, check the Logarithmic scale checkbox for the x-axis under the Axis Options in the Format Axis side panel.
⦿ Step 2: Find the Slope of the Logarithmic Graph
- Let’s say we have data that could fit the theoretical y = c. xa curve. The slope of a natural log-log plot is equal to the exponent a‘s value, which is calculated as
- Now we will select Add Trendline to find the equation of our data like the image below.
- Now we will select Exponential as our trendline like in the following image.
- After selecting Display Equation on Chart in the Trendline Options, we will get the equation shown below.
y = 12.484e
- After differentiating the equation, we will get the following equation to find the slope in each point of our x-value for this example the x-values are Day.
dy/dx=12.484 x 0.7387 x e
- Using the differential equation, we will get the slope in Excel like the following image.
The formula is as follows:
- Lastly, drag the fill handle down to complete the series.
Follow these steps and stages to find the slope of a logarithmic graph in excel. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.
- How to Calculate Confidence Interval for Slope in Excel
- Find Slope of Tangent Line in Excel (2 Suitable Ways)
- How to Find Instantaneous Slope on Excel (2 Effective Ways)
- Find Slope of Trendline in Excel (2 Easy Methods)
- How to Find Slope of Polynomial Trendline in Excel (with Detailed Steps)
- Calculate Standard Error of Regression Slope in Excel
- How to Use Excel SLOPE Function (5 Quick Examples)