Intermediate Excel
Purpose of Course showclose
This class is intended for students who have a basic understanding of spreadsheets and are now ready to delve deeper into formatting, formulas and functions, multipage spreadsheets, charting data, creating tables that have database features, and be introduced to pivot tables. This class was designed to be an active, handson class. You will be creating Excel® spreadsheets and have files to open and follow along as you progress through the units.
This class is not intended for a specific occupation or activity, but when you are finished with this class, you will be able to use Excel® in a variety of circumstances to format and manipulate numerical data. Although the resources in this course use Microsoft Excel® 2010, it should be noted that all of the skills and tasks that you will be asked to complete can be done in any version of Excel®. If you stay flexible enough in your thinking and search out the commands and icons on whatever software you are using, you will succeed.
Course Information showclose
Course Designer: Professor Chris Wilkins
Primary Resources: This course is comprised of a range of different free and online materials. However, the course makes primary use of the following materials:
 How to Use Microsoft Excel®
 Goodwill Community Foundation’s Microsoft Excel® 2010
In order to pass this course, you will need to earn a 70% or higher on the Final Exam. Your score on the exam will be tabulated as soon as you complete it. If you do not pass the exam, you may take it again.
Time Commitment: This course should take approximately 30 hours to complete. The 10 units are designed to take about 3 hours each.
Tips/Suggestions: Open your own version of Excel® as you go along and do the work that you are seeing. Doing is the fastest way to learn any software program. Think of this class as a handson class and dig into what you are learning and start exploring with whatever version of Excel® you have access to. Each unit includes a time advisory that lists the amount of time you should expect to spend on each subunit. These should help you plan your time accordingly.
If you are working with an older version of Excel® and want to see a more recent Excel® environment, Microsoft offers a series of eight short videos (13 minutes each) that take you through the changes and show Excel® 2010. You can access them here. Since Excel® 2010 is the version that you will see being used in this class, you may want to familiarize yourself with it.
Learning Outcomes showclose
 use formatting features to format text, cells, and numbers, including dates;
 explain the various uses for conditional formatting;
 write formulas for sum, average, maximum, minimum, and count;
 use common functions such as IF, COUNT, and PMT to formulate calculations;
 describe the categories of functions in the Insert Function dialog box;
 explain the difference between a pie chart and a column chart, and understand when to use each appropriately;
 describe the function of Sparklines;
 rename and format worksheet tabs;
 describe how to use Headers and Footers, including how to format them in the Page Setup dialog box;
 show formulas in the Formulas view and print them on one page;
 explain how to create, name, sort, and filter a Table; and
 create and format a PivotTable and PivotChart.
Course Requirements showclose
√ have access to a computer;
√ have continuous broadband Internet access;
√ have the ability/permission to install plugins or software (e.g., Adobe Reader or Flash);
√ have the ability to download and save files and documents to a computer;
√ have the ability to open Microsoft files and documents (.doc, .ppt, .xls, etc.);
√ have competency in the English language; and
√ have read the Saylor Student Handbook.
It is assumed you have completed the PRDV004 course or have read through it and have a basic knowledge of Excel®, including the ability to:
√ define the basic principles of spreadsheets;
√ differentiate between workbooks, sheets, cells, rows, and columns;
√ identify tabs and toolbars in the Microsoft Excel® window;
√ save and print a spreadsheet; and
√ use the SUM function to aid in working with data.
Unit Outline show close

Unit 1: Revisiting the Fundamentals of Spreadsheets
This unit will review the basics of Spreadsheets. We will review commands on the Home and File Ribbons. We will look at some basic formatting techniques that will enhance the appearance of your worksheets including the formatting of text and numbers, using cell borders, and preparing a worksheet for printing. We will also review the most commonly used function of all, the SUM function, and we will be adding a simple chart to a worksheet. Each of these units will build upon the next so make sure that you have mastered this unit before moving on.
Unit 1 Time Advisory show close
Unit 1 Learning Outcomes show close

1.1 Entering, Editing, and Formatting Data
 Reading: How to Use Microsoft Excel®: “Section 1.2”
Link: How to Use Microsoft Excel®: “Section 1.2” (PDF)
Instructions: Go to Chapter 1: Fundamental Skills. We will be skipping over Section 1.1: An Overview of Microsoft Excel® as these are skills you should already have before joining this class. We will start with Section 1.2: Entering, Editing, and Managing Data on page 26. If you feel you need the extra prep work, go back through Section 1.1: An Overview of Microsoft Excel® before starting Section 1.2.
You will need to have your version of Excel® open and ready to follow along to create the document that will be used throughout Unit 1. Please save it to your desktop or flashdrive as Excel® Text Chapter 1as the chapter explains. As you work through the reading along with Excel®, if something is unclear in the directions/reading, refer to your Excel® spreadsheet and experiment to see if you get the results you are looking for by comparing them to the pictures that accompany the text.
Reading this section should take approximately 1 hour.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 1.2”

1.2 Formatting and Data Analysis
 Reading: How to Use Microsoft Excel®: “Section 1.3”
Link: How to Use Microsoft Excel®: “Section 1.3” (PDF)
Instructions: Read through Section 1.3 beginning on page 54, and follow along to create the document you will seein the reading. The starting file will be the same file namedExcel® Text Chapter 1that you created in Subunit 1.1 above.
Reading this section should take approximately 1 hour.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 1.3”

1.3 Printing
 Reading: How to Use Microsoft Excel®: “Section 1.4”
