MOS Excel 2013
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.
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.