Excel for Architects - Part 2

Excel Pivot Tables

Up until a few months ago, I had no idea how to use Pivot Tables in Excel. I had heard the term before but I didn’t know what they did, how to create them, or even why you’d use them. While working on a particularly complicated area take-off spreadsheet, I needed some way to summarize the area data. After some quick research, I realized Pivot Tables would do the job perfectly.

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. As I mentioned above, I had a situation where I needed to summarize area data. Using a series of Pivot Tables, I was able to easily generate area totals by floor, department, and room type. Doing this manually would have taken a lot of time and wouldn’t have been as accurate. 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 into 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 is the Slicer. A Slicer provides 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 “Analyze” 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.

Conclusion

That’s it! As I mentioned at the beginning, creating Pivot Tables is very easy and worth the trouble. They add a lot of power to your spreadsheets and are a great way to extract key information from your data. Pivot Tables also provide useful tools for dynamically visualizing the data. You can learn even more about Pivot Tables on Microsoft’s Excel page.

How do you see Pivot Tables helping you better understand and present your data? Can you think of some ways to incorporate them into your work? Leave a comment with your thoughts.