PivotTables are like databases. They are tables that can be manipulated in many ways to obtain different kinds of outputs. As the name indicates, the pivot is like an axis around which various data outputs are managed. This is especially useful when you have interrelated data. In simple words, it is like a library of data where you can read either the history section or the literature section.
To see how exactly this works let us take an example.
Let us take that there have been sales of certain items, according to date, Item & Price. All these have been put as structured data into the cells of a new spreadsheet. The data are entered as
Select all the cells containing the data. Now go to Insert tab and click the PivotTable ribbon. A dialog box will open asking you to choose the data you want to analyze.
The data range will be preselected (the cell numbers that contain your data). If you are newly creating or new to creating this table, select the New worksheet option and click OK to proceed.
The data range will be preselected (the cell numbers that contain your data). If you are newly creating or new to creating this table, select the New worksheet option and click OK to proceed, else, you can insert it into an existing worksheet.
You will see an empty worksheet. Don’t panic! Your data is safe. In Excel 2007 you could simply drag and drop to create the PivotTable. Data tables in Excel 2010 are populated in a different way.
You have to check all the boxes on the ‘choose fields to add report’ to create your pivot table. Click the boxes in the order you want to see the data in the pivot table.
If you want to see Date as first instead of Product Code, check Date and then Product Code so on and so forth.
Or you can drag each item, in corresponding area. Drag dates to Column Area, and Product Code to Row Area.
You can GROUP dates to MONTH, QUARTER or YEAR. Simply Right click on any date, and select GROUP.
Select Month & Year to group dates.
Now drag, Quantity / Price, in Values area, to get depth analysis.. like,
In Value area, you can click and select different option, to change SUM to COUNT , AVERAGE or MAX
You just need to drag & sort each item in different area, and play with it.
With a little experiment, you can able to extract many data crunching analysis.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
