Microsoft Certified Application Specialist:
Microsoft Excel 2007

Selected courseware addresses Microsoft Certified Application Specialist skills for Microsoft Office.
The following indicate where Excel 2007 skills are covered in courses. For example, in "Fill a series with formatting
only" "1" is the course level, 3-A indicates lesson and topic applicable to that skill, and 3-1 indicates the lesson
and activity number.

1. Creating and Manipulating Data
Objective        Level        Topic(s)        Activity(s)
1.1        Insert data using AutoFill                        
1.1.1        Fill a series                        
•        Fill a series without formatting        1        3-A        3-1
•        Fill a series with formatting only        1        3-A        3-1
1.1.2        Copy a series        1        3-A        
1.2        Ensure data integrity
            
1.2.1        Restrict data using data validation                        
•        Restrict the type of data that can be entered in cells  
o         Less than x  
o        Specified character length         3        1-D        1-6
•        Restrict the values entered in cells        3        1-D        1-6
•        Create drop-down lists        3        1-D        1-6
1.2.2        Remove duplicate rows from spreadsheets        2        2-A        
•        Select columns used to determine duplication         2        2-A        
1.3        Modify cell contents and formats        1        3-A        3-1
1.3.1        Cut, copy, and paste data and cell contents        1        2-C        
•        Transpose data during paste        1        4-C        4-3
•        Paste data from a bordered cell without pasting borders        1        4-B        
•        Paste Special (Paste Values)        1        4-B        4-2
1.4        Change Worksheet Views                        
1.4.1        Change views within a single window        1        5-C        
•        Change zoom level         1        5-B        
•        Change view to normal, page layout, and page break preview        1        5-C        5-4
•        Freeze and unfreeze panes        1        6-C        6-3
•        Hide the Ribbon to maximize space        2        6-A        6-1
1.4.2        Split windows        1        6-C        6-3
1.4.3        Open and arrange new windows        1        6-C        6-3
1.5        Manage worksheets                        
1.5.1        Copy worksheets        1        6-B        6-2
•        Within a workbook        1        6-B        6-2
•        Between workbooks        1        6-B        
1.5.2        Reposition worksheets within workbooks        1        6-B        6-2
1.5        Rename worksheets        1        6-A        
1.5.4        Hide and unhide worksheets        1        6-B        6-2
1.5.5        Insert and delete worksheets        1        6-B        6-2

2. Formatting Data and Content
2.1        Format worksheets                        
2.1.1        Use themes to format worksheets        2        6-B        6-2
Apply a theme to a workbook        2        6-B        6-2
Modify and save a theme        2        6-B        6-2
2.1.2        Show and hide gridlines and headers        2        6-A        6-1
•        Hide gridlines for presentation purposes (such as displaying a table without showing all the gridlines on the worksheet)        2        6-A        6-1
•        Hide headers for presentation purposes (such as displaying a table without showing all the headers on the worksheet)        2        6-A        
2.1.3        Add color to worksheet tabs        1        6-A        6-1
2.1.4        Format worksheet backgrounds                        
•        Color        1        4-B        4-2
•        Watermarks        2        6-A        6-1
2.2        Insert and modify rows and columns                        
2.2.1        Insert and delete cells, rows, and columns                        
•        Insert a column or row above, below, to the left, or right of an existing column or row        1        3-B        3-2
•        Insert multiple rows or columns simultaneously        1        3-B        3-2
2.2.2        Format rows and columns                        
•        Format all cells in a row or column simultaneously        1        4-A, 4-C        4-1, 4-3
2.2.3        Hide and unhide rows and columns                        
•        Unhide a row or column        1        4-C        
•        Hide a row or column        1        4-C        4-3
2.2.4        Modify row height and column width                        
•        Using AutoFit        1        4-C        4-3
•        Using a specified value        1        4-C        
2.3        Format cells and cell content                        
2.3.1        Apply number formats                        
•        Format the values in a cell with a specified number of decimal places        1        4-D        4-4
•        Format the values in a cell as a date        1
2        4-D
2-C        
2-3
2.3.2        Create custom cell formats        1        4-D        4-4
2.3.3        Apply and modify cell styles        1        4-F        4-6
•        Format cells using Quick Styles        1        4-D        4-4
•        Format cells using other methods        1



2        4-A, 4-B, 4-D, 4-E
6-B        4-1, 4-2, 4-4

6-2
2.3.4        Format text in cells                        
•        Font, alignment, attributes, wrapping, etc.        1        4-A, 4-E, 4-F        4-1, 4-5 4-6
2.3.5        Convert text to columns        2        1-C        1-4
2.3.6        Merge and split cells        1        4-E        4-5
2.3.7        Add and remove cell borders        1        4-B        4-2
2.3.8        Insert, modify and remove hyperlinks        2        6-B        6-2
2.4        Format data as a table                        
2.4.1        Apply Quick Styles to tables                        
•        Apply and change Quick Styles        2        2-A        2-1
•        Add and remove header rows        2        2-A        2-1
•        Band the rows or columns in a table (using banded rows)        2        2-A, 2-B        2-2
•        Emphasize the first column or last column in a table        2        2-B        2-2
•        Change a table with banded rows to have banded columns        2        2-A, 2-B        2-2
2.4.2        Add rows to a tables        2        2-A        2-1
•        Add total rows to a tables        2        2-A, 2-D        2-5
•        Change the summary function of total rows in tables        2        2-D        2-5
2.4.3        Insert and delete rows and columns in tables        2        2-A        2-1

