The Excel logarithmic trendline formula is the model y = a × ln(x) + b, where Excel estimates the coefficients a and b from your data. In a worksheet, fit it by regressing your y-values against the natural logarithms of your x-values; for a chart, add a Logarithmic trendline to an XY scatter plot.
What a logarithmic trendline means
A logarithmic trendline describes a relationship that changes quickly at first and then changes more gradually as x increases. Its mathematical model is y = a × ln(x) + b. Here, ln(x) is the natural logarithm of x, a is the fitted slope, and b is the fitted intercept. The line is straight when y is plotted against ln(x), even though it curves when y is plotted against the original x-values.
This model is not the same as taking the logarithm of y. Excel’s logarithmic trendline transforms the x-values and leaves the y-values on their original scale. That distinction matters: if the pattern you intend to model is a constant percentage change in y for each equal increase in x, an exponential model may be more suitable instead.
Logarithmic trends can be useful when a response has diminishing increments: for example, an early increase in practice time may bring a large improvement, while additional hours bring smaller gains. That is an example of a possible shape, not proof that a logarithmic model is appropriate. The data and the purpose of the analysis should support the choice.
Add a logarithmic trendline to an Excel chart
In Excel for Microsoft 365 on Windows, use an XY (Scatter) chart when the horizontal values are numerical and their spacing matters. A Line chart treats horizontal positions as categories, which can distort the relationship if the x-values are unevenly spaced.
- Arrange the x-values in one column and the corresponding y-values in another. Keep each pair on the same row.
- Select both columns, then choose Insert > Insert Scatter (X, Y) or Bubble Chart and select a scatter style.
- Select a data point in the plotted series. Choose Chart Design > Add Chart Element > Trendline > More Trendline Options. You can also right-click the series and choose Add Trendline.
- In the Format Trendline pane, select Logarithmic. If you want the chart to show the fitted equation or R-squared value, select the corresponding display options in the pane.
The chart’s equation is useful for understanding or communicating the fit, but the displayed coefficients may be rounded. To use the curve for calculations, derive coefficients in worksheet cells at full stored precision rather than copying rounded numbers from the chart label. Excel for Mac has similar chart and trendline controls, though the ribbon and pane arrangement can differ slightly by release. If a command is not visible, use the chart’s context menu or search for the command in Excel.
Calculate the coefficients in worksheet cells
Excel does not have a built-in worksheet function named LOGTREND. Instead, transform the x-values with LN and use the standard linear regression functions. LN returns the natural logarithm, so fitting y against LN(x) estimates the same form of curve as a logarithmic trendline.
For a simple setup, put x-values in A2:A6 and matching y-values in B2:B6. In C2, calculate the natural log of the first x-value and fill the formula down through C6. In this layout, enter =LN(A2) in C2. The formula returns the natural logarithm of the value in A2.
With the transformed x-values in C2:C6 and the observed y-values in B2:B6, calculate the slope in D2 using =SLOPE(B2:B6,C2:C6). Calculate the intercept in D3 using =INTERCEPT(B2:B6,C2:C6). SLOPE estimates a for the model, and INTERCEPT estimates b. The comma separates function arguments in standard English-language Excel settings; some regional settings use semicolons instead.
Once the coefficients are in D2 and D3, suppose a new positive x-value is entered in A7. With the slope in D2, the intercept in D3, and the value to predict in A7, calculate its fitted y-value in B7 with =$D$2*LN(A7)+$D$3. The dollar signs keep the coefficient references fixed if you copy the prediction formula to other rows. This calculates a model estimate, not a guarantee that a future observation will equal that value.
Worked example with known results
Consider three x-values and y-values chosen to follow the curve exactly: x = 1, 2, and 4; y = 10, 13.4657359, and 16.9314718. Put the x-values in A2:A4 and the y-values in B2:B4. In C2, with the first x-value in A2, enter =LN(A2) and fill down. The transformed x-values are 0, approximately 0.693147, and approximately 1.386294.
With the transformed values in C2:C4 and the y-values in B2:B4, =SLOPE(B2:B4,C2:C4) returns approximately 5. With the same cell layout, =INTERCEPT(B2:B4,C2:C4) returns approximately 10. The fitted model is therefore y = 5 × ln(x) + 10, allowing for normal floating-point precision. For x = 8, the model predicts approximately 20.3972 because 5 × ln(8) + 10 is approximately 20.3972.
Real data will rarely sit exactly on a curve. In an actual analysis, the coefficients are chosen to make the overall residuals—the differences between observed and fitted y-values—as small as possible under the usual least-squares criterion. The fitted curve may therefore miss individual observations even when it summarizes the overall pattern reasonably well.
Conditions and data checks
Every x-value used in a logarithmic model must be greater than zero. LN(0) is undefined, and LN of a negative number does not return a real-valued result. A zero or negative x-value is not fixed by choosing a different logarithm base: logarithms of nonpositive real numbers remain outside the model’s domain. Do not silently delete such rows or add an arbitrary constant without a defensible reason, because that changes the analysis.
Before fitting, check that the x- and y-ranges contain the same number of observations and that each row represents a matched pair. Blank cells, text stored as numbers, errors, and outliers can affect the result or cause a function to return an error. A scatter plot of the raw data is a useful first check. Also inspect a residual plot if the model matters for a decision: a systematic curve or funnel shape in the residuals can signal that the model does not describe the data adequately.
A logarithmic curve has a vertical asymptote at x = 0 and is only defined for positive x. It should not be extended to zero or negative x, and predictions far outside the observed x-range are extrapolations. Even if Excel can calculate them, those predictions may be unreliable because the data do not establish that the same pattern continues.
Understand the equation and R-squared
The coefficient a controls the direction and steepness of the curve. If a is positive, the fitted y-value rises as positive x increases; if a is negative, it falls. The coefficient b is the model’s y-value at x = 1, because ln(1) is zero. It is not generally the value at x = 0, where this model is undefined.
Excel can display an R-squared value for a chart trendline. It is a summary of how well the fitted model accounts for variation in the data under Excel’s calculation for that trendline. It does not prove that the model is appropriate, that x causes y, or that predictions will be accurate outside the observed range. Compare the chart shape and residuals as well as R-squared, and be cautious about comparing values from different transformations or different data sets as if they were a complete model-selection test.
Logarithmic trendline versus other trendlines
Choose a model based on the pattern and the question, not merely on which option produces the largest R-squared. Common alternatives have different shapes and assumptions:
- Linear: y changes by a roughly constant amount for each equal increase in x. The plotted relationship is a straight line.
- Exponential: y changes by a roughly constant percentage for each equal increase in x. The model is often written mathematically as y = a × e^(b×x), and it is different from a logarithmic trendline.
- Power: y varies as a power of x. This is also different from a logarithmic model and requires positive x-values; depending on the model and data, y may also need to be positive.
- Polynomial: a curved model with one or more powers of x. It can follow bends in data, but a high-order polynomial may fit noise and behave erratically beyond the observed range.
A practical comparison starts with a scatter plot, asks whether the curve makes sense for the process, and checks residuals and prediction purpose. A smooth-looking line alone does not establish that the selected relationship is scientifically or operationally meaningful.
Common problems and fixes
- The chart option is unavailable or the trendline fails: Check that the chart is an XY scatter chart with a numeric x-series, and confirm that every x-value is positive.
- The displayed equation seems wrong: Increase the number of decimal places shown for the trendline equation in its formatting options. The label is rounded for display and may not reproduce the chart exactly when copied.
- SLOPE returns an error or unexpected result: Confirm that the transformed x-range and y-range have matching dimensions and contain usable numeric values. Try calculating LN values in a helper column and inspect each result.
- The fitted curve looks like a straight line: Across a narrow x-range, a logarithmic curve can appear nearly straight. Check the scale and the actual spread of x-values rather than assuming the trendline type was ignored.
- The worksheet formula returns #NUM!: One or more x-values may be zero or negative. Identify the source row; do not replace problematic values with arbitrary small positives just to suppress the error.
In Microsoft 365, entering LN over a range inside a function may work as an array calculation, but the helper-column method makes the transformed inputs visible and is easier to audit. It also helps diagnose data issues without relying on how a particular chart version handles the series.
Frequently Asked Questions
What is the Excel formula for a logarithmic trendline?
Its model is y = a × ln(x) + b. In a worksheet, calculate the coefficients by using SLOPE and INTERCEPT with the y-range and a range of LN-transformed x-values. Excel charts can also fit and display a logarithmic trendline.
Does Excel use LOG or LN for a logarithmic trendline?
The chart trendline is based on a logarithmic relationship, and the worksheet equivalent can use LN, the natural logarithm. Changing the log base changes the slope coefficient but not the family of fitted curves when the coefficient is adjusted accordingly. Use LN consistently for the worksheet method described here.
Can a logarithmic trendline include zero or negative x-values?
No. The model requires x greater than zero because the real natural logarithm is undefined for zero and negative values. Review the source data and the reason for those values before deciding whether a different model is appropriate.
Why does my chart equation differ from my worksheet result?
The equation printed on the chart is usually rounded for readability, while worksheet functions retain more precision. Make sure the chart and worksheet use the same observations and x-values, and compare coefficients at sufficient displayed precision.
Is a logarithmic trendline the same as a logarithmic x-axis?
No. A logarithmic trendline changes the fitted relationship; a logarithmic axis changes how values are spaced visually on the chart. You can use one without the other. A trendline’s equation should be interpreted in relation to the actual data values, not just the visual appearance of an axis.
Related
- Excel: How to Hide Columns With No Data Automatically
- Excel: Hide Duplicates Based on One Column in a List
- Excel Hide Columns With an Expand Button: Grouping Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Adding Power Pivot to Excel: Enable and Use ItTo add Power Pivot to Excel for Microsoft 365 on Windows, go to File > Options > Add-ins,…
- Excel Dashboard Exercises: Practice Data and Reporting SkillsExcel dashboard exercises are hands-on projects for turning a worksheet of raw data into a clear, interactive summary.…
- Excel Pivot Table Average: Add a Column and Set It UpTo add an average column to an Excel PivotTable, drag the numeric field into the Values area a…
- Excel Gantt Chart Template: Build and Customize OneAn Excel Gantt chart template is a worksheet that lays out tasks against dates, then uses colored cells…
- How to Make a Graph in Google Sheets: A Step-by-Step GuideTo make a graph in Google Sheets, arrange your information in a table with headings, select the cells…
- Delete a Trendline in Excel Without Removing the ChartTo delete a trendline in Excel for Microsoft 365 on Windows, click the trendline in the chart to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.