How to Create a Summary Report in Excel (2 Easy Methods)

If you want to create a summary report in Excel, this article is for you. Here, we will walk you through 2 quick and effective methods that will help you to do the task effortlessly.

How to Create a Summary Report in Excel: 2 Methods

The following Sales Report of ABC Book Store table contains the Book Name, Units Sold, and Price columns. We will create a summary report of this table by using 2 methods. Here, we used Excel 365. You can use any available Excel version.

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

Here, we will use the Advanced filter option to filter unique Book Name, and after that, we will use the SUMIF function to calculate the Total Units Sold and Total Price in the Summary Report table. Along with that, we will use the SUM function to calculate the grand total in the Summary Report table.

Step 1: Applying Advanced Filter

Here, we will use the Advanced filter option to find out the unique Book Name in the Summary Report table.

➤ First, we will go to the Data tab > select Sort &Filter > select Advanced.

An Advanced Filter window will appear.

➤ After that, we will select Copy to another location > we select from cell B4 to B19 as List range.

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

➤ Afterward, we will select cell F4 in the Copy to box > mark on Unique records only > click OK.

Finally, we can see the unique book name in the Summary Report table’s Book Name column.

Step 2: Define Names

Now, we will define the column names of the Sales Report of ABC Book Store table in the Name Box.

➤ First, we will select the entire dataset of the Sales Report of ABC Book Store table.

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

➤ After that, we will go to the Formulas tab > select Defined Names > select Create from Selection.

A Create Names from Selection window will appear.

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

➤ Now, if we 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

Now, we want to calculate the Total Units Sold and Total Price in the Summary Report table. We will use the SUMIF function for this case.

➤ First, we will type the following formula in cell G5.

Here, the SUMIF function sums the value in a range that meets certain criteria.

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

➤ After that, press ENTER.

We can see the result in cell G5.

➤ After that, we will drag down the formula with the Fill Handle tool.

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

Now, we will calculate the Total Price in the Summary Table.

➤ First, we will type the following formula in cell H5.

Here,

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

➤ After that, press ENTER.

Now, we can see the result in cell H5.

➤ Afterward, we will drag down the formula with the Fill Handle tool.

Finally, we can see the Summary Report table with complete Total Units Sold and Total Price columns.

Step 4: Calculating Grand Total

Now, we will calculate the Grand Total of the Summary Report table by using the SUM function.

➤ First, we will type the following formula in cell G10.

Here, the SUM function adds up the cells from G5 to G9.

➤ After that, press ENTER.

We can see the result in cell G10.

➤ Afterward, we will type the following formula in cell H10.

Here, the SUM function adds up the cells from H5 to H9.

➤ Then, press ENTER.

Finally, we can see the complete Summary Report.

Method-2: Summary Report Using Pivot Table

In this method, we will use a Pivot Table to create a summary report quickly.

➤ First, we will 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.

A PivotTable from table or range window will appear.

➤ After that, we will click on Next Worksheet > click OK.

➤ Afterward, in the PivotTable Fields we will drag the Book Name in the Rows box, and we will drag the Units Sold and Price in the Value box.

Finally, we can see the Summary Report is created by a Pivot Table.

Download Workbook

Conclusion

Here, we tried to show you two methods to create a summary report in Excel. Thank you for reading this article. We hope this was helpful. If you have any questions or suggestions, feel free to let us know in the comment section.

Related Articles

• How to Make Report Card in Excel
• How to Automate Excel Reports Using Macros
• How to Generate Report in Excel using VBA pdf
• How to Generate Reports in Excel Using Macros
• Create a Report in Excel as a Table
• How to Generate PDF Reports from Excel Data
• How to Generate Reports from Excel Data

<< Go Back to Report in Excel | Learn Excel