3. Creating and Modifying Formulas
3.1        Reference data in formulas                        
•        Create a formula using relative references that, when copied to an adjacent cell, updates its cell references to reflect its new location        1        2-C        
2-4, 2-5
•        Create a formula using absolute references that maintains its reference point when copied to a new location        2        2-C        2-5
•        Create a formula with mixed relative and absolute references        1        2-C        
•        Troubleshoot a formula        3        3-B, 3-C        3-3, 3-2
3.1.2        Create formulas that reference data from other worksheets or workbooks                        
•        Using data from other worksheets, create summary sheets that categorize the data.         2        1-B        1-2
3.1.3        Manage named ranges                        
•        Select and name a range of cells        2        1-A        1-1
•        Modify a named range by adding a column        2        1-A        
•        Delete a named range        2        1-A        
3.1.4        Use named ranges in formulas                        
•        Using data named in different ranges, create formulas for the data that summarize the data into different categories. i.e. Using sales figures named in
three ranges, create formulas for sales figures that summarize the sales figures for three teams        2        1-A        1-1

3.2        Summarize data using a formula                        
3.2.1        Use SUM, COUNT, COUNTA, AVERAGE, MIN, and MAX                        
•        SUM        1        2-B        2-2
•        COUNT        1        2-B        2-3
•        COUNTA        1        2-B        2-3
•        AVERAGE        1        2-B        2-2
•        MIN        1        2-B        2-2
•        MAX        1        2-B        2-2
3.3        Summarize data using subtotals                        
3.3.1        Create and modify list ranges                        
•        Create a formula that finds the average of displayed values in a filtered list        2        1-C        1-3
•        Create a formula that finds the sum of displayed values in a filtered list        2        1-C        1-3
•        Create a formula that finds the mean or count of displayed values in a filtered list        2        1-C        1-3
3.4        Conditionally summarize data using a formula                        
3.4.1        Using SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, and AVERAGEIFS                        
•        SUMIF        2        1-D        1-5
•        SUMIFS        2        1-D        
•        COUNTIF        2        1-D        1-5
•        COUNTIFS        2        1-D        
•        AVERAGEIF        2        1-D        1-5
•        AVERAGEIFS        2        1-D        
3.5        Look up data using a formula                        
3.5.1        Using VLOOKUP and HLOOKUP                        
•        Use VLOOKUP to locate values associated with an exact match (FALSE)        2        1-D        1-6
•        Use VLOOKUP to locate a cell in a list that has the value closest to the target value (TRUE)        2        1-D        
•        Use HLOOKUP to locate values associated with an exact match (FALSE)        2        1-D        1-6
•        Use HLOOKUP to locate values associated with an exact match (FALSE)        2        1-D        
3.6        Use conditional logic in a formula                        
3.6.1        Using IF, AND, OR, NOT, IFERROR                        
•        IF        2        1-D        1-5
•        AND        2        1-D        
•        OR        2        1-C        
•        NOT        2        1-D        
•        IFERROR        2        1-D        
3.7        Format or modify text using formulas                        
3.7.1        Format text by using formulas                        
•        PROPER        2        1-C        1-3
•        UPPER        2        1-C        
•        LOWER        2        1-C        
•        SUBSTITUTE        2        1-C        
3.7.2        Convert text to columns using formulas         2           1-C        1-4
3.8        Display and print formulas         3           3-B        


4. Presenting Data Visually
4.1        Create and format charts                        
4.1.1        Select appropriate data sources for charts                        
•        Select appropriate data to create a column chart        2        3-A        3-1
4.1.2        Select appropriate chart types to represent data sources                        
•        Create a chart that shows what part of the budget the x department uses        2        3-A        3-1
•        Create a chart that shows a trend over time        2        3-A        3-1
•        Create a chart that compares two data sets over time        2        3-A        3-1
4.1.3        Format charts using Quick Styles                        
•        Quick Styles        2        3-C        3-4
•        Quick Layouts        2        3-C        3-4
4.2        Modify charts                        
4.2.1        Add and remove chart elements                        
•        Axis information        2        3-B        3-2
•        Titles        2        3-B        3-2
•        Legends        2        3-B        3-2
•        Percentages        2        3-B        3-2
4.2.2        Move and size charts                        
•        Move an embedded chart to a new sheet        2        3-B        3-2
•        Move an embedded chart from a chart sheet to a worksheet by embedding as an object        2        3-B        
•        Size a chart by increasing length or width        2        3-B        3-2
•        Size a chart while maintaining scale        2        3-B        
4.2.3        Change chart types        2        3-B        3-2
4.3        Apply conditional formatting
            
