How to create a dynamic table in excel to summarize data

Creating a dynamic table in excel

In this example, we will be creating a dynamic table in excel from a dataset. The dataset has 3 columns out of these three 2 are categorical values while the third is numerical. Now we wanted to create a dynamic table that will have these two columns as rows and columns. Also, we wanted to summarise our data by as count of entries of these two categorical columns. Dataset is illustrated in the below image.

Creating a dynamic table in excel
Creating a dynamic table in excel

To create this kind of excel dynamic table we will first convert our dataset into the excel table. You must be wondering why we are converting this into the table, this is because whenever in the future we will add or remove data our dynamic table will take it as well.

How to create table in excel

To create a table in excel we will go to Insert Tab then in Tables click on Table icon as shown in the below image or alternatively we can press CTRL + T to convert our dataset to table.

How to create a table in excel
How to create a table in excel

Our dataset will get converted into table.

Tabel in excel
Tabel in excel

In the second step, we will use UNIQUE function to convert our two categorical columns values to unique so that we can use them in our dynamic table. Also, we will use transpose function to move our data from rows to columns.

Transpose function in excel
Transpose function in excel

In the above example, we used unique function to show only unique values. For City column, we used transpose function so that it can be shown in columns.

In the final step, we will use COUNTIFS function to calculate the count of every horizontal value against the vertical values.

countifs function in excel
countifs function in excel

Now we can see for NY city color of red are of two times. We can also sum our data or average or any other calculations.

Leave a Comment

Your email address will not be published.

fb-share-icon