Excel Histogram by Date: Group Time into Useful Bins

Charts & pivot tables · Updated November 19, 2026

To make an Excel histogram by date, select a column of real Excel dates, then choose Insert > Insert Statistic Chart > Histogram. Right-click the horizontal axis, choose Format Axis, and set a bin width in days; for calendar months or years, use a PivotTable or COUNTIFS formulas instead, because those periods are not equal-length date intervals.

What a date histogram shows

A histogram counts how many observations fall within successive numeric intervals, called bins. With dates, the observations might be order dates, service calls, sign-ups, or inspection records. The chart can show whether activity is concentrated in one period, spread evenly, or unusually high at certain times.

Excel stores dates as serial numbers, so a date can be treated as a number for this purpose. The whole-number portion represents a day, while a decimal portion can represent a time of day. A histogram can therefore divide a range of dates into equal spans, such as seven days per bin. It does not automatically understand that a month is a named calendar period or that months have different numbers of days.

That distinction matters: a seven-day bin is useful for a rolling distribution, but it is not necessarily the same as a Monday-to-Sunday calendar week. Likewise, a 30-day bin is not the same thing as January, February, or any other calendar month.

Check that Excel recognizes the dates

Before creating a chart, confirm that the source values are dates Excel can use as numbers. A cell that looks like 4/12/2026 might instead contain text imported from a file. A quick check is to change a sample cell’s number format to General. A genuine date displays as a serial number; text generally remains text. Restore a date format afterward if needed.

Check the column for blanks, headings mixed into the data, error values, and inconsistent date conventions. For example, 03/04/2026 can mean March 4 or April 3 depending on the source’s regional format. Verify ambiguous imports before analyzing them. If the records include timestamps, keep in mind that a value such as noon is not an integer date; it includes a fractional day.

For a straightforward chart, put one observation date in each cell of a single column and use a descriptive header. Keep the source as raw observations rather than a pre-counted summary. If the data is in an Excel Table, adding new dates to the table can make the source easier to maintain, though you should confirm that the chart’s source range includes the new rows.

Create a histogram with day-based bins

  1. Select the date values, including the header if it is clearly labeled. Avoid selecting unrelated columns.
  2. On the Windows ribbon, choose Insert > Insert Statistic Chart > Histogram. Excel creates a chart and assigns bins automatically.
  3. Right-click the horizontal axis and choose Format Axis. In Axis Options, find Bins and select Bin width.
  4. Enter the number of days for each bin. For example, enter 7 for seven-day intervals or 30 for 30-day intervals.
  5. If appropriate, set an Underflow bin or Overflow bin to collect values below or above a chosen boundary.
  6. Give the chart a specific title and check that the horizontal-axis labels display as dates.

The bin width is a numeric interval in the underlying date values. A width of 7 means seven days, not seven weeks. Excel’s automatic boundaries may also start from a point determined by the data, rather than lining up with the start of a calendar week or month. If exact calendar boundaries matter, use one of the calendar-based methods below.

To make the axis easier to read, right-click it, choose Format Axis, and use the Number options to apply a date format if the labels appear as serial numbers. The exact display format depends on your Excel build and the available axis settings. A date format changes how a value is shown; it does not change the bin boundaries or the data.

Choose a bin size that answers the question

Bin width affects the story the chart tells. Narrow bins reveal short-lived peaks and gaps but can make a small data set look noisy. Wider bins smooth the pattern, though they can conceal useful detail. There is no single ideal width: choose one that matches the reporting period and the number of observations.

  • Daily bins: Useful when dates span a short period and the timing of individual busy days matters. They can create many empty bins over a long period.
  • Seven-day bins: Useful for a broad weekly view. Remember that these are consecutive seven-day spans, not necessarily calendar weeks.
  • Thirty-day bins: A rough way to summarize longer spans, but not a substitute for calendar months.
  • Automatic bins: A reasonable starting point for exploration. Review the resulting number and range of bins before using the chart in a report.

For example, a 90-day set of daily service records might be readable with seven-day bins. A multi-year sales history may need monthly calendar totals instead. If people need to compare January with February across years, use calendar grouping and include the year; pooling every January together answers a different question from showing each year separately.

Group dates by calendar month in a PivotTable

For monthly, quarterly, or yearly counts, a PivotTable is often the clearest option. It groups dates by calendar units rather than by equal numbers of days. Start with a data table containing a date field and, if useful, a record ID or another field that is populated once per observation.

  1. Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
  2. In the PivotTable Fields pane, drag the date field to Rows. Drag a field that is present for each record, such as an order ID, to Values.
  3. In Values, confirm that the field is summarized by Count, not Sum. If Excel chooses a different summary, open Value Field Settings and select Count.
  4. Right-click a date label in the PivotTable and choose Group. Select Months and Years if you need separate monthly results for each year, then confirm.
  5. With the PivotTable selected, choose PivotTable Analyze > PivotChart and select a column chart to display the grouped counts.