Link: How to Use Microsoft Excel®: “Section 1.4” (PDF)
Instructions: Read Section 1.4: Printing, which starts on page 86. Follow along using the Excel® file you created above namedExcel® Text Chapter 1.
Reading this section should take approximately 30 minutes.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee
 Reading: How to Use Microsoft Excel®: “Section 1.4”

1.4 Exercises for Unit 1
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 1”
Link: The Saylor Foundation’s “Practice Exercise for Unit 1” (PDF)
Instructions: Open your Excel® program and follow the directions on the accompanying PDF document.
Completing this activity should take approximately 30 minutes.
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 1”

Unit 2: Formatting Spreadsheets
This unit will discuss formatting in more depth for cells, text, numbers, including dates, and conditional formatting as well as the worksheet tabs themselves. Formatting is often needed on spreadsheets because it assists the reader to conceptualize numbers. It can be used to highlight certain information that needs to be emphasized or create a coherent organization for large pieces of data. This unit will deal with many of the features and commands on the Home Ribbon.
Unit 2 Time Advisory show close
Conditional Formatting will also be introduced. Conditional Formatting is used to format text and numbers based on certain criteria or conditions. This feature was greatly improved in the newer versions of Excel®. If you have an older version or some other spreadsheet software, this would be a good topic to investigate in the Help files of your program.
Unit 2 Learning Outcomes show close

2.1 Using Help
Note: The first thing we will look at in this unit is Excel®’s Help feature. Microsoft puts a lot of effort into creating useful Help files. No matter what version of Excel® you are using, the Help directory that comes with the program is quite large. With this much information available, it is useful to become familiar with this tool.
 Web Media: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic B: Finding and Using Help”
Link: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic B: Finding and Using Help” (YouTube)
Instructions: Watch the short fourminute video and open your version of Excel® to explore your Help feature. Investigate the tools on the toolbar. Use the keyword CHARTS as was done in the video and see what shows up in your Help window. Use the back arrow or Home icon and then take a few minutes and investigate a topic of your choice. Click the various links to see how you can better use the Help feature.
Completing this assignment should take approximately 10 minutes.
Terms of Use: This resource is licensed under a Creative Commons Attribution 3.0 Unported License. It is attributed to TheAppTrainer, and the original version can be found here.
 Web Media: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic B: Finding and Using Help”

2.1.1 Advanced Formatting Cells and Numbers
Note: in this subunit you will be working with different aspects of Formatting using both the tools on the Home ribbon in the Font and Paragraph groupings and the Format Cells Dialog box. Both can be used to format but you may need the dialog box to do more advanced formatting. It is helpful to become familiar with this dialog box.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Formatting Cells”
Link: GCFLearnFree.org’s “Excel® 2010: Formatting Cells” (HTML)
Instructions: Click on the link above. Read through the six pages and watch the video on page 2. Note that this resource shows formatting features on the ribbon. If you have Excel® 2010, open the Excel® practice spreadsheet and follow the directions on page 6 to practice what was demonstrated in the video. This video shows many different ways to format text using the tools on the Home ribbon.
Completing this assignment should take approximately 20 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.  Reading: The Saylor Foundation’s “Format Cells Dialog Box”
Link: The Saylor Foundation’s “Format Cells Dialog Box” (PDF)
Instructions: Read through the document. Open your version of Excel® and compare it to what you just read about. This dialog box is found in every version of Excel® with only small variations as new tools are added. Try using some of the features you think might be useful. Take note of which features you can find on the Home ribbon and which features you will have to use through the dialog box.
Completing this assignment should take approximately 20 minutes.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Formatting Cells”

2.1.2 Create Borders and Shading
 Web Media: The Saylor Foundation’s “Applying Borders and Shading to Cells”
Link: The Saylor Foundation’s “Applying Borders and Shading to Cells” (YouTube)
Instructions: Please click on the link above and watch the short video. After you have finished, open your version of Excel® and try adding borders and shading to a spreadsheet.
Completing this assignment should take approximately 10 minutes.
 Web Media: The Saylor Foundation’s “Applying Borders and Shading to Cells”
 2.2 Working with Worksheets

2.2.1 Navigating Worksheets
This subsection will highlight various ways to navigate through large worksheets and select data in Excel®. One spreadsheet can have thousands of columns and/or rows. Learning how to quickly navigate through large spreadsheets is a real timesaver.
 Web Media: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic C: Navigation and Selection Techniques”
Link: The AppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic C: Navigation and Selection Techniques” (YouTube)
Instructions: Watch the 15minute video. Pay particular attention to the way the speaker selects noncontiguous ranges of text and numbers using the Ctrl key on the keyboard. This is an important technique to master. Then open up your version of Excel® and try these techniques on your own.
Completing this assignment should take approximately 30 minutes.
Terms of Use: This resource is licensed under a Creative Commons Attribution 3.0 Unported License. It is attributed to TheAppTrainer, and the original version can be found here.
 Web Media: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic C: Navigation and Selection Techniques”

2.2.2 Formatting Worksheet Tabs
A workbook can have over a thousand worksheets so learning to name and format worksheet tabs will help you quickly navigate through a large workbook visually. This makes dealing with the large workbook with many worksheets easier to manage. The videos within this link also discuss moving, grouping and ungrouping, and rearranging worksheets within a workbook as well as Freeze Panes for working within a large worksheet. There is a lot of information here so take your time and practice these skills in your own version of Excel®.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Worksheet Basics”
Link: GCFLearnFree.org’s “Excel® 2010: Worksheet Basics” (HTML)
Instructions: Read through the six pages and watch the videos on page 2, 4, and 5. If you have Excel® 2010, open the Excel® practice spreadsheet and follow the directions on page 6 to practice what was demonstrated in the videos.
Completing this assignment should take approximately 30 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Worksheet Basics”

