Download
1 / 33
330 likes | 622 Vues
Pivot Tables. What are Pivot Tables?. A pivot table gives you a way to group, summarize and compare data from a spreadsheet You can do some of the same tasks with COUNTIFs and SUMIFs, but they are much easier with pivot tables
E N D
What are Pivot Tables? • A pivot table gives you a way to group, summarize and compare data from a spreadsheet • You can do some of the same tasks with COUNTIFs and SUMIFs, but they are much easier with pivot tables • John Walkenbach says, “Excel’s pivot table feature is, arguably, its most innovative and powerful feature.” [pg. 565]
Getting Started • We’ll start with a small example created with the RealEstateLoops application. • In reality pivot tables really shine when applied to large file, but this will let us see everything in action. • [Note.. These examples were created on a Windows computer. We’ll briefly show the Mac version as well. The examples transferred over to the Mac with no problem.]
About the Data • Your data should be in a contiguous rectangular table and every column should have a heading • Get rid of any blank rows or columns within the data before you start • Highlight your data range and then you are ready to start (although if you just select the upper left corner, and your data is surrounded by blank rows and columns, Excel will figure things out; useful for very large tables)
The Pivot Table Dialog I started with cell A1 selected and Excel decided I wanted to use my whole table I created a sheet named pivot in my current workbook earlier and entered this information
Dialog to Create Table My next step is to choose some fields
I chose Neigborhoods and got this… Clicking the box to the right of Row Labels gives me a bunch of choices
I also chose Price Excel totaled the sales for each neighborhood
Now I added Agent Excel broke down the neighborhood information by agents
The final result, with formatting added for the dollar amounts.
Another Approach • Instead of clicking the boxes by the field names, you can drag them to the boxes at the bottom • On the next slide, I dragged Agent and then Neighborhood to the Row Labels box, and then Sales and then Commission to the Values box. Then I formatted the columns with the dollar values in them
How many times does each appear? Here I dragged Agent to both the Row Labels and Sum of Values boxes. I got the count of how many sales each agent had.
Filters • You can apply a filter to your data to only see selected items • For example, you might want to only see entries greater than or less than a certain number, or the top 10 values, or something similar
To Add a Value Filter… First, click the drop-down arrow next to the Row Labels heading and choose Value Filters
Choose a Filter… Let’s look at who sold over $1M
I selected the upper left corner of the data and clicked the pivot table button
Bottom Line • Pivot tables are a very useful and powerful tool • You can do everything with programming that you can do with pivot tables, and more • But if a pivot table takes care of what you need, then it’s much quicker to use • Playing with the pivot tables for a bit will help you learn how to use them
