Microsoft Partner

MOS Excel 2013 (77- 420)

Microsoft Partner Silver Learning

print this page

Audience: This course is designed for those preparing for exam code 420.

Course Duration: 1 day(1)

Course: Group bookings, public courses , 1-2-1 sessions, bespoke tailored courses and Saturday club.

At Course Completion

After completing this course, students will be able to:

  • Create and manage worksheets and workbooks
  • Create cells and ranges
  • Create tables
  • Apply formulas and functions
  • Create charts and objects

Course Outline

Module 1-1: Create worksheets and workbooks

  • Creating new blank workbooks
  • Creating new workbooks using templates
  • Importing files
  • Opening non-native files directly in Excel
  • Adding worksheets to existing workbooks
  • Copying and moving worksheets

Module 1-2: Navigate through worksheets and workbooks

  • Searching for data within a workbook
  • Inserting hyperlinks
  • Changing worksheet order
  • Using Go To
  • Using Name Box

Module 1-3: Format worksheets and workbooks

  • Changing worksheet tab colour
  • Modifying page setup
  • Inserting and deleting columns and rows
  • Changing workbook theme
  • Adjusting row height and column width
  • Inserting watermarks
  • Inserting headers and footers
  • Setting data validation

Module 1-4: Customise options and views for worksheets and workbooks

  • Hiding worksheets
  • Hiding columns and rows
  • Customising the Quick Access toolbar
  • Customising the Ribbon
  • Managing macro security
  • Changing workbook views
  • Recording simple macros
  • Adding values to workbook properties
  • Using zoom
  • Displaying formulas
  • Freezing panes
  • Assigning shortcut keys
  • Splitting the window

Module 1-5: Configure worksheets and workbooks to print or save

  • Setting a print area
  • Saving workbooks in alternate file formats
  • Printing individual worksheets
  • Setting print scaling
  • Repeating headers and footers
  • Maintaining backward compatibility
  • Configuring workbooks to print
  • Saving files to remote locations

Module 2-1: Insert data in cells and ranges

  • Appending data to worksheets
  • Finding and replacing data
  • Copying and pasting data
  • Using AutoFill tool
  • Expanding data across columns
  • Inserting and deleting cells

Module 2-2: Format cells and ranges

  • Merging cells
  • Modifying cell alignment and indentation
  • Changing font and font styles
  • Using Format Painter
  • Wrapping text within cells
  • Applying Number format
  • Applying highlighting
  • Applying cell styles
  • Changing text to WordArt

Module 2-3: Order and group cells and ranges

  • Applying conditional formatting
  • Inserting sparklines
  • Transposing columns and rows
  • Creating named ranges
  • Creating outlines
  • Collapsing groups of data in outlines
  • Inserting subtotals

Module 3-1: Create a table

  • Moving between tables and ranges
  • Adding and removing cells within tables
  • Defining titles

Module 3-2: Modify a table

  • Applying styles to tables
  • Banding rows and columns
  • Inserting total rows
  • Removing styles from tables

Module 3-3: Filter and sort a table

  • Filtering records
  • Sorting data on multiple columns
  • Changing sort order
  • Removing duplicates

Module 4-1: Summarise data with functions

  • Using the SUM function
  • Using the MIN and MAX functions
  • Using the COUNT function
  • Using the AVERAGE function

Module 4-2: Using conditional logic in functions

  • Using the SUMIF function
  • Using the AVERAGEIF function
  • Using the COUNTIF function

Module 4-3: Format and modify text with functions

  • Using the RIGHT, LEFT and MID functions
  • Using the TRIM function
  • Using the UPPER and LOWER functions
  • Using the CONCATENATE function

Module 5-1: Create a chart

  • Creating charts and graphs
  • Adding additional data series
  • Switching between rows and columns in source data
  • Using Quick Analysis

Module 5-2: Format a chart

  • Adding legends
  • Resizing charts and graphs
  • Modifying chart and graph parameters
  • Applying chart layouts and styles
  • Positioning charts and graphs

Module 5-3: Insert and format an object

  • Inserting text boxes
  • Inserting SmartArt
  • Inserting images
  • Adding borders to objects
  • Adding styles and effects to objects
  • Changing object colours
  • Modifying object properties
  • Positioning objects




Call us now for further information Tel:08001601666