Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Excel 224 Handbook
By Richard Rost   Richard Rost on LinkedIn Email Richard Rost   17 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 224
Course Handbook Supplement

By Richard Rost



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


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


Welcome

Welcome to the ExcelLearningZone.com Microsoft Excel 224: Data Analysis 5 Handbook.

This handbook is designed to be a supplement to the full ExcelLearningZone.com video course for Microsoft Excel 224: Data Analysis 5. 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 3
Table of Contents 3
Introduction 4
Lesson 1. Grouping Data 6
Lesson 2: Grouping Dates & Values 11
Lesson 3: Subtotals, Subdetails, and Advanced Field Settings 19
Lesson 4: Formatting PivotTables and copying PivotTable Data 26
Lesson 5: PivotTable Options 32
Lesson 6: Calculated Fields 37
Lesson 8: Base Fields & Items 41
Lesson 9: (Bonus Lesson) Tracking Employee Costs 45
Lesson 9. Review 57


Introduction

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



Objectives for today’s class:

· Grouping
· Subtotals
· Formatting
· Advanced options
· Calculated Fields & Items
· Base Fields
· Employee Costs


Pre-Requisites: Before taking this class, you should have taken Excel 223: Data Analysis 4 (PivotTables Part 1) 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. The sample data for this class you found at ExcelLearningZone.com/Excel/224.

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 224 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. Grouping Data

In this lesson, we’re going to learn about Grouping Data – grouping different records together. We’re also going to learn about a feature called Drill to Detail.

Here I am back in my Store PT PivotTable.




This is the PivotTable that we created from our store list.




And just in case you’ve forgotten from the previous class: Excel 223, we grabbed this information from off of our website ExcelLearningZone.com/Excel/224. Here, you’ll find links to the different sheets that we’re using.




Here’s our Store Sales & Expenses sheet




And we’ll just highlight all of these with Control + A, Control + C to copy, flip back over to Excel, Control + V to past that information in. And now I’ve made a basic PivotTable which is right here on the StorePT. I also have the Store PT2 which is a more complicated PivotTable that we made, but we’ll stick with StorePT. StorePT is basically the store.




We built this in the last class: Excel 223. What I want to do is show you how to group different items together. For example, we have our Buffalo and our Chicago stores. I would like to be able to group them together. Perhaps, let’s call it a United States division. We’ll group those together so we can see information related to both of those stores together as opposed to perhaps, our Canadian division which will have the Toronto store in it. So how do we group ‘like’ information together in a PivotTable? We’ll here’s what we’re going to do.

We’re going to highlight Buffalo and Chicago together. We’re going to right-click on them. Select Group and Show Detail, and then click Group...




Notice that Excel has created Group 1.




Now if you don't like the name, “Group 1,” just click on it once and type something over it like, “US Division.”




Notice where it says “Toronto,” we can type in “Canadian Division.”




Now if you want to, you can double-click to shrink that up and that will hide and expand all the different stores in that division. Double-click to hide them. Double-click to expand them.




You also get a new Store2 that you can drop down and you can see where you can hide, or basically filter all the information that you want to.




Notice when I have the US Division stores hidden, the Grand Total reflects the total for the whole division. Notice there are also Hide Detail and Show Detail buttons on the toolbar. This does pretty much the same as double-clicking on the division.




Now while we’re talking about detail, check this out! Click on anyone of these individual data items – for example, Buffalo’s 2,151 sales. Click on the Show Detail button and watch what happens. Look at that! A brand new sheet was created for you. This is called Sheet1 now. And notice that I’ve got all the 2000 Buffalo individual records from our original sheet that derived that data from the PivotTable.

In other words, when I drilled for detail (that’s called Drilling For Detail), it created a new sheet for me that had all those records that allowed that number to be derived.




Now we don’t need that sheet, so I’m going to delete it. But that’s a neat little trick if you ever wonder about a number, “Okay, where did this number come from?” Just double-click on it (or click it and select Show Detail) and you’ll get a whole other sheet that’s got all those records on it.


Lesson 2: Grouping Dates & Values

