How to Add a New Name to Multiple Excel Worksheets and Update Totals

Hold down the Ctrl key (or Shift key for consecutive sheets) and click the tabs of all the weekly worksheets you want to update.

Navigate to the correct alphabetical position for the new name. Right-click the row number on the left side of the screen and select Insert.

Type the new person's name into the newly inserted row. Because the sheets are grouped, this name is typed into all selected sheets simultaneously.

Right-click any sheet tab and select Ungroup Sheets. This is a critical step to prevent accidentally overwriting data during normal editing.

Check your Totals sheet. If you used a 3D reference like =SUM(Week1:Week4!B2), you may need to drag the formula down or ensure the new row is encompassed by your summary calculations.

Select the data in each weekly sheet and press Ctrl + T to convert them into standard Excel Tables. Name them logically (e.g., Week1, Week2).

Go to the Data tab, click on Get Data, choose From File, and then From Workbook to import your current file.

In the Navigator window, select multiple items and choose the tables you just created. Click Transform Data, then use the Append Queries feature to combine all weekly tables into one master query.

Once appended, click Close & Load To... and choose to output the data as a PivotTable Report on a new sheet.

Set up your PivotTable with Names in the Rows area and Attendance/Values in the Values area. Moving forward, you only need to add names to your weekly tables and hit Data > Refresh All to update your totals.