Chart & Visualization Issues

How to Use a Logarithmic Y-Axis in an Excel Box-and-Whisker Chart

Huda Qurayshi Oct 7, 2026 869 views

Question details

The user needs to apply a logarithmic scale to the Y-axis of an Excel box-and-whisker chart to better visualize and compare variations among low values.

How to Create a Logarithmic Y-Axis in an Excel Box-and-Whisker Chart

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a statistical chart to compare data that includes extreme high-value outliers, making lower clustered values difficult to read.
Observed behavior: The native Excel box-and-whisker chart does not provide an option to format the vertical axis with a logarithmic scale in its axis-formatting settings.

Before you start

Before attempting data transformation, ensure your dataset does not contain zero or negative values, as logarithmic calculations cannot be applied mathematically to these numbers.

Solution 1Recommended

Transform Data and Build a Custom Chart

Since the native Box-and-Whisker chart lacks a log scale option, you can manually calculate the logarithmic values of your data and use a stacked column chart with error bars to simulate the visualization.

By applying a Base-10 logarithm to your raw data first, the resulting standard chart will inherently display a logarithmic relationship. You can then calculate the statistical quartiles required for a box plot manually.

Calculate Logarithmic Values

Create a new column next to your raw data. Use the formula =LOG10(A2) (assuming A2 is your first data point) and drag the fill handle down to transform all values.

Calculate Custom Quartiles

Using the new transformed column, calculate the required box plot metrics using Excel functions: =MIN(), =QUARTILE.INC(range, 1), =MEDIAN(), =QUARTILE.INC(range, 3), and =MAX().

Insert a Stacked Column Chart

Select the calculated quartile data, navigate to the 'Insert' tab on the ribbon, and choose a 'Stacked Column' chart. This will serve as the base 'box' for your visualization.

Add Error Bars for Whiskers

Select the chart, click the 'Chart Elements' (+) button, and check 'Error Bars'. Format the error bars to use custom values pointing to your calculated MIN and MAX differences to simulate the whiskers.

Data Limitations: A logarithmic scale cannot handle zero or negative numbers. You must filter these out or apply an offset before transforming your dataset.

Solution 2

Use Alternative Visualization Tools

If manually building a custom box plot in Excel is too time-consuming, exporting the data to specialized statistical tools can quickly generate native logarithmic box-and-whisker plots.

Free Microsoft Office alternative

Need a seamless and lightweight alternative for your spreadsheet tasks?

While native box-and-whisker charts in standard spreadsheet tools may lack logarithmic scaling, WPS Office provides a highly compatible, lightweight, and free alternative to Microsoft Office for all your everyday data analysis, reporting, and charting needs.

1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and follow the on-screen instructions to set up WPS Spreadsheets.
2. Open Your Existing Excel Files: Launch WPS Spreadsheets, click 'Open', and select your existing .xlsx or .csv files. Your data and formulas will load seamlessly.
3. Explore Charting Options: Highlight your dataset, go to the 'Insert' tab, and click 'Chart' to explore various visualization options tailored for data analysis.

Free to use with a familiar, user-friendly interface similar to ExcelFully compatible with standard Microsoft Excel file formats (.xlsx, .xls, .csv)Lightweight installation that runs smoothly on older or lower-spec devicesRich library of built-in statistical templates and standard chart types

Frequently Asked Questions

Why doesn't Excel allow logarithmic scales on Box-and-Whisker charts?

Excel's native Box-and-Whisker chart is a specialized statistical chart type introduced in Excel 2016. Microsoft has not included the logarithmic scaling option in the axis formatting settings for this specific chart family, although the feature remains available for standard charts like line or scatter plots.

Can I apply a logarithmic scale to zero or negative values in my dataset?

No. Mathematical rules dictate that the logarithm of zero or a negative number is undefined. You must remove, filter, or apply an offset to zero and negative values in your dataset before attempting any logarithmic transformation.

Is Microsoft planning to add log scales to Box-and-Whisker charts in future updates?

While Microsoft frequently updates Office 365 features based on user feedback, there has been no official announcement regarding the addition of a logarithmic Y-axis specifically for native box-and-whisker charts.

Excel & Data Problems