Intermediate Excel

The Intermediate Excel 2016 training course builds on the techniques and concepts presented in the Excel Introduction course. It focuses on how to work more efficiently with calculations, how to further enhance the appearance of the data in the spreadsheet and introduces some data analysis features of Excel.

This course will demonstrate how to control more complex formulae and functions and how to use formulae auditing. It will cover the use of various tools to analyse data such as sorting, filtering and a basic introduction to Pivot tables. You will also learn how to organize and present your data using tables, charts and conditional formatting.

This course is designed for Microsoft Excel users who wish to extend their knowledge and skills beyond building simple workbooks.

WORKING WITH MORE ADVANCED CALCULATIONS AND FUNCTIONS
Use of Brackets in Calculations.
Use of Absolute References (Dollar Signs) In Calculations
Building “3-D” Calculations by Linking Formulas across Sheets or across
Workbooks. Controlling 3-D Links.
Using Formula Auditing
Using some Statistical Functions – Average, Max, Min, Count and CountA
Use of Dates in Calculations.
Some basic Date and Time Functions
An introduction to Logical Functions IF, AND, OR
Basic Use of VLOOKUP and HLOOKUP

USING EXCEL TABLE FEATURE
Advantages of Table feature and applying it to data.
How to working with Data Tables
Creating Calculated Columns
Filtering and Sorting in Tables

CONDITIONAL FORMATTING
How to applying basic Conditional Formatting
Using “Traffic Lights” Conditional Formats
Sorting and Filtering based on Conditional Formats

AN INTRODUCTION TO PIVOT TABLE REPORTS
What are Pivot Table Reports?
Creating a basic Pivot Table Repot
The Design Window and how to use it
“Pivoting” and Refreshing the Data
Using the Pivot Table Ribbons

WORKING WITH GRAPHIC OBJECTS
More Advanced Charting:
Formatting Charts.
Adding trend lines to Charts.
Use of Spark lines.
Dual Axis charts and combination charts.
Using SmartArt
Use of Screenshot Tool