Excel Histogram Log Scale: What Works and What Doesn’t

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, you can usually put a built-in Histogram chart’s vertical frequency axis on a logarithmic scale: right-click that axis, choose Format Axis, then under Axis Options select Logarithmic scale. But Excel’s built-in histogram uses equal-width numeric bins, so a log-scaled axis does not create logarithmically spaced bins; for that, transform the data or build a chart from custom bins.

What a log scale changes

A logarithmic axis spaces values according to their ratios rather than their differences. On a base-10 scale, the distance from 1 to 10 is the same as the distance from 10 to 100. On a standard, or linear, axis, equal distances represent equal additions: for example, 0 to 10 and 10 to 20.

This can help when values cover a wide range. A few large observations can dominate a linear chart, making the smaller values hard to distinguish. A log scale can make patterns across several orders of magnitude easier to see. It does not change the underlying observations or their counts; it changes how the axis represents those counts.

“Logarithmic histogram” can mean two different things, so it helps to identify the goal before changing a setting:

  • Logarithmic frequency axis: the bar heights, which represent counts, are displayed on a log scale.
  • Logarithmically spaced bins: the intervals along the data axis grow by a constant ratio, such as 1–10, 10–100, and 100–1,000.
  • Histogram of transformed data: each observation is converted, often with a base-10 logarithm, before the histogram is made. Equal-width bins in the transformed values correspond to ratio-based intervals in the original data.

These approaches answer different questions. Changing the vertical axis does not change which observations fall into each bin. Transforming the data or choosing geometric bin edges does.

Set the frequency axis to logarithmic

For a built-in Histogram chart in Excel for Microsoft 365 on Windows, use these steps:

  1. Select the data and choose Insert > Insert Statistic Chart > Histogram.
  2. Click the vertical axis, which shows frequency or count, and right-click it.
  3. Choose Format Axis. In the Format Axis pane, open Axis Options and select Logarithmic scale.
  4. Check the axis labels and bars. If needed, adjust the logarithm base or the axis bounds in the same pane.

The horizontal axis of a Histogram chart represents bins. It is not the numeric frequency axis, so selecting the logarithmic option for the vertical axis will not make the bin boundaries logarithmic. If Excel does not offer Logarithmic scale for the selected axis or chart, use a custom frequency table and a regular column chart instead.

On Mac, the chart and Format Axis controls are available in desktop Excel, although ribbon labels and pane layout can differ slightly by release. Select the vertical axis and open its formatting pane to look for the logarithmic option. Excel for the web may not expose every chart-formatting control; if the option is missing, open the workbook in desktop Excel.

Understand Excel’s histogram bins

A histogram groups numeric observations into intervals and displays the number of observations in each interval. Excel’s built-in Histogram chart chooses bins automatically, but you can change the grouping by formatting the horizontal axis. Right-click the horizontal axis and choose Format Axis. In Axis Options, look for controls such as bin width, number of bins, underflow bin, and overflow bin.

Bin width is an ordinary difference between values. For example, a bin width of 10 creates intervals roughly like 0–10, 10–20, and 20–30. Those are equal-width bins on the original measurement scale, even if the vertical count axis is logarithmic. A chart with edges at 1, 10, 100, and 1,000 instead uses intervals that expand by a factor of ten.

Automatic bins are convenient for an initial view, but they may not suit data with a long right tail or values spanning many powers of ten. Always inspect the bin boundaries and labels. The same data can look very different under different bin choices, and a log axis does not remove that choice.

Make bins that grow by a ratio

For positive measurements spanning a broad range, geometric bin edges can be more informative than equal-width bins. A simple example is 1, 10, 100, and 1,000. Each interval covers a tenfold increase. You can create a frequency table for those intervals and plot the counts with a regular column chart.

For example, suppose the observations are in A2:A100, and the lower and upper edges of each bin are in B2 and C2. With the first interval defined as 1 inclusive to 10 exclusive, enter =COUNTIFS($A$2:$A$100,">="&B2,$A$2:$A$100,"<"&C2) in D2. If B2 is 1 and C2 is 10, it counts values from 1 up to, but not including, 10. Copy the formula down for subsequent intervals, making the upper edge of one interval the lower edge of the next. This convention prevents values on a boundary from being counted twice.

Label each interval clearly, such as 1–<10 and 10–<100, and select the labels and counts to insert a column chart. A standard column chart treats the labels as categories; it will show the custom groups you supplied rather than infer a continuous logarithmic horizontal axis. If you want to emphasize the ratio spacing visually, label the groups explicitly and explain the boundaries in the chart or its caption.

Make a histogram of log-transformed data

