Hello and welcome to the Although “pivot table” sounds like a complicated and advanced feature of Excel, it’s actually a very simple but highly useful tool. Pivot tables provide an excellent way to sum up data into categories. For example, if you have thousands of employee records showing employees from different departments within different locations, you can create a pivot table that will show you at a glance how many employees you have at each location, in each department, and in each department at each location.
First, make sure that your list of data has no blank rows or columns - Excel looks at a blank row or column as indicating the end of the data. Place the cursor anywhere in the data list and click on Data in the Menu Bar, click on Pivot Table and Pivot Table Reports. The PivotTable and PivotChart Wizard - Step 1 of 3 window will appear. Click on the Finish button. From the Pivot Table Field List window, click and drag the field name of the category you want as row headers to the area marked “Drop Row Fields Here.” Click and drag the field name of the category you want as column headers to the area marked “Drop Column Fields Here.” Now, click and drag the name of the field you want to summarize to the area marked “Drop Data Items Here.”
Let’s use, for example, a spreadsheet that lists employees (in a column called NAMES) along with their location (in a column called LOCATION) and department (in a column called DEPARTMENT) to create a pivot table that summarizes how many employees are in each department within each location. After placing the cursor in the data and clicking on Data in the Menu Bar, Pivot Tables and Pivot Table Reports, the PivotTable and PivotChart Wizard - Step 1 of 3 window will appear - simply click Finish. In the Pivot Table Field List window click and drag LOCATION to the “Drop Column Fields Here”, click and drag DEPARTMENT to the area marked “Drop Row Headers Here”, and click and drag NAMES to the area marked “Drop Data Items Here.” The resulting pivot table will show you how many employees you have in each department, in each location, and in each department at each location. Additionally, the pivot table headers LOCATION and DEPARTMENT have dropdown lists that allow you to select only the department(s) or locations(s) you want to display. Now, how easy and how useful is that?