A new view on your data: Pivot Tables

looking around cornersExcel’s pivot tables reorganize, categorize, and summarize your data dynamically however you want. Look at it one way, then in seconds, look at it another way. It has a lot of features, but the basics alone are powerful. Today I’ll show you how to use it on a simple example.

Let’s take the example from last time, a spending log.spending

 

 

Pivot table dialog box

Click anywhere in the data. Then select Data > PivotTable or Insert > Table > PivotTable, depending on your version of Excel. You’ll get a Create PivotTable dialog box. Click OK.

 

Blank Pivot Table Builder

Now you’ll get a new sheet with a blank table, marked only Column area, Row area, and Values area. You’ll also get the PivotTable Builder. This is where you tell Excel what you want for rows and columns and what you want in the body of the table.

 

Start by deciding what you want for Values. This is the data you’re going to total by categories, so it only makes sense if it’s numerical, something you can sum. Here, the only field name like that is Amount. Of course, that’s exactly what you want to look at, your spending. Drag the Amount field name into the lower right Values box. You will now see a tiny pivot table showing your total spending. You’d really like to break it down by category, so drag the Category field name into the Column Labels box. Now you have your total spending by category. Break that down by month by dragging the Month field name into the Row Labels box. (You may have to scroll in the Field name to find Month.) Here’s what the PivotTable Builder looks like when it’s filled in.

Completed Pivot Table Builder

Completed Pivot Table Builder

 

It looks complicated, but this just took a few steps. Now you can slice and dice your data any way you want. Drag Category out of the window and you immediately get a new table, summarized only by month. Drag Paid by into the Column Labels box to get your spending by month and payment method.

This feature does more, but these simple steps are all you need to slice and dice on the fly. I love Excel for its flexibility. You don’t need to pre-define reports the way you do in a database program. Try it on your data.

Final pivot table

Final pivot table