How to Create a Summary Report in Excel: 2 Easy Methods

Method 1 – Advanced Filter and SUMIF Function to Create a Summary Report

Step 1: Applying Advanced Filter

Use the Advanced filter option to find out the unique Book Name in the Summary Report table.

➤Go to the Data tab > select Sort &Filter > select Advanced.

An Advanced Filter window will appear.

➤ Select Copy to another location > we select from cell B4 to B19 as List range.

Select from cells B4 to B19 quickly by clicking on cell B4 and pressing CTRL+SHIFT+Down arrow.

➤ Select cell F4 in the Copy to box > mark on Unique records only > click OK.

See the unique book name in the Summary Report table’s Book Name column.

Step 2: Define Names

Define the column names of the Sales Report of ABC Book Store table in the Name Box.

➤ Select the entire dataset of the Sales Report of ABC Book Store table.

Select the entire table quickly by clicking on cell B4 and pressing CTRL+SHIFT+Right arrow+Down arrow.

➤ Go to the Formulas tab > select Defined Names > select Create from Selection.

A Create Names from Selection window will appear.

➤ We will unmark the Left column box, and make sure the Top row box is marked > click OK.

➤ Click on the Name Box, we will see the column names of the Sales Report of ABC Book Store there. We will use these column names in the SUMIF function.

Step 3: SUMIF for Calculation

Calculate the Total Units Sold and Total Price in the Summary Report table. We will use the SUMIF function for this case.

➤ Type the following formula in cell G5.

=SUMIF(Book_Name,F5,Units_Sold)

The SUMIF function sums the value in a range that meets specific criteria.

Book_Name is the range of the SUMIF function, F5 is the criterion, and Units_Sold is the sum range.

➤ Press ENTER.

We can see the result in cell G5.

➤ Drag down the formula with the Fill Handle tool.

In the Summary Report table, we can see the Total Units Sold for every Book Name.

Calculate the Total Price in the Summary Table.

➤ Type the following formula in cell H5.

=SUMIF(Book_Name,F5,Price)

Book_Name is the range of the SUMIF function, F5 is the criterion, and Price is the sum range.

➤ Press ENTER.

See the result in cell H5.

➤ Drag down the formula with the Fill Handle tool.

See the Summary Report table with the complete Total Units Sold and Total Price columns.

Step 4: Calculating Grand Total

Calculate the Grand Total of the Summary Report table by using the SUM function.

➤ Type the following formula in cell G10.

=SUM(G5:G9)

The SUM function adds up the cells from G5 to G9.

➤ Press ENTER.

We can see the result in cell G10.

➤ Type the following formula in cell H10.

=SUM(H5:H9)

The SUM function adds up the cells from H5 to H9.

➤ Press ENTER.

See the complete Summary Report.

Method-2 – Summary Report Using Pivot Table

➤ Select the entire dataset of the Sales Report of ABC Book Store table > go to the Insert tab > click on Pivot Table > select From Table/Range.