Excel Box and Whisker Plot on a Log Scale?

Yes—Excel for Microsoft 365 on Windows can display a Box & Whisker chart with a logarithmic vertical value axis, as long as every plotted value is positive. Select the chart’s vertical axis, open Format Axis, and under Axis Options select Logarithmic scale.

What a log scale changes on a box plot

A logarithmic axis spaces values by ratios rather than by equal differences. On the usual base-10 scale, the distance from 1 to 10 is the same as the distance from 10 to 100. This can make a chart of strongly right-skewed data easier to read: small values do not get compressed against the baseline just because a few observations are very large.

The axis changes how values are positioned visually; it does not ask Excel to calculate the box plot from logged data. Excel still calculates the median, quartiles, whiskers, and any displayed outliers from the values in the source data. The plotted positions of those statistics are then shown on the logarithmic axis. As a result, equal vertical distances correspond to equal ratios, not equal numeric increases.

That distinction matters when interpreting a box. Its height on a log axis reflects the ratio between the upper and lower quartiles, rather than the raw difference between them. A taller box therefore indicates a larger multiplicative spread, but it does not necessarily indicate a larger raw interquartile range.

Make a Box & Whisker chart in Excel

First arrange the observations so Excel can identify the groups. A common layout has one group per column, with the group name in the first row and observations below it. Groups can have different numbers of observations; leave unused cells blank rather than entering zero as a filler, since zero cannot appear on a logarithmic axis.

Select the data, including the group labels.
Choose Insert > Insert Statistic Chart > Box and Whisker.
Check that each box represents the intended group. If Excel has interpreted rows as groups instead of columns, use Chart Design > Switch Row/Column.
Select the chart’s vertical value axis, right-click it, and choose Format Axis.
In the Format Axis pane, open Axis Options and select Logarithmic scale. Excel normally uses base 10; if the base field is available, you can enter another positive base.

Excel may adjust the visible minimum and maximum when you turn on the scale. Review the axis labels and the plotted data range afterward. The logarithmic option applies to the chart’s value axis, so it affects all the boxes that use that axis rather than letting you put just one group on a different scale.

Check the data before switching scales

A standard logarithmic axis only represents positive numbers. If any plotted observation is zero or negative, Excel cannot place it on this axis. Do not treat a zero as a small positive value merely to make the chart work: that changes the meaning of the measurement and can substantially affect the chart.

Before changing the axis, check the source range for genuine zeros, negative values, and accidental entries such as a dash stored as a number or a zero used to fill an empty cell. Also check whether formulas return zero for missing observations. If the values are meaningful, consider whether a log axis is appropriate at all. If zero means not detected, for example, the measurement method and reporting limit matter; replacing it with an arbitrary constant can give a misleading visual result.

For data that include both positive and negative values, a conventional log axis is not suitable. A different chart or a clearly explained transformation may be needed. Some specialized transformations can represent zero or negative values, but they are not the same as Excel’s ordinary logarithmic axis and should not be presented as one.

Understand quartiles, whiskers, and outliers

A box plot summarizes a distribution. The line inside the box marks the median, and the ends of the box mark the first and third quartiles. Excel’s chart options let you choose inclusive or exclusive median calculations, which can affect quartile positions for some sample sizes. That setting changes the statistics used to draw the box; it is separate from the axis scale.

Excel can also show mean markers, inner points, and outlier points. In the standard box-and-whisker convention, whiskers extend to the most extreme observations within 1.5 times the interquartile range, while more distant observations are plotted as outliers. The log axis does not make a value cease to be an outlier: it changes where that value appears on the chart. Because Excel identifies the box-plot statistics from the original data, an outlier on a log-scaled chart is not necessarily an outlier calculated from the logarithms of the observations.

To review these display choices, right-click a box and choose Format Data Series. The available options include quartile calculation and whether to show outlier points, inner points, mean markers, or a mean line. These settings can help explain a distribution, but they do not replace checking the data or stating which quartile convention is in use when precise comparisons matter.

Example: comparing measurements across several orders of magnitude

Suppose three groups contain positive measurements in the approximate ranges 0.01–0.2, 0.1–5, and 5–200. On a linear axis, the group reaching 200 can dominate the chart’s vertical range, making differences among the smaller measurements difficult to inspect. A base-10 logarithmic axis spreads the values across powers of ten, so 0.1, 1, 10, and 100 occupy equal vertical intervals.

