When you track changes on a shared workbook, you can list changes on a new sheet, review changes, accept some or all changes, or reject some or all changes.
- [Instructor] A PivotTable is Excel's most powerful analytical tool, and it gives you a quick capability to create summary totals based on list-like data without any formulas whatsoever. We've got a list on this worksheet called PivotTable data in the workbook 10 data management features. It's about 900 rows or so, it's not in any particular order. We'd like to come up with some totals, for example, like we see over in column I. I'll make column H narrower here. A list like this, how long would it take you to create a list like this? I'm gonna double-click one of the cells here.
Are you familiar with that function? It's not the most complex of functions, but, to create a list like this it's gonna take some time. And if you were making a presentation of this data or just analyzing it yourself, could you easily switch and say okay, let's do this with the products that we see, they're coming out of column B they're five different products. How long would it take you to restructure all this? And imagine the ability of somehow doing this in a presentation quickly and easily. Well, it's gonna take some time. This is not a great set of procedures here. With a PivotTable, you could create a list like this in about five or ten seconds.
And furthermore, in a few more seconds you could change the headings and have product here, or customer. There are all kinds of possible comparisons we might want to make between these different columns in this list here. So PivotTable's a new creation, somewhat like a chart is new as well too. And the source data for a PivotTable, you need to have a list with no empty rows or columns within it. If there's other data on he worksheet as with sorting, you wanna make sure you've got at least an empty column off to the right, segregate the other data. Titles in a single row on top.
Unlike other data management features, this is not found on the Data tab. It's on the Insert tab. The same way that we would insert a chart, we're about to insert a PivotTable. And there's a feature called Recommended PivotTables. Excel will analyze the data, give you some thoughts, some ideas and maybe no one of these is quite right but this one's kind of close. I'll just start with this one. There's a bigger preview, it looks good, I'll click okay. And suddenly we'll be on a new worksheet. There we are, and there's our PivotTable getting started already. Off to the right is the all important PivotTable Fields list, that has all of our field names that we saw earlier.
I wanted to show items instead of amounts, so I'm gonna check the box for items here and uncheck the box for amounts. And also I wanted to see region across the top. So I'm gonna drag this into an area called columns. So a PivotTable is based on putting information in rows in columns, and as we do this using the PivotTable Fields list, we put information in columns, rows. If you click a box here, if it's a text field it automatically goes into the rows area but you can drag it elsewhere.
I dragged region into the columns area. If it's a numeric field and you check it, it goes into what we call the values area. So here's the information we're seeing click here, click there, you don't see a formula. Are you sure these are the same numbers we got before with those formulas? Eight one one one? Quick jump back to that worksheet and off to the right. There it is, eight one one one. Who's our top performer? Willy Loman right there. We go back to the PivotTable, yup, that's Willy Loman. So here's our list. I'm gonna zoom in with that slider bar in the lower right-hand corner.
We also have a lot of flexibility here. If we were making a presentation of this somebody in our audience might say, can we see this break out by product? How many products have been sold here? And in that PivotTable Fields list to the right, we'll uncheck region and drag the word product into columns and now I've got a whole new set of numbers. And at different times you'll be moving these fields around in a different ways. Could we see the product break out by region? I'm gonna check the box for region. It's gonna go into the rows area. Now salesperson's already there.
But now we're seeing both of them. And that could be something we want. It is a bit long, there's probably a tendency for a PivotTable to be somewhat compact, but on the other hand, we can have two fields over there, or more. If we don't want salesperson there for the moment, we can uncheck the box and now we're seeing region and the items over here. If you don't wanna see row labels and column labels, we also have contextual tabs that appear up on the ribbon. A new ribbon, PivotTable Tools, and any time the active sale's within the PivotTable we've got an Analyze tab and a Design tab.
When I create a PivotTable, often on the Design tab, I'll go to report layout and although you wouldn't know this instinctively if you use outline or tabular form, you'll see field names instead of the terms row labels and column labels. There we are, how about region and product. Pivoting the data means sometimes we take data like this, and for example put the products over in column A, put the regions across the top. It's no single action, but in the PivotTable Fields list we're gonna flip the order of product and region. And we can do that quickly and easily.
Imagine if you've got any kind of analytical instincts how powerful this tool is. You never worry about in any way threatening or disrupting your source data as your work with the PivotTable. And you do have the option of creating a PivotTable and then putting it on the same sheet as your source data if you wish. As you've seen in the example, creating a PivotTable from a list of data is a fast, powerful, and dynamic way to create summary totals.
- Navigating Excel tabs and menus
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros