MOS: Excel 2010

Course details

Module One: Managing the Worksheet Environment

• Navigate through a worksheet: Hot keys, name box.
• Print a worksheet or workbook: printing only selected worksheets, printing an entire workbook, constructing headers and footers, and applying printing options (scale, print titles, page setup, print area, grid lines).
• Personalize environment by using Backstage: Manipulate the Quick Access Toolbar, manipulate the ribbon tabs and groups, manipulate Excel default settings, import data to Excel, import data from Excel, demonstrate how to manipulate workbook properties, manipulate workbook files and folders. apply different name and file formats for different uses by using save and save as features.

Module Two: Managing Worksheets and Workbooks

• Create and format worksheets: Insert worksheets, delete worksheets, copy, reposition, copy and move, rename, grouping, apply coloring to worksheet tabs, hiding worksheet tabs, un-hiding worksheet tabs.
• Manipulate window views: splitting window views, arranging window views, and opening a new window with contents from the current worksheet.
• Manipulate workbook views: using Normal, Page Layout, and Page Break workbook views, and creating custom views.

Module Three: Formatting Cells and Worksheets

• Apply and modify cell formats: aligning cell content, applying a number format, wrapping text in a cell, and using Format Painter.
• Merge or split cells: using Merge & Center, Merge Across, Merge cells, and Unmerge Cells.
• Create row and column titles: Print row and column headings, print rows to repeat with titles, print columns to repeat with titles, configure titles to print only on odd or even pages, configure titles to skip the first worksheet page.
• Hide and unhide rows and columns: Hide a column, unhide a column, hide a series of columns, hide a row, unhide a row, hide a series of rows.
• Manipulate Page Setup options for worksheets: Configure page orientation, manage page scaling, configure page margins, change header and footer size.
• Create and apply cell styles: Apply cell styles, construct new cell styles.

Module Four: Applying Formulas and Functions

• Create formulas: Use basic operators, revise formulas.
• Enforce precedence: Order of evaluation, precedence using parentheses, precedence of operators for percent vs. exponentiation.
• Apply cell references in formulas: Relative, absolute.
• Apply conditional logic in a formula: Create a formula with values that match your conditions, edit defined conditions in a formula, use a series of conditional logic values in a formula.
• Apply named ranges in formulas: Define, edit and rename a named range.
• Apply cell ranges in formulas: Enter a cell range definition in the formula bar, define a cell range using the mouse, define a cell range using a keyboard shortcut.

Module Five: Creating Cell Data

• Construct cell data: using paste special (formats, formulas, values, preview icons, transpose rows and columns, operations, comments, validation, paste as a link).
• Apply AutoFill: Copy data using AutoFill, fill series using AutoFill, copy or preserve cell format with AutoFill, select from drop-down list.
• Apply and manipulate hyperlinks: Create a hyperlink in a cell, modify hyperlinks, modify hyperlinked-cell attributes, remove a hyperlink.

Module Six: Presenting Data Visually

• Create charts based on worksheet data.
• Apply and manipulate illustrations: Clip Art, SmartArt, shapes, screenshots.
• Create and modify images by using the Image Editor: making corrections to an image (sharpen or soften an image, changing brightness and contrast), using picture color tools, and changing artistic effects on an image.
• Apply Sparklines: using Line, Column, and Win/Loss chart types, creating a Sparkline chart, customizing a Sparkline, formatting a Sparkline, and showing or hiding data markers.

Module Seven: Sharing worksheet data with other users

• Share spreadsheets by using Backstage: sending a worksheet via E-mail or Skydrive, changing the file type to a different version of Excel, and saving as PDF or XPS.
• Manage comments: inserting, viewing, editing, and deleting comments.

Module Eight: Analyzing and Organizing Data

• Filter data: Define, apply, remove, search, filter lists using AutoFilter.
• Sort data: using sort options (values, font color, cell color).
• Apply conditional formatting: Apply conditional formatting to cells, use the Rule Manager to Apply Conditional Formats, use the IF Function and Apply Conditional Formatting, icon sets, data bars, clear rules.