2.3 Conditional Formatting: Highlight Rules and Databars
Conditional formatting is an easy way to format large pieces of data or numbers to make important information stand out. This feature changed quite a bit with the newer versions of Excel®: Excel® 2007 and 2010. If you are using an older version of Excel®, you will not be able to use most of the features that this subunit talks about, although you can still conditionally format. You should explore the Help file in your version of Excel® to find out how to conditionally format in your version. It can be done, and you can probably find YouTube videos as well. This subunit will show the examples in the newer versions which have greatly expanded the availability of different types of conditional formatting tools.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Using Conditional Formatting”
Link: GCFLearnFree.org’s “Excel® 2010: Using Conditional Formatting” (HTML)
Instructions: Go through the five pages, watching the video on page 2. When you have finished, there is a practice document at the end of this presentation for those with a newer version of Excel®. Open spreadsheet and follow the instructions to practice conditional formatting.
Completing this assignment should take approximately 25 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.  Web Media: Sophia.org’s “Conditional Formatting in Excel®”
Link: Sophia.org’s “Conditional Formatting in Excel®” (HTML)
Instructions: Watch the video on conditional Formatting. After the video has concluded and if you have a newer version of Excel®, click on the link to download the Excel® document and practice the techniques you learned. Try each type of conditional formatting until you are comfortable with it use and how to adjust and adapt the formatting to fit your needs.
Completing this assignment should take approximately 35 minutes.
Terms of Use: The resource used in the link above is released under a Creative Commons AttributionNonCommercial 3.0 Unported License. It is attributed to Pete Dulany, and the original version can be found here.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Using Conditional Formatting”

Unit 3: Mathematical Computations
This is where the fun begins. Excel®’s usefulness shines in the way it handles formulas and functions. Formulas are mathematical equations that you write yourself. Functions are the prebuilt formulas that Excel® has available for use, such as finding the average or standard deviation for a set of numbers. A new vocabulary word you need to watch for is RANGE. A range is a set of numbers that are used in a formula or function. The range is usually, but not always, contiguous numbers, which are next to each other in the spreadsheet. Also keep in mind that most often cell references are used, not actual numbers. For example, if you were adding two numbers, say 12 and 15, and the first number is in cell A3 and the second in cell B3, then the formulas would look like: =A3+B3. It would not look like: =12+15. Cell references are used so that later if you wanted to change the number in A3 all of the associated formulas and functions that use that number would automatically update, even if they are on a different worksheet or in a different workbook!
Unit 3 Time Advisory show close
Each of the subunits in this unit builds on the next. Be advised that you should not skip units as you may write a formula in Subunit 3.1 that you will need to revise or add to in Subunit 3.3.
There is a lot of information in this unit so don’t rush through it. It may be a bit confusing at first but keep in mind that this is the third unit of this Excel® class but it uses the second chapter of the textbook. So you may be working in classroom Subunit 3.2 but it is textbook Section 2.2.
Unit 3 Learning Outcomes show close

3.1 Formulas
 Reading: How to Use Microsoft Excel®: “Section 2.1”
Link: How to Use Microsoft Excel®: “Section 2.1” (PDF)
Instructions: Go to Chapter 2: Mathematical Computations and find Section 2.1: Formulas, which starts on page 104. Then, open the link in the next Resource Box for the followalong Excel® file to work on as you read the chapter.
Completing these assignments should take approximately 30 minutes.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.  Activity: The Saylor Foundation’s “Excel® Text Chapter 2”
Link: The Saylor Foundation’s “Excel® Text Chapter 2” (XLS)
Instructions: This Excel® file is in the earlier version of Excel® with the file extension .xls. If you have the newer version of Excel®, open it up and resave it in the current version of Excel® which has the file extension of .xlsx. There are features that the newer version has that you may want to play with so you don’t want to leave it in Compatibility Modeview. If you have a newer version and leave it as an .xls, you will see the text [Compatibility Mode] on the Title Bar. This will not affect the document at all. But remember, the book will be showing the newer version in the pictures that accompany the text. As this chapter deals with formulas and functions, all Excel® versions write formulas and functions the same. This Excel® workbook has several worksheets. You will complete all of the worksheets while working through Unit 3. Save the file often so that you don’t lose any of your work.
Completing this activity should take approximately 1 hour.
 Reading: How to Use Microsoft Excel®: “Section 2.1”

3.2 Statistical Functions
 Reading: How to Use Microsoft Excel®: “Section 2.2”
Link: How to Use Microsoft Excel®: “Section 2.2” (PDF)
Instructions: Read Chapter 2, Section 2.2: Statistical Functions that starts on page 120. You will continue to use the file you started above (Excel® Text Chapter 2). The functions in this chapter are the basic ones that you should master in Excel®. Once you are finished with the chapter, practice using these functions in a spreadsheet of your own making.
Completing this assignment should take approximately 1 hour.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 2.2”

3.3 Functions for Personal Finance
In this section, you will finish Chapter 2 of the text. It introduces functions for personal finance. You will learn the PMT and the FV function. The PMT, or Payment function, is a useful function and can be used with all personal loans. You can work out payment options using the variables of interest rate and time duration of the loan so you can make better decisions when acquiring a loan. The one item that you need to remember is that loans are usually based on an annual interest rate. Since we usually make payments on a monthly basis, the annual or yearly rate must be divided by 12. So if the interest rate is 6%, that rate should be 6%/12 in the payment function because you only pay a portion of the 6% each month (which would be 0.005% a month). In addition when you are deciding on the length of the loan, if it is a 10year loan, when talking about the payment, you would need to multiple 10 years times 12 months since you pay monthly. Or you could just use 120 months as the length (10*12). Either way, the length, or Nper in the function box, needs to reflect monthly payments. No one wants to make a car payment once a year – ouch! These are often forgotten by someone just learning the PMT function.
 Reading: How to Use Microsoft Excel®: “Section 2.3”
