Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Excel 223 Handbook
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   14 years ago

This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.






Microsoft Excel 223
Course Handbook Supplement

By Richard Rost



Published By
Amicron Computing
PO Box 1308, Amherst NY 14226 USA
www.599CD.com


First Printing 1/25/2006
Copyright 2006 by Amicron Computing
All Rights Reserved


Welcome

Welcome to the ExcelLearningZone.com Microsoft Excel 223: Data Analysis 4 Handbook.

This handbook is designed to be a supplement to the full ExcelLearningZone.com video course for Microsoft Excel 223: Data Analysis 4. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.

We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.


Table of Contents

Welcome 2
Table of Contents 2
Introduction 3
Lesson 1. What is a PivotTable? 5
Lesson 2. Our First PivotTable 7
Lesson 3. PivotTable Rules Create Another PivotTable 15
Lesson 4. Field Settings, Pivoting Data, and Inner & Outer Fields 19
Lesson 5. Example: Sales Log 28
Lesson 6. Example: Employee List 35
Lesson 7. Refreshing PivotTable Data, Filtering Data, and Using Page Fields 40
Lesson 8. Sorting PivotTable Data 48
Review 51




Introduction

Welcome to Microsoft Excel 223: Data Analysis 4, brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.




Objectives for today’s class:

· What is a PivotTable
· The PivotTable Wizard
· Pivoting Data
· Sales Log
· Employee List
· Filtering PivotTable Data
· Sorting


Pre-Requisites: Before taking this class, you should have taken Excel 222: Data Analysis Part 3 and all the previous Microsoft Excel courses in this series.

In class, we will be using Microsoft Excel XP (2002). However this lesson is valid for every version of Microsoft Excel. You will notice some cosmetic differences with different versions of Excel. Some of the features may be slightly different, however the vast majority of the topics we’ll cover will be the same for almost every version of Excel.


Normally for all my classes I recommend that you type in all of the data that I type in during class. However, in order to work with pivot tables, we need some pretty large lists of data. I encourage you to copy the sample worksheet data from the website at:

http://www.ExcelLearningZone.com/excel/223

On the website you will see links for all the different sheets that we’re going to use in today’s class.




If you click on them you can copy this information onto your excel worksheets.




You will also find a sample worksheet file on your computer that has been installed with this video in your Program Files - 599CD Excel – Excel 223 folder.

Of course if you have problems or questions while working on this course, you can always contact us at http://www.excellearningzone.com/contact.


Lesson 1. What is a PivotTable?

In this lesson, we’re going to learn all about PivotTables. We’re going to start by answering the question, “What is a PivotTable?”

PivotTables are one of the most sophisticated yet widely under-utilized features of Excel. Most people just don’t understand them, but once you learn how to use PivotTables, you’ll find that they’re very easy to use and they add a lot of power and sophistication to your Excel spreadsheets.

A PivotTable is basically a dynamically created and updated report generated from an Excel list or other database. We’ve been working with Excel lists in this data analysis series, so we’re going to take a list to create a PivotTable out of it. A PivotTable can take a huge list of data of hundreds – even thousands of records - and make a nice, easy-to-read summary of the information.

You can take thousands upon thousands of rows of sales data for example, and have your PivotTable quickly and easily break it down into sales by month and by state – or sales by store and by sales rep. Or, whatever you want to do with it!




A PivotTable uses a 2-dimensional list of data to create a 3-dimensional table to summarize that data. Your list is a 2-dimensional construct. You’ve got basically, rows upon rows of data. We’re going to take that and use a PivotTable to create a 3-dimensional table.

PivotTables help you to analyze data and reveal relationships in that data that might not otherwise be apparent just by looking at rows upon rows of data. You might have a PivotTable that shows you, for example, that this particular store does very well on this particular month every year. You might not see that just by looking at hundreds of rows of sales data. But once you put that information into a PivotTable, the relationships between the pieces of data might become apparent.

You can use PivotTables to format, filter, or hide data on a mass scale. This makes it easier to emphasize a point – such as which store had the highest quarterly sales. If you’re generating a PivotTable to show information to other people, you can order the data any which way you want to emphasize your point.

PivotTables are very powerful because they allow you to analyze the data in a list without having to create lots of manual functions and formulas. You can summarize a large amount of data in a small space with only a few clicks.

PivotTables allow you to perform cross-tabulation, which is summarizing multiple pieces of data in various ways. For example, you can quickly and easily show sales by product and store, or sales by store and year, or time worked by employee and office. The possibilities are endless.

