Pivot table is great tool in Excel to create reports and analyze data. Go thru the simple exercise below to understand the concept
Open Excel and enter data below
| EmpNo | Ename | Designation | Department | Gender | Salary | DOJ |
| E001 | John | Associate | Finance | Male | 40000 | 1/1/2009 |
| E002 | Denise | Manager | IT | Female | 90000 | 1/2/2009 |
| E003 | Maya | CEO | Operations | Female | 200000 | 1/3/2009 |
| E004 | Carol | Manager | Finance | Female | 120000 | 1/4/2009 |
| E005 | John | Associate | Operations | Female | 50000 | 1/5/2009 |
| E006 | Sheela | Associate | Operations | Female | 50000 | 1/6/2009 |
| E007 | Rajiv | Manager | Operations | Male | 80000 | 1/7/2009 |
| E008 | Asha | Manager | Admin | Male | 75000 | 1/8/2009 |
| E009 | Kevin | Sr. Associate | Operations | Male | 60000 | 1/9/2009 |
| E010 | Arnold | Sr. Associate | Finance | Male | 65000 | 1/10/2009 |
| E011 | Kevin | Associate | Operations | Male | 40000 | 1/11/2009 |
| E012 | Julia | Associate | Operations | Male | 30000 | 1/1/2007 |
| E013 | Robert | Associate | Finance | Male | 30000 | 1/12/2009 |
| E014 | Ray | Associate | IT | Male | 40000 | 1/16/2009 |
| E015 | Shaun | Associate | IT | Male | 40000 | 12/10/2008 |
| E016 | John | Associate | Operations | Male | 50000 | 1/14/2009 |
| E017 | Karen | Associate | Operations | Male | 40000 | 11/10/2008 |
| E018 | Brian | Manager | Operations | Male | 90000 | 1/17/2009 |
| E019 | Nancy | Associate | Operations | Male | 40000 | 12/20/2007 |
| E020 | Debra | Associate | Admin | Male | 50000 | 1/2/2007 |
| E021 | Michal | Associate | Admin | Male | 40000 | 1/21/2009 |
| E022 | Charles | Sr. Associate | Finance | Male | 70000 | 1/23/2009 |
| E023 | Sandy | Associate | IT | Male | 40000 | 12/1/2007 |
| E024 | Doug | Associate | Operations | Male | 45000 | 1/24/2009 |
Now select data and Go to Insert –> Pivot table. Click OK.
Pivot table has been created. Now drop fields in the right frame to Report filter, Colum levels, Row levels and Sum of values windows to create the report as desired. Then filter various values and see how report appears.
For example, when you filter on Designation Manger, reports shows you how managers in different departments are paid.
