Library

Course: Essential Excel Formulas and Tools

Essential Excel Formulas and Tools

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
About this Course

This course is ideal for those who already use Excel at Introduction level and want to build on the knowledge you know. You will be introduced to a whole range of useful tools, shortcuts and functions. From managing large amounts of data to advanced functions such as VLookup, HLookup and IF Statements. Whatever you use Excel for in your work life, this course will give you the essential skills to take your Excel knowledge to the level expected in the working environment. 

Who is the target audience?

  • Anyone who wants to enhance their knowledge of Excel
  • Users who want to learn more advanced formulas, tools and shortcuts
Basic knowledge
  • You should have a basic knowledge of Microsoft Excel. From creating SUM formulas and using AutoSum, to data entry
  • Microsoft Excel installed (course is compatible with Excel 2007, Excel 2010, Excel 2013, or Excel 2016)
What you will learn
  • Create Advanced Formulas such as VLookup/HLookup
  • Apply Conditional Formatting to change cell colour based on High/Low Value
  • Use IF Statements to perform tests based on a condition
  • Manipulate Large Worksheets to make viewing large amounts of data easier
  • Link data between different spreadsheets
  • Use Date and Text formulas and functions for formatting and calculating dates and text
  • Use Paste Special to adjust how and what you paste. Including how to compare 2 sets of data together
Curriculum
Number of Lecture: 25
Total Duration: 03:33:12
Introduction
  • Introduction  
Module 1 - Using Large Worksheets
  • Hiding Columns and Rows  
  • Freeze Panes  
  • Group and Outline  
Module 2 - Working with Multiple Files
  • Arrange All - Multiple Excel Files  
  • Multiple Sheets - New Window  
Module 3 - Linking
  • Linking Data  
Module 4 - Paste Special
  • Paste Special  
Module 5 - Range Names and VLookup/HLookup
  • Introduction to VLookup and HLookup  
  • Range Names - Applying and Using  
  • VLookup Syntax  
  • Using VLookup  
  • Using HLookup  
Module 6 - IFs and Logic
  • Introduction to IF Statements  
  • Creating IF Statements  
  • Using CountIF  
  • Using SumIF  
  • Using AverageIF  
Module 7 - Conditional Formatting
  • Applying Conditional Formatting  
  • Top/Bottom Rules  
  • Text, Duplicate and Formula Rules  
Module 8 - Date and Text Functions
  • TODAY, NOW and NETWORKDAYS Functions  
  • YEARFRAC Function - Calculate number of years between dates  
  • TEXT, LOWER, UPPER, PROPER, TRIM Functions - Great for formatting text  
  • CONCATENATE  

    When you need to join 2 or more cell contents together. For Example: First Name and Surname. Or Address Line 1, Address Line 2 etc

Reviews (0)