Capital Business Machines Excel 2010 Level 2 - Intermediate

Excel 2010 Level 2-Intermediate

This course builds on the skills and concepts taught in Excel 2 1 : Basic. Students will learn how to use multiple worksheets and workbooks efficiently, and they will start working with more advanced formatting options including styles, themes, and backgrounds. They will also learn how to create outlines and subtotals, how to create and apply cell names, and how to work with tables. Students will learn filtering and sorting. This course also covers advanced charting techniques, pivot tables, and workbook templates.

Course Length

1 day

Prerequisites

Windows Basic, Excel Basics or equivalent experience

Topics

Unit 1 : Using multiple worksheets and workbooks

Topic A: Using multiple workbooks

A-1: Switching between workbooks

A-2: Copying a worksheet to another workbook

Topic B: Linking worksheets with 3-D formulas

B-1: Creating 3-D formulas

B-2: Adding a Watch window

Topic C: Linking workbooks

C-1: Examining external links in a worksheet

C-2: Creating external links in a worksheet

C-3: Editing links

Topic D: Managing workbooks

D-1: Creating a workspace

Unit 2 : Advanced formatting

Topic A: Using special number formats

A-1: Applying special formats

A-2: Controlling the display of zero values

A-3: Creating custom formats

Topic B: Using functions to format text

B-1: Using PROPER, UPPER, and LOWER

B-2: Using SUBSTITUTE

Topic C: Working with styles

C-1: Creating and applying styles

C-2: Modifying styles

Topic D: Working with themes

D-1: Changing to a different theme

D-2: Saving new colors and themes

Topic E: Other advanced formatting

E-1: Merging cells

E-2: Changing the orientation of text in a cell

E-3: Splitting cells

E-4: Transposing data during a paste

E-5: Using Paste Special to add values

E-6: Adding and deleting backgrounds

E-7: Adding a watermark

Unit 3 : Outlining and subtotals

Topic A: Outlining and consolidating data

A-1: Creating an outline

A-2: Creating custom views

A-3: Using the Consolidate command

Topic B: Creating subtotals

B-1: Creating subtotals in a list

B-2: Using multiple subtotal functions

Unit 4 : Cell and range names

Topic A: Creating and using names

A-1: Naming and selecting ranges

A-2: Using names in formulas

A-3: Using the Create from Selection command

A-4: Applying names to existing formulas

Topic B: Managing names

B-1: Modifying and deleting named ranges

B-2: Defining and applying 3-D names

Unit 5 : Tables

Topic A: Sorting and filtering data

A-1: Examining the structure of data

A-2: Sorting data

A-3: Filtering data by using AutoFilter

A-4: Using cell color and attributes to sort and filter data

Topic B: Advanced filtering

B-1: Using Custom AutoFilter criteria

B-2: Using the Advanced Filter dialog box

B-3: Copying filtered results to another range

Topic C: Working with tables

C-1: Creating a table

C-2: Formatting a table

C-3: Adding and deleting rows and columns

C-4: Using structured references

C-5: Naming tables

C-6: Creating functions with the [@] argument

Unit 6 : Web and sharing features

Topic A: Saving workbooks as Web pages

A-1: Making Web commands available

A-2: Saving a workbook as a Web page

A-3: Using the Publish as Web Page dialog box

Topic B: Using hyperlinks

B-1: Inserting a hyperlink

B-2: Modifying and deleting a hyperlink

Topic C: Sharing workbooks

C-1: Examining workbook sharing options

C-2: Using e-mail to send an attachment

Unit 7 : Advanced charting

Topic A: Chart formatting options

A-1: Adjusting the scale of a chart

A-2: Formatting a data point

Topic B: Combination charts

B-1: Creating a combination chart

B-2: Creating a trendline

B-3: Inserting sparklines

B-4: Using a chart template

Topic C: Graphical elements

C-1: Adding graphical elements

C-2: Formatting a graphical element

C-3: Adding a picture to a worksheet

C-4: Modifying a picture

Unit 8 : Documenting and auditing

Topic A: Auditing features

A-1: Tracing precedent and dependent cells

A-2: Tracing errors

Topic B: Comments in cells and workbooks

B-1: Viewing comments in a worksheet

B-2: Adding a comment to a cell

B-3: Adding comments to a workbook

Topic C: Protection

C-1: Password-protecting a worksheet

C-2: Unlocking cells and protecting part of a worksheet

C-3: Protecting the workbook structure

C-4: Discussing digital signatures

Topic D: Workgroup collaboration

D-1: Sharing a workbook

D-2: Merging workbooks

D-3: Tracking changes in a workbook

D-4: Using the Document Inspector

D-5: Marking a workbook as final

Unit 9 : Templates and settings

Topic A: Changing application settings

A-1: Changing application settings

A-2: Customizing the Ribbon

Topic B: Using built-in templates

B-1: Using a downloaded template

Topic C: Creating and managing templates

C-1: Creating a template

C-2: Modifying a template

C-3: Specifying an alternate template location

Unit 10 : PivotTables and PivotCharts

Topic A: Working with PivotTables

A-1: Creating a PivotTable

A-2: Adding fields to a PivotTable

A-3: Using slicers to filter PivotTable data

Topic B: Rearranging PivotTables

B-1: Moving fields

B-2: Collapsing and expanding fields

B-3: Refreshing the data in a PivotTable

Topic C: Formatting PivotTables

C-1: Applying a PivotTable style

C-2: Changing field settings

Topic D: Using PivotCharts

D-1: Creating a PivotChart