How to answer questions across multiple spreadsheets

The four question patterns

Stack or join first?

Files that share columns (one export per store or per month) are stacked into one long table — see combining Excel sheets into one. Files that share a key (customer ID, SKU, email) are joined side by side — see joining spreadsheets. Many questions need both: stack twelve monthly order files, then join them to the customer list.

A quick test: put the two header rows next to each other. If they are mostly the same, stack. If they share one or two identifying columns and the rest differ, join. If they share nothing, you need a mapping table first, such as a list that links product codes in one system to SKUs in another.

Getting the files ready to answer questions

Questions fail more often because of the shape of the files than because of the formula. Ten minutes of preparation saves an afternoon of wrong answers.

1. One table per sheet, headers in row 1, no title rows, merged cells or subtotal lines inside the data. Turn each range into an Excel table (Ctrl+T, or Cmd+T on a Mac) so formulas can refer to Orders[Customer] instead of fixed ranges.
2. One grain per table: one row per order, or one row per order line, but not both in the same sheet. Mixing them double-counts when you sum.
3. A clean key in every file you will join: the same customer ID format, trimmed, in the same case. Email addresses are a reasonable key if you lower-case them; names usually aren't.
4. Real types: dates as dates, amounts as numbers. A date stored as text cannot be filtered by month, and a number stored as text will not sum.
5. Known coverage: note the date range of each export. A “September” file that stops on the 28th makes the last two days look like lost customers.

Pattern 1: membership — in one list but not the other

Membership questions ask which records appear in list A and not in list B: leads that never bought, invoices with no payment, registrants who didn't attend, products in the catalogue that never sold. The operation is an anti join.

Always run the test in both directions. Leads with no customer record is one question; customers with no lead record (people who bought without ever filling in a form) is a different and often surprising one. Recipes: leads that never became customers, unpaid invoices older than 30 days and registrations against attendance.

Pattern 2: change between two periods

Change questions compare the same measure across two files: last month and this month, last year's price list and this year's, the budget and the actuals. Group each period to the level you care about (one row per customer, per product, per region), then put the two side by side with a full outer join so records that exist in only one period are kept, with zero for the missing side.

In a spreadsheet, the easiest version is a pivot table over both periods stacked together, with Period in the columns. A Change column is then a simple subtraction. In SQL:

SELECT COALESCE(a.customer, s.customer) AS customer,
       COALESCE(a.amount, 0) AS august,
       COALESCE(s.amount, 0) AS september,
       COALESCE(s.amount, 0) - COALESCE(a.amount, 0) AS change
FROM aug a
FULL OUTER JOIN sep s ON s.customer = a.customer
ORDER BY change, customer;

More recipes: month-over-month growth from monthly files, comparing two price lists and comparing two quotes line by line.

Pattern 3: roll-ups across many files

Roll-up questions need every file stacked before they can be grouped: top products across all stores, revenue by month across a year of exports, a ranking of sales reps across regional files. The stacking method depends on where the files live.

Add a column that says where each row came from (store, region, month) before or while stacking. Without it, you can total everything but can't break the total down. Recipes: top products and customers across stores and ranking sales reps across regional files.

Pattern 4: enrichment — join, then group

Enrichment questions need a fact from one file to be grouped by an attribute in another: support tickets by customer tier, returns by supplier, revenue by the sales rep who owns the account. Join the detail table (tickets, order lines) to the lookup table (customers, products), then group.

The trap is a lookup table with duplicate keys. If a customer appears twice in the customer list, every one of their tickets is counted twice after the join. Check first: =COUNTIF(Customers[ID], [@ID]) > 1 flags duplicates, or in SQL SELECT id, COUNT(*) FROM customers GROUP BY id HAVING COUNT(*) > 1. Recipes: support tickets against customer accounts, products with high return rates and late deliveries by supplier.

The same question in a formula and in SQL

“Which customers ordered last month but not this month?”, with each month's orders in its own sheet:

=UNIQUE(FILTER(Last[Customer], COUNTIF(This[Customer], Last[Customer]) = 0))

SELECT DISTINCT l.customer
FROM last_month l
LEFT JOIN this_month t ON t.customer = l.customer
WHERE t.customer IS NULL;

The formula works inside Excel or Google Sheets; the SQL is what a database (or Analistable, behind the scenes) runs. Both return the same list.

Before you trust the answer

• Check the key: trailing spaces and different capitalisation make matching rows look missing.
• Check for duplicates: a customer listed twice in a lookup table doubles their totals after a join.
• Check the period: make sure both files cover the dates you think they do.
• Sanity-check totals: the sum after a join should equal the sum before it, unless rows were meant to drop out.

Asking in plain language

Analistable loads your Google Sheets, Excel and CSV files into an in-browser database. You confirm which columns link the files, then ask a question such as “which customers stopped ordering in September?”. It writes read-only SQL, runs it on your computer and shows both the answer and the query. The question pages linked below show the data, the formula, the SQL and the result for each pattern.

Your rows never leave the browser — see our privacy policy.

A worked example: three questions, one dataset

Take two monthly order exports and a customer list. Three useful questions come from the same three files:

Each answer takes one formula or one short SQL query, and none needs the files copied together by hand. Worked versions: customers who stopped ordering and new customers this month.

Here is the data behind those answers, one total per customer per month:

Running the change query from Pattern 2 on these rows gives the fourth, and often most useful, answer — *where* the 55 drop came from:

Revenue fell from 400 to 345, a drop of 55 (13.75%). Lost customers took away 150, a smaller order from Bakery Lune another 30, and new customers added back 125: −150 − 30 + 125 = −55. That breakdown is far more useful than the headline number, and it needed both files joined, not just summed.

A second worked example: tickets by customer tier

Enrichment answers go wrong quietly, so it's worth seeing the trap with numbers. A support export has six tickets: three from customer C1, one from C2 and two from C3. The customer list gives each customer a tier, but C3 was entered twice when the account was migrated.

The join turned six tickets into eight, because each of C3's two tickets matched both C3 rows. The check that catches it is simple: the ticket count after the join must equal the ticket count in the export (six). If it doesn't, look for duplicate keys before reading anything into the result. In a spreadsheet, XLOOKUP would not show this problem, because it returns only the first match; Power Query merges, pivot tables over the Data Model and SQL joins all would.

Answering across Google Sheets and Excel files together

Real questions often span tools: the customer list lives in a shared Google Sheet, the orders come from an Excel or CSV export. There are three practical routes:

• Bring everything into Google Sheets: upload the Excel file to Drive, open it as a Google Sheet and use IMPORTRANGE or plain cross-tab references. Good for small files that colleagues edit together.
• Bring everything into Excel: download the Google Sheet as .xlsx or CSV and load it into Power Query alongside the other files. Good when the result feeds an Excel report, but the download is a snapshot that goes stale.
• Query both in place: a tool that reads the Google Sheet and the local files together and joins them, so the sheet stays live and nothing is copied by hand. See combining data from multiple sources.

Whichever route you take, check that the key columns survive the trip: Google Sheets and Excel can both turn long numeric IDs into scientific notation or drop leading zeros when a file is converted. Format ID columns as text before exporting.

Asking precise questions

Whether you write the formula yourself or ask a tool, vague questions get vague answers. A precise question names the measure, the grain, the period and the rule for edge cases.

Writing the precise version first also tells you which files you need: the third question needs twelve months of orders stacked; the fourth needs orders and returns joined on product.

Troubleshooting answers that look wrong

Making it a monthly routine

Most cross-sheet questions come back every month. Save the work so the next answer takes minutes:

• Keep each month's export with the same file name pattern and the same columns, in one folder or one set of tabs.
• Build the question once as a Power Query, a formula sheet or a saved SQL query, and point it at the new file each month.
• Record the answer alongside the row counts and date ranges of the inputs, so next month's comparison starts from known numbers.
• When the source system changes its export (a renamed column is common), fix the mapping in one place rather than in every formula.

Question recipes by area

• Customers: repeat purchase rate, customer lifetime value, first and last order per customer.
• Finance: mismatched totals between two reports, vendors billed twice, discounts applied without a promo code.
• Products and stock: products bought together, inventory turnover, orders refunded but still shipped.
• Marketing: shop vs GA4 revenue by day, keywords ranking on both sites, and the guide to combining GA4 and Search Console data.

When the two files are records of the same activity that should agree, the question is a reconciliation; see reconciling data in Excel.

Choosing the right tool

Every guide in this topic

• Are any invoice numbers missing from the sequence?

  Stack invoice lists from several files and find gaps in the number sequence — missing or deleted invoices — with SEQUENCE and a recursive SQL query.

• Have any suppliers billed us twice?

  Spot duplicate supplier invoices: exact duplicates on vendor, invoice number and amount, and near-duplicates with the same amount a few days apart.

• How did each product grow from last month to this month?

  Compare two monthly sales files product by product: revenue change, growth %, new and discontinued items — with XLOOKUP and a full outer join in SQL.

• How do sales reps rank across all regions?

  Stack regional sales files and rank every rep by revenue and by percentage of target, overall and within each region — formulas and SQL.

• How do two quotes compare, line by line?

  Put two quotes side by side by item, compare line totals, and catch items included in one quote but not the other before choosing a supplier.

• How fast is each product's stock turning over?

  Join a stock report to a sales export by SKU to calculate inventory turnover and days of cover per product, and spot slow movers.

• How much has each customer spent, net of refunds?

  Calculate each customer's net lifetime revenue by joining an orders export to a refunds export — formula, SQL and a worked example.

• How much of our revenue does GA4 actually record?

  Join your shop's daily sales to GA4's daily purchase revenue to measure tracking coverage and spot days where tracking broke.

• What is our repeat purchase rate?

  Calculate the share of customers who ordered more than once, across one or several order exports: formula, SQL, and the choices that change the number.

• What sells best, and who spends most, across all our stores?

  Combine order exports from several stores or brands to rank products and customers overall, and find customers who buy in more than one store.

• When did each customer first and last order?

  Stack order exports from several files or stores and get each customer's first order, last order and order count — with MINIFS, MAXIFS and SQL.

• Which customers are new this month?

  List customers who appear in this month's orders but in no earlier month: a COUNTIF formula, the SQL query, and why you need full history, not just last month.

• Which customers stopped ordering this month?

  Compare two monthly order exports to list customers who ordered last month but not this month — with a COUNTIF formula, the SQL anti join and pitfalls.

• Which invoices are more than 30 days overdue?

  Join invoices to payments to get the outstanding balance per invoice and days overdue, then list invoices more than 30 days past due — formula and SQL.

• Which keywords do both sites rank for?

  Join two Search Console or rank-tracker exports on query to find keywords both sites rank for — overlap, cannibalisation between your own sites, and gaps.

• Which leads never became customers?

  Match a lead export to your customer list by email to find unconverted leads and lead-to-customer conversion rates by source — formula and SQL.

• Which orders got a discount without a promo code?

  Audit an order export for discounts given without a promo code — manual price overrides and unauthorised discounts — with FILTER and SQL.

• Which orders were refunded but still shipped?

  Join a refunds export to shipping data by order ID to find orders refunded before they shipped — goods sent for free — with formulas and SQL.

• Which products are most often bought together?

  Find cross-sell pairs from order line exports: a self-join on order ID counts how often two products appear together, plus support and confidence.

• Which products are returned more than average?

  Combine sales and returns from several marketplaces, calculate return rate per product and channel, and flag products above the average.

• Which suppliers deliver late, and by how much?

  Join purchase orders (promised dates) to delivery records (actual dates) to measure on-time delivery and average days late per supplier.

• Why don't these two reports add up to the same total?

  Two reports disagree on a total? Join them on the grouping column to find which rows differ and which exist in only one report — formula and SQL.

Frequently asked questions

How do I analyse data from multiple spreadsheets?

Stack the files that share columns, join the files that share a key, then filter or group the combined table with formulas, a pivot table or SQL.

Do I need SQL to compare spreadsheets?

No. COUNTIF, XLOOKUP, FILTER and pivot tables answer most questions. SQL is easier for multi-step questions, and tools like Analistable write it for you.

What's the most common cross-sheet question?

Which records are in one list but not the other — new customers, lapsed customers, missing invoices. It's an anti join, or COUNTIF = 0 in a spreadsheet.

Can I ask questions across Google Sheets and Excel files together?

Yes. Export one format to match the other and join them in Excel or Sheets, or connect both in Analistable, which loads them into one in-browser database and answers across both.

Why does my total go up after joining two sheets?

The lookup table has duplicate keys, so each matching row is repeated. De-duplicate the lookup table, or total it per key before joining.

How do I compare two months when some customers only appear in one?

Use a full outer join (or a pivot table over both months stacked together) so customers from either month are kept, with zero for the month they're missing from.