Link: How to Use Microsoft Excel®: “Section 2.3” (PDF)
Instructions: Read Section 2.3: Functions for Personal Finance, beginning on page 144. This subunit will finish Chapter 2 of the text. You will continue to use the file you used in Subunit 3.2 above (Excel® Text Chapter 2).
Completing this assignment should take approximately 1 hour and 10 minutes.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 2.3”

3.4 Exercises for Unit 3
 Web Media: GCFLearnFree.org’s “Excel® 2010: Creating Complex Formulas”
Link: GCFLearnFree.org’s “Excel® 2010: Creating Complex Formulas” (HTML)
Instructions: Go through the 6 pages, watching the video on page 2. Pay particular attention to the explanation of Order of Operations. This is what Excel® uses to calculate formulas. When you have finished, there is a practice document at the end of this presentation. If you have a newer version of Excel®, open the spreadsheet and follow the instructions to practice creating formulas.
Completing this assignment should take approximately 20 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Creating Complex Formulas”

Unit 4: Logical and Lookup Functions
In this unit we will be looking at Chapter 3 of the textbook, which uses more advanced functions. As the chapter explains these functions, go beyond the basic functions of Excel®. The IF function can be used in many different instances that use numbers or text as the answer. Remember that a comma separates the different parts of each function so you should not type commas in large numbers. You should format the numbers after the calculations have been made. We humans need to separate numbers for easier reading, not the software program. You will be nesting one function inside another. This will take some logical thinking. Although you may not need to be a mathematician to master Excel®, you do have to be able to logically set up formulas and functions so the math can be done by the software. Linear or logical thinking can be very helpful in this process. Again, it is recommended that you not skip subunits in the Unit as each subunit builds the spreadsheet for the next subunit.
Unit 4 Time Advisory show close
Unit 4 Learning Outcomes show close

4.1 Logical Functions
The text explains that logical functions are used to assess the content within cells and produce custom outputs, either text or numbers. When an answer is text, you must put quotation marks both before and after the word. This signifies to the software that the answer is a word, not a mathematical computation. There is a lot of information in this subunit so go slowly and reread any section that you need to. If you are doing the work along with the text, and if anything does not match the answers that the text gives, delete the formula and start again. A misplaced comma or erroneous letter or number will make a formula wrong. I have worked through each problem and if you do them as the book instructs you should get a matching answer. Really pay attention to what you are typing or clicking and have fun!
 Reading: How to Use Microsoft Excel®: “Section 3.1”
Link: How to Use Microsoft Excel®: “Section 3.1” (PDF)
Instructions: Read Chapter 3: Logical and Lookup Functions through page 192 and then proceed to Section 3.1: Logical Functions, which starts on page 193. You will find the workalong Excel® file for this unit in the next resource box: Excel® Text Chapter 3.
Completing this assignment should take approximately 1.5 hours.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.  Activity: The Saylor Foundation’s “Excel® Text Chapter 3”
Link: The Saylor Foundation’s “Excel® Text Chapter 3”
Instructions: This Excel® file is in the earlier version of Excel®, with the file extension .xls. If you have a newer version of Excel®, open it up and resave it in the current version of Excel® which has the file extension of .xlsx. If you have a newer version and leave it as an .xls you will see the text [Compatibility Mode] on the Title Bar. This will not affect the document at all. Again, the book will be showing the newer version in the pictures that accompany the text. As this chapter deals with formulas and functions, all Excel® programs write formulas in the same way and the Function dialog box is the same, so it doesn’t matter what version of Excel® you have for this unit. You will be completing the Investment Detail worksheet in this section.
Completing this activity should take approximately 1 hour.
 Reading: How to Use Microsoft Excel®: “Section 3.1”

4.2 Statistical IF Functions
This subunit will introduce the COUNTIF and COUNTIFS, the AVERAGEIF and AVERAGEIFS, and the SUMIF and SUMIFS functions. These functions can be confusing because they have nested criteria but, again, go slowly and work through the logic. You will be completing the Portfolio Summary worksheet from the spreadsheet you started in Subunit 4.1.
 Reading: How to Use Microsoft Excel®: “Section 3.2”
Link: How to Use Microsoft Excel®: “Section 3.2” (PDF)
Instructions: Read Chapter 3, Section 3.2: Statistical IF Functions, which starts on page 231. You will continue to use the same spreadsheet that you started above in Unit 4.1 of this class (Excel® Text Chapter 3).
Completing this assignment should take approximately 1.5.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 3.2”

Unit 5: Lookup Functions and Review of Functions
This unit will introduce the VLOOKUP and HLOOKUP functions as well as how to create a web query that imports stock prices to your worksheet from a website.
Unit 5 Time Advisory show close
Unit 5 Learning Outcomes show close

5.1 Lookup Functions
 Reading: How to Use Microsoft Excel®: “Section 3.3”
Link: How to Use Microsoft Excel®: “Section 3.3” (PDF)
Instructions: Read Chapter 3, Section 3.3: Lookup Functions, which starts on page 265. You will continue to use the followalong file from Unit 4 above (Excel® Text Chapter 3) as you finish this chapter.
Completing this assignment should take approximately 1 hour.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 3.3”

