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.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel: