Excel Course Introduction (level 1)

 

Who is the course for?

 

Are you self-taught with gaps in your learning, this course is ideal for you. Or perhaps you need Excel for your work but don't know what it's all about? 

I would thoroughly recommend this course for anyone who needs to know Excel better. You will be surprised how much you didn't know when you attend this course. You will find that your efficiency levels improve tremendously, by saving time on those painstaking tasks of cutting and pasting, scrolling down your page, creating headings on each page, and using Autofill to its full potential. 

 

What the course will cover:

 

• An overview of the screen, navigation and basic spreadsheet concepts
• Familiarity with the Ribbons and what they can do
• Customising the Quick Access Toolbar
• Opening and Saving your workbook 
• Understanding workbooks, worksheets, rows, columns, and cell references
• Various selection techniques of cells
• The mouse pointer and its many functions
• Using navigation techniques around a worksheet, and workbook
• Entering & deleting data
• Moving and copying data
• Paste Special - Pasting values, formats, pictures, links and transposing cells
• Inserting, deleting, changing width and hiding, Rows and Columns
• Using the Fill handle including Autofill and Custom Fill
• Format Painter
• Formulae
• Creating and editing basic formulas such as Addition, Subtraction, Multiplication, and Division
• Gain understanding and using Formulae rules (making sense of BODMAS)
• Using Excel Functions such as: Sum, Average, Max, Min and Counting cells 
• Changing text by using text functions 
• Understanding and using absolutes $ format numbers 
• Formatting and Proofing
• Formatting Cells 
• Number, Font, Time, Date, Currency, Alignment, Merging cells, Borders and Fills
• Conditional formatting (changing cells automatically based on a given criteria)
• Copying and Clearing Formats 
• Printing and Page Setup
• Page Orientation portrait to landscape 
• Adding Headers and Footers 
• Setting a print area for a worksheet
• Shrinking fonts to fit on a page
• Adjusting page breaks using Page Break Preview
• Basic List Management
• Sorting by one column or more
• Filtering your rows to show only certain criteria 
• Using a Format As a Table feature including formulas (follow link to see video clip)
• Charts & graphs 
• Creating a Chart: pie, column or bar
• Editing a Chart: adding labels, a table, colours (or pictures), changing location, or even changing your mind to another chart 
• Working with multiple worksheets
• Freezing panes to see your top row when you scroll down
• Selecting and inputting data over multiple sheets at one time
• Creating a Summary with formulas over multiple worksheets
• Changing, copying and moving worksheet tabs

 

 

Excel Course Intermediate (level 2)

 

Course Outline 

 

Is this course for me? 

 

If you know the basics of Excel, have written formulas and used simple functions. You have created spreadsheets and charts and you feel competent at managing lists, sorting and using the different printing options available, then this course is ideal for you

 

Short recap from the Introduction course 

 

• Quick keys and shortcuts
• Quick Access Toolbar, dialogue box launcher and the status bar
• Using the new Paste Features such as Transpose, Values, image etc. Pasting visible cells only 
• Using Find and Replace to replace unwanted data 
• Using FlashFill 
• Using the Quick Analysis tool
• Using Autofil and displaying text or numbers how you wish. 

 

Sorting and Filtering Data

• Sorting data in ascending or descending order 
• Sorting data using two different fields for example sorting by location and age 
• Using a Format As a Table feature including formulas, filtering, and slicer options

 

Basic Formulae & Functions

• Concept of Formulae 
• Create and editing a formula in Excel including adition, subtraction, multiplication and division 
• Understanding when to use absolute cell references i.e. $B$17
• Understanding and using Excel Functions such as: =SUM, =AVERAGE, =MAX, =MIN, =COUNT, =COUNTA, =NOW(), =SUBTOTAL() or =TODAY(), =DATEDIF() =EDATE(), 
• Creating and using range names to simplify calculations 

 

Using Text functions like: 

• Working with text strings (adding text together) such as =B4&” “&B5
• Using a formula to change text case such as =UPPER(), =LOWER(), =TRIM(), =CONCETANATE, =LEFT(), =RIGHT(), =MID()

 

IF Functions

• Using the IF Function to insert text based on a criteria 
• Create a =SUMIF, =COUNTIF formula 

 

Conditional Formatting

• Using conditional formatting to highlight a cell based on its contents and adjusting formatting to be specific for your needs 

 

Charts

• How to format a chart i.e. change colours, legends and titles 
• Using a secondary axis
• Sparklines

 

Multiple Worksheets

• How to move / copy a worksheets 
• Working with cascading/tiled windows together
• Splitting a worksheet 
• Freezing panes 
• Using formulas over different worksheets 
• Working with multiple worksheets 
• Creating a formula over worksheets 
• Headers and Footers 

 

Linking Data

• Create hyperlinks between worksheets, workbooks, email accounts, webpages, and ranges

 

Data Tools 

• Using Text to Column to change one column data to two or more (to separate information)
• Importing Data from another type of file 
• Removing duplicates
• Using Creating Data Validation and setting parameters (which stops people entering incorrect information showing a warning)
• Creating a drop down list so that you can select items from a filter arrow

 

Consolidation Exercise 

 

If time:

• Using Subtotal feature
• Introduction to Pivot Tables

Comments

• Adding and amending Comments to a cell 
• How to show or hide all of the comments in a worksheet 

Print

• How to print a worksheet with the comments visible 
• Printing titles on every page 

Security

How to add a password to open or modify the spreadsheet

 

Excel Course Further Features (Level 3)

 

Is this course for me?

 

If you have used Excel for analysing data, can use functions and want to know some of the more advanced functions this is the course for you.

 

What the course will cover:

 

A short recap on the basics including shortcuts and Autofill options

Formulas and functions including:

 

• Using absolute formulas ($)
• Sum, Max, Min, Average, Count, CountA
• IF functions including IF, CountIf, and SumIf – plus CountIfs and SumIfs
• Round, Roundup, Rounddown, Int formulas
• If, And, Not and Or functions
• Vlookups and Hlookups for accessing data from lists
• Index and Match functions
• XLookup (new function to 365)
• Unique data function (new)
• Sort function (new)
• Filter function (new) to extract only certain information and paste into a dynamic list
• Textjoin and Concat functions
• Naming ranges and using names in formulas
• Database functions such as DSum, DCount, DMax, DMin
• Formatting and working with dates and times including DateDif, Edate, Now, Today, Workday and Weekday
• Nesting a function within a function
• IFERROR to tidy your #Div/0! Errors
  

Importing data from Text/Database/other external sources

Custom Sorting, Advanced Filtering (to extract information based on a complex criteria, or a unique lists) 

Conditional formatting data based on cell contents and using formula

Groups and Subtotals

PivotTables, Slicers and PivotCharts including Pivot Calculations and sharing slicers

Recording and playing Macros (SQL not included)

Assigning a Macro to a shortcut key, Quick Access Toolbar button, or a Command button

Charts new 365 map chart etc.  

Tracing precedents and Dependents

Using the Evaluate Formula Feature

 

Note: Excel has hundreds of functions and permutations of functions. This course covers some of the more popular and commonly used functions. If you are confident with the above areas and are looking for more information, it may be better to have one to one consultancy based on your areas of need.