EXCEL PIVOT TABLES

Basically, a Pivot Table is a table that summarizes data in a specified worksheet. The table can be inserted into an existing worksheet or into a new worksheet. A Pivot Table doesn’t require complicated formulas or advanced Excel skills. It’s a feature built-in to Excel. Pivot Tables are very easy to create and extremely powerful.

Uses for Pivot Tables

Say you’re creating a work plan in Excel. You’re listing tasks and estimating the number of hours each task should take to accomplish. In the work plan, you’re also assigning a team member to each task. To ensure you’re not overloading a particular team member, you want to see the number of hours you’ve assigned to each team member. This is easily accomplished using a Pivot Table to summarize the work plan data.

Likewise, you can use Pivot Tables with area spreadsheets. For example you can summarize area data. Using a series of Pivot Tables you can easily generate area totals by floor, by department and by room type. Also, Pivot Tables provide a number of ways to visualize the data. Given that we architects are visual people, this is definitely a plus.

Creating Pivot Tables

Creating a Pivot Table is really easy. In Excel, open the Excel file and activate the worksheet with the data you want to summarize. In the “Insert” ribbon, click the “Pivot Table” icon to open the “Create PivotTable” dialog box.

In the Create PivotTable dialog, select the table or cell range of the data you want to summarize. You can also create Pivot Tables using external data sources such as databases. Once you’ve specified the range of data, choose where you want the Pivot Table placed. You can either place it in the current worksheet or a new worksheet. Click OK to insert the Pivot Table. This will insert a blank Pivot Table report in the worksheet.

In the “PivotTable Fields” dialog on the right-hand side of the screen, select the fields you want to display in the Pivot Table.

Once you’ve selected the fields, the Pivot Table will display in the current worksheet or a new worksheet depending on the setting you selected in the “Create PivotTable” dialog.

If you want to drill down on a particular value of the Pivot Table, simply double-click the value in the table. Excel will create a new worksheet with the corresponding data.

Updating Pivot Tables

Pivot Tables do not maintain a live link with the data. If you change the original data, you’ll need to manually refresh the Pivot Table. To refresh the table, first click the Pivot Table then click the “Refresh” button in the “PivotTable Tools” context ribbon.

To modify an existing Pivot Table, simply click on the table then change the fields as needed in the Pivot Table Fields window. Change the table formatting using the tools in the “Design” tab of the “Pivot Table Tools” ribbon. Choose either an existing table style or create a new style.

Since Pivot Tables are inserted into worksheets, they print just like any other worksheet. You may need to fine-tune the print settings in the Page Layout view to ensure the table prints with the desired labels, etc. . .

Visualizing Pivot Table Data

In addition to viewing the data as a table, you can create chart views of the Pivot Tables. Select a Pivot Table then click the “Analyze” tab of the “Pivot Table Tools” ribbon. Click the “PivotChart icon”.

Choose the type of chart you want in the “Insert Chart” dialog. Excel provides a number of different options.

The chart will insert on the same worksheet as the Pivot Table. Since the chart is linked to the Pivot Table data, it will update automatically once the Pivot Table is refreshed.

Using Slicers

Another really useful tool you can use with Pivot Tables are Slicers. Slicers provide a dynamic way to graphically filter the Pivot Table data. To create a Slicer, select the Pivot Table in the worksheet then select the “Insert Slicer” icon in the “Analyse” section of “PivotTable Tools” ribbon. Choose the fields you want to “slice” then click OK. This will create the Slicer graphic object. Clicking any value in the slicer object will filter the Pivot Table and any associated Pivot Charts accordingly.

Page updated

Google Sites

Report abuse