PivotTables allow you to quickly and easily change the types of functions you’re performing on your data (sums, averages, etc.). This allows you to change the way you view your data on the fly... you can even add groups and subtotals to your data very quickly.

PivotTables allow you to analyze your data from different angles. By changing the rows, columns, and subtotals, you an move – or “pivot” – the data to look at it from different perspectives.


Lesson 2. Our First PivotTable

In lesson one, we learned about PivotTables, what they’re used for, and why we want to create them. In this lesson, we’re going to create our first PivotTable. I’m going to show you how to quickly make a PivotTable using the wizard just so you can see how easy they are to work with. Then, after this lesson, we’ll go back and look at all the various features of PivotTables including field settings, filtering, sorting, custom fields, grouping, subtotals, PivotTable options, and lots more. But in this first lesson, I just want to show you how to quickly create your first PivotTable.

Before we can begin building a PivotTable, we need to have a list of data to work with. Let’s create a list of data that shows sales information for our various stores – showing the manager’s name, sales expenses, profits, the year and the quarter for each of our stores. So first, I’ll create a header row that describes the data. We learned how to do this in our previous data analysis classes when we started working with data lists. I’ll create Year, Quarter, Store, Manager, Sales, Expenses, and Profit.




Now we fill in some data: year 2000, first quearter, the Buffalo store, the manager is Davis, 465 sales, 245 in expenses, and we can calculate the profit, right? Profit eaquals our sales minus our expenses.





In order for our PivotTable to have enough data to actually work with, we’re going to need lots of rows of information. Yes – you can do it with only a couple of rows, but it’s good if you have a whole bunch of data so you can see the PivotTable in action. Now if you want, go ahead and type several rows. I suggest that you use ten rows of data from various years and quarters. However, if you don’t want to sit here typing all day, you can copy this information from our website http://www.excellearningzone.com/excel/223. Here, we’ve got all the different spreadsheet data that we’re going to be using in today’s class up on this page.

At this page, click on the Stores, Sales, and Expenses link.




All you have to do to copy this into your Excel workbook, is click on Edit – Select All or Control + A.






Then copy it by clicking Edit – Copy (or Control + C). Now switch back over to Excel, and click Edit – Paste (or Control + V).



And there we go!



That’s the quickest and easiest way to get it. Okay – now that we’ve got this information, we can now create our PivotTable. Let’s say the boss wants to see the total sales for each year based on each store. It’s not very evident looking at the date the way it is in the list. But to see the sales broken down by store for each year, is really easy to do with a PivotTable. And here’s how we do it.

Let’s click on Data and then PivotTable and PivotChart Report.




The wizard starts up. First it says, “Where is the data that you want to analyze?” Well, we’re using a Microsoft Excel list. Next it says, “What kind of report do you want to create?” Let’s click on Next.




Next, the wizard wants to know where is the data that we want to use. And it guessed correctly here, A1 to G37. Notice the little dancing ants flying around our table on the background. Since that’s where we had our cursor in, the wizard guessed that entire range of data was the data that we wanted to use for our PivotTable. If that’s not correct, you can just click and drag and highlight the entire set of data. But since the wizard guessed correctly, I’ll just hit Next.




Where do you want to put the PivotTable report? Let’s put it into a New Worksheet so it’s all by itself. Click on Finish.




Here we are now on a blank sheet and you can see different fields that say, “Drop Data Items Here,” “Drop column Fields Here,” and “Drop Row Fields Here.”





What’s this? Off to the side over here, you should see a PivotTable Field List. You should also see a PivotTable toolbar floating somewhere over your spreadsheet.




If you don’t see the PivotTable toolbar, you may have to turn it on. Click View – Toolbars – and then PivotTable.




Make sure the PivotTable is on. If you don’t see the PivotTable Field List – it’s this button right here on the end of the PivotTable toolbar.




Make sure you can see both of these things. So we’ve got our blank PivotTable in the background. We’ve got our field list. Now we’re going to drag over the fields that we want to see summarized. I want to put the Store where it says Drop Row Fields Here.




There’s my list of stores.




Now I want to drag the year to where it says Drop Column Field.





Now for the data items, I want to see Sales. So I’m going to grab Sales, drag it, and drop it where it says, “Drop Data Items Here.”