The chart still reports quartiles in the original units. If one group has a median of 1 and another a median of 10, their medians are one tenfold step apart on a base-10 axis. That visual spacing is useful when proportional changes are meaningful, as with some concentration, size, or biological measurements. It does not mean the groups differ by the same raw amount: the difference between 1 and 10 is 9, while the difference between 10 and 100 is 90.

If the question is instead about the distribution of logged measurements, transform the source data first and make a box plot from those transformed values. With the original measurement in A2, the formula =LOG10(A2) returns its base-10 logarithm; for example, a value of 100 returns 2. Fill the formula down for the observations, then chart the transformed column or columns on a linear axis. This changes the statistics being summarized: the median and quartiles are calculated in log units. Label the chart clearly so readers know whether the axis shows original values on a log scale or values that have already been transformed.

When a log axis helps—and when it does not

A log scale is useful when the data span several orders of magnitude, when relative differences are more meaningful than absolute differences, or when a few large positive values make the smaller observations hard to see. It can also make multiplicative patterns easier to compare between groups. For example, a change from 2 to 4 is a doubling, just as a change from 20 to 40 is; those pairs occupy the same distance on a logarithmic axis with the same base.

Use a linear axis when absolute differences are the main point, when the data include zero or negative values, or when readers could mistake equal visual distances for equal numeric increments. A log scale can make a small absolute difference at low values look visually comparable to a much larger absolute difference at high values. Axis labels and a note stating the base can help, but they cannot make an unsuitable scale appropriate.

For comparisons across groups, keep the same axis scale and base. Changing the range or base between charts can make visual comparisons unreliable. If the box plot is meant for a broad audience, consider showing the units, the scale, and a small sample of the underlying observations or a companion table where exact values matter.

Common problems and quick checks

The option is missing or unavailable: Make sure you selected the vertical value axis, not the horizontal category labels. Confirm that the chart is a Box & Whisker chart and that the selected axis is a value axis.
Excel rejects the scale or the chart looks wrong: Look for zeros and negative numbers in every series plotted on that axis. Check formulas and placeholder cells as well as visible values.
A group disappears or has a strange box: Inspect the selected range, blank cells, and chart orientation. Use Chart Design > Switch Row/Column if groups and observations were interpreted in the wrong direction.
Boxes do not match an outside calculation: Check the chart’s inclusive or exclusive median setting and whether the outside calculation uses the same quartile convention. Also distinguish raw-data quartiles from quartiles calculated after a log transformation.
Small values are still hard to compare: Check the axis minimum and maximum and confirm that a log scale is actually selected. Consider whether a transformed-data chart or a separate view would answer the question more clearly.

Excel for Mac and Excel for the web

The steps above are for Excel for Microsoft 365 on Windows. In current desktop Excel for Mac, select the vertical axis and open its formatting pane to find the logarithmic option under axis settings; the pane and contextual controls may be arranged differently from Windows. If the option is unavailable, check that you selected the value axis and that the plotted data are all positive.

Excel for the web has a different chart-editing interface, and availability of statistical chart creation or detailed axis controls can depend on the current web version and workbook. If you cannot find the Box & Whisker chart type or logarithmic setting in the browser, open the workbook in the desktop app to create or format the chart. The workbook can then be saved and viewed online, although some advanced editing controls may still require desktop Excel.

Frequently Asked Questions

Can an Excel box plot include zero on a log scale?
No. A standard logarithmic axis cannot plot zero or negative values. Use a linear axis or choose a defensible alternative analysis; do not replace zero with an arbitrary small number simply to enable the scale.

Does a logarithmic axis recalculate the box plot’s quartiles?
No. It changes how the existing statistics are displayed, not how Excel calculates them from the source data. To calculate quartiles in log units, transform the observations first and build the chart from those transformed values.

Can I change the log base in Excel?
Usually. In the Format Axis pane, select the logarithmic scale option and use the base field if it is available. Base 10 is the familiar default; changing the base changes the tick spacing, not the underlying data or quartile calculations.

Why is Logarithmic scale greyed out?
You may have selected the horizontal category axis instead of the vertical value axis, or the chart or axis may not support that option in the Excel version you are using. Also check for zero or negative values and try the desktop app if you are editing in Excel for the web.

Should I use a log axis or chart logged values?
Use a log axis when you want the original-unit quartiles and values displayed with ratio-based spacing. Chart logged values when the statistical summary itself should be calculated in logarithmic units. State which approach you used, since the resulting box plots answer different questions.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.