5.2 Review of PMT using Goal Seek
 Web Media: GCFLearnFree.org’s “Excel® 2010: Using WhatIf Analysis”
Link: GCFLearnFree.org’s “Excel® 2010: Using WhatIf Analysis” (HTML)
Instructions: Go through the 5 pages, watching the video on page 2. This review uses the PMT function that you learned in subunit 3.3. The last page also introduces Scenarios and Data Tables. The class will not dig any deeper into these tools but you can use Help to learn more if these are features you would find useful. If you have Excel® 2010, when you have finished, there is a practice document at the end of this presentation that you can open and follow the instructions to practice the skills you learned. If you have an older version of Excel®, open your version and create the spreadsheet you see on page 3. Find Goal Seek in your program to practice what you learned here.
Completing this assignment should take approximately 30 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Using WhatIf Analysis”

5.3 Printing Formulas and Functions
So now you know a lot about writing formulas in Excel®. If you want to share your formulas with someone in a printed format, you will need to be able to see them in the spreadsheet for them to print.
 Reading: The Saylor Foundation’s “Printing Formulas”
Link: The Saylor Foundation’s “Printing Formulas” (PDF)
Instructions: Read the PDF about printing formulas. Then, open your own spreadsheet program and turn on the Formulas view and try printing it to one page.
Completing this assignment should take approximately 15 minutes.
 Reading: The Saylor Foundation’s “Printing Formulas”

5.4 Exercises for Unit 5
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 5”
Link: The Saylor Foundation’s “Practice Exercise for Unit 5” (PDF)
Instructions: Open the accompanying PDF document and follow the directions to complete the Practice for the skills you just learned. This file is quite comprehensive and it reviews many of the functions you learned in Chapters 35. Think of it as a test of what you have learned so far.
Completing this assignment should take approximately 1hour and 15.
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 5”

Unit 6: Presenting Data with Charts
This unit will introduce charts, which are a very important feature in Excel®. Charts help people to visual the data. Sometimes too much information or lists of numbers cannot be compared or processed by our brains efficiently. Charts help us humans to organize and visualize information so we can grasp the concepts quicker. There are many different types of charts. Which chart type to use will depend on the information you are charting. For example, if you want to compare sales totals from each branch office of a company, and that company had five or six branches, then a pie chart would work perfectly. If the company had 15 or 20 branches, that would be too many pie pieces, so a column chart could be used. The reading will give you some good examples of this concept. You will need to have the newer versions of Excel® to use the file that accompanies this chapter. Charting and Chart Formatting changed drastically from Excel® 2007 onward. If you have an older version, you can still chart but you will need to use Help to learn about the features in your version of Excel®.
Unit 6 Time Advisory show close
Unit 6 Learning Outcomes show close

6.1 Choosing a Chart Type
 Reading: How to Use Microsoft Excel®: “Section 4.1”
Link: How to Use Microsoft Excel®: “Section 4.1” (PDF)
Instructions: Go to Chapter 4, which starts on page 308. Read through the first page then proceed to Section 4.1: Choosing a Chart Type, which starts on page 309. If you have a recent version of Excel® (2007 or newer), you can click on the link in the resource box below to open up the starting document that goes with this chapter, Excel® Text Chapter 4. You can use this file as you work through the chapter.
Completing these assignments should take approximately 1 hour and 30 minutes.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.  Activity: The Saylor Foundation’s“Excel® Text Chapter 4”
Link: The Saylor Foundation’s “Excel® Text Chapter 4”
Instructions: This file is in the newest version of Excel® and uses the file extension .xlsx. You will need to have Excel® 2007 or newer to open the file. The charting feature changed quite a lot with the newer versions of Excel®. The book will show many of these new features on three ribbons that go along with charting: Design, Layout, and Formatting.
Completing this activity should take approximately 1 hour.
 Reading: How to Use Microsoft Excel®: “Section 4.1”

6.2 Formatting Charts
 Reading: How to Use Microsoft Excel®: “Section 4.2”
Link: How to Use Microsoft Excel®: “Section 4.2” (PDF)
Instructions: Go to Chapter 4, Section 4.2: Formatting Charts, which starts on page 342. Continue to use the file you started in Subunit 6.1 above (Excel® Text Chapter 4). There is a lot of detail work in formatting charts. This subunit shows only a small portion of the many, many ways a chart can be formatted. I encourage you to explore as many formatting features on the three chart toolbars as you can. Use your Help feature to learn more about each feature if something gets confusing.
Completing this assignment should take approximately 1 hour and 30 minutes.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 4.2”

Unit 7: More Work with Charts
To start this unit off we will take a look at a couple of videos from the Goodwill Foundation’s Excel® 2010 series. We have used this video series in previous units of this class, but now we will look at how they build charts in Excel® 2010. Also, we will look at Sparklines – a new feature in Excel® 2010. Sparklines are very small charts that can fit into a cell, usually adjacent to the cell where the data are derived from. The Sparkline graphically represents the number, and when used in consecutive cells, can create a visual way to differentiate numerical data for easier comprehension by the reader, without a large separate chart either embedded on a page or separate from the data.
Unit 7 Time Advisory show close
Unit 7 Learning Outcomes show close

7.1 The Scatter Chart
 Reading: How to Use Microsoft Excel®: “Section 4.3”