And there we go! That’s all you have to do to create a PivotTable. Now we can quickly and easily see all of our stores, what their sales were for each year, grand totals for each, and grand totals for everything.



Lesson 3. PivotTable Rules Create Another PivotTable

In this lesson, we’ll talk about some of the rules that you have to follow to work with PivotTables. We’ll create another PivotTable using the wizard and we’ll talk about some of the options during the wizard in more detail.

Now that we’ve got our first PivotTable under our belt, let’s take a step back and examine some of the rules and some of the things we have to know before we start working with PivotTables. The example that I gave you in the last lesson was just a quick and dirty one to get you in there – to see how PivotTables work.

First, let’s go back to Sheet 1. Sheet 1 is where I have my list of data and I’m going to change my sheet tab name to StoreList.




And Sheet 4 is where the wizard dropped my PivotTable. I’m going to change Sheet 4 to StorePT.




Let’s examine our StoreList. There are a couple of rules before you begin making PivotTables. First, your table can not have any subtotal rows, empty rows, empty columns, or totals in it. You literally just want a list of data. Your list can have only one header row and the field names have to be unique. And all of the fields in the header row should have text in them. Don’t have a field with a number in it. They should all be meaningful – and they should all have text in them. You can however, have calculated fields, like the in the Profit fields.

Now if you copied this data from the website, the data in the Profit field won’t be calculated for you. It’s just a number because we copied down a list of data from the website.


So now that we know some of the rules for our data list, lets create a second PivotTable based on this information. So just click somewhere in the PivotTable range – you don’t necessarily need to select the whole thing. Click Data – and then PivotTable and PivotChart Report.





Let’s talk some more about these options. Where is the data that you want to analyze? A Microsoft Excel list or database is what we’re using. You can also use an External data source – maybe a text file, or a Microsoft Access database, or some other external data source. There’s Multiple consolidation ranges if you want to get your data from multiple places. Another PivotTable report or PivotChart report lets you get your data from another PivotTable. For this lesson, we’re going to stick with the Excel list that we’re working with now.

Click Next. Now where is the data that you want to use? Again, the wizard guesses correctly. It guesses A$1 to G3$7. If you look at the table in the background and scroll down, you can see that there are 37 rows worth of data. Here’s a little trick though. Instead of G$37, I’m going to make this G$5000. The reason why I do that is because if I go back to my data and add a couple of records, then I have to go back in and change the PivotTable because the PivotTable will not automatically grow if I add more records.




So by making this something huge, and I do add more records in the future, they’re automatically added into my PivotTable. Click Next. And don’t worry - the PivotTable will take those blank rows into consideration. Where do you want to put the PivotTable report? Select New worksheet. Existing worksheet takes the PivotTable and sticks it into an existing worksheet where you’ve already got some data.




Now, the Layout button shows the PivotTable layout. In older versions of Excel, this is what you had to use to drag your fields onto your PivotTables.







In the newer versions of Excel, you don’t have to do that. It’s no surprise that this stuff is going to work almost exactly the same whether you’re using the new version of Excel and you can do it right in the spreadsheet or you have to use an older version of Excel and work with the layout. Guess what – it works the same. It just looks a little different. Let’s cancel out of this so we can use the new way and then hit Finish.

Last time we looked at the Sales for Each Year by Each Store. This time, let see our Store by Manager and then drop our Sales in. Notice this time it says, “Count of Sales.” In other words, it’s counting up the number of sales for each of our managers for each of our stores.




You can scroll across and see the totals for each store.



This again is just another way that you can take two bits of data and put them on your PivotTable and see the information that you want to see.


Lesson 4. Field Settings, Pivoting Data, and Inner & Outer Fields

In this lesson, we’re going to learn about field settings, we’ll learn how to pivot our data across the axis, and we’ll learn about inner and outer fields. In the last lesson, we created this PivotTable which shows us the number of sales per store broken down by manager. Now let’s say for example that we didn’t want to see the number of sales that each manager had at each store, let’s say that we want to see the dollar total of sales. Excel used the Count function when it created this PivotTable. So what I’m going to do now is double-click on the “Count of Sales” box and that will bring up the PivotTable Field with field settings. The name is Count of Sales and you can change this if you want to but I’ll leave it as that. Come down to where it says, “Summarize By” and click on “Sum,” and then hit OK.




And look at that! Now we have the “Sum of Sales.




Now you can see each manager and the sum of Davis’ sales, and the sum of Jeffries’ sales, and if you scroll over to the right – you’ll see the sum of each store.




