Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Like the other applications in Microsoft Office 2007, Excel 2007 boasts upgraded features and a brand-new look. In Excel 2007 Essential Training , instructor Lorna A. Daly introduces the new version in detail. The training begins with the essentials of using the program, including how and why to use a spreadsheet, how to set up and modify worksheets, and how to import and export data. Lorna then moves on to teach more advanced features, such as working with functions and macros. Exercise files accompany the tutorials.
We're going to take a high level look at creating Pivot Tables, so the information we're going to be working with in the next few movies are going to show you how to easily do a Pivot Table. We're not going to get in depth in all the details behind this, but we're going to give you a taste for what a Pivot Table will actually let you work with, and hopefully spur some interest for you to explore a little bit more on your own. The data that we're going to be working with is our inventory data, and it's included in Pivot Table A's worksheet in your Exercise files, if you'd like to follow along.
Right now I'm looking at my the Data tab. In order to create a Pivot Table, I simply select the table itself, go to the Insert tab, and pick Pivot Table. I've then select the Pivot Table option, confirming what data range I'm looking at. And you can tell that you've selected all of your data because it's highlighting it up here in your worksheet. And then you determine where you want that Pivot Table to go, so I want it to go on a brand new worksheet, which is what the default is, I click OK.
And you'll see that the information is now pulled into its own worksheet. With the Pivot Table field list on the right, and the area where the Pivot Table is going to be created here on the left. Before we go on, let's go down to the bottom and select the Sheet4 name, right-clicking on it and selecting the Rename option, and calling this Pivot Table, so if we happen to use this again in our exercises, we know that this particular tab contains our Pivot table information.
We then want to be able to go over to our Pivot Table field list, and I start identifying some of the pieces of information we want a show on here. First thing we want to select is Item Type. So if we select that, you'll see that you have all the different Item Types shown here along your rows. If I select Item Category, it will also include them on my rows. Now this makes it a little bit difficult to understand the information that I'm looking at here.
This is where you get into the real value of working with a Pivot Table, because all you need to do is to select one of your row labels, in this case Item Category, click and drag it, and pop it into the Column Labels box. So what this section of the pivot table field list allows you to do, is to mix and match where the information is stored. And what you're doing is you're pivoting your rows and columns so that you get the information in a way that's going to make sense for you to understand and analyze.
Hence, Pivot Tabling. Next what you want to identify, is now that you've got the information aligned correctly, What are you going to track? The current Costs, the Inventory levels, or the Final Costs of the different pieces of Inventory that you've got to look at. Well let's just take a look very quickly at the current cost. If I select Current Cost, it then goes and presents for me, the current costs that are sitting in the different cells in my original data file, and it presents it here.
You'll notice as I mouse over them, that you get this little dialog box that pops up, it says Sum of Current Costs and the value is No value. A Pivot Table will automatically default to want to add items up, so in this case, because I'm just pulling an individual value from my data source, it's not going to give me the sum of anything because there's nothing add up. But if I did deselect Current Cost and wanted to add Inventory, I could then see that the Inventory is the addition of all the different pieces.
So it's showing me and it's helping me identify very, very quickly the inventory levels of my particular store. I also have a Grand Total column, of the column and row summarizing all of the inventory levels for all the different ways that I'd like to look at it. So you can see, with three minutes of instruction and a few clicks, you've been able to pull the information from your data source into a Pivot Table and quickly analyze what you need to know.
Let's look further at some of the options that we can find at Pivot Tables.
Find answers to the most frequently asked questions about Excel 2007 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.