About This Course
Organizations all over the world rely on information to make sound decisions. With the amount of available data growing on a daily basis, the ability to make sense of all of that data is becoming more and more challenging. Fortunately, this is where the power of Microsoft® Office Excel® can help. Excel can help you organize, calculate, analyze, revise, update, and present your data in ways that will help the decision makers in your organization steer you in the right direction. It will also make these tasks much easier for you to accomplish, and in much less time, than if you used traditional pen-and-paper methods or non-specialized software.
This course aims to provide you with a foundation for Excel knowledge and skills, which you can build upon to eventually become an expert in data manipulation. The Microsoft Excel course is aligned to the Microsoft Office Specialist certification and compromises of three level Essentials, Intermediate and Advanced as follows:
Microsoft Excel Level 1 ( 1 day)
Module 1: Principles of Spreadsheets
After completing this module, the learner will be able to demonstrate an understanding of the principles of spreadsheets.
- Define spreadsheet in terms of its purpose and use
- Name examples of spreadsheet programs
- Provide examples of spreadsheets that can be produced using a spreadsheet application
- Explain the benefits of using a spreadsheet application for producing and working with spreadsheets
- Identify and describe the properties of a spreadsheet in terms of its purpose and use
- Work within organisational standards and procedures with MS Excel
Module 2: Getting Started with Excel
If you are new to Excel and spreadsheets in general, the vast array of features and controls can seem quite daunting. However, once we cover the workings of a spreadsheet and how to deal with the basics, you will be well on your way to becoming an expert in Excel.
- Open Microsoft Excel
- Identify the elements of the Excel interface
- Create a basic worksheet § Navigation options
- Save and close spreadsheets and excel
- Use the help system in Microsoft Excel
Module 3: Performing Calculations
One of the most powerful features in Excel is the ability to perform real-time calculations on data within workbooks. Using these powerful features allows you to save time and reduce errors when trying to calculate complex and simple equations.
- Create formulas in a worksheet
- Demonstrate practices to ensure the integrity of the data
Module 4: Modifying a worksheet
One of the most basic and common tasks that you will complete using Excel is modifying the contents of your worksheets. This topic covers various tools that you can use to work with the data in your worksheets in the most efficient ways possible.
- Manipulate data
- Insert, manipulate, and delete cells, columns, and rows
- Search for and replace data
Module 5: Formatting a Worksheet
Excel contains many of the text formatting tools that you might be familiar with from other Microsoft Office applications. In this topic, we will learn about a variety of different font options, as well as how to use the Format Painter, the mini toolbar, and more.
- Modify fonts
- Add borders and colors to cells
- Apply number formats
- Align cell contents
- Apply cell styles
Module 6: Check and print the spreadsheet
It is often the case that printouts of your workbook (or parts of your workbook) will be required for one purpose or another. In this topic, we will explore how to check for spelling and grammar and use the printing functionality in Excel
- Check spelling and grammar in spreadsheet
- Define the basic page layout for a workbook
- Refine the page layout and apply print options
Module 7: Customizing the Excel Environment
In this lesson, you will begin examining how to customize Microsoft Excel 2016 to suit your specific needs.
- Customize general, language, formula, proofing, and saving options
- Use Excel’s version control features
- Customize the ribbon and the Quick Access toolbar
- Customize the functionality of Excel by enabling add-ins
- Customize advanced and Trust Center options
Microsoft Excel level 2 (1 day)
Module 1: Solve a problem using a Spreadsheet
After completing this module, the learner will be able to prepare and produce a spreadsheet to provide a solution to a given problem.
- Ensure that the spreadsheet produced addresses the given problem
- Work with MS Excel
- Adjust settings to customise the view and preferences of the spreadsheet
Module 2: Creating Advanced Calculations
In this lesson the learner will learn how to create advanced calculations, working with Range Names, formulas and functions.
- Apply range names
- Use MS Excel to apply formulae to provide alternative solutions to the given problem
- Insert Functions in a Worksheet
Module 3: Organizing Worksheet Data with Tables
Tables allow you to use Excel’s powerful organizational capabilities without modifying the data itself.
- Create and modify tables
- Use subtotal features and database functions
Module 4: Visualizing Data with Charts
While pure data can be analyzed by those familiar with this data and the tools that Excel provides, sometimes you need graphical assistance to help interpret it.
- Create charts
- Modify and format existing charts
- Create a trendline
- Create advanced charts
Module 5: Inserting Graphics
While the default appearance of your worksheets can be quite plain, Excel offers you access to a variety of graphical objects that you can use to enhance their visual appeal.
- Insert and modify graphical objects
- Layer and group graphic objects
- Incorporate SmartArt into your workbooks
Module 6: Apply formatting and special effects
You can help improve the readability of a worksheet by applying different types of formatting.
- Use MS Excel to apply formatting and special effects applicable to the given problem
- Manage themes
- Utilize International Symbols
Module 7: Auditing Worksheets
In this lesson you will learn how to evaluate formulas, troubleshoot invalid data and work with the trace cells feature.
- Use MS Excel to evaluate a spreadsheet to comply with the given problem
- Trace cells
- Troubleshoot invalid data and formula errors
- Watch and evaluate formulas
- Create Scenarios
- Perform a What-If Analysis
Module 8: Working with Multiple Worksheets
After completing this module, the learner will be able to work with multiple worksheets to suit the solution to the given problem.
- Format worksheet tabs
- Manage worksheets
- Manage the view of worksheets and workbooks
- Work with Page Breaks
- Printing Large Worksheets
Microsoft Excel Level 3 (1 day)
Module 1: Enhancing Workbooks
Your workbooks can be customized in a number of different ways. In this topic you will learn how to customise in more ways.
- Customize workbooks
- Create and use templates
- Protect files
Module 2: Automating Worksheet Functionality
This section looks at speeding up repetitive tasks such as adding automatic worksheet functionality to spreadsheet
- Update workbook properties
- Create and edit a macro
- Apply conditional formatting
- Add data validation criteria to a workbook
Module 3: Analysing and Presenting Data
This section looks at analysing data and presenting data in an easy to understand manner.
- Create Sparklines
- Create a Data List Outline
- Perform statistical analysis with the Analysis ToolPak
- Create interactive data with Power View
Module 4: Import and Export Data
This section looks at importing and exporting data to and from Excel.
- Export Excel data
- Import a delimited text file
- Integrate Excel data with the web
- Create a web query
- Import and export XML data
Module 5: Managing Large Workbooks
After completing this module, the learner will be able to consolidate, link and sort data within spreadsheets.
- Consolidate data
- Merge workbooks
- Sort and filter data
Module 6: Analysing data within PivotTables, Slicers and PivotCharts
One of the most powerful tools that you have your disposal when analysing data in Excel is the PivotTable. It is important to understand how they work and gain some fundamental understanding of their purpose before creating PivotTables of your own data.
- Create a PivotTable
- Filter data using slicers
- Analyze data using PivotCharts
Module 7: Analysing Data with Logical and Lookup Functions
While you are now familiar with Excel’s more commonly used functions, you still need to learn about some of its more specialized ones. In this topic you will learn about functions that are specific to text analysis.
- Use text functions
- Use logical functions
- Use lookup functions
- Use date functions
- Use financial functions
- Use specialised functions
The Excel training is aligned to the Microsoft Office Specialist certification.