An Excel logarithmic trendline is a curve that models how a value changes as the predictor grows by progressively larger amounts, often showing a fast initial change that later levels off. To add one in Microsoft Excel for Microsoft 365 on Windows, make an XY scatter chart, select a data series, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options and select Logarithmic.
What a logarithmic trendline shows
Excel fits a logarithmic trendline to a relationship of the form y = a ln(x) + b. Here, x is the horizontal-axis value, y is the modeled vertical-axis value, and a and b are coefficients estimated from the data. The ln term is the natural logarithm. In practical terms, the model describes a change in y associated with changes in the logarithm of x, rather than a constant change in y for each additional unit of x.
This curve is useful when an outcome changes quickly at low x values and then changes more slowly as x increases. Examples might include early learning gains with practice, an initial rise in a response that tapers off as exposure increases, or diminishing improvement as a process is repeated. Those are possible shapes, not proof that the underlying process follows a logarithmic law. The data and the subject matter still need to support the model.
A logarithmic trendline is not the same as putting a logarithmic scale on an axis. The trendline changes the mathematical model used to fit the data. A logarithmic axis changes how values are spaced visually. You can use one without the other, and an axis scale alone does not calculate a logarithmic trendline.
How to add one in Excel
- Put the numeric x-values and corresponding y-values in separate columns. Each row should represent one paired observation.
- Select the data and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. A scatter chart is usually the clearest choice because it treats the horizontal values as numbers.
- Click a point in the series to select the series. If the chart has several series, make sure you select the one you want to model.
- Choose Chart Design > Add Chart Element > Trendline > More Trendline Options.
- In the Format Trendline pane, select Logarithmic. Excel draws the fitted curve for the selected series.
- To show the fitted equation or a goodness-of-fit statistic, select Display Equation on chart or Display R-squared value on chart in the pane.
On Excel for Mac, select the chart and use the Chart Design tab to add a trendline; the exact arrangement of ribbon controls can differ slightly from Windows. The Format Trendline options include the logarithmic type and the display choices. Excel for the web may offer a more limited chart-editing interface than the desktop app, so use the desktop version if you cannot find a trendline option.
Excel adds the curve to the chart; it does not replace your source data or automatically create a new column of predicted values. You can move or format the equation label and trendline like other chart elements. Keep the equation label close enough to the chart that readers can tell which series it describes.
Which data and chart type to use
An XY scatter chart is generally best for this model. It uses each row’s numeric x-value as a position on a continuous horizontal axis. A line chart, by contrast, commonly treats horizontal entries as categories spaced evenly apart. If your x-values are unevenly spaced, a line chart can make the curve appear to fit a different relationship than the actual numbers support.
The x-values used in a logarithmic trendline must be greater than zero. Zero and negative x-values have no real natural logarithm, so Excel cannot fit this model to them as ordinary real-valued observations. The y-values may be positive, zero, or negative; it is the predictor values on the horizontal axis that impose this particular restriction.
Before fitting the curve, check that:
- Each x-value is numeric and paired with the correct y-value.
- Every x-value in the selected series is positive.
- The values are measurements on a scale where differences and numeric spacing are meaningful.
- The observations show a plausible pattern of strong early change followed by slower change.
If your x-values are dates, Excel stores dates as serial numbers, but fitting a logarithm to those serial numbers may not be meaningful. A more interpretable approach can be to calculate elapsed time from a chosen starting date and use that positive duration as x. If the elapsed time includes zero, the logarithmic model still cannot use that zero observation without changing the model or choosing another approach.
Understanding the equation and R-squared
In the displayed equation, the coefficient a controls the direction and scale of the curve. If a is positive, the modeled y-value rises as x increases; if it is negative, the modeled y-value falls. The coefficient b is the predicted y-value at x = 1, because the natural logarithm of 1 is zero. It is not generally the predicted value at x = 0, which is outside the model’s valid domain.
The model’s rate of change depends on x. For example, multiplying x by 2 changes the fitted y-value by a ln(2), approximately 0.693 times a. That change is the same for any doubling of x, under the model, but the change per one additional unit of x gets smaller as x grows. This is why the curve often rises or falls steeply at first and then flattens.
R-squared summarizes how closely the fitted values correspond to the observed y-values in the data used for the fit. A value closer to 1 indicates a closer fit by that measure, but it does not establish that the model is appropriate, causal, or reliable for prediction. A high value can result from a small or narrow dataset, and another model may fit better while making more sense for the subject. Inspect the plotted points and residual pattern as well as the statistic.
Excel rounds the equation shown on the chart, so do not treat the displayed coefficients as full-precision values. If you need to report them, select the equation label and use its formatting options to show more decimal places. Even then, retain sensible precision: excessive digits can imply accuracy the observations do not support.
A concrete example of interpretation
Suppose a training study records practice time as x and a performance score as y. If the first few hours are associated with large score increases but later hours bring smaller gains, a logarithmic curve may describe the visual pattern better than a straight line. The fitted coefficient a represents the change in predicted score for a one-unit increase in ln(x), not the score gain for every additional hour.
For instance, if the fitted a were 10, doubling practice time would correspond to a fitted change of about 6.93 score units, since 10 multiplied by ln(2) is approximately 6.93. That is a model-based comparison, not a guarantee that each person improves by that amount. It also does not mean the score can increase indefinitely; real score scales may have limits that the logarithmic curve does not represent.
The example also shows why the units matter. Changing x from hours to minutes multiplies every x-value by 60. The fitted curve’s shape over the observations remains related, but the intercept and the interpretation of the coefficient can change. Record the units and the chosen zero point when presenting a trendline so another reader can understand what the equation means.
Common problems and how to fix them
- Excel will not offer the logarithmic option. Confirm that you selected a data series in a supported two-dimensional chart. Rebuild the chart as an XY scatter chart if necessary, then open More Trendline Options.
- The trendline is missing or does not fit. Check for zero, negative, blank, or text-formatted x-values in the selected series. Correct invalid entries or choose a model that can handle the data as collected; do not change values merely to force a curve.
- The curve looks wrong on a line chart. Use a scatter chart when the x-values are numeric and unevenly spaced. A category axis can space observations equally even when the actual x-values are not equally spaced.
- The displayed equation differs slightly from another calculation. The label is rounded, and small differences can also arise from the selected observations or chart-series setup. Show more label decimals and verify that the plotted range contains the intended pairs.
- The line extends beyond the observations. A trendline can be extended forward or backward in the Format Trendline pane. Extension is extrapolation, not additional observed evidence. Use it only when the model is reasonable over that range and explain the uncertainty.
Also watch for a few influential points. A single unusual observation can affect the fitted coefficients, especially when there are few data points. Check whether the point is a data-entry error, a legitimate unusual case, or evidence that one curve is not a good summary of the whole dataset. Do not remove valid observations just because they weaken the fit.
Logarithmic versus other trendlines
A linear trendline assumes a constant change in y for each one-unit increase in x. It is often easier to interpret and may be appropriate when the points follow an approximately straight pattern. A logarithmic trendline instead assumes that equal multiplicative changes in x, such as doubling, correspond to equal changes in fitted y.
An exponential trendline has a different form: it models y as changing exponentially with x and is often used when the rate of change is proportional to the current level. A power trendline models a relationship based on a power of x. These models are not interchangeable with a logarithmic trendline. In particular, plotting an exponential or power relationship on logarithmic axes does not make it the same model as Excel’s Logarithmic trendline option.
Choose by combining the plotted shape, knowledge of the process, and a comparison of fit. R-squared values can be useful when comparing models fitted to the same y-values and observations, but they are not the only criterion. Consider whether the model’s assumptions are sensible, whether residuals show a pattern, and whether the intended use is explanation or prediction.
Frequently Asked Questions
Can an Excel logarithmic trendline include zero or negative x-values?
No. The natural logarithm is undefined for zero and negative real values, so every x-value in the fitted series must be positive. If zero is meaningful in your data, choose a model that can represent it rather than silently altering the observations.
Can y-values be zero or negative?
Yes. Excel’s logarithmic trendline takes the logarithm of x, not y. The vertical values may be zero or negative, although the suitability of the resulting curve still depends on what y represents.
Is a logarithmic trendline the same as a logarithmic axis?
No. A trendline is a fitted relationship, while an axis setting changes the visual spacing of tick marks. A chart can have a logarithmic trendline with a regular axis, or a logarithmic axis without that trendline.
Why does Excel show an equation but not calculate forecast cells?
The equation label is a chart annotation, not a worksheet formula. It does not fill cells with predictions. For forecasts in cells, you need to implement the fitted model using its coefficients and the natural-log calculation, taking care to use positive x-values and sufficiently precise coefficients.
Should I use the trendline to predict far beyond my data?
Usually, treat far-out predictions cautiously. The curve summarizes the observed range and may not reflect limits, changing conditions, or other factors outside it. Use the trendline for extrapolation only when there is a defensible reason to expect the same relationship to continue.
Related
- Excel Heatmap Table: How to Create and Read One
- Excel: Hide Column Headers Without Hiding Data
- Excel: Hide a Column When a Cell Contains Text
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Dashboard Inspo: Ideas, Layouts, and Design TipsFor Excel dashboard inspo, start with one clear question your dashboard should answer, then build a compact view…
- Excel Gantt Chart Automatic? Setup, Updates, and LimitsYes. In Microsoft Excel for Microsoft 365 on Windows, a Gantt chart can update automatically when you change…
- Excel Combo Chart Bar Width: How to Change Gap WidthTo change bar width in an Excel combo chart, select a column series, open Format Data Series, and…
- Google Sheets Chart Sort by Value: How to Control OrderGoogle Sheets charts do not generally have a separate control for sorting categories by a series’ values: the…
- Excel Histogram X-Axis Labels: Set Bins and FormatIn Excel for Microsoft 365 on Windows, a histogram’s x-axis labels represent bins: ranges that group numeric values.…
- Excel Histogram: Set Bins ManuallyTo set histogram bins manually in Excel for Microsoft 365 on Windows, select the chart’s horizontal axis, right-click…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.