Table of Contents
Creating a summary table in Excel is a great way to organize and analyze data quickly and efficiently. To create a summary table in Excel, start by selecting the data you want to include in the table, then go to the Insert tab and select Table. In the Create Table dialog box, make sure the data range is correct and click OK. The summary table will now appear in the worksheet. You can further customize the summary table with formulas and formatting options to create a summary table that fits your needs.
Often you may want to create a summary table in Excel to summarize the values in some dataset.
Fortunately this is easy to do using built-in functions in Excel.
The following step-by-step example shows how to create a summary table in Excel in practice.
Step 1: Enter the Original Data
First, we’ll enter the following dataset that contains information about product sales for some company:
Step 2: Find Unique Values
Next, we’ll use the following formula to identify the unique product names in column B:
=UNIQUE(B2:B13)
We can type this formula into cell F2:
We can see that this formula is able to extract the three unique product names from column B.
Step 3: Calculate Summary Statistics for Each Variable
Next, we can calculate the total units sold, average number of units sold, and total revenue for each product by using the following formulas:
Total Units Sold:
=SUMIF($B$2:$B$13, F2, $C$2:$C$13)
Average Number of Units Sold:
=AVERAGEIF($B$2:$B$13, F2, $C$2:$C$13)
Total Revenue:
=SUMIF($B$2:$B$13, F2, $D$2:$D$13)
We can type these formulas into cells G2, H2, and I2, respectively:
We now have a summary table that tells us the total units sold, average units sold, and total revenue for each of the three products from our original dataset.
Step 4: Format the Summary Table (Optional)
Lastly, feel free to add a border around each cell in the summary table and add background colors to make the summary table more aesthetically pleasing:
The summary table is even easier to read now.
Note: We chose to calculate mean values and sum values in our summary table, but feel free to calculate other values of interest such as the minimum, maximum, median, range, and other metrics.