Summary spreadsheet combining attendance totals from several program sites

One Summary from Many Trackers: Combine Site Sheets with IMPORTRANGE and PivotTables

November 18, 2026•2 min read

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.

  1. Open your summary sheet and add a tab for each site.

  2. In cell A1 of the first site tab, enter: =IMPORTRANGE("paste the site sheet URL here", "Log!A1:D2000")

  3. The first time, click Allow access when prompted.

  4. 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.

  1. Go to Data, then Get Data, then From File, then From Folder.

  2. Choose the folder and click Combine and Transform.

  3. 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.

Back to Blog
Steps To Achieve logo

Steps To Achieve supports education and workforce organizations with program performance, reporting systems, and training.

Get in Touch

  • 724-744-8791

724-744-8791

Monday to Friday 8 AM to 7:30 PM | Saturday 9 AM to 12 PM | Sunday closed

Main office: 8865 Norwin Avenue Ste 27 PMB 323, North Huntingdon, PA 15642

Second office: 11232 Boyette Rd Suite 1107, Riverview, FL 33569

Steps To Achieve © 2026 | All Rights Reserved.

  • Main office: 8865 Norwin Avenue Ste 27 PMB 323, North Huntingdon, PA 15642

  • Second office: 11232 Boyette Rd Suite 1107, Riverview, FL 33569

Let's Connect

Monday to Friday 8 AM to 7:30 PM | Saturday 9 AM to 12 PM | Sunday closed

Steps To Achieve © 2026 | All Rights Reserved.