In this lesson, we’re going to continue grouping our PivotTable data. We’re going to group by dates and by other numerical values. Let’s go over and take a look at our SalesLog. Remember this one from our last class? Again, it’s on our website if you can’t find it.




Let’s create a new PivotTable to group these sales by month. (Now watch this – you’ll like it.) Click anywhere inside the PivotTable and start the wizard. From the menubar, click Data – PivotTable and PivotChart Report...





Click Next. But change that $G$20 range to $G$2000 just in case we add more records later. Click Next. Now I’m getting a message. This message says, “Your new report will use less memory if you base it on your existing report [Excel 224.xls]SalesLogPT!PivotTable6, which was created from the same source data. Do you want your new report to be based on the same data as your exsiting report?” Well essentially what’s happening here is that if you try to make two PivotTables off the same data, Excel has to make a copy of that data which each PivotTable which can make your work files very large. For class and the example that we’re working on, I’m not too worried about that. But if you had a really big file and are concerned about space, you might not want to do that. If space is a problem, click Yes, and basically one PivotTable will be based on the other one. I’m going to say No and keep the two reports separate.

Where do you want to put the PivotTable report? A new worksheet is fine and I’ll click Finish.




So here’s what I want to do. I want to make a new PivotTable here and I want to put the Dates in a row.











I want to put my sales Amount in the Data Items field.




I want to change Count of Amount to Sum of Amount.





Now what I’d like to do is make one row here representing each month. Right now, I’ve got 5 dates in January and I want to sum all of those up. Alright, how do we do that?




Right-click on the date header. Come up to Group and Show Detail, and click Group.





You’ll get this Grouping box. How do you want to group them? How about months? Select Months and hit OK.




And there we go. Notice how our data is easily broken down by month.




If you did have more than one year of data here, then you’d need to add years to this. So again, just go to Group and Show Details - Group, and in addition to month, also add Years. You’ll notice that you’ll have a separate one here for years and for months as well.





Now if you want to, you can add some other qualifiers in here. You can add some column headers – maybe department...




Now let’s say that you don’t want the Years and Months on here. Go back to Group and Show Details – Group and maybe instead of that you want to group by Days or Weeks. Well there is no option here for days so we can just change the number of days to 7.




Here they are grouped by weeks.




There’s all kinds of different options in here for gruoping by dates. Let’s change the sheet name to SalesLogDatesPT.




One more example for you. Here’s my employee table.














Let’s say I want to group these employees based on their salaries. Let’s create a PivotTable out of these gues real quick. Let’s say Salary is going to be the row. And let’s group that by thousands and see what we get. Look at that. It’s grouping them by every ten thousand.





Perhaps I want even ten thousands. Well let’s go back to to Group and Show Details – Group and start at ten thousand and end at sixty thousand. There we go! But we’ve got no data in here so let’s go put some in. How about we put Salary the data field so we have Count of Salary. Now we can see the number of records that fall into each salary range.




If we put the Department accorss the top, we get Sales and Service.




Want to see numbers instead of just counts? Well you know how to do that, right? There we go!





Here are the average salaries in those salary ranges.




And you can do things in here that you would normally do in your spreadsheets. For example, I can highlight all the Sales, Service, and Grand Total fields and increase or decrease the decimals. So there’s all kinds of things that you can do when you learn how to group your data in a PivotTable.




Lesson 3: Subtotals, Subdetails, and Advanced Field Settings

In this lesson, we’re going to work with Subtotals, Subdetails, and we’ll see some of the Advanced Field Settings. Let’s go back to our StoreList.




And let’s create one more new PivotTable. We’re going to put two row headers in here. I’m going to show you how to make Subtotals with these row headers. So let’s say Store and Manager will also be our row header.





Let’s put our Sales in. Notice how you don’t always have to have a column to have a handy PivotTable. This PivotTable right here gives me a lot of information just from looking at it without even having the header columns.




If you wanted to, you could drop Year or Quarter here – let’s drop Year here. Let’s double-click on the store header.




Here, we’ve got the name of the store. And for Subtotals, we’ve got Automatic, Custom, or None. Let’s change it to Custom and go with Sum and Average (you can select multiple items). Click OK.


Look at that! I’ve got the Sum and the Average of my Subtotals. Very Nice!




