Computer Magic Training Logo
Course Outline

Excel Intermediate Class

With an impressive array of automated functions and tools to display data graphically, Microsoft Excel is the premier spreadsheet software application used by businesses, organizations and professionals worldwide. Microsoft Excel training covers the powerful set of features which will enable you to accomplish more with this industry-standard application. This hands-on class will prepare you to link workbooks to manage larger data sets, work with table data and name ranges, create custom filters to target specific data for analysis and use PivotTables to explore different relationships among data. Your instructor brings a wealth of experience using these advanced Excel techniques, and will work with you to make sure you leave class with a new set of skills ready to apply immediately.


  • Familiarity with the Microsoft Office Ribbon.
  • Understanding of cell references, formulas and functions.

Navigating and Linking Workbooks to Manage Large Interconnected Data Sets

  • Freeze row and column headings in place so that they will always appear when scrolling.
  • Divide a larger worksheet into smaller, more manageable panes.
  • Hide columns and rows, as needed, to emphasize key data.
  • Define page breaks at logical points in your spreadsheet.
  • Use 3-D formulas to link multiple workbooks.
  • Reference cells and ranges in other workbooks accurately ... on the first try!)
  • Implement best practices for easy maintenance of linked workbooks.

Managing and Manipulating Table Data

  • Consolidate lists into collapsable outlines for simplified viewing.
  • Target specific data for analysis with sorting and filtering
  • Create powerful data filters with multiple criteria.
  • Maintain data integrity with data validation and structured references.

Outlining and Subtotals

  • Outline to summarize data and use the consolidate command
  • Use the Subtotal dialogue box to create various summary fromulas

Working with Cell and Range Names

  • Define names to make functions more readable
  • Select name ranges based on column and row labels
  • Apply the Names command to replace cell references
  • Use the Name Manager to manage names and create 3-D names

Date Structure and Tables

  • Identify records, fields, and field anmes as data components
  • Utilize Autofilter to display only those rows that meet specified criteria
  • Sort and filter data using fill color and other attributes
  • Simplify data creation and formating tesks with tables
  • Work with structured references in formulas
  • Incorporate the [@] argument in a SUM function to total cells in the same row

Auditing, Collaborating and Securing Spreadsheet Data

  • Test formulas and trace errors with Error Checking and Auditing tools.
  • Include cell comments for increased usability and collaboration.
  • Prevent accidental or deliberate data deletion by protecting specific portions of a workbook.
  • Permit multiple users to edit a spreadsheet simultaneously, without creating inconsistencies.

Advanced Data Management in Excel

  • Set up cells to accept only valid dates, whole numbers, or other valid values you specify
  • Use database functions to summarize values that meet complex criteria, for example, the total sales in a specific region for a specific year

Excel PivotTables and PivotCharts

  • Create and use interactive PivotTables to analyze and compare large amounts of data from your workbooks or from external databases
  • Make a PivotTable easier to read with effective automatic formatting
  • Create a PivotChart to graphically display PivotTable data

Revised 1/14/16