Intermediate Microsoft Excel
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.
Description
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.
Course Objectives
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.
How will this Training Course be Presented?
-
Presenter-taught training in a computer lab
-
Online training via the platform Zoom or MS Teams
Prerequisites
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
Who is this Training Course for?
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.
The Course Content
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