Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
Before I get into the details of how to create and manipulate PivotTables, I'd like to demonstrate a few of the ways PivotTables help you analyze your worksheet data. A PivotTable looks a lot like a regular worksheet, with rows and columns of data, labels, subtotals, and grand totals, but what PivotTables enable you to do is to change the structure of your data to emphasize different aspects of that data. So let's get a sense of the lay of the land here. Let's take a look at the PivotTable. You see that I have the data in here, and I have the total for each row here in this column for the Grand Total, and each row has data for, in this case a specific month of the year.
I've 2008, broken down into January, February, March, and so on, and then I have a subtotal for 2008. And sales are divided between two firms, two of my customers: FirmA and FirmB. If I scroll down, you'll see that I have a subtotal for each of these two columns for the year 2008, and then I have the same for 2009, with the subtotal here, and then the grand total for each of these two columns and a grand total for the entire PivotTable. I'll scroll back up.
On the right side, you'll see that I have what's called the PivotTable Builder. The PivotTable Builder is a dialog that you can use to manipulate the PivotTable. If for any reason you don't see it- and I will hide it now intentionally by clicking the Close box - if you want to see it, you can click the PivotTable contextual tab on the Ribbon, and over here in the View group, click the Builder button, and the PivotTable Builder comes back. So now, what are some things that you can do with the PivotTable? Well, you can reorganize your data. And what I'd like to do is focus on the data in this cell here, the value 128, which represents revenue generated from FirmA in March of 2008.
And to identify that cell as we pivot, I'm going to mark it in yellow. So now, let's say that I want to reorganize the data in my PivotTable. If I want to organize by Year, by Month, and then by Company, I can drag the Company field header below Year and Month, and when I release it, you'll see that I have 2008, January, and then FirmA and FirmB, and the value 128, which corresponded to March 2008 for FirmA, has moved down. And you'll see that this structure mirrors the structure here in the PivotTable Builder.
I go by Year, by Month and by Company, and that structure is indicated here. Now let's say that I want to go by Year and Company in the rows and Month in the column labels. If I change that, the PivotTable pivots, technical name for how the PivotTable changes, and you can see that the value 128, again, March, 2008 for FirmA, has changed position, but it is still in the same place relative to its values, March, 2008, and FirmA. And that's exactly the same thing that happens with every other value.
The only thing that has changed is your perspective on the data. This PivotTable tends to emphasize monthly sales, so that means that you can look through and look it for May, and 2008, for January, February and so on, and if you prefer vertical to horizontal, well, you can reorganize the data that way too. I will put month down here, just for a moment, and then I'll put Year and Company in the Column Labels area, and when I do that, you get a different organization of the data that can provide a different perspective.
I hope this brief demonstration has given you a feel for PivotTable's capabilities. In the remaining movies in this chapter, I'll go into more detail on how to create and manipulate PivotTables, and the data they contain. The real benefit of using PivotTables is that you can, in seconds, reorganize and gain new perspectives on your data in ways that would take at least several minutes of cutting and pasting without them.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.