MOS Excel 2013
Home

elearnuk

Search

MOS Excel 2013

25 Lessons only £219.99
Online Study
Offline Study
Enrol

Syllabus

Unit 1 – Getting started

  • Define a spreadsheet and identify spreadsheet components.
  • Identify the main components of the Excel window.
  • Open and navigate workbooks.
  • Create a workbook from a template.

Unit 2 – Entering and editing data

  • Enter and edit text and values in a worksheet.
  • Enter and edit formulas in a worksheet.
  • Save and update a workbook, and save a workbook in a different file format.

Unit 3 - Modifying a worksheet

  • Move and copy data in a worksheet.
  • Move, copy and view formulas in a worksheet.
  • Use absolute references in formulas.
  • Insert and delete ranges, rows and columns in a worksheet.

Unit 4 - Functions

  • Apply the SUM function to calculate the sum of values.
  • Use AutoSum to enter SUM functions.
  • Use the AVERAGE, MIN, MAX, COUNT and COUNTA functions to find average, minimum and maximum values and the count of cells in a range.

Unit 5 - Formatting

  • Apply text formatting.
  • Customise column widths, row heights and alignment and apply colour and border formatting.
  • Format values as currency, percentages and ordinary numbers.
  • Apply conditional formatting based on specific criteria.
  • Copy formatting, apply built-in cell styles and create and modify custom cell styles.

Unit 6 - Charts

  • Create charts based on worksheet data and move charts within a workbook.
  • Customise charts and format chart elements.

Unit 7 - Graphics

  • Insert a picture into a worksheet.
  • Use graphics as conditional formatting to represent cell data.
  • Insert and modify SmartArt graphics.

Unit 8 - Printing

  • Preview how a worksheet will look when printed, use the spelling checker and use Find and Replace to update data.
  • Set page orientation, scaling and margins and create headers and footers.
  • Print a worksheet and a selected range.

Unit 9 - Managing workbooks and worksheets

  • Freeze panes, split a worksheet and hide and display data and window elements.
  • Set print titles and page breaks.
  • Navigate, manage and print multiple worksheets.
  • Create 3-D formulas to link worksheets and add a Watch window.
  • Switch between workbooks, copy a sheet from one workbook to another and arrange workbooks into a workspace.
  • Create and manage linked workbooks.

Unit 10 - Advanced formatting

  • Apply built-in and custom number formats to display data in specific formats.
  • Apply and modify themes.
  • Merge and change orientation of cells to display text in special ways; transpose data and add backgrounds and watermarks.

Unit 11 - Outlining and subtotals

  • Create outlines to group and organise data, create custom views and consolidate data from different worksheets.
  • Summarise the data in a worksheet by creating automatic subtotals.

Unit 12 - Cell and range names

  • Use names to make your formulas easier to understand.
  • Use the Name Manager to modify named ranges.

Unit 13 - Data structure and tables

  • Organise data logically, sort it by the contents of it’s columns and filter it to show only those rows that meets certain criteria.
  • Create and format tables and use structured references to include table column names in formulas.

Unit 14 - Web and sharing features

  • Save a workbook as a Web page and use the AutoRepublish feature to keep the Web version of a workbook updated.
  • Add and remove hyperlinks in a worksheet.

Unit 15 - Documenting and auditing

  • Use auditing features to trace precedent and dependent cells and trace errors.
  • Add and edit comments for a cell and a worksheet.
  • Protect a workbook or part of a worksheet from unauthorised access or unintentional changes.
  • Share workbooks, merge versions of a workbook, track changes made by various users, remove personal data from a workbook and mark a workbook as final.

Unit 16 - Templates and settings

  • Change Excel’s default application settings.
  • Create and modify custom templates.

Unit 17 - Advanced functions and formulas

  • Use logical functions to calculate values based on specific criteria.
  • Use conditional functions to summarise count and average data.
  • Use the PMT function to calculate periodic payments for a loan.
  • Use text functions to extract specific text strings from cells and to format and modify text.
  • Use date functions to calculate duration expressed as the number of days.
  • Use array formulas to perform multiple calculations on multiple sets of values.
  • Use options to control calculations of formulas.

Unit 18 - Lookups and data tables

  • Use lookup functions to find values in a worksheet list.
  • Use data tables to see the effects of changing the values in a formula.

Unit 19 - Advanced data management

  • Use the data validation feature to validate data entered in cells.
  • Use the Custom AutoFilter and Advanced Filter dialog boxes to filter data based on complex criteria.

Unit 20 - Advanced charting

  • Adjust the scale of a chart and format data points.
  • Create combination charts, trendlines and sparklines to highlight different kinds of data.
  • Insert and format graphical objects in charts

Unit 21 - PivotTables and PivotCharts

  • Use the PivotTable command to create a PivotTable for analysing and comparing large amounts of data.
  • Change PivotTable views by grouping data, moving fields and using calculated fields.
  • Improve the appearance of a PivotTable by applying a style and changing it’s field settings.
  • Create a PivotChart to graphically display data from a PivotTable.
  • Use PowerPivot to integrate data from different Excel worksheets.

Unit 22 - Exporting and importing data

  • Export data from Excel to a text file and import data from a text file into an Excel workbook.

Unit 23 - Analytical tools

  • Use the Goal Seek utility to meet a target output for a formula by changing the values in the input cells.
  • Create scenarios to save various sets of input values that produce different results.
  • Use the Quick Analysis tool to instantly analyse data.

Unit 24 - Macros and Visual Basic

  • Create and run macros to automate complex and repetitive tasks.
  • Use the Visual Basic Editor to edit a macro and copy a macro to another workbook.

Unit 25 - Accessibility and language features

  • Modify worksheets to conform to accessibility guidelines.
  • Use language features to prepare workbooks for an international audience.

 

Assessment

To earn the MOS certification you must sit a separate examination (Excel 2013 77-420). The exam is in a multiple choice/multiple answer format. It must be sat at a registered Certiport testing centre. You can locate testing centres and schedule exam appointments via Prodigy UK (www.prodigyuk.co.uk), where you can also buy exam vouchers for £65.00.

Please note that you may be charged by the exam centre an additional proctoring fee of approximately £20 for sitting the exam.