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.
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.
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.
Our dataset will get converted into table.
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.
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.
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.