Duration

1 Days

6 CPD hours

This course is intended for

To ensure success, students will need to be familiar with using personal computers and should have experience using a keyboard and mouse. Students should also be comfortable working in the Windows environment and be able to manage information on their computers.

Overview

Upon successful completion of this course, you will be able to create and develop Excel worksheets and workbooks in order to work with and analyse the data that is critical to the success of your organisation.

Description

This course is intended for those who wish to gain the foundational understanding of Excel that is necessary to create and work with electronic spreadsheets. Excel can help you organise, calculate, analyse, revise, update, and present your data.

Getting Started with Excel

• Navigate the Excel User Interface
• Use Excel Commands
• Create and Save a Basic Workbook
• Enter and Edit Cell Data
• Fill Cells with Series of Data (AutoFill and Flash Fill)
• Use Excel Help

Performing Calculations

• Create Worksheet Formulas
• Insert Functions and use Autocomplete
• Reuse Formulas and Functions
• Create an Absolute Reference

Modifying a Worksheet

• Insert, Delete, and Adjust Cells, Columns, and Rows
• Move and Copy Data between Cells
• Search for and Replace Data
• Use Spellcheck and Proofing

Formatting a Worksheet

• Apply Text Formats
• Apply Number Formats
• Align Cell Contents
• Apply and Copy Cell Styles
• Apply Basic Conditional Formatting

Printing Workbooks

• Preview and Print a Workbook
• Controlling Page Layout
• Adding Headers and Footers
• Set Print Titles and Print Ranges

Managing Workbooks

• Manage Worksheets
• Insert and Delete Worksheets
• Manage Workbook and Worksheet Views
• Manage Workbook Properties

 

Duration

1 Days

6 CPD hours

About this course

To ensure success, students should have completed Excel Essentials or have the equivalent knowledge and experience.

Overview

Upon successful completion of this course, you will be able to leverage the power of data analysis and presentation in order to make informed, intelligent organizational decisions.

Description

This course is designed for students who already have foundational knowledge and skills in Excel and who wish to begin taking advantage of some of the higher-level functionality in Excel to analyse and present data.

Working with Functions and Complex Calculations

• Summarising Data using Functions (Sum/SumIf, Count/CountIf etc.)
• Work with Logical Functions
• Work with Lookup Functions
• Work with Date and Time Functions
• Work with Text Functions

Working with Lists and Tables

• Sort and Filter Data
• Remove Duplicate Values
• Group and Outline Data
• Subtotal Data

Visualizing Data with Charts

• Create and Modify Charts
• Use Advanced Chart Features
• Save a Custom Chart Template

Using Tables, Pivot Table Reports and Pivot Charts

• Create and Modify Tables
• Use Functions to Calculate in Tables
• Create a Pivot Table Report
• Manipulate Pivot Tables
• Present Data with Pivot Charts
• Filter Data by Using Slicers and Timelines

 

Duration

1 Days

6 CPD hours

Overview

Upon successful completion of this course, students will be able to enhance productivity and efficiency by streamlining workflow, collaborate with others, and audit and analyse data.

Description

This course is designed for students desiring to gain skills necessary to create macros, collaborate with others, audit and analyse data, incorporate multiple data sources, and import data.

Topics

Working with Multiple Worksheets and Workbooks

• Working with Named Ranges
• Link Cells Across Worksheets and Workbooks
• Use 3D References to Calculate Across Worksheets
• Consolidate Data

Use Formula Auditing and Error Checking

• Reveal Formulas
• Trace Cell Precedents and Dependents
• Locate Errors in Formulas
• Watch and Evaluate Formulas

Reviewing and Protecting Workbooks

• Control Data Entry via Data Validation
• Protect Workbook Access
• Protect Worksheets and Cell Content
• Add and Edit Comments
• Prepare a Workbook for Distribution
• Modify Excel's Default Settings

Using Macros to Automate Workbook Functionality

• Create Macros via Recording
• Run Macros via Buttons and Shortcuts
• Assign Macros to the Quick Access Toolbar and Ribbon
• Assign Macros to Objects
• View Macro Code

Forecasting and Analysis Data

• Use Conditional Formatting to Highlight, Sort and Filter Key Data
• Advanced Conditional Formatting using Formulas
• Create Sparklines to Visualise Data
• Add Trendlines to Charts to Visualise and Forecast Trends
• Use Data Tables and Scenarios to Project Potential Outcomes
• Use Goal Seek to Calculate Outcomes
• Forecast Data Trends Using Solver

Duration

1 Days

6 CPD hours

Overview

Upon successful completion of this course, students will understand the programming environment of Visual Basic for applications and know how to create custom procedures and functions that can expand their ability to use Excel more effectively.

Description

This course is intended for the experienced Excel users that want to gain knowledge of visual basic for applications (VBA).

Prerequisites

To ensure your success in this course, you should be an experienced Excel user who is comfortable creating and working with Excel workbooks, including tasks such as entering worksheet formulas, using absolute and relative addressing, formatting cells, and creating pivot tables and charts. This level of skill could be acquired by taking the Microsoft Excel courses, Parts 1, 2, and 3.

Topics

Introduction to Macros

• Recording Macros
• Absolute and Relative
• How to Run a Macro

Basics of Code Writing

• The VBA Environment (the VBE window)
• Essential Tips, Tricks, Shortcuts
• Understanding VBA and Best Practice

Crucial VBA Objects and Structures

• Variables and Data Types
• Conditionals (If, Select Case)
• Loops (For, Do)

Interaction

• Interact with Data, Sheets, the Excel Application
• Interact with User (Message Box, Input Box)

Errors

• Run
• Editing
• Debuging
• Handeling Errors