Let’s double-click on Manager and select Custom and Count and see what we get. Well, let’s scroll down and we’ll see them all at the bottom. All the sums are all the way at the bottom.





















Let’s get rid of Manager and let’s say that we just have this. We have the Store and we have the Year. And I want to see just the Chicago managers – not the rest of them.




I’m going to double-click Chicago and it says Show Detail – Choose the field containing the deatil you want to show. In other words, give me the inner field. I’m going to Click Manager, and hit OK.




















And there you go! Essentially, it brings in Mangager but only for that one particular field. And you can still double-click to hide it or show it. These inner fields are sometimes called Subdetails under the row headers or an inner row header.




Now on this PivotTable, I’ve got an inner and an outer row header. Double-click on the outer row header to get the PivotTable field settings and click on Layout.




Click on Show Items in Outline Form, click OK and let’s see if we can tell the diffference is here.



Okay, we have a little bit of a different layout here.




Let’s go back to the PivotTable Field Layout options and click Show items in tabular form. But notice where it says Display subtotals at the top of the group. You can also check that if you want to see the subtotals if you want to see the subtotals up on top of the group.



Going back to the PivotTable Field Layout, you have another option that says Insert blank line after each item. That will insert a blank line.




These are just some personal preferences. None of this stuff is ground-shaking. You can select Insert page break after each item if you want a page break to occur when printing. Notice on the inner item, the layout box is not available – it’s only available for the outer option. Click on the Year and drag it and drop it in the page field up top.




There’s also some options in here if you double-click on the field header. You can hide items by clicking on them, you can check the Show items with no data so let’s say there’s no 2002 data, this option will hide them.





Lesson 4: Formatting PivotTables and Copying PivotTable Data

In this lesson, we’re going to learn how to format our PivotTables using the AutoFormat Tool. We’re also going to learn how to copy our PivotTable data to another sheet. So here I am back in my Store PivotTable.




You can right click on any of these guys and select Format Cells.









You can change the number, currency, and all that stuff – the alignment, the font...




Or you can come up top and bold them and do all the things you would with regular spreadsheets.




But Excel also has a neat little AutoFormat available for PivotTables. On your toolbar, you’ve got the Format Report button. Let’s click on that.




















You can scroll down this list and see a whole bunch of different kinds of PivotTables and you can pick whichever PivotTable format that you like.




Here’s one! You can see that it’s automatically formatted.




Now be careful because sometimes these slightly rearrange your PivotTable. The data should still be the same, but sometimes your layout will change. You might have to go through a couple of different ones before you find one that you like. And you might even have to do a little tweaking. You can of course, apply your own formats. Maybe you want to highlight something and change the color. Or maybe you want to bold a number.

Be careful though, because when you apply a new format, you might lose all the different formats that you put in already.

But that’s what the AutoFormat does. It allows you to quickly format the PivotTable to make it look pretty if you want to drop a report or something.

Now that brings up a good question. What if you want to take some of this information and you want to put it into a new workbook for example. Let’s create a blank new sheet. Right-click, select Insert, and select Worksheet.






Let’s say I want to take the StorePT PivotTable and put some of the data over here into the new worksheet. Let’s copy this stuff...




Flip over to the new worksheet and paste it. Notice you still can’t type any information in there. It copied the PivotTable itself. So let’s Undo that. How do I get the stuff on the StorePT PivotTable over to the new spreadsheet?





I’m going to use Paste Special and select Values. I want the values that I’ve copied from that PivotTable. Hit OK to past them in.




Okay – there’s the values! Now before you do anything, notice that I lost my formatting. That’s okay because I can click Edit, Paste Special, and then Formats and hit OK.

















Look at that! It pasted the format right over those same cells.




Now I’ve got a copy of the PivotTable that I can go ahead and tweak. And I can change this now because this is just a copy of the PivotTable – it’s not the exact PivotTable itself. I change the names, number, and even colors. So what do we have to do? We have to copy the information, copy the PivotTable, go to a new sheet, Edit – Paste Special, and then paste the Values. And if you want the formatting, then go to Paste Special and paste the format.

