Who asked: Rosa, operations lead at Tallpine Outdoor, a chain of three outdoor gear stores.
Why: every store manager keeps their own sales file. At month end Rosa copies the three files into one by hand, and last month a pasted row went missing. She wants a report file that reads the store files directly, so it is right the moment the month closes.
The data: three store files, Harbor, Midtown and Westfield, each with a Sales tab: Store, Date, Category, Product, Units, Revenue, one row per sale for October 2026. The addresses are:
- Harbor:
https://docs.google.com/spreadsheets/d/1kTallpineHarborOctPracticeR7v2Lp/edit
- Midtown:
https://docs.google.com/spreadsheets/d/1kTallpineMidtownOctPracticeT3q8Bd/edit
- Westfield:
https://docs.google.com/spreadsheets/d/1kTallpineWestfieldOctPracticeK5m1Xc/edit
The report file has a Report tab with each store's monthly target, and a Data tab with headings ready for the combined table.
What done looks like: revenue per store and % of target, one combined Data table, revenue by category, the top five products by units, a pivot table of category by store, stores below target highlighted, a one-line headline, and the store Rosa should visit first.
About this practice copy: it cannot reach other files or your account, so the three store files are built in and work with the addresses above.
This project closes the Advanced level by putting its tools to work on one deliverable. You connect files with IMPORTRANGE, stack them inside QUERY, summarize with QUERY and a pivot table, flag rows with conditional formatting, and write a headline with TEXT.
Each step starts where the last one ended. Two habits make this kind of report last: read the other files with open-ended ranges such as "Sales!A2:F", so next month's longer files still fit, and drop the empty rows those ranges bring with where ... is not null.