|
|
||
|
|
|
Excel Index and Search Tool (Click
on the buttons below) |
|
Excel Lesson 13 of 30: Pivot Tables First let's demystify the pivot table. The pivot table is a powerful data analyzing tool but less than 1% of the thousands of people that I have worked with use it. It is designed for ad hoc analysis of large quantities of data but as a reporting tool it is somewhat limited. When you learn about the magic function and the database functionalities in Excel you don't really need pivot tables and these formulas allow you to develop a report with the exact layout that you need. 75% of the automation in reporting that I develop is made possible through the magic function The pivot table allows the analyst to organise any database into tables with all kinds of sub-totals (SUM, COUNT, AVERAGE...) comparing sales of products by stores, sales of products by cities, sales of products by stores, by month AND by cities, etc. Here is an example: You have data supplied to you as TXT or CSV files or your import data from a database with Microsoft Query in the following format:
Imagine such a table in your spreadsheet with 50,000 lines of data and you want to create a table answering to the following questions: How many of each products were sold by city?
How much of each products were sold by city?
To complete such a task instantaneously, you will use the pivot table. To create the first pivot pable (Products by City), copy the raw data in Excel. Select the table go to "Menu/Data/PivotTable and PivotChart Report". In the first dialog box, choose "Microsoft Excel list or database", click "Next". In the second dialog box, accept the "Range" by clicking "Next". In the third dialog box Click on "Layout..." and you will see the following dialog box:
Drag the small gray buttons on your right onto the white shape in the middle, "Qty" over "DATA", Product over "ROW" and "City" over "COLUMN". Click "OK" and then "Finish". You have just created your first pivot table. To create the second Pivot Table with the same data, right click anywhere on the first Pivot Table, select "Wizard/Layout". Drag the "QTY" button off "DATA" and replace it by "Amount". To create any other Pivot Table from the same data, right click anywhere on the first Pivot Table, select "Wizard/Layout" and move the gray buttons around. For example, "Product" over "ROW" and "Date/Month" over "COLUMN" will give you a pivot table about "Sales of products by month". Try this one: "Product" over "ROW" and, "City" and "Date/Month" over "COLUMN". You have now "Sales of products by city and by month". Discover the magic function SUMPRODUCT before your go too fat with pivot tables. |
|
Excel Index and Search Tool (Click
on the buttons below) |
|
|
||
|
|
