Improve your experience. We are very sorry but this website does not support Internet Explorer. We recommend using a different browser that is supported such as Google Chrome or Mozilla Firefox.
This course aims to provide more experienced users with proficient skills in Excel’s three major strands: formulas, list management and charts, as well as providing tips to assist efficiency.
This short course will extend your knowledge of Excel beyond Basic Microsoft Excel: Level 1 and provide you with the skills and knowledge to produce more effective and productive workbooks. Learn essential functions as well as how to manage list data, cover formulas and function techniques, PivotTables, intricate formatting, setting complex printing options and using intricate charting features.
Completion of this course provides sufficient knowledge to progress onto Advanced Microsoft Excel: Level 3.
By attending this ICL training course, delegates will be able to:
Use the fill operations available to fill a data series
Use a range of techniques to work with worksheets
Protect data in worksheets and workbooks
Use common worksheet functions
Understand and create simple pivottables
Create more complex formulas and functions
Apply a range of number formatting techniques to worksheet cells
Apply conditional formatting to ranges in a worksheet
Use goal seeking to determine the values required to reach a desired result
Understand and use excel’s quick analysis tools
Create and work with tables
Use a range of elements and features to enhance charts
Select and change the format of objects in a chart.
Presenter-taught training in a computer lab
Online training via the platform Zoom or MS Teams
Assumes prior skill and knowledge required to create, edit, print and chart simple worksheets, as well as understand ranges and the copying process.
You should be also familiar with the basic functions of a computer operating system such as navigating the environment using a mouse and keyboard, starting applications, copying and pasting objects, formatting text, creating folders, opening and saving files, familiarity with the accessing the Internet and web browsers.
Additional information
Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.
Software
Recommended operating system: Windows or Mac
Recommended software for PC: MS Excel 2013, 2016, 2019, 365
Recommended software for Mac OS: MS Excel 2016, 2019, 365
Recommended browser: Chrome
Designed for existing users of Excel who would like to expand their skills and knowledge and learn more of the techniques associated with creating better and more productive workbooks.
Filling Data
Creating a Custom Fill List
Modifying a Custom Fill List
Deleting a Custom Fill List
Extracting With Flash Fill
More Complex Flash Fill Extractions
Extracting Dates and Numbers
Worksheet Techniques
Hiding a Worksheet
Unhiding a Worksheet
Copying a Sheet to Another Workbook
Changing Worksheet Tab Colours
Grouping Worksheets
Hiding Rows and Columns
Unhiding Rows and Columns
Freezing Rows and Columns
Splitting Windows
Protecting Data
Understanding Data Protection
Providing Total Access to Cells
Protecting a Worksheet
Working With a Protected Worksheet
Disabling Worksheet Protection
Providing Restricted Access to Cells
Password Protecting a Workbook
Opening a Password Protected Workbook
Removing a Password From a Workbook
Essential Functions
Key Worksheet Functions
Using IF With Text
Using IF With Numbers
Nesting IF Functions
The LOOKUP Function
Using Counting Functions
The Round Function
Rounding Up and Rounding Down
Manipulative Functions
The MOD Function
The TODAY Function
The NOW Function
The DATE Function
Using VLOOKUP
Using VLOOKUP for Exact Matches
Using HLOOKUP
PivotTables
Understanding PivotTables
Recommended PivotTables
Creating Your Own PivotTable
Defining the PivotTable Structure
Filtering a PivotTable
Clearing a Report Filter
Switching PivotTable Fields
Formatting a PivotTable
Complex Formulas
Scoping a Formula
Long-Hand Formulas
Preparing for Complex Formulas
Creating the Base Formula
Adding More Operations
Editing a Complex Formula
Adding More Complexity
Copying Nested Functions
Switching to Manual Recalculation
Pasting Values From Formulas
Documenting Formulas
Number Formatting Techniques
Applying Alternate Currencies
Applying Alternate Date Formats
Formatting Clock Time
Formatting Calculated Time
Understanding Number Formatting
Understanding Format Codes
Creating Descriptive Custom Formats
Custom Formatting Large Numbers
Custom Formatting for Fractions
Padding Numbers Using Custom Formatting
Aligning Numbers Using Custom Formats
Customising the Display of Negative Values
Conditional Formatting
Understanding Conditional Formatting
Formatting Cells Containing Values
Clearing Conditional Formatting
More Cell Formatting Options
Top Ten Items
More Top and Bottom Formatting Options
Working With Data Bars
Working With Colour Scales
Working With Icon Sets
Understanding Sparklines
Creating Sparklines
Editing Sparklines
Goal Seeking
Understanding Goal Seeking
Using Goal Seek
The Quick Analysis Tools
Understanding Quick Analysis
Quick Formatting
Quick Charting
Quick Totals
Quick Sparklines
Quick Tables
Worksheet Tables
Understanding Tables
Creating a Table From Scratch
Working With Table Styles
Inserting Table Columns
Removing Table Columns
Converting a Table to a Range
Creating a Table From Data
Inserting or Deleting Table Records
Removing Duplicates
Sorting Tables
Filtering Tables
Renaming a Table
Splitting a Table
Deleting a Table
Chart Elements
Understanding Chart Elements
Adding a Chart Title
Adding Axes Titles
Repositioning the Legend
Showing Data Labels
Showing Gridlines
Formatting the Chart Area
Adding a Trendline
Adding Error Bars
Adding a Data Table
Chart Object Formatting
Understanding Chart Formatting
Selecting Chart Objects
Using Shape Styles
Changing Column Colour Schemes
Changing the Colour of a Series
Changing Line Chart Colours
Using Shape Effects
Colouring the Chart Background
Understanding the Format Pane
Using the Format Pane
Exploding Pie Slices
Changing Individual Bar Colours
Formatting Text