Matrix Visualization in Excel Power View

A matrix is a type of visualization similar to a table, made up of rows and columns. Unlike a table, it supports hierarchical data and allows drill-down and drill-up operations. It can also display totals and subtotals, and avoids repetition of values for better readability.

Matrix Visualization

Let's take a dataset as an example in order to understand matrix visualization. This is the major dataset that will be used in this case.

Steps to Create a Matrix Visualization

Step 1: To make a matrix, begin with a table. Choose from the following options:Country, Region, Festival, and Days. In Power View, a table with these fields is displayed.

power-view-fields-displayed — Create a Power View table using Country, Region, Festival, and Days fields.

Step 2: Convert thetable to a matrix now. Select the Table. Navigate to the DESIGN tab. In the Switch Visualization group, choose Matrix from the dropdown menu.

selecting-design-option — Convert the table to a Matrix using the “Matrix” option in the Switch Visualization menu.

Step 3: As you can see, The Table has been changed to a matrix.

table-changed-to-matrix — Matrix displays grouped data with rows and columns, replacing repeated values.

Subtotals and Totals

By default, a Matrix displays subtotals and totals for grouped data. However, you can choose to hide or show them as needed.

navigating-design-tab — To hide Totals/Subtotals, go to the Design tab > Totals > Select “None.”

To remove subtotals and totals, select the Matrix and go to the Design tab. In the Totals option under the Options group, select None.

choosing-none-from-totals — Matrix with Subtotals and Totals hidden using the “None” option in the Totals menu.

subtotals-and-totals-removed — Matrix showing Subtotals and Totals after selecting “Rows” from the Totals dropdown.

To display subtotals and totals again, go to the Design tab, click Totals, and choose Rows.

select-rows-from-totals — Re-enabling Subtotals and Totals in the matrix by selecting “Rows” under the Totals menu in the Design tab.

This is the default setting in a Matrix.

matrix-default-setting — Matrix displaying default view with Subtotals and Totals visible in rows.

Power View Filtering Matrix

You can filter the data in a Matrix to display only specific records. For example, you may want to view countries along with their religions and festivals where the number of days is greater than or equal to 2.

To apply this filter, select the Matrix visual and go to the Filters pane. Under the Days field, set the filter condition to “is greater than or equal to 2”. Then click Apply filter.

clicking-apply-filter — Filtering the matrix to display festivals lasting 2 or more days using the Days field in the Filters pane → Days field → Apply condition

The Matrix will now display only the filtered data based on the selected condition.

data-without-repeatition — The matrix displays data without repeating the values.

Benefits of Matrix Visualization

The following are the benefits of using a matrix:

It is capable of displaying data without repeating values.
Rows and/or columns can be compressed and enlarged.
You can dig down/drill up if it has a hierarchy.
Totals and subtotals can be shown by columns and/or rows.

Matrix Visualization in Excel Power View