Generating matrix using Microsoft Excel’s Pivot Tables

6 Feb 2008 by Chetan Shah.

Often times there is a need to generate a matrix. For simplicity sake, let’s assume that you have a table containing Products and States in which they are sold. Microsoft Excel has a very efficient utility called pivot tables which one can leverage to generate the required matrix.

Following are the steps to generate the desired output using Microsoft Excel’s Pivot tables

1. Go to Data –> PivotTable and Pivot Chart Report
2. On Pivot Table and Pivot Chart Wizard Step 1 of 3
2a. Select the Microsoft Office Excel List or database
2b. Select “Pivot Table” for the 2nd question.
2c. Click “Next.
3. Select range (where your data resides on the worksheet) and click next
4. Determine where you want to put this pivot table and click next
5. Click Finish
6. Drag and Drop the “Product” in “Drop Row Fields Here” area.
7. Drag and Drop the “States” in “Drop Column Fields Here” area.
8. Drag and Drop the “States” in “Drop Data Items Here” area.
9. Done!

If you have a product which is not being sold in any states and if you want to show it on the matrix, you can add a row to the product/states table. Set the value of state to . If you followed the above mentioned process all you will have to do is right click on the pivot table area and hit “Refresh Data”, your matrix will be updated in a split second!

Posted in Technology | 5 Comments »