You can also use different functions. Double-click “Sum of Sales.” You can use the Average of Sales, the Max, the Min, the Product, the Count and the Count Nums. Count Nums counts the numeric data. And we’ve got Standard Deviation.




Let’s go back to Sum. You can also get to the PivotTable Field by right-clicking on that cell and selecting field settings.



There’s also a Number button that will bring up a Format Cells dialog box. And you can change this to Currency for example. (Click OK)




And then all your data inside here shows up as currency.




Now let’s see how we can move these fields around. Let’s say we want to take Manager and put it down on the row headings. Just click on it and drag it and drop it down.






Now grab Store and drag it up top.




And there we go! I’ve rearranged the PivotTable to see the data a different way. It’s pretty much the same data – but I’ve pivoted the data about the axis. Let’s say I want to remove Sales and instead, see Expenses. I’m going to click and drag the Sum of Sales box and drag it off. Notice how the icon changes to a little red “x.” I just pulled the data item off.




Now I’ll drop Expenses on there. Just click, drag, and drop.




Notice how it’s gone from Sum of Sales to Count of Expenses. All I have to do is double-click and change that to Sum and I can see the Sum of Expenses per Store by Manager.




Now sometimes you might want to see more than just two pieces of information. For example, I’m going to remove Store as the column header. Let’s grab Year and drag it and drop it in the column header. Let’s change Sum of Expenses too. Let’s drop Profit where Sum of Expenses used to be and change it to Sum of Profit. There’s the Profit for each Manager by Year.




Let’s say you want to break that down even more. Let’s say in addition to Year, you also want to see a break-down by quarter. Well, watch this. Grab Quarter and drop it next to Year. Boom – Look at that!




Now in this particular case, it came in backwards. It put the Quarter first – then the Year. To ‘flip-flop’ these, just grab one and drag it after the other one.







There we go! You can also right-click on these and select Order – Move to Beginning – Move Left.- Move Right – Move to End.




And that creates the Inner and Outer row and column. It’s actually easier to see this if we use the other axis. So let’s pivot the data one more time. Grab Manager and drop it next to Quarter. Then take Year and put it where Manager was. And then take Quarter and place it next to Year.




If I scroll down, you can see the totals for each year. Here, you get a nice break-down by year and by quarter. You can also put Store back on here. You can make these PivotTables pretty complicated. Grab Store and drop it next to Manger.




Now look what we’ve got: We’ve got Year by Quarter and we’ve got a break-down by Store. Scroll to the right to see more.




These PivotTables can get pretty complicated if you want them to be. They can be as complex or as simple as you want them to be. I’ll pull Manager off. I’ll right-click on it and look at that! There’s no way to remove it with the right-click! So just click and drag it all the way to the bottom and eventually you’ll get a spot down there with an “x” where you can let it go.




So there’s a nice break-down by Store and by Quarter. Now here’s a nice trick: Let’s say you don’t want to see all this detail all the time. If you double-click on one of these years, like “2000,” notice how it hides up the quarters. This is considered a group of data and it’s the outer field (outermost field) compare to the inner fields to give you more detail.








Lesson 5. Example: Sales Log

In this lesson we’re going to take a look at an example of another sheet – our Sales log - we’ll grab off the website. And we’ll generate a new PivotTable and show you a couple of different examples of some other things that you can do with PivotTables.

While we still have this sheet open, let’s change “Sheet5” to “StorePT2,” and let’s go to a blank sheet and we’ll put some more sample sheets in here. Let’s see what we have on the website. Back on the website, http://www.excelllearningzone.com/excel/223/ let’s pick the “Sales Log” sheet. This is a simple sheet that has some dates, customers, what state the customer’s from, the store, the department and the sales amount.




So let’s go ahead and select all, copy all that to the clip board, switch back over to Excel and paste that stuff in.




See how nice and simple that was? I’ll highlight the letter columns and double click on them to resize the columns to fit the data nicely.




And if you want, you can highlight the rows and put some color in and make your stuff pretty! Click Data – and then PivotTable and PivotChart Report. Click Next and change “G$20” to “G$2000” just in case we add records. Then click Next. And then hit Finish.




While we’re thinking about it, let’s “Sheet2” to “SalesLog.”




And “Sheet6” to “SalesLogPT.”




Alright. Now the boss wants to see the total sales for each store. That’s pretty easy to do. Drag Store to where it says Drop Row Data Here.