Link: How to Use Microsoft Excel®: “Section 4.3” (PDF)
Instructions: Go to Chapter 4, Section 4.3: The Scatter Chart, which starts on page 366. Continue to use the file you started in Subunit 6.2 (Excel® Text Chapter 4). This is the last section of the textbook. I hope you have saved the text so you can reread it later if you want to.
Completing this assignment should take approximately 30 minutes.
Terms of Use: The textbook used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.
 Reading: How to Use Microsoft Excel®: “Section 4.3”

7.2 Working with Charts
 Web Media: GCFLearnFree.org’s “Excel® 2010: Working with Charts”
Link: GCFLearnFree.org’s “Excel® 2010: Working with Charts” (HTML)
Instructions: Read through the six pages and watch the video on page 2. On page 4 they show you how to change a chart type after a chart has been created. This is something that was not shown before and can be useful when you want to compare charts and how your data will look before printing or sharing with others. If you have Excel® 2010, open the Excel® practice spreadsheet and follow the directions on page 6 to practice what was demonstrated in the videos.
Completing this assignment should take approximately 30 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Working with Charts”

7.3 Sparklines
 Web Media: GCFLearnFree.org’s “Excel® 2010: Working with Sparklines”
Link: GCFLearnFree.org’s “Excel® 2010: Working with Sparklines” (HTML)
Instructions: Read through the six pages and watch the video on page 2. If you have Excel® 2010, open the Excel® practice spreadsheet and follow the directions on page 6 to practice what was demonstrated in the videos.
Completing this assignment should take approximately 30 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.  Web Media: Merlot’s “Microsoft Excel® 2010: Understanding Data: Create Sparklines”
Link: Merlot’s “Microsoft Excel® 2010: Understanding Data: Create Sparklines”
Instructions: Click on the link above which will take you to a page with several categories of topics. Scroll down to the section on Understanding Dataand click on the link: MS: Create Sparklines. In the link, the left sidebar contains links to four short videos that thoroughly explain Sparklines and how to format them. You don’t need to do the Practice page but you may want to do the Test Yourselfquestions at the end to ensure that you understood the concepts.
Completing this assignment should take approximately 45 minutes.
Terms of Use: The resource used in the link above is released under a Creative Commons AttributionNonCommercialShareAlike 3.0 Unported License. It is attributed to Velda Arnaud.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Working with Sparklines”

7.4 Combination Charts
 Reading: The Saylor Foundation’s “Combo Charts in Excel®”
Link: The Saylor Foundation’s “Combo Charts in Excel®”
Instructions: Open the PDF document to learn about combination charts by creating one.
Reading this document should take approximately 45 minutes.
 Reading: The Saylor Foundation’s “Combo Charts in Excel®”

Unit 8: Graphics in Excel®
Although graphics are not necessarily needed in Excel® to get the point across, it is sometimes good to use them to help the reader understand the information. Just as charts help to explain complex data in a visual format, graphics can point out or help to emphasize information that you want the reader to easily grasp. This unit will look at many of the features on the Insert ribbon in the Illustrations and Text groupings. The only caveat I would put on this is that you should remember that you are only using graphics to enhance the document. Too much visual busyness will distract from the important information that is on the spreadsheet. You will be learning all different types of graphics in this unit, but only use a few on an actual business document.
Unit 8 Time Advisory show close
Unit 8 Learning Outcomes show close

8.1 Add Callouts and Clipart to Charts or Spreadsheets
 Web Media: The Saylor Foundation's Callouts and Clipart
Link: The Saylor Foundation's "Callouts and Clipart" (YouTube)
Instructions: Watch the video showing how to insert shapes and clipart into a spreadsheet. Note that a toolbar pops up once a picture is added and/or selected, for further formatting and editing of the object. Once you have seen it done, open up a spreadsheet and try it on your own using some of the tools from the new toolbars that show up when you insert these objects.
Completing this assignment should take approximately 30 minutes.
 Web Media: The Saylor Foundation's Callouts and Clipart

8.1.1 Adding a Picture to a Spreadsheet
 Web Media: The AppTrainer’s “MS Office 2010. Excel® Basic: Unit 2 Topic C: Inserting Basic Pictures”
Link: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 2 Topic C: Inserting Basic Pictures” (YouTube)
Instructions: Watch the short video showing how to insert a saved picture into a spreadsheet. Note that the picture Format toolbar pops up once a picture is added and/or selected. Once you have seen it done, open a spreadsheet and try it on your own using some of the tools from the picture Format toolbar.
Completing this assignment should take approximately 15 minutes.
Terms of Use: This resource is licensed under a Creative Commons Attribution 3.0 Unported License. It is attributed to TheAppTrainer, and the original version can be found here.
 Web Media: The AppTrainer’s “MS Office 2010. Excel® Basic: Unit 2 Topic C: Inserting Basic Pictures”

8.1.2 WordArt and Symbols
 Web Media: The Saylor Foundation's "WordArt and Symbols"
Link: The Saylor Foundation's "WordArt and Symbols" (YouTube)
Instructions: Watch the short video showing how to use WordArt and Symbols. Once you have seen it done, open up a spreadsheet and try it on your own using some of the tools from the picture Format toolbar.
Completing this assignment should take approximately 30 minutes.
 Web Media: The Saylor Foundation's "WordArt and Symbols"

8.1.3 Adding a Hyperlink
If your document is going to be shared electronically, then you may want to add a hyperlink to another worksheet, workbook, or an external site on the web or within a company intranet site.
 Web Media: The Saylor Foundation's "Adding a Hyperlink"