Now you don’t have to copy entire PivotTables over too. Let’s go to a different PivotTable. Let’s go to our StorePT2 PivotTable. Let’s copy just the 2000 numbers out of here. Copy them and flip over to your new spreadsheet.




Click Edit – Paste Special – Values, and then OK. And there they are! You can copy just pieces of a PivotTable as long as you now how to do it.




That’s how you copy information. You can format it with the AutoFormat if you want, or you can format it yourself, copy it, Paste Special, and you can drop it into a blank sheet or you can drop it into Microsoft Word or whatever else you want to do with it
Lesson 5: PivotTable Options

In this lesson, we’re going to talk about PivotTable options. You can find table options by either right-clicking on the table somewhere and picking Table Options.




Or you can go to PivotTable



















That will open up this big window. Now there are a whole bunch of different options in here. First, we’ve got the PivotTable name. By and large, you should never really need to change the name of a PivotTable however, if you’re going to be making other Microsoft Excel objects that need to reference this PivotTable, you may need to change it.

We have Grand totals for columns and Grand totals for rows. You can turn these off and click OK.




And you can see how the Grand Totals are gone:




Let’s go back in there and turn them back on. Pretty basic. AutoFormat table if you want to format the table. Remember that with all these options, you have the mini-help available






Click on it and then click on one of these options and it will tell you what it is.




Subtotal hidden page items asks if you want those included in your subtotals. Merge labels merges the cells of the outer perimeter column and row headers. For example, here we’ve got the multiple row headers.




When merged, you can see the difference.





Preserve formatting keeps your formatting when you update a PivotTable. If you update a PivotTable with an exclamation point, sometimes the formatting that you specify will get erased and overwritten. Repeat item labels on each printed page – if your PivotTables are going to separate into multiple pages when you print them, this will repeat the item labels on each printed page. Page Layout has to do with the page fields across the top. Let me show you.


Let’s remove Store and Quarter – put Store back, and put Year, Quarter, Manager, across the top. Notice that this is Down and Over.




By going to Table Options and changing that to Over and Down, and changing Fields per row to 2 fields per row, we get this Over and Down format:




For error values – if the table values show an error in one to the cells, you can specify a value. For empty cells – you can put an “x” in there and it will show an “x” where ever there’s an empty cell.





Down in Data Options, you have Save data with table layout. Excel actually saves the data with the table itself. You can turn that off and the PivotTable won’t save data inside it’s own structure and then the PivotTable is forced to refresh itself. Enable drill to details – that’s the thing that when you double-click on it, and it creates a separate spreadsheet with all the records that contribute to that piece of data. Refresh on open forces the PivotTable to refresh itself everytime you open it. The rest of these options have to do with if you’re pulling your data in from an exteranal database.

Refresh every 0 minutes forces the PivotTable to go out to the external database and refresh the information. Save password – if you need a password to access that database. Background query reduces memory. We’ll talk about some of htese options when we talk about external databases.

But these are basically the options that you’re going to need to use. The best way to figure out how all this stuff works is just to experiment with it. Play with the options and see if they do what you want them to do.

Lesson 6: Calculated Fields

In this lesson, we’re going to learn how to create calculated fields in our PivotTables. Let’s begin by talking about calculated fields. Sometimes you want to throw in a calculated field which essentially is a value that you’re going to construct in the PivotTable itself. Now here’s my sales log. Once I put this information into a PivotTable, I’d like to be able to calculate the commissions for my sales reps. So let’s throw this into a PivotTable real quick and see how quickly I can calculate commissions.




Let’s put Date into the row fields and I want to bring the sales rep’s names over to the column.







And let’s group the dates by month as we’ve done before. Okay. I want to be able to put the commissions they’re paide in the data field. Let’s assume that all of our sales reps have paid 15% commissions. We don’t have commassions in our PivotTable Field List – all we have is the amount of sales. So we need to add a calculated field to the PivotTable. How do we do that?




Well we’re going to click on PivotTable – Fomulas – and then Calculated Field.


















A calculated field is a field based on other fields, like Commissions calulated from Sales for example. What do we want to call this field? Call it “Commission.”