Drag the Amount field and drag it to where it says Drop Data Items Here. Notice it says, “Count of Amount” where you dragged your row data. Double-click and make that “Sum of Amount.” And that might be all you want right there! That’s a perfectly fine PivotTable of Sales Total for each Store.




You don’t have to break it down based on a column of data. You can just do that!

But for the second example, the boss wants to see the state that the customer is from. So let’s drag State and drop it next to “Sum of Amount.” Now you can see the Store, the State, and the Total for each State.




If you scroll to the right, you can see the Grand Totals for each.




Next, the boss wants to see how many different sales were made to each customer. Let’s get rid of Store, let’s get rid of State, and let’s pull “Sum of Amount” off of there too. Drag Customer to where it says Drop Row Fields Here. Drag Amount to where it says Drop Data Items Here. That tells you how many different sales were made to each customer.




Now – what if you want to see the count of sales and the total of the sales? Just grab Amount again and drop it in the data region. Now you’ve got “Count of Amount” and “Count of Amount2.” Let’s double-click.




Okay, we’ve double clicked on it and it yelled at us! (Just click OK).




Let’s right click on the “Count of Amount2” and pick “Field Settings.” If double-clicking doesn’t work, try right-clicking.





Change “Count of Amount2” to “Sum of Amount2.”




Now we’ve got “Count of Amount” and “Sum of Amount2.”




You can even change “Count of Amount” to “Number of Orders” and “Sum of Amount2” to “Total Sales.”




So now we can see how many sales there were and the total dollar amount of the sales. Scroll down and you can see the “Total Number of Orders” and the “Total Total Sales.”




And finally the boss wants to see what other types of products are sold most often. It’s basically the same kind of thing we’ve generated already. We can drag off Data, and get rid of Customer. Drag Departments to where it says Drop Row Items Here. Then drag in Amount again.




If you want, you can drop Date in there or State – whatever you want to see.

So that’s how we can use the Sales Log to generate different Reports.



Lesson 6. Example: Employee List

Of course, learning by example is always the best way to learn. Let’s take a look at one more sample sheet. We’re going to take a look at an Employee List and see how we can do some stuff with PivotTables that’s not necessarily numeric. Let’s go back to the website again and get a new sheet: the Employees sheet. This is a basic employee list, it’s got the employee name, their department, their supervisor, and what their salary is.




So let’s highlight all the data again (Control + A), copy (Control + C), flip back to Excel on a new blank sheet, and paste (Control + V).




Highlight the columns and then double-click on one of them to space them out nicely – maybe add a little color. And there we go!




We haven’t saved our sheet in quite some time! So let’s save our workbook.




Let’s save it as “Excel 223.xls.”




Let’s change “Sheet3” to “Employees” and we’ll make a PivotTable out of it.




Make sure to click somewhere inside the table and click Data from the toolbar to get to the PivotTable and PivotChart Wizard. Hit Next and change the range to “D$5000” just in case we add more records.





Click Next and Finish. See how easy that is to set up? Let’s change the “Sheet7” tab name to “EmployeePT.”




Okay, now the supervisor wants to know how many employees are assigned to each department. So let’s drag Employee to where it says Drop Row Fields Here. Let’s put Department on the top. And let’s grab Departments and put it where it says Drop Data Items Here.

Look at that. It shows you a count of the employees by department for each department.




Now scroll down to the bottom. There’s the total that you’re looking for.




Likewise, you might want to know how many employees are assigned to each supervisor. So let’s get Department out of here and get rid of “Count of Department.” And instead of Department, we can use Supervisor. Grab Supervisor and put it on the top and inside where its the data.





Using this information, you can see which supervisors might have too many employees and which have too few. How about this. Let’s say we want to check on the salary information for each department to see which departments are costing us money. Well let’s clear the table and get rid of all this stuff. Now let’s put Department on the left and let’s put Salary in as our data. And let’s change “Count of Salary” to “Sum of Salary.”

Here, we can easily see how much Sales and Service is costing us.




Want to break it down by supervisor? Feel free - Just drag it on over and there you go!




And of course you can add Department up top as well.





Now notice what happened when I did that. The Department up top took the Department off the other axis and dropped it up top. See how that works? You can only have it on one of those two axis. We can put Department on top and maybe put Employee as the rows to get a break-down by department and supervisor.