Link: The Saylor Foundation's "Adding a Hyperlink" (YouTube)
Instructions: Watch the short video showing how to create hyperlinks, both to internal links within the Excel® workbook and external links on the web or to another file. The video will show text and graphics as the link. Once you have seen it done, open up a spreadsheet and try it on your own.
Completing this assignment should take approximately 15 minutes.
 Web Media: The Saylor Foundation's "Adding a Hyperlink"

8.2 Headers and Footers
 Web Media: The Saylor Foundation's "Headers and Footers"
Link: The Saylor Foundation's "Headers and Footers" (YouTube)
Instructions: Watch the short video showing how to use WordArt and Symbols. Once you have seen it done, practice setting up headers and footers in Excel.
Completing this assignment should take approximately 30 minutes.
 Web Media: The Saylor Foundation's "Headers and Footers"

8.3 Unit 8 Exercise
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 8”
Link: The Saylor Foundation’s “Practice Exercise for Unit 8” (PDF)
Instructions: Follow the instructions on the PDF. This exercise will be using skills you learned in Units 6–8.
Completing this activity should take approximately 1 hour.
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 8”

Unit 9: MultiPage Spreadsheets
In this unit, you will learn how to work with large workbooks, and this unit will highlight various features that will make working in Excel® more productive. We will explore formulas and functions that drill downthrough multiple worksheets for their answers. When a workbook gets large, it is important to understand how to format each worksheet tab so that information is easier to find. You learned about formatting worksheet tabs in Subunit 2.2.2. If needed, reread that section.
Unit 9 Time Advisory show close
Unit 9 Learning Outcomes show close

9.1 3D Formulas
3D formulas are used to combine information from multiple worksheets onto one worksheet for an answer. Information can be taken from as many worksheets as needed. When using a 3D formula, it is easier if your worksheets are adjacent to each other in the workbook. It makes it easier because the syntax of the formula is easier to read. Each worksheet needs to be named in the formulas if working with nonadjacent workbooks, and this can get cumbersome to write as well as read. Let’s say you are working in Sheet 7 and want to add two numbers from two other worksheets in your workbook. The 3D formula will look like this: =Sheet1!C3+Sheet4!$C$6. This will add the number in Sheet 1 cell C3 to the number in Sheet 4 cell C6 and place it in the worksheet you are in, Sheet 7. The exclamation point (!) is needed in the formula at the end of each sheet name followed by the cell reference, which can either be relative or absolute. You learned about relative (C6) and absolute ($C$6) referencing in Unit 3. If you want to add all of the cells B3 from all worksheets in the workbook and they are adjacent to each other, it would look like: =sum(Sheet1:Sheet6!$B$3). This formula tells us that it is adding each number in B3 in each of the worksheets 1 through 6.
 Web Media: The Saylor Foundation’s “3D Formulas”
Link: The Saylor Foundation’s “3D Formulas” (YouTube)
Instructions: Watch the video about 3D formulas. When you are finished, open up your own spreadsheet software and try to create some 3D formulas.
Completing this assignment should take approximately 45 minutes.
 Web Media: The Saylor Foundation’s “3D Formulas”

9.2 Formulas Across Workbooks
Now that you’ve created 3D formulas within one workbook using various worksheets, let’s look at creating a formula that uses two workbooks. This is a little bit more complicated because you have to be cognizant of where the workbooks are saved and if they are linked. If the workbooks are linked, they shouldn’t be moved once the formula is created or the formula may not work. If they are moved, both need to be moved so that the link can stay useful. When you create formulas between workbooks, you can either link them or just create the formula without linking them. But, if they are notlinked and you change or update a number in one workbook, the formula you created will not be updated with the new information. This may make more sense after you have watched the video.
 Web Media: The Saylor Foundation’s “Formulas Across Workbooks”
Link: The Saylor Foundation’s “Formulas Across Workbooks” (YouTube)
Instructions: Watch the video about 3D formulas across workbooks. When you are finished, open up your own spreadsheet software and try to create a linked formula between two workbooks. You can use spreadsheets that you created earlier in this class.
Completing this assignment should take approximately 45 minutes.
 Web Media: The Saylor Foundation’s “Formulas Across Workbooks”

9.3 Unit 9 Exercises
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 9”
Link: The Saylor Foundation’s “Practice Exercise for Unit 9” (PDF)
Instructions: Follow the instructions on the PDF. You will need the accompanying Excel® file below to complete this exercise.
 Activity: The Saylor Foundation’s “Practice Exercise for Unit 9”

Unit 10: Tables
Tables are an important feature of Excel®. They are used to store large pieces of data, usually on an ongoing basis. The data can then be sorted and filtered to answer specific questions. Excel® tables have database properties. Once a table has been created in Excel®, there are database functions that can be used to do math on different categories within the table. Pivot Tables and Charts will also be introduced. Once you have a lot of data in one place, you can use Pivot Tables and Charts to manipulate it to answer business questions.
Unit 10 Time Advisory show close
Unit 10 Learning Outcomes show close

10.1 Creating a Table
 Web Media: The Saylor Foundation’s “Creating a Table”
Link: The Saylor Foundation’s “Creating a Table” (YouTube)
Instructions: Click on the link to watch a video on creating tables. Once you have watched the video, try creating a table in your own Excel® program. Tables are an important feature of Excel® and something you should become comfortable using.
Completing this assignment should take approximately 15 minutes.
 Web Media: The Saylor Foundation’s “Creating a Table”

10.1.1 Formatting a Table
 Web Media: GCFLearnFree.org’s “Excel® 2010: Formatting Tables”
Link: GCFLearnFree.org’s “Excel® 2010: Formatting Tables” (HTML)
Instructions: Read through the four pages and watch the short video on page 2. If you have Excel® 2010, on page 4 you can download the practice document they give you and follow the directions to practice the skills you just learned.
Completing this assignment should take approximately 20 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Formatting Tables”

