Yes. In Excel for Microsoft 365 on Windows, create a PivotTable, place the date field in Rows and Values, then right-click a date in the PivotTable, choose Group, and select Months and Years. This gives you a monthly count you can chart; for a true histogram of numeric measurements, use Excel’s Histogram chart instead.
What “monthly histogram” can mean
The phrase can describe two different charts. One shows how many records fall in each calendar month—for example, the number of orders in January, February, and March. The other shows the distribution of numeric values, such as the number of orders in different price ranges, with the data filtered or separated by month. Choosing the right approach depends on which question you need the chart to answer.
A standard histogram groups numeric observations into ranges, called bins, and plots the frequency in each range. Excel’s built-in Histogram chart treats date values as numbers too: Excel stores dates as serial numbers. It does not automatically understand that you want calendar-month categories. If you select dates and insert a histogram, Excel may group the serial values into numeric intervals rather than produce one sensible bar per named month.
For a count of events over time, a PivotTable or a monthly summary table is usually clearer. The resulting chart is commonly a column chart, even if people informally call it a monthly histogram. If you actually want the distribution of sales amounts, durations, or another measurement, a Histogram chart may be the better fit.
Prepare the date data first
Before grouping or charting, make sure each observation has a usable date. A simple source table might have one row per order, with an Order Date column and an Order ID column. Select the data and use Insert > Table if it is not already an Excel table; tables make it easier to include new rows in later summaries. Give the columns clear headers and avoid blank rows within the data.
Check that dates are real Excel dates, not text that merely looks like a date. One clue is that changing the cell’s number format changes how a genuine date displays. Text dates may sort incorrectly or prevent date grouping. If the source contains text dates, convert them to actual dates before proceeding, taking care to confirm the day-and-month interpretation matches the source. Regional date settings can make an ambiguous value such as 03/04/2025 mean different dates.
- Use one consistent date field for the event date you want to analyze.
- Remove or correct blank and invalid dates; a blank date cannot be assigned to a calendar month.
- Decide whether each row represents one event. If there are repeated rows per event, a simple count will count rows, not distinct events.
- Include multiple years in the source if needed. Keeping the year is essential when comparing, for example, January 2025 with January 2026.
Make a monthly count with a PivotTable
Click a cell in the source data, then choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK. In the PivotTable Fields pane, drag the date field to Rows. Drag a field that is populated for every record—often an order ID or the date field itself—to Values.
Excel may summarize the Values field as Sum or Count depending on its contents. To count records, open the field’s drop-down in the Values area, choose Value Field Settings, select Count, and confirm. If the chosen field contains blanks, those blanks are not counted, so use a reliably populated field when you want the number of records.
To group the row dates, right-click one of the date labels in the PivotTable and choose Group. In the Grouping dialog, select Months and Years, then choose OK. Selecting both matters: grouping by Months alone can combine all Januaries together, all Februaries together, and so on across different years. With Years included, the PivotTable keeps each calendar year distinct and places its monthly periods in chronological context.
If Group is unavailable or Excel reports that it cannot group the selection, inspect the source dates for text, blanks, or errors, and refresh or rebuild the PivotTable after correcting the data. The exact PivotTable Analyze tab label can vary with context, but the right-click Group command is the direct way to open date grouping in desktop Excel for Windows.
Turn the monthly summary into a chart
Click inside the grouped PivotTable and choose PivotTable Analyze > PivotChart. Select a chart type that suits the comparison, such as a clustered column chart, and confirm. A column chart makes it easy to compare monthly totals. A line chart can emphasize a trend over time, especially when you have many months, though it is not a histogram either.
Check the chart’s horizontal axis before relying on it. It should show months in chronological order, separated by year where appropriate. If it instead shows only Jan through Dec while your data spans several years, revisit the grouping and ensure Years is selected. Add a useful title, such as “Orders by Month,” and label the value axis if readers might not know whether the bars represent orders, revenue, or another measure.
PivotCharts are linked to their PivotTable. To include new records, add them to the source table and refresh the PivotTable using PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. If the chart does not update as expected, verify that the PivotTable’s source includes the newly added rows. A source formatted as an Excel table generally makes expansion more straightforward.
Build a formula-based monthly summary
A helper table is useful when you want a simple chart that is not tied to a PivotTable, or when you want to control the month labels and layout. Put the first day of each month in a Month column as a real date, then format those cells to display a label such as “Jan 2025.” Keeping the underlying value as a date preserves chronological sorting; typing month names as text can lead to alphabetical ordering.
For a concrete example, suppose event dates are in A2:A8, with January 5 and January 20, 2025; February 3, February 3, and February 28, 2025; and March 1 and March 31, 2025. Put the first of January, February, and March 2025 in D2:D4. With event dates in A2:A8 and the month-start date in D2, enter this formula in E2 and fill it down alongside the other month-start dates:
=COUNTIFS($A$2:$A$8,">="&D2,$A$2:$A$8,"<"&EDATE(D2,1))
The formula counts dates on or after the month start and before the first day of the next month. The example returns 2 for January, 3 for February, and 2 for March, for seven records altogether. The inclusive start and exclusive end also handle dates that contain times: an event late on the last day of a month is still counted in that month. If the source dates are stored as text, COUNTIFS will not reliably treat them as date values, so correct the source first.
Select the month and count columns and choose Insert > Insert Column or Bar Chart > Clustered Column. Format the month-start cells to show readable labels without changing their underlying dates. Unlike a PivotChart, this ordinary chart does not refresh a PivotTable; formulas recalculate when the source changes, but make sure the source range includes any new records.
When to use Excel’s Histogram chart
Choose the built-in Histogram chart when the question is about the distribution of a numeric measurement, not the number of events in each calendar month. For instance, you might ask how many delivery times fall into intervals of 0–2 days, 2–4 days, and 4–6 days. Select the numeric observations, then choose Insert > Insert Statistic Chart > Histogram. Excel calculates bins, which you can adjust by formatting the horizontal axis and changing options such as bin width or number of bins.
A histogram’s bins are ranges of values, and its bars normally touch because the ranges are continuous. A monthly count chart uses discrete calendar categories; columns may have gaps, and the axis labels represent periods, not numeric intervals. Changing a Histogram chart’s bin width does not turn it into a calendar-aware monthly report. For a monthly comparison of counts, first aggregate by month and chart that summary.
You can also use both views for different questions. For example, summarize each month’s average delivery time in a PivotTable to compare month-to-month performance, then use a Histogram chart on the individual delivery times to see whether most deliveries cluster around a typical duration or whether a long tail of delays exists. The summary chart and the histogram answer complementary questions and should not be treated as interchangeable.
Choose the right chart and measure
Before building the chart, define what one count represents and what belongs in each month. A row count is suitable when every row is one event. If an order can occupy several rows, counting rows may overstate the number of orders; count a unique order identifier instead, or restructure the data so each order appears once. A PivotTable’s Count is not automatically a distinct count.
- Monthly volume: Use a PivotTable grouped by Months and Years, then a column or line chart.
- Seasonality across years: Keep the year in the PivotTable and compare matching months without merging years unintentionally.
- Distribution of numeric values: Use a Histogram chart with the measurements, and choose bins that make sense for the data.
- Monthly distribution of a measurement: Filter or separate the measurements by month and create a histogram for each period, or use a PivotTable to summarize suitable statistics by month.
Counts and rates also tell different stories. If one month has more days, a higher event count may partly reflect a longer period. If you are comparing rates, such as incidents per operating day, calculate or summarize a rate with an appropriate denominator rather than labeling raw counts as rates. Make that distinction clear in the chart title and axis labels.
Common problems and fixes
Months appear out of order: Use actual dates as month labels, or use the PivotTable’s date grouping. Text labels can sort alphabetically, putting April before February. If you use month names without years, you also lose the year distinction.
January totals combine across years: Group by both Months and Years. A month name on its own does not uniquely identify a period when the source spans multiple years.
The count is lower than expected: Check whether the field in Values contains blanks, whether some dates are text, and whether the PivotTable is refreshed. Also confirm that the intended event is represented by one row. A Count of a partially blank field counts only its nonblank cells.
Dates are grouped into odd numeric ranges: You likely inserted a Histogram chart directly from date serial values. Replace it with a grouped PivotTable and a column chart, or build a month-start summary table and chart that.
Only some months appear: A PivotTable normally displays categories present in its source. If a month has no records, it may be absent unless you configure the PivotTable to show items with no data or provide a complete month list in a separate summary. A formula-based helper table with every month can make zero-count periods explicit.
Frequently Asked Questions
Can Excel make a histogram with one bar for each month?
For a monthly count, use a PivotTable grouped by Months and Years and create a column chart. Excel’s Histogram chart groups numeric values into bins and does not interpret dates as calendar months.
How do I group dates by month in an Excel PivotTable?
Put the date field in Rows, right-click a date label, choose Group, select Months and Years, and choose OK. Use Years as well as Months when the data covers more than one year.
Why can’t Excel group my dates?
Look for blank cells, text-formatted dates, or error values in the source date field. Convert valid date text to real Excel dates, correct or remove invalid entries, then refresh or recreate the PivotTable.
Should I use a histogram or a column chart?
Use a Histogram chart for the frequency distribution of numeric measurements across value ranges. Use a column chart for monthly categories such as orders per month; it is often informally called a monthly histogram but is technically a different chart.
How can I show months with zero records?
Create a complete list of month-start dates in a helper table and calculate each month’s count, or configure a PivotTable to show items with no data where applicable. A complete month list makes missing activity visible rather than leaving a gap unexplained.
Excel for Mac and Excel for the web
The date-grouping and chart concepts are the same in Excel for Mac, although ribbon placement and labels can differ by release. Right-click a PivotTable date and look for Group; if the command is missing, check the source dates and blanks as described above. On Mac, many shortcuts differ from Windows—for example, common copy and paste shortcuts use Command rather than Ctrl—but the steps in this guide do not depend on a keyboard shortcut.
Excel for the web supports many PivotTable and chart tasks, but some grouping or formatting controls can differ from the desktop application. If a particular grouping option is unavailable in the browser, open the workbook in desktop Excel for a more complete set of PivotTable controls. In either version, keep the source dates valid and the year visible when a report spans multiple years.
Related
- Excel Auto Group Rows: How to Group and Ungroup
- Excel Auto Group Rows by Cell Value: What You Can Do
- Excel Conditional Formatting on All Sheets
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Google Sheets Chart Custom Font: What You Can ChangeYes, you can change a chart’s font in Google Sheets: select the chart, open the Chart editor, choose…
- How to Hide Zeros in an Excel Pivot Chart ProperlyIn Excel for Microsoft 365 on Windows, the right way to hide zeros in a PivotChart depends on…
- Excel Bar Graph Too Skinny? Fix Bar Width and SpacingIf an Excel bar graph looks too skinny, select a bar, open Format Data Series, and reduce Gap…
- Excel Pivot Table Not Showing Dates Correctly?If an Excel PivotTable is showing dates as numbers, text, individual days, or unexpected months, first check that…
- Pivot Chart Formatting Options in Excel 365 ExplainedIn Excel for Microsoft 365 on Windows, format a PivotChart with the contextual Chart Design and Format tabs:…
- Excel Bar Graph: Sort Values Largest to SmallestTo sort an Excel bar graph from largest to smallest, sort the chart’s source data by its value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.