What do we want the Forumla to be equal to? We want it to be 15% of the Sale amount. So jut click on Amount and the Insert Field button. That will put it right in the Formula text box. But we need to multiply that by 0.15. Click on the Add and then the OK button.




Now we have a new field called Commission and it brought it into the PivotTable for us as Sum of Commissin. So basically, it’s calculating the commissinos for each of our sales reps based on the Amount.




We could do something very similar with our sales log. Let’s say we want to be able to calculate the sales tax we have to pay based on our sales. Let’s see how we can do that with just Sales. So again, let’s create a PivotTable. And let’s bring in Year, and Quarter, and Store.



But we need to calculate the sales tax. So select PivotTable, Formulas, and Calculated Field. Rename the field to SalesTax and change the Forumla to equal Sales times 0.0825. Add this to your PivotTable.




And there you go! Now you can see the sales tax due for each store, year, and quarter.




So that’s how we can enter a calculated field into our PivotTables.

Lesson 8: Base Fields & Items

In this lesson, we’re going to learn how to see our data in a PivotTable using Base Fields and Base Items, which will allow you to essentially compare the data to previous items in a list. Using this technique, you can show all kinds of interesting information. You can see data based on previous entries – you can see running totals, you can see percent differences, and all kinds of neat stuff.

Let’s go back to our Store list and create one more PivotTable. And let’s bring Year to the row field, Store to the column field, and let’s bring in Sales. Right click on Sum of Sales and select Field Settings.




You can change this to Count or Average or whatever. But over on the right is an Options button that we haven’t talked much about yet.












Let’s say that instead of just seeing the sales in terms of numbers, I want to see how all of the sales relate to a previous year. So back in the options, we want to change the entry in Show data as to Difference From, leave Year selected in Base field and select (previous) in Base item. That will give us the difference from a previous year. Click OK.




And look at that. (The first year is blank because there is no previous year.)




Let’s try another one. Instead of basing it on the previous year, why don’t we base it on 2002. This will show the difference from 2000.











See how that works? Let’s try it again. Let’s say we want to compare all the stores to the Buffalo store. This time, you’ll select in Options, Differnce from Store and Buffalo.




Now Chicago and Toronto are compared to Buffalo.

.

You can do any of the axis that you want on here. You could represent these as percentage if you want to. For example, instead of Difference From, you could choose Percent of, Year, and (previous) to see the sales of the percentage the previous year’s sales. That’s a common thing that sales managers or executives want to see.




Let’s try the Running Total in by Year option. Well here are the results using that option.








Here’s what you’d get if you use the Running Total in by Store option.




Let’s try another one. Let’s try Percent of Row. This now shows that Toronto made up 38 percent of all sales for 2000. See how that works?




Let’s go the other way and try Percent of column. Now you can see by looking down the number of sales for each year.




These are just different ways of viewing information. If you’ve got 10,000 records, this can really show you the data that’s buried or the information that’s buried in your data! Let’s try Percent of Total. This shows you all of the information the across and down at the same time.




So that’s how the Base Fields and Base Items let you look at your data in a different way.


Lesson 9: (Bonus Lesson) Tracking Employee Costs

This is a special bonus lesson that I’m throwing in that’s going to tie in some topics we’ve learned in both our Data Analysis series and our previous lessons with V-LookUp. This is going to show you how to track employee costs.

First, I’ve created an Employee Pay Rates sheet with a list of my employees, their regular pay, and their overtime pay.




And their overtime pay is basically a function of their regular pay - which is time and a half.


















Next, I need to log their weekly hours. So we can type in the week beginning, the Employee name, their hours worked, and figure out how many were regular or overtime. And then we start to calculate how much they get paid.




So let’s begin by bringing in the first week of the year with 1/02/05. Now we can type in the Employee names but if we do, we have to make sure that we type them in exactly the same as they are on our PayRates sheet otherwise the VLookup function won’t find them. So let’s copy these...














Flip over to the Weekly Hours sheet and paste them in for the first week. (Let’s assume that they all worked for the first week.)




Let’s put some hours worked in here...




... and copy the dates down too (left-aligned).










