IT Training‎ > ‎

Excel Intermediate (level 2)

Excel Intermediate 2010 or 2013

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.
  • Using Find and Replace to replace unwanted data

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() or =TODAY(), =DATEDIF()
  • How to use Autofill to create a series of dates, or numbers i.e. in intervals of 5

Using simple functions like:

  • Working with text strings (adding text together) such as =B4&” “&B5
  • Using a formula to change text case such as =UPPER()

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

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

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
  • Use a filter to just show certain data in a table

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

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) 
  • 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
  • Using the SubTotals feature

Security

  • How to add a password to open or modify the spreadsheet

Consolidation Exercise

 

Only £130 for the day including workbook, lunch and free parking

Comments