10.1.2 Sorting Data
As you saw in the previous section, it is easy to sort and filter data once the data are put into a table format. Let’s look a little closer at ways to sort and filter data or lists. The techniques that you will learn here can be used on a table or just a list of information you have in Excel®.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Sorting Data”
Link: GCFLearnFree.org’s “Excel® 2010: Sorting Data” (HTML)
Instructions: Read through the five pages and watch the video on page 2. If you have Excel® 2010, then download the practice workbook they provide on page 5 and try sorting the data as they showed in the lesson. You can also use the Data tab Sort commands as shown in the lesson to do advanced sorts on the table columns.
Completing this assignment should take approximately 20 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Sorting Data”

10.1.3 Filtering Data
Filtering can be done on a data set or a table to give you control over what information is shown on your spreadsheet. It allows you to narrow down your data in different ways.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Filtering Data”
Link: GCFLearnFree.org’s “Excel® 2010: Filtering Data” (HTML)
Instructions: Read through the five pages and watch the fourminute video on page 2. Filtering can be used on just a list or on data that has been put into a table format. You’ll notice in the example that the column headings have names; this makes sorting and filtering much easier and is a good technique to use with data lists or tables. If you have Excel® 2010, download the practice workbook they provide on page 5 and follow the steps they give you to practice filtering. Note that you have to have done the reading, not just watched the video, to complete the filtering correctly because the reading differs from the video.
Completing this assignment should take approximately 20 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Filtering Data”

10.2 Outlining Data
Outlining data helps to organize large pieces of information into manageable chunks on screen. This can be done to lists of data, NOTtables. You cannot create outlines or use the Subtotals command if your data are still in a table format. This cannot be stressed enough. If your data are still in a table format and you have access to the Table Tools  Design toolbar, then you will not have access to Subtotals and the outlining tools. You must click on your table and use the Convert to Rangecommand in the Tools grouping on the Table Tools  Design toolbar. It is often a good idea to use a table to sort or filter data the way you would like before you convert it to be subtotaled. You may need to think about why you are subtotaling it and how the levels should be grouped. This will make more sense after you have read through the material. This idea of sorting then outlining may take some practice but it is a good skill to master.
 Reading: The Saylor Foundation’s “Convert to Range for Subtotaling”
Link: The Saylor Foundation’s “Convert to Range for Subtotaling” (PDF)
Instructions: Read the short explanation about converting a table to a list so that the Subtotal command can be utilized.
Reading this material should take approximately 15 minutes.  Web Media: GCFLearnFree.org’s “Excel® 2010: Outlining Data”
Link: GCFLearnFree.org’s “Excel® 2010: Outlining Data” (HTML)
Instructions: Read through the six pages and watch the video on page 2. If you have Excel® 2010, you can download the practice document they give you on page 6 and follow the directions to practice the skills you just learned.
Completing this assignment should take approximately 30 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
 Reading: The Saylor Foundation’s “Convert to Range for Subtotaling”

10.3 PivotTable and PivotChart
Now that you understand about tables, let’s talk about PivotTables, sometimes called PivotTable Reports. If you have a large amount of data that you can sort and filter, you may still want to be able to manipulate it further to answer business questions or predict future scenarios like sales or marketing events. PivotTables can help managers answer or give a bestguess scenario.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Creating PivotTables”
Link: GCFLearnFree.org’s “Excel® 2010: Creating PivotTables” (HTML)
Instructions: Read through the seven pages and watch the two videos on page 2. If you have Excel® 2010, you can download the practice document and follow the directions on page 7 to practice creating a PivotTable and PivotChart. There is a lot of information here so go slowly and really practice these skills. Like charts, PivotTables changed a lot with Excel® 2007 and newer. If you are using an older version of Excel®, most of this information will be different and you will need to use your help feature to learn about PivotTables in your version of Excel®.
Completing this assignment should take approximately 30 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.  Web Media: Daniel Norris’ “Intermediate Excel® (1/2)  Pivot Tables”
Link: Daniel Norris’ “Intermediate Excel® (1/2)  Pivot Tables” (YouTube)
Instructions: Watch the video. There is no practice document with this exercise, but you may want to create your own data to practice with or try opening another spreadsheet from an earlier lesson in the class and create a PivotTable and PivotChart for it. Employees with PivotTables and PivotCharts experience are seen as an asset in the office.
Completing this assignment should take approximately 30 minutes.
Terms of Use: This resource is licensed under a Creative Commons Attribution 3.0 Unported License. It is attributed to Daniel Norris, and the original version can be found here.
 Web Media: GCFLearnFree.org’s “Excel® 2010: Creating PivotTables”

Conclusion
Now that you have finished the class and are familiar with the Goodwill Foundation’s LearnFree.org site, there are additional videos that you may want to explore. The link to the main page is provided at the beginning of this course in the Course Information section. Learning any piece of software is like learning a foreign language, you have to use it or you lose it. So, continue to practice and learn new things and soon you will be comfortable working in Excel®.
When you are ready, proceed on to the final exam. 
Final Exam
 Final Exam: The Saylor Foundation’s “PRDV252 Final Exam”
Link: The Saylor Foundation’s “PRDV252 Final Exam” (HTML)
Instructions: Log into your Saylor Foundation School account in order to access this exam. If you do not yet have an account, you will be able to create one, free of charge, after clicking the link.
 Final Exam: The Saylor Foundation’s “PRDV252 Final Exam”