Another practical method is to calculate the logarithm of every positive observation and make a histogram of those results. With the original measurements in A2:A100, put this formula in B2 and fill it down: =LOG10(A2). If A2 contains 100, the formula returns 2, because 10 raised to the second power is 100. All values in the source range must be greater than zero; zero and negative values do not have real base-10 logarithms.

Insert a Histogram chart using the transformed values in column B, then choose a bin width that makes sense in log units. A width of 1 groups values by powers of ten: a transformed interval from 1 to 2 corresponds to original values from 10 to 100. A width of 0.5 corresponds to a factor of about 3.16 in the original data, since 10 raised to 0.5 is approximately 3.16.

This chart answers a specific question: how are the logarithms of the measurements distributed? Its horizontal labels are in log units, not the original units. If readers need the original ranges, label or document them—for example, log10 values 1 to 2 correspond to measurements 10 to 100. Do not describe this as merely turning on a log axis; the data have been transformed, which changes the grouping.

Zeros, negative values, and empty bins

A logarithmic axis cannot represent zero or negative values. This matters especially for a frequency axis: an empty bin has a count of zero, and zero cannot be placed on a log scale. If the chart contains empty bins, Excel may refuse the setting, omit or fail to display those values, or produce a chart that is misleading for the intended comparison.

Do not replace zero counts with an arbitrary small positive number just to make a logarithmic chart work. That changes the data being shown. Instead, consider combining bins so that every displayed bin has a positive count, restricting the chart to a clearly stated range, or using a linear frequency axis. If you create a custom table, omit empty categories only when doing so will not hide meaningful gaps; state that choice in the chart notes.

For a log-transformed horizontal variable, zero and negative observations also require attention. A measurement of zero cannot be logged, and a negative measurement cannot be logged in the ordinary real-valued way. Do not silently discard them. Explain how they are handled, and consider a separate category or a different analysis if those observations are meaningful.

Choose the method that answers your question

Use the method that matches what needs to be compared. The choice is not simply between a linear and a logarithmic-looking chart:

  • Use a log frequency axis when counts are all positive and the differences among small and large counts are both important. The bin definitions remain unchanged.
  • Use a log-transformed data histogram when the measurements are positive and multiplicative differences matter—for instance, comparing values that differ by factors of ten.
  • Use custom geometric bins when the groups themselves should represent ratio ranges and the counts in those ranges are the quantities of interest.
  • Keep a linear scale when zero counts matter, when the range is modest, or when readers need to compare absolute differences in frequency.

For instance, if a sample contains measurements from 2 to 800, equal-width bins may put most observations into a few low-value groups. A histogram of log10 values or a table of geometric intervals may show the distribution more clearly. But if the question is how many observations fall in each 10-unit interval, keep equal-width bins; transforming the data would answer a different question.

Common problems and checks

If the logarithmic option is unavailable, first confirm that you selected the vertical frequency axis rather than the horizontal bin axis. Then check whether the chart includes zero or negative values. If the control still is not available for that chart, build the bin counts in cells and use a regular column chart, where the count axis can be formatted separately.

If the bars look unexpectedly different after changing the axis, remember that equal visual heights on a log axis represent equal ratios, not equal count differences. A change from 1 to 10 has the same vertical distance as a change from 10 to 100, though the added counts are 9 and 90 respectively. Include a clear axis title such as “Frequency (log scale)” and retain readable tick labels so readers do not mistake the display for a linear count scale.

Finally, check that the chart is actually a Histogram chart and that the selected range contains numeric observations rather than pre-counted bins. A histogram chart groups raw observations automatically. A custom frequency table is already summarized, so it normally belongs in a column chart; feeding those counts to a histogram can cause Excel to bin the counts themselves, which is not the intended result.

Frequently Asked Questions

Can the horizontal axis of an Excel histogram use a log scale?
Not as a conventional numeric log axis in the built-in Histogram chart: its horizontal axis is organized around bins. To get ratio-based intervals, create custom geometric bin edges and count the observations, or transform positive data with LOG10 and histogram the transformed values.

Can I use a log scale when a histogram has zero-count bins?
A log axis cannot display zero. Combine or redefine bins only if that remains meaningful, or use a linear count axis. Avoid substituting a made-up positive count for zero.

Does setting the frequency axis to log change the bin width?
No. It changes only the display of the counts on the vertical axis. Change the bin width separately, or use transformed data or custom edges if you need different intervals.

Can I take the log of data that includes zero?
Not with the ordinary LOG10 function: zero and negative numbers do not produce real-valued base-10 logarithms. Investigate those observations and choose a transparent treatment rather than silently dropping them.

Should I use a log scale or a logarithmic transformation?
Use a log scale to display positive axis values by ratios while retaining the chart’s underlying counts or measurements. Transform the data when you want the histogram’s bins and distribution to be based on logarithms of the observations. State which method you used because they are not interchangeable.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.