4.3.1        Manage conditional formats by using the Conditional Formatting Rules Manager                        
•        Create a new conditional formatting rule        3        1-C        
•        Edit an existing conditional formatting rule          3        1-C        1-4
•        Delete a conditional formatting rule        3        1-C        1-4
4.3.2        Allow more than one rule to be true        3        1-C        
4.3.3        Apply conditional formats                        
•        Highlight        3        1-C        1-3
•        Top and bottom rules        3        1-C        
•        Data bars        3        1-C        
•        Color scales        3        1-C        
•        Icon sets        3        1-C        
4.4        Insert and modify illustrations                        
4.4.1        Insert and modify pictures from files (not clip art files)                        
•        Insert pictures        2        5-A        5-1
•        Modify pictures        2        5-A        5-1
4.4.2        Insert and modify SmartArt graphics                        
•        Insert SmartArt graphics        2        5-D        5-4
•        Modify SmartArt graphics using Quick Styles        2        5-D        5-4
•        Add effects to SmartArt graphics        2        5-D        5-4
4.4.3        Insert and modify shapes        2        5-B, 5-C        5-2, 5-3
4.5        Outline data
            
4.5.1        Group and ungroup data        3        3-E        3-5
4.5.2        Subtotal data                        
•        Add subtotals to grouped data without using the AutoSum button        2        2-D        2-5
4.6        Sort and filter data                        
4.6.1        Sort data using single or multiple criteria        2        2-C        2-4
4.6.2        Filter data using AutoFilter        2        2-C        2-4
•        Filter data using Custom AutoFilter        2        2-C        2-4
4.6.3        Filter and sort data using conditional formatting
3        1-C        1-3
4.6.4        Filter and sort data using cell attributes        3        1-C        1-3

5. Collaborating and Securing Data
5.1        Manage changes to workbooks
            
5.1.1        Insert, display, modify, and resolve tracked changes                        
•        Begin tracking changes        3        2-C        2-5
•        Resolve tracked changes        3        2-D        2-6
5.1.2        Insert, display, modify, and delete comments        2                
•        Insert comments        2        6-B        6-2
•        Edit comments        2        6-B        6-2
•        Delete comments        2        6-B        
5.2        Protect and share workbooks  
            
5.2.1        Protect workbooks and worksheets                        
•        Prevent changes to worksheets        3        2-A        2-1
•        Allow users to edit ranges        3        2-A        2-2
•        Require passwords to open workbooks        3        2-A        2-3
5.2.2        Enable workbooks to be changed by multiple users                        
•        Share a workbook using the share workbook command        3        2-B        2-4
5.3        Prepare workbooks for distribution
            
5.3.1        Remove private and other inappropriate data from workbooks                        
•        Determine when to use Document Inspector          3        2-A        2-2
5.3.2        Restrict permissions to a workbook                        
•        Enable a specific user to change a workbook using information rights management (IRM)          3        2-H        2-9
5.3.3        Add keywords and other information to workbook properties                        
•        Add keywords, author name, and other information to properties        3        1-E        1-7
5.3.4        Add digital signatures        3        2-G        2-8
5.3.5        Mark workbooks as final                        
•        Certify and protect content from changes by marking as final        3        2-H        2-11
5.4        Save workbooks                        
5.4.1        Save workbooks for use in a previous version of Excel                        
•        Using Compatibility Checker, determine which feature of a workbook is incompatible with a previous version        1        1-D        1-4
•        Save a specific feature to the Excel 97-2003 format        1        1-D        1-4
5.4.2        Using the correct format, save a workbook as a template, a Web page, a macro-enabled document, or another appropriate format                        
•        Save as a template        2        6-C        6-3
•        Save as a PDF        1        1-D        
•        Save as a Web page        3        7-A        7-1
•        Save as a macro-enabled workbook        1        1-D        
5.5        Set print options for printing data, worksheets and workbooks                        
5.5.1        Define the area of a worksheet to be printed                        
•        Set a print area on a single sheet        1        5-A        5-1
5.5.2        Insert and move a page break                        
•        Preview and change a page break from different columns. i.e the third to the fourth column        1        5-C        5-4
5.5.3        Set margins                        
•        Set worksheet margins to specified values        1        5-B        5-3
5.5.4        Add and modify headers and footers        1        5-B        5-3
•        Change the date or other information in a footer        1        5-B        
5.5.5        Change the orientation of a worksheet        1        5-A        5-2
5.5.6        Scale worksheet content to fit a printed page        1        5-A        5-2
1994-2010 ConnecTotal All rights reserved.
What our customers say...

"Makes me comfortable when
questioning everything!!"

"Very on track with pace of class."

"Fantastic class.  I like the small
group and hands on."

"Very patient and clear instruction."

"This class has been a great help in
furthering my knowledge."

"I learned a lot and refreshed what I
already knew."