Document Editing Problems

How to Update an Excel Employee Attendance Calendar from 2023 to 2024

Amos Gikunda Oct 10, 2026 869 views

Question details

The user needs to transition an existing Excel employee attendance tracking workbook from the year 2023 to 2024.

How to Update an Excel Employee Attendance Calendar to a New Year

Product: Excel
Device & OS: not provided
Scenario: Preparing an attendance tracking workbook for a new calendar year without manually recreating the entire document.
Observed behavior: The current workbook is configured for 2023 using named ranges and tables. The calendar year and holiday values must be updated to automatically reflect 2024 dates.

Before you start

Verify if the workbook is protected with a password, as sheet or structural protection will prevent you from editing defined names and tables.

Solution 1Recommended

Update Calendar Year and Holidays using Name Manager

Modify the defined names for the calendar year and holiday lists to instantly update the workbook's formulas for 2024.

Attendance templates often rely on a centralized 'Name Manager' to control the global year setting and holiday exclusions. Updating these master values will automatically recalculate the dates across your entire workbook.

Access the Name Manager

Open your attendance workbook, navigate to the Formulas tab on the top ribbon, and click on 'Name Manager'.

Update the calendar year

In the Name Manager dialog box, scroll to find the named range labeled 'calendar_year' (or similar). Select it, and change its 'Refers to' value or direct numeric value from 2023 to 2024.

Modify the holiday dates

Locate the named range designated for holidays. Edit the corresponding cells or the range values to replace the old 2023 holiday dates with the correct dates for 2024.

Add new employee rows

Navigate to your employee data table. Because the workbook uses formatted Excel tables, simply type new employee information in the row immediately below the table, and the range will automatically expand to include them.

Update Calendar Year and Holidays using Name Manager

Cannot Edit Defined Names?: If you select a defined name but cannot edit its value (the edit options are grayed out), check your workbook protection settings by going to Review > Unprotect Workbook.

Seamless Spreadsheet Management

Easily Manage Attendance Calendars with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel features like Name Manager, named ranges, and dynamic tables. You can easily update your attendance workbooks for the new year with a highly compatible, familiar interface.

1. Open the Workbook: Launch WPS Spreadsheet and open your existing attendance tracker file.
2. Open Name Manager: Navigate to the Formulas tab and select Name Manager from the ribbon.
3. Update the Year: Select the 'calendar_year' item, change the reference value to 2024, and apply the changes.
4. Refresh Holidays: Update the holiday date ranges within the sheet, and the calendar formulas will adjust automatically.

100% compatible with Microsoft Excel (.xlsx, .xls) filesAdvanced Name Manager for handling complex calendar formulasDynamic tables that expand automatically with new dataFree built-in attendance tracker and calendar templates

Frequently Asked Questions

Why won't my Excel tables automatically expand when I add a new employee row?

If your table does not automatically expand, the auto-expand feature might be disabled. Go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type, and ensure the box for 'Include new rows and columns in table' is checked.

Why is the Edit button grayed out in the Name Manager?

The Edit button may be disabled if the worksheet or the entire workbook is protected. To fix this, navigate to the Review tab and click 'Unprotect Sheet' or 'Unprotect Workbook'. You may need to enter a password if the original author locked it.

Do I need to manually change every date cell in the attendance tracker?

No. If the workbook is properly built using a central 'calendar_year' named range and DATE formulas, changing the single master year value in the Name Manager will dynamically recalculate and update all the linked dates across the calendar.

Document & File Issues