
One Summary from Many Trackers: Combine Site Sheets with IMPORTRANGE and PivotTables
Multi-site programs often end up with one spreadsheet per site. That is fine for the people entering data, but it is painful for the director who needs one number for the board, the funder, or the state. Copying and pasting from six files every month is slow, and it is easy to miss a row.
Here are two ways to build a single summary that updates itself: IMPORTRANGE in Google Sheets and PivotTables in Excel.
Before you start: use the same layout everywhere
Combining sheets only works if every site uses the same columns in the same order, for example Date, Student ID, Activity, and Attendance Code. Build one template, lock the header row, and give a copy to each site. Drop-down lists for codes and activities help keep entries consistent.
Option 1: Google Sheets with IMPORTRANGE
IMPORTRANGE pulls data from another Google Sheet into yours.
Open your summary sheet and add a tab for each site.
In cell A1 of the first site tab, enter:
=IMPORTRANGE("paste the site sheet URL here", "Log!A1:D2000")The first time, click Allow access when prompted.
Repeat for each site.
To stack every site into one list, use curly braces on a Combined tab:
={Site1!A2:D; Site2!A2:D; Site3!A2:D}
Then wrap it in FILTER to drop the blank rows:
=FILTER({Site1!A2:D; Site2!A2:D}, {Site1!A2:A; Site2!A2:A}<>"")
Now the Combined tab updates whenever a site adds a row.
Option 2: Excel with Power Query and PivotTables
In Excel, put every site file in one shared folder.
Go to Data, then Get Data, then From File, then From Folder.
Choose the folder and click Combine and Transform.
Power Query stacks every file into one table and adds a column showing which file each row came from. Click Close and Load.
Each month, drop the new files in the folder and click Refresh All. No copying, no pasting.
Build the summary with a PivotTable
With all the rows in one place, insert a PivotTable (Insert, then PivotTable in Excel, or Insert, then Pivot table in Sheets).
Put Site in Rows.
Put Attendance Code in Columns.
Put Student ID in Values, set to Count.
In a few clicks you see present, absent, and excused counts for every site side by side. Add a slicer for month, and your leadership team can filter the view themselves.
Check your numbers before you share them
A quick quality check saves embarrassment later. Compare the total row count in the combined table with the sum of each site's rows. Use conditional formatting to flag blank Student IDs or codes outside your list. Consistent numbers build trust with funders and boards.
Bring this to your team
Steps To Achieve teaches IMPORTRANGE, PivotTables, Power Query, and more in hands-on Excel and Google Sheets sessions built from your own trackers. Groups are capped at 18 so everyone gets real help, and Pennsylvania educators can earn Act 48 hours.
See Excel and Google Sheets training or Book a free 20-minute call.