Now how do we figure out regular time and overtime? Well regular is essentially the first 40 hours. Overtime would be anything over 40 hours. So I need an IF statement here. I need to say IF the hours worked is greater than 40, then 40. Otherwise, if it’s not true, if the hours worked are less than 40, then put the hours worked there. That case would be C4.




Let’s Autofill that down.




Overtime is actually pretty simple to calculate, right? It’s just the difference between the two: Hours worked minus regular hours.




You can Autofill that down too.




Pretty straight forwared so far. Let’s put in another week’s worth of data here. Coppy and all of this below:





















I’ll just change the date from 1/2/05 to 1/9/05 and copy and paste it down. And I’ll slightly change the hours worked.




I did the same thing with a couple more weeks of data just so our PivotTable has some stuff to do.




Now, I can figure out how to pay this someone. But before I do, I’ve got to look up their base rate and their overtime rate. How am I going to get these? Well I’ve got to look them up with Vlookup. This Vlookup will be based on an employee’s nae, so first I’ve got to create a PayRatesTable.











Highlight the employee names on the PayRates spreadsheet and change the A4 range to PayRatesTable.





Returning to the Weekly Rates sheet, I’ll enter for the Base, “=Vlookup(B4,PayRatesTable,2)”.




Now I can drag down the entire Base column to get the following. But I have a problem. The Vlookup function tries to look up in a range of the pay rates, and it tries to find values that are less than or equal to the value that it’s looking up. What we have to do here is use an optional perameter for the Vlookup fuction to tell us that we want it to find exact values – we’re not looking for a range of values.




So we have to change the function value to =VLOOKUP(B4,PayRatesTable,2,FALSE).




Now you can click and drag it down and it works perfectly.




Why? Again, we need to look up these specific names in that other table. We’re not looking for a range of values. The OT-Rate works the same. Let’s copy the function for the Base to the OT-Rate. But this tiem, we’re looking for column 3 (where the overtime pay rate is).








Now just drag it down to fill in the values for the rest of the employees.




Now that we know this, we’ve got the regular hours and the overtime hourse. We can do some multliplication and figure out how much to actually pay. Let’s make two more coloumns: RegPay and OTPay.




Regular pay equals regular hours times base pay.













Overtime pay equals overtime hours times overtime pay.




Again, grab these and drag them down.




Want to calculate total pay? Well that’s easy now isn’t it? Create a Total Pay column. Total pay equals Regular pay plus Overtime pay.















You can Autofill that one too.





Okay, where’s the PivotTable coming in you’re asking! Well now it’s time! So let’s click somewhere in this big monster table and create a PivotTable. Let’s say you want to see where all the overtime pay is going. Let’s bring over Employee to the row field, bring in your Overtime Pay into your data, change Count of OTPay to Sum of OTPay. And of course you can bring Week in to page fields and break it down by week.




And of course, now that we can see that, we can double-click on that drill down and look at that!







The drill down gives us a sheet that has all the weeks and exactly how much overtime pay received.

Let’s say you want to see who’s not working all the hours they shoud be. Let’s take Ovrtiem Pay off of the PivotTable and drop Hours Worked on there. Again, change “Count” to “Sum” and look who has only 39 hours for an entire month.




You can do all kinds of things. You can see hours worked, you can bring in Regular Hours and the Overtime.

I hope this example has refreshed your memory showed you how to use things like the IF function, the Vlookup, and how all this stuff can be used to build big weekly hour sheet that we can feed into a PivotTable and get any kind of information out of it that we want.

Lesson 9. Review

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

· We learned about grouping our data in our PivotTable
· We learned about creating subtotals and subitems.
· We learned how to format our PivotTables using the AutoFormat wizard
· We went over all the advanced options and the table options menu.
· We learned about calculated fields and calculated items
· We learned about base fields and base items
· We learned how to do running totals and percent differences
· We learned about the employee costs sheet, how to use the IF function, the Vlookup function
· We learned how to generate a nice big employee costs page that we can drop into our PivotTable and get all kinds of information out of our employee data

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 on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
 
Subscribe
Subscribe to Excel 224 Handbook
Get notifications when this page is updated
 
 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
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
PCResale.NET
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
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 5:26:24 AM. PLT: 0s
Keywords: excel handbook  PermaLink  Excel 224 Handbook