To put a calendar in Excel, enter a year in B1 and a month number in B2, add weekday headings, then place a formula in B4 to fill a six-by-seven date grid. In Microsoft 365 or Excel 2021, a SEQUENCE formula can generate the grid automatically; format the results to show day numbers and change B1 or B2 to display another month.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or Excel 2021 or later for the spill formula used below. Excel for the web and supported Mac versions also include these functions, though ribbon placement can vary slightly.
- A blank worksheet with room for a seven-column, six-row calendar, plus cells for the year and month.
- The year and month you want to display. The month will be entered as a number from 1 to 12.
Step-by-Step
- Set up the month controls. Open a blank worksheet. In A1, type Year; in B1, enter a year such as 2026. In A2, type Month; in B2, enter a number from 1 to 12. For example, enter 9 for September. Use numeric values rather than typing a month name so the formula can compare dates reliably.
- Add weekday labels. In B3:H3, type the weekday headings in order: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday. This example uses a Monday-first week. If you prefer a Sunday-first calendar, use Sunday through Saturday instead and follow the Sunday-first adjustment described in the Tips section.
- Keep the calendar area clear. Leave B4:H9 empty. The formula will return a six-row by seven-column array, so existing content in any of those cells can block it. You can widen columns B:H and increase row heights to make the calendar easier to read, but do that before or after the formula is in place.
- Enter the calendar formula. With the year in B1, the month number in B2, weekday labels in B3:H3, and the empty calendar grid in B4:H9, enter this formula in B4:
=LET(first,DATE($B$1,$B$2,1),start,first-WEEKDAY(first,2)+1,dates,SEQUENCE(6,7,start,1),IF(MONTH(dates)=$B$2,dates,""))
Press Enter. Excel should spill the results into B4:H9 automatically. The formula finds the first day of the selected month, backs up to the Monday at the start of that calendar week, creates 42 consecutive dates, and displays a date only when its month matches B2. Days belonging to the previous or following month appear as blank cells, keeping the view focused on the selected month.
- Check the dates before formatting. For the example year 2026 and month 9, September 1 is a Tuesday. The first calendar row should therefore show a blank Monday followed by Tuesday the 1st, Wednesday the 2nd, and so on through Sunday the 6th. The last row should include Monday the 28th, Tuesday the 29th, and Wednesday the 30th, with the remaining cells blank.
- Show only the day number. Select B4:H9. On Windows, go to Home > Number Format drop-down > More Number Formats, choose Custom, enter
das the format, and select OK. This changes the display of real Excel dates to numbers such as 1, 2, and 30; it does not change the underlying date values. Depending on the Excel build, you may also be able to typedin the Number Format box on the Home tab and press Enter. - Make the calendar readable. Select B3:H3 and use Home > Bold, then center the weekday labels. Select B4:H9 and choose Home > Alignment > Center. Adjust the column widths and row heights using the Home tab’s Format controls or by dragging the row and column boundaries. Add borders or a fill color if useful, but keep the date cells easy to read.
- Change the displayed month. Replace the value in B1 to change the year, or replace B2 with another month number. The spilled calendar recalculates automatically. If you want to reuse the layout, save the worksheet as a template or duplicate the sheet before changing its formatting.
Using the calendar formula in older Excel
Excel versions without SEQUENCE or dynamic arrays cannot spill the full calendar from one cell. You can still create the same Monday-first calendar by entering a formula in every date cell and filling it across and down. With the year in B1, month number in B2, weekday headings in B3:H3, and the first date cell at B4, enter this formula in B4:
=IF(MONTH(DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),2)+COLUMN(A1)+7*(ROW(A1)-1))=$B$2,DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),2)+COLUMN(A1)+7*(ROW(A1)-1),"")
Copy B4 across to H4 and then copy B4:H4 down through row 9. Relative references in COLUMN(A1) and ROW(A1) adjust as the formula is copied, while the dollar signs keep the year and month controls fixed. For September 2026, the filled grid should again show September 1 on Tuesday and September 30 on Wednesday. Apply the same custom d number format to B4:H9. In Windows, Ctrl+C and Ctrl+V copy cells; on Mac, use Command+C and Command+V. Excel for the web also supports copying and filling formulas, though the fill handle may look slightly different.
Common Mistakes
- Putting text where a number is expected. B1 should contain a year such as 2026, and B2 should contain a month number such as 9. If B2 contains September as text, the month comparison in the formula will not work as intended.
- Blocking the spill range. The SEQUENCE formula needs all cells from B4 to H9 available. A stray space, an old value, or a merged cell in that area can stop the results from appearing. Clear the area and unmerge any cells before entering the formula.
- Using the wrong weekday order. The formula shown calculates a Monday-first week. If the headings begin with Sunday, the dates and labels will not line up unless the start-date calculation is changed too.
- Typing a formula with different separators. The formulas here use commas, as required by standard English-language Excel settings. Some regional installations use semicolons in formulas. If Excel rejects a correctly copied formula, check your regional list-separator setting and replace argument commas with the separator used by your installation.
- Changing the date format instead of the date. A cell that displays 1 may hold the full date September 1, 2026. That is intentional. Avoid replacing formula results with typed day numbers, because doing so breaks the automatic calendar.
- Expecting all six rows to contain dates. Some months occupy only five calendar weeks, so the extra row can be mostly blank. Other months need all six rows. A six-row grid handles both cases without having to rebuild the layout.
Tips
- Switch to a Sunday-first week. If B3:H3 contains Sunday through Saturday, change the weekday calculation in the LET formula so it starts on Sunday. With the same layout in B1, B2, and B4:H9, use
=LET(first,DATE($B$1,$B$2,1),start,first-WEEKDAY(first,1)+1,dates,SEQUENCE(6,7,start,1),IF(MONTH(dates)=$B$2,dates,"")). The argument 1 in WEEKDAY sets Sunday as day one. Do not change the headings without changing this argument. - Highlight today’s date. With the calendar dates in B4:H9, select that range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With B4 as the active cell, enter
=B4=TODAY(), choose a format, and confirm. The relative B4 reference adjusts for each cell in the selected range. The highlight updates based on the current date when Excel recalculates. - Add a title that follows the controls. You can type a fixed title such as September 2026 above the calendar, but it will not update automatically. If you want a changing title, place a formula in a separate title cell. With the year in B1 and month number in B2, enter
=TEXT(DATE(B1,B2,1),"mmmm yyyy")in A4. For 2026 and 9, it returns September 2026. Keep that title outside the B4:H9 spill range. - Keep the underlying dates available. The custom
dformat only hides the month and year portion visually. This is useful if you later add conditional formatting, compare dates, or use the calendar in other formulas. If you need to display the full date again, choose a date format from Home > Number Format.
Troubleshooting
The formula shows #SPILL!. In a dynamic-array version of Excel, select the warning icon or inspect B4:H9 for content, merged cells, or other obstructions. Clear the obstructing cells, unmerge the range, and re-enter the formula in B4. The formula belongs in the top-left cell only; do not copy it into the other five rows and six columns.
The formula is visible as text instead of calculating. Select B4 and check Home > Number Format. If the cell is formatted as Text, change it to General, then press F2 and Enter on Windows to make Excel evaluate the entry again. On Mac, use Control+U to edit the cell and press Return, or re-enter the formula after changing the format. Also check that the entry begins with an equals sign and that Show Formulas is not enabled. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut can differ by keyboard and Excel version, so check Formulas > Show Formulas.
The calendar is one day out of alignment. Compare the weekday headings with the formula’s WEEKDAY setting. The main formula uses WEEKDAY(first,2), which means Monday is day one. For a Sunday-first layout, use WEEKDAY(first,1) and place Sunday in column B. Make sure both the formula and the headings use the same week start.
You see a number such as 46266 instead of a day. Excel stores dates as serial numbers. Select B4:H9 and apply the custom day format d, or choose a date format. If the values still look wrong, verify that the year and month controls are numeric and that the formula has not been overwritten.
Excel reports a problem with LET or SEQUENCE. These functions are available in Microsoft 365 and Excel 2021 or later. If you use an older version, use the fill-across-and-down formula above. If your Excel uses semicolons as argument separators, adapt the formula separators to your regional settings rather than removing parentheses or quotation marks.
Frequently Asked Questions
Can I make a calendar in Excel with one formula?
Yes, in Microsoft 365 or Excel 2021 and later. Enter the LET and SEQUENCE formula once in B4, and it fills the six-by-seven grid. Earlier versions need a formula copied into each date cell.
Does this calendar update when I change the month?
Yes. Change B2 to a month number from 1 to 12, or change B1 to another year. The formula recalculates the displayed dates automatically, provided the formula and spill area remain intact.
Can I show dates from the previous and next month?
Yes. The current formula returns blanks for dates outside the selected month. To show the adjacent-month dates too, use the same grid-generation logic but return the dates array without the IF month test. With the year in B1, month number in B2, and Monday-first layout, the formula is =LET(first,DATE($B$1,$B$2,1),start,first-WEEKDAY(first,2)+1,SEQUENCE(6,7,start,1)). Apply a date format such as d. The first and final rows will then include neighboring-month day numbers.
Can I use month names instead of numbers?
The main setup expects B2 to contain a number from 1 to 12. You can create a dropdown with month names, but that requires a formula or data-validation setup that converts the selected name into a month number. For a simple calendar, entering 1 for January through 12 for December is the least error-prone option.
Will the calendar work in Excel for Mac or Excel for the web?
It works in versions of Excel for Mac and Excel for the web that support LET and SEQUENCE, including current Microsoft 365 versions. The formulas are the same, but ribbon names or menu placement can vary slightly. In older editions without those functions, use the copied-cell formula instead.
Related
- How to Lock an Excel File With a Password to Open It
- How to Lock an Excel Page and Protect Its Cells Safely
- How to Lock an Excel Cell or Range and Protect the Sheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel AVERAGEIF vs AVERAGEIFS: Rules and ExamplesUse AVERAGEIF when you need to average values that meet one condition; use AVERAGEIFS when every result must…
- Excel Day Count Formula: Calculate Days Between DatesTo count calendar days between two dates in Excel, subtract the earlier date from the later one: with…
- Excel Percentage Difference Formula: How to CalculateFor the percentage difference between two nonnegative values in Excel, use =ABS(B2-A2)/AVERAGE(A2,B2), then format the result as a…
- Excel Table Header Formula: What Works and What Doesn’tExcel table headers are column labels, not the place to put a formula you expect to calculate or…
- IF Statements for Dates in Excel: Examples and RulesTo write an IF statement for dates in Excel, compare a cell containing a real date with a…
- Filter Cells by Color in Excel: Steps and TipsTo filter cells by color in Excel for Microsoft 365 on Windows, select your data, choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.