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.
PivotTables are powerful and flexible Excel analysis tools. A PivotTable lets you rearrange, sort, and filter a data set on the fly, so you can analyze it from several different perspectives with a minimum of effort. In this lesson, I'll show you how to create a PivotTable from a data list in your workbook. So the first thing I have is my data list, and several things I would like you notice. It's a long list, so I won't scroll all the way down through it. But first, you'll notice that I've column headers, and they are formatted differently than the data underneath in the columns.
So, for example, instead of just this plain text here for FirmA, Company is formatted in blue, it's in white, and it's centered. So that clearly distinguishes it from the data, and also there is no extraneous data to the right or to the left, and you'll have to take my word for it below. There's no extraneous data below that, and also there are no breaks. There are no blank rows. If Excel were to encounter a blank row, it would say, "Okay, that's the last bit of data." Even if you have a lot more data than you intended to be in the table or in the data list, it wouldn't be included, because the blank row basically tells Excel to stop looking.
Once your data is in the proper format, you can click any cell in the PivotTable and then on the Data tab, click the PivotTable button's down arrow, and it is important you click the down arrow, to do it exactly, as I'm going to show you how, and then click Create Manual PivotTable. When you do, the Create PivotTable dialog appears, and you can select Use a table or a range in this workbook, and in this case, the data table or the Excel table I'm working from is called Table1. That is correct. If you want, you can use an external data source, and you would select this option here, click Get Data button, and then you would be able to go out and look around and find the data.
I won't do that here, but if you work on the network then you can definitely do that. It just takes a few steps. I want to place my PivotTable on a new worksheet, so it doesn't crowd out this data or be interfered with by this data, and then I don't need to worry about the specific Location, so I can just click OK. So now I have a PivotTable. It's bare bones, it has no data or anything else in there right now, and it's divided into four main areas. The Values Area is where the actual data will go, in this case company revenue, and the Row Area and Column Area are places where you can add fields or columns from the original data list, and use that to create organization for your PivotTable.
The Report Filter Area allows you to put fields there which don't affect the structure of the PivotTable; instead, it allows you to filter the contents of the PivotTable based on any field that's in the report Filter Area. So now let's start populating our PivotTable, and we can do it over here in the PivotTable Builder. If for any reason the PivotTable Builder doesn't appear, or if you close it by accident, or on purpose, you can get it back. I'll close it here. So all you need to do is click your PivotTable. That way the PivotTable contextual tab will appear. Then you can go over to the View group and click Builder.
When you do, the PivotTable Builder will come back. So first, I'll just make a very simple PivotTable with a single field in the Row Area, and then I'll add values to the Value Area. So let's say that I will pull Year. From here in Field name, I click the left mouse button and drag Year, in this case to the Row Labels Area, and you can see that it's available to go there when the Row Labels Area is outlined and also the Year Label has a green circle with a Plus sign; that means I can drop it there and something will happen.
When I drop it, Excel changes the organization of the PivotTable by putting 2008 and 2009 - the Years for my data - into the Row Labels Area. Now I can add the Revenue to the Values Area, also called the Data Area, and when I drop it - let me zoom in on that for you, go up to 200% - you can see now that Excel has populated the data in a PivotTable. There are no details. I only know the total for 2008 and the total for 2009 and the grand total, but it's a start.
Now let's say that I wanted to add another field, and I will just add Company to the top in the Column Labels Area. So, when I drag the Company field to the Column Labels Area, click it here, drag it down, and when I do, my two Companies, FirmA and FirmB appear here, and I find it out how much they have contributed to the revenue for 2008 and 2009. Now that you've learned how to create a PivotTable, you can move forward and discover how to rearrange your data dynamically through pivoting.
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.