Courses Details

Who Should Attend?: Existing users of Excel who wish to learn how to use Excel effectively.

Prerequisites: Delegates will need to have a good working knowledge of creating/formatting simple spreadsheets, basic formulas and functions for example AutoSum. You will be self-taught or would of attended our Excel Level 1 course.

Course Objectives: Our Level 2 course, which will see you learning at a steady pace, and will show you how to; create dynamic spreadsheets, create 3D workshops that are linked, use essential advanced formulas, manipulate large data within Excel tables and analyze data through PivotTables and Charts. We will provide you with the tips and shortcuts to change the way you use Excel, saving you valuable time and improving your efficiency.

Course Duration: 2 Days Classroom Training or Online

Course Contents: The subjects below are an outline. If there are any additional subjects you wish to cover, please feel free to call us prior to the course. All of our courses can be tailored to meet your business needs.

Software Versions: We offer training solutions for all versions of Excel - 2003, 2007, 2010, 2013, 2016 and Office 365.

Overview of shortcuts

• Recap of Shortcuts and Tips
• Customizing the Quick Access Toolbar to include useful shortcuts
• Refresh on everyday Formulae
• Recap of Absolute Cell & Relative Cell Referencing

Range Names

• Create Range Names
• Navigate using Range Names
• Print Range Names
• Compose formulas using Range Names • Create Custom Autofill Lists

Creating Spreadsheets

• Creating 3D spreadsheets which have identical design and data on each sheet
• Setup Print design for all sheets, including Headers and Footers
• Copy, Move, Rename, Color Sheet Tabs
• Hide and Unhide Tabs
• Link data from one sheet to another with Formulae
• Group and Edit all sheets

Linking Data

• Copying worksheets
• Copying to New Workbooks
• Linking data from one file to another
• Linking data to a Microsoft Word document

Functions

• IF Statements (includes nested IFs)
• SUM, MIN, MAX, AVERAGE, COUNT, COUNTA
• COUNTIF, COUNTIFS, SUMIF, SUMIFS, DATEDIF, CEILING.MAX, SUBTOTAL,

Conditional Formatting

• Apply Shading, Icon Sets and Data Bars to cells based on criteria
• Formatting for Duplicate or Unique values
• Format Painter

Data Validation

• Setting Restrictions on how Cells are Inputted as Dates, Numbers or Time
• Range Names in Data Validation
• Create Error Messages

Working with Large Data

• Split your Worksheet
• Freeze Panes
• Hiding and Unhiding Columns and Rows
• Filter, AutoFilter and Custom Views
• Sort and Custom Sort
• Removing Duplicates

Date Functions

• Entering dates
• TODAY & NOW
• Customizing Date Formats

Text Functions

• TEXT
• Use the TRIM, PROPER, LOWER, UPPER, LEFT and RIGHT Functions
• Flash Fill vs CONCATENATE
• Text to Columns
• Paste Special Options

Tables

• Creating Tables
• Formatting Tables
• Totals in Tables
• Slicers

Charting

• Creating Charts -Keyboard shortcut / Insert Tab
• Changing the Chart Type
• Formatting Charts
• Sparkline’s