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, multi-page spreadsheets, charting data, creating tables that have database features, and be introduced to pivot tables. This class was designed to be an active, hands-on 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

Welcome to PRDV252: Intermediate Excel®. General information on this course and its requirements can be found below.
 
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:
Requirements for Completion: In order to complete this course, you will need to work through each unit and all of its assigned materials. Pay special attention to Units 3–5 as they are the heart of the software and how to use Excel® to your mathematical advantage. You should also complete and practice activities when presented at the end of some units. This will help prepare you for the Final Exam. Note that you will only receive an official grade on your Final Exam.
 
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 hands-on 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 (1-3 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

Upon successful completion of this course, you will be able to:
  • 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

In order to take this course, you must:

√    have access to a computer;

√    have continuous broadband Internet access;

√    have the ability/permission to install plug-ins 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


Expand All Resources Collapse All Resources
  • 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 flash-drive 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 Attribution-NonCommercial-ShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.

  • 1.2 Formatting and Data Analysis  
  • 1.3 Printing  
  • 1.4 Exercises 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.
     
    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 Time Advisory   show close
    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.

  • 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.

  • 2.1.2 Create Borders and Shading  
  • 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. 

  • 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.

  • 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.

  • 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!
     
    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 Time Advisory   show close
    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 follow-along 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 Attribution-NonCommercial-ShareAlike 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.

  • 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 Attribution-NonCommercial-ShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.

  • 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 10-year 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.

  • 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.

  • 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 work-along 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 Attribution-NonCommercial-ShareAlike 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.

  • 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.

  • 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  
  • 5.2 Review of PMT using Goal Seek  
    • Web Media: GCFLearnFree.org’s “Excel® 2010: Using What-If Analysis”

      Link: GCFLearnFree.org’s Excel® 2010: Using What-If 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.

  • 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. 

  • 5.4 Exercises 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 Attribution-NonCommercial-ShareAlike 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.

  • 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 Attribution-NonCommercial-ShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.

  • 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  
  • 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.

  • 7.3 Sparklines  
  • 7.4 Combination Charts  
  • 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.

  • 8.1.1 Adding a Picture to a Spreadsheet  
  • 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.

  • 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.

  • 8.2 Headers and Footers  
  • 8.3 Unit 8 Exercise  
  • Unit 9: Multi-Page 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 non-adjacent 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.

  • 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.

  • 9.3 Unit 9 Exercises  
  • 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.

  • 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.

  • 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.

  • 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 four-minute 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.

  • 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.

  • 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 best-guess 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.

  • 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