The best way to learn this stuff, really, is to play with it. Just come in here, start moving these things around, start playing with different fields, and use some of your data that you actually work with.


Lesson 7. Refreshing PivotTable Data, Filtering Data, and Using Page Fields

In this lesson, we’re going to learn a couple of different concepts. We’re going to learn about refreshing our PivotTable data if our records and our lists change. We’re going to learn how to filter data in a PivotTable and we’re going to learn about page fields.

Okay, let’s go back to our StorePT sheet – the first store PivotTable that we made. It’s basically the Sum of Sales by Store for each Year. Now take a note that the total for 2000 sales is 7466. I’ve discovered an error in my data.




So we have to go back to our StoreList sheet and change the Buffalo sales for the first quarter were 472.




Now let’s go back and take a look at our PivotTable and the total is still 7466. What’s wrong? Well, PivotTables do not automatically refresh their data. You have to do that manually. The reason why is because PivotTables are very complicated and if they were to automatically refresh themselves, it would suck a lot of system resources down. Fortunately, it’s pretty easy to refresh your data. Right on the PivotTable toolbar is a little exclamation point (or a “boot print”). Click on that and your data will automatically be refreshed.





See that? Now the total is 7473. That’s how you refresh your data. You can also right-click anywhere in the PivotTable and refresh data.




And you can also click on the PivotTable drop-down from the PivotTable toolbar.




Also on the PivotTable toolbar, you’ll see Table Options.




Click on the Table options and a big Options menu opens up. One of the options on here says Refresh on open. If you want your PivotTable to automatically refresh itself when you open the workbook, check that box on. Then when you open up the workbook, it will go out and automatically pull in the updated data.




Click on OK.

Now let’s say you don’t want to see all of this information. Let’s say you only want to see some of it. Let’s say you only want to see the years 2000 and 2001 for example. So how do we hide certain information? Well, in a previous lesson we learned how to filter data using the auto-filters and the advanced custom filters. You can do something very similar in a PivotTable. Notice the little drop-down button the Year? Click on that and you’ll see some filtering options. You can either Show All, or you can show only specific years. If you want to hide 2002, guess what? Uncheck it. This will only show us 2000 and 2001.




Click OK. And there you go! If you want to see all the data again, just click on Show All.




Same thing for Store.




Now that’s a real basic type of filtering. But there’s something even more powerful that you can do called a page field. Let’s say for example that you want to see all the information shown here, but you only want to see it for particular quarters. Let’s say you only want to see the first quarter of each year.

Grab Quarter and drop where it says Drop Page Fields Here.




Now by default, we’re still seeing all of the quarters.




So just click on the drop-down arrow button and select 1 and hit OK.




Now, notice the numbers got noticeably smaller. That’s because you’re only seeing first quarter data for each of these years.




Change it to second quarter. Notice the numbers have changed. This is your second quarter data for each of those years.




So that’s what page fields do. Page fields allow you to filter the data for the entire PivotTable based on one or more of these fields.

Let’s see another example. Let’s go over to our Sales Log PivotTable. Let’s clear these fields off. And let’s see the Store, and let’s see by Department across the top. We’ll put the Sales Amount in. And let’s change “Count of Amount” to “Sum of Amount.”





Now let’s say we only want to see the hardware department. Well let’s drop that down and uncheck Service, Software, and blank. Now there’s just the hardware totals.




You can do the same thing for Stores. And there’s the information we want to see with the basic filters.




Now let’s say we want to see all the data, but break it down by state. So let’s grab State and put it where it says Drop Page Fields Here. Click the drop-down arrow so we can only see results from New York. Hit OK.




Now look at that!




So see how page fields allow you to filter all the results inside your tables. We can do something similar with our Employee PivotTable. So again, let’s clear everything. And we want to see our list of Employees, by Supervisor, with the Salary. And let’s change “Count of Salary” to “Sum of Salary.” And let’s make Department our page field. But we only want to see the Sales department.




That’s the benefit of a page field. Let’s go back to our initial Store PivotTable. You can have multiple page fields. Let’s say you want to break this down and also be able to filter by Manager. Let’s drop Manager in the page field.




Let’s say I want to see all Quarters, but I only want to see for Adams.




See how that works? This is awesome! You can get all kinds of data out of PivotTables but with just a couple of clicks.




Those numbers would not be easy to get out a huge gigantic list of data. That’s why PivotTables are so powerful, yet as you can see they’re very easy to use. You’ve just got to know how to use the information. That’s where we come in. I’ll show you how to do all this stuff.