Grouping by both Months and Years prevents January records from different years from being combined into one January total. If the PivotTable reports that it cannot group the selection, inspect the date field for blanks, text, or errors. Correct those source values, refresh the PivotTable, and try grouping again. A PivotTable chart built from monthly categories is a grouped count chart; it is not the same calculation as a histogram with equal-width numeric bins.

Count custom date periods with formulas

COUNTIFS is useful when you want to define the exact intervals yourself or make a small summary table next to the data. Use inclusive start dates and exclusive end dates. The exclusive end is important when source dates include times: a record at 11:00 p.m. on the last day is still counted before the next period begins.

For this example, assume dates are in A2:A100, the first period’s start date is in D2, and the next period’s start date is in E2. The formula counts records on or after D2 and strictly before E2:

=COUNTIFS($A$2:$A$100,">="&D2,$A$2:$A$100,"<"&E2)

With D2 set to January 1, 2026, E2 set to February 1, 2026, and four source dates in January, the result is 4. Copy the formula down alongside later start and end dates to calculate successive periods. Format the start-date cells as dates so the summary is understandable.

For a monthly summary, set each period’s start and next-start cells to the first day of consecutive months. For example, January 1 and February 1 define January, while February 1 and March 1 define February. This method handles the varying lengths of calendar months and includes timestamps on the final day of a month without needing to guess at the final time value.

If you want a chart from the summary, select the period labels and counts, then insert a column chart. The resulting chart is a frequency summary by chosen period. It can be easier to label and control than a built-in histogram when the reporting boundaries must match a calendar, a fiscal year, or a custom business period.

Interpret and format the result carefully

In a histogram, the height of each column is the number of observations in that bin. A high column means many records fell within that interval; it does not, by itself, explain why. Compare the pattern with the underlying business process and check whether missing records, seasonal effects, or a change in how dates were recorded could account for a peak.

Use a chart title that identifies the measure and period, such as “Service requests by seven-day date interval.” Label the vertical axis as a count if the chart is not self-explanatory. Avoid implying that the bins are calendar weeks if they are simply seven-day spans. If a report needs exact dates, add a companion table with interval start and end dates.

Counts are not rates. If the number of customers, operating days, or opportunities changes over time, a higher count might reflect a larger underlying population rather than a higher rate. A histogram of dates also does not show the order of individual events in the way a time-series line chart does. Choose the chart based on whether the question is about frequency within intervals or change over time.

Common problems and practical fixes

  • The chart will not accept the dates: Check whether the values are text, and verify that the selection contains usable dates rather than mixed labels or errors.
  • The axis shows large numbers: Those may be Excel date serials. Apply a date number format to the axis, while remembering that formatting does not alter the bins.
  • The monthly counts look wrong: A fixed-width histogram does not create calendar months. Use a grouped PivotTable or a COUNTIFS summary with calendar boundaries.
  • Records at the end of a period are missing: Check the interval logic. A formula using less than the next period’s start includes every time on the prior day; a formula using less than or equal to a midnight end may not.
  • PivotTable grouping fails: Look for blank, text, or error entries in the date field. Make the source values consistent, refresh the PivotTable, and try again.
  • New rows do not appear: Check the chart or PivotTable source range. Using an Excel Table as the source can help include added records, but refresh a PivotTable after changes.

Histogram, PivotTable, or line chart?

Use Excel’s Histogram chart when the question is how observations are distributed across equal numeric date intervals. It is quick to create and offers bin controls, but it is less suitable when labels must follow named calendar periods.

Use a PivotTable when readers expect monthly, quarterly, or yearly categories and want to filter or regroup the results. Use COUNTIFS when interval boundaries must be explicit, when you need a custom fiscal calendar, or when a transparent calculation table is more useful than an interactive summary.

Use a line chart when the goal is to show a sequence over time, such as daily totals or month-by-month trends. A line chart usually needs a prepared summary if the source contains many individual events. These tools can complement one another: a histogram can show the distribution of event dates, while a calendar-based chart can show how counts changed across reporting periods.

Frequently Asked Questions

Can Excel make a histogram from dates?
Yes. Excel dates are stored as numeric serial values, so a Histogram chart can bin them. Select real date values and choose Insert > Insert Statistic Chart > Histogram. Use a PivotTable or formula summary when you need calendar months rather than equal-length bins.

How do I make a monthly histogram in Excel?
For calendar-month totals, create a PivotTable, put the date field in Rows, and group the dates by Months and Years. Alternatively, count dates between each month’s first day and the next month’s first day with COUNTIFS, then chart the summary.

What bin width should I use for dates?
Choose a width that fits the time span and the question. A width of 7 represents seven days, while 30 represents 30 days; neither automatically aligns with calendar weeks or months. Try a sensible width, inspect the chart, and adjust it for readability.

Why does my date axis show numbers?
Excel stores dates as numbers, and the chart may display those serial values. Open Format Axis and apply a date number format if available. If the source values are text rather than dates, convert or clean them first; changing the display format alone will not convert text.

Can I include dates with times?
Yes. Excel stores time as a fraction of a day, so timestamps can be included in a histogram. For period counts, use a start-inclusive and next-period-exclusive rule, such as greater than or equal to the start and less than the next start, to include all times on the final day.

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.