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:
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)
=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.
The following tutorials explain how to perform other common tasks in Excel:
How to Perform Exploratory Data Analysis in Excel
How to Calculate the Sum by Group in Excel
How to Count by Group in Excel