Lesson 8. Sorting PivotTable Data

In this lesson, we’re going to learn all about sorting PivotTable data. Sorting our PivotTable data is relatively easy. Just like a normal sheet, you can click on one of the columns and click on the Sort to A-Z button.




If you click on the column headers, you can sort the columns ascending or descending (with Shift + Click).




You can also sort totals. Click in the total column and sort them that way. We can also click on Data and then Sort. A sort window appears that’s slightly different from the sort that you get from a normal sheet. It’s just says Sort By and then the range which you can change. Just click OK.




Let’s go back over to our Employee PivotTable.




Here’s one of the neat auto-sort features of PivotTables. Go to your PivotTable toolbar. Drop-down the PivotTable option and pick Sort and Top 10.




Here’s the PivotTable Sort and Top 10 Options for PivotTables. Now the AutoSort options are manual – you can drag items around to rearrange them. Or you can have them AutoSorted ascending or descending and specify the field that you want to sort by. With the Top 10 AutoShow, you can have so it only shows the top 10 records. Let’s say I only want to see the top 5 names of Employees using the Sum of Salary field.




Hit OK. And there we go! You can see the top 5 employees based on the total salary. Notice that Employee is in blue. That tells you that there is an AutoSort on.




You can change this to the top 3... the bottom... the bottom 5. Let’s turn that AutoSort off and put the AutoSort option at Manual, and notice how easy I can just click and drag these guys around – and it moves entire rows of data. Or likewise, you can right-click and pick Order – Move to Beginning, Up, Down, or End.




So that’s how we can sort information in our PivotTables.



Review

Let’s take a moment now to review what we’ve learned in today’s class.

· We learned about PivotTables, what they’re used for, why you should want to use them.
· We built a couple of PivotTables using a PivotTable wizard.
· I showed you how to pivot your data and add fields and move fields, change fields.
· I showed you how to filter your data.
· We worked with a Sales Log, and Employee List.
· We also learned how to sort our information and show a Top 10 List.

What’s next? Following this class, you’ll want to take Excel 224: Data Analysis 5. It’s the “Part 2” of our PivotTables courses. There’s still a lot to learn about PivotTables.

· We’ll learn how to group PivotTable data.
· We’ll learn about creating sub-totals and sub-detail sections.
· We’ll learn how to customize and format our PivotTables.
· We’ll learn about advanced field settings.
· We’ll go over all the different PivotTable options.
· We’ll learn about calculated fields and calculated items that you can insert into PivotTables.
· I’ll teach you about base fields and base items and how to do running totals.
· We’ve got a bunch of miscellaneous tips and tricks to cover.
· And I’ve got an excellent side project that’s going to mix together PivotTables, V-Lookups and a whole bunch of other techniques that we’ve learned over the last couple of classes.
· Also still yet to come, we’ve got PivotCharts, PivotTable functions including the Get PivotTable Data function, creating PivotTables from external data sources like an Access database, saving your PivotTables as an HTML document, multidimensional PivotTables, and PivotTable programming.

We’re going to cover those topics in a future class.

Tell us what you think. Log on to www.excellearningzone.com/Survey and take a short survey about this course.


RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!


Now it’s time for your skills check. Head on over to the website at www.excellearningzone.com/test. If you pass, you can print out a Certificate of Completion.

What’s next? Visit www.excellearningzone.com for our complete list of courses.

Need Help? Visit www.excellearningzone.com/TechHelp for technical assistance.

Make sure you’re on our Mailing List. Go to www.excellearningzone.com/MailingList for details.

Contact Us. If you have any questions, go to www.excellearningzone.com/Contact for information on how you can contact us by phone, email, or live online chat.

Don’t forget to visit our User Message Forums online at: www.excellearningzone.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your questions, get answers, and tell us what you thought of our class.





This course, handbook, videos, and other materials are copyright 2002 - 2006 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.

This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:

Amicron Computing
PO Box 1308
Amherst NY 14226 USA
www.excellearningzone.com











Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

11/30/2021Import Multiline Cells
11/11/2021Link to Excel
11/11/2021Read Write Excel Data
11/10/2021Excel Moving Average
9/11/2021Circular References
8/25/2021Stock History
8/23/2021Concatenation
8/17/2021Flash Fill
8/5/2021Stock Portfolio
8/4/2021Stock Portfolio
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: excel handbook  PermaLink