Quick & Simple Excel Summary Reports Anyone Can Make

The example dataset includes customer names, units sold, and invoice amounts across thousands of rows (about 7,000). You’ll learn a no‑formula approach to get a unique customer list, a SUMIF method using named ranges to total values per customer, and a PivotTable method that does it in seconds.

When to use each method

• Advanced Filter + SUMIF — great when you want a static summary on the same sheet or another sheet, and prefer explicit formulas you can customise.
• PivotTable — best for quick interactive summaries, easy rearranging, and when you want drill-down or grouping capabilities.

Method 1 — Advanced Filter + SUMIF (step-by-step)

This method creates a unique list of customers using Excel’s Advanced Filter, then uses SUMIF formulas to total Units Sold and Invoice Amount for each customer.

1. Identify your base column

Decide the field you want to summarise by — in our case, it’s Customer Name. The other columns to total are Units Sold and Invoice Amount.

2. Create the unique customer list (Advanced Filter)

1. Go to the Data tab and click Advanced (Advanced Filter).
2. Set List range to the full Customer Name column (include the header).
3. Select Copy to another location and choose the cell where the unique list will start (for example, F1).
4. Check Unique records only and click OK.

You’ll now have a list of every customer (one row per customer) in the chosen location.

3. Create named ranges for your columns

1. Select the three columns (Customer, Units Sold, Invoice Amount) including the headers.
2. Go to the Formulas tab and choose Create from Selection.
3. Use the Top row option to generate named ranges based on the headings (for example: Customer, UnitsSold, InvoiceAmount).

Named ranges make formulas easier to read and maintain.

4. Write SUMIF formulas

In the column next to your unique customer list, use SUMIF to total Units Sold for each customer. With named ranges set up, an example formula is:

=SUMIF(Customer, F2, UnitsSold)

Where F2 is the customer on the summary row. For Invoice Amount use:

=SUMIF(Customer, F2, InvoiceAmount)

1. Enter the formula for the first customer and press Enter.
2. Drag the formula down to fill the rest of the list.
3. Optionally, use absolute references (dollar signs) if you prefer ranges instead of named ranges.

5. Final touches

• Format numbers (commas, currency) to improve readability.
• Sort the summary list if you want top customers first (by Units Sold or Invoice Amount).

Method 2 — PivotTable (fast and flexible)

If you’re comfortable with PivotTables, you can create the same summary in seconds:

1. Go to the Insert tab and click PivotTable.
2. Select the entire table or range holding your detailed report and click OK (it will create a new sheet by default).
3. In the PivotTable Fields pane, drag Customer Name to Rows.
4. Drag Units Sold and Invoice Amount to Values. They will default to SUM.

You now have total units and total sales per customer. PivotTables are interactive: change fields, add filters, or group customers with a few clicks.

Tips & best practices

• Convert your source data into an Excel Table (Ctrl+T). PivotTables and formulas handle Tables better and they expand automatically when you add data.
• Use named ranges if you want clearer formulas and easier maintenance.
• If you use the Advanced Filter method and your data changes, re-run the Advanced Filter to refresh the unique list, and then recalculate or fill formulas again.
• With PivotTables, right-click and choose Refresh after your source data updates.
• For very large datasets, PivotTables are typically faster and more memory-efficient than many formulas.

For more information, visit https://avanasolutions.com