Analysing Data Lists with Microsoft Excel

Who is this course for?

This specialist hands on course is for you if you would like to analyse, extract and manage data lists. If you are unfamiliar or new to formulas or calculations then attending our “Introduction to Formulas and Functions” session would be recommended before attending this course.

What are the benefits

This course will give you the power and confidence to create functions to extract and manipulate data. You will have time to explore the more sophisticated formulas and functions within Microsoft Excel to return and analyse data.

Enquire about this course

Course Enquiry

Please type your full name.
Invalid email address.
Invalid email address.
Invalid Input

Course Duration

1 Day

Course Content

Getting Started

  • Review Function writing
  • Recap basic functions and function structure
  • Keyboard navigation shortcuts
  • Overview of Filters and Advanced Filtering
  • Use the Advanced Filter to create unique lists
  • Filter with Slicers (2013)
  • Multi-level sorting
  • Rank data without sorting
  • Use functions to find the largest and smallest values
  • Work with custom lists to speed up data entry and sorting

Preparing Data

  • Understand design concepts involved in setting up large lists
  • Remove blank rows, columns and white space
  • Convert your data list into a table
  • Add Named Ranges to help readability by others
  • Change text case
  • Join data from multiple fields
  • Extract data into separate fields
  • Create Flash Fill lists (2013)
  • Substitute text
  • Remove duplicate rows
  • Trace formula precedents, dependents and errors
  • Display and highlight all the worksheet formulas
  • Control the creation of new data with Data Validation
  • Quickly expand and collapse data with grouping
  • Consolidate data with 3D formulas
  • Create interactive custom formatting rules to highlight data

Analysing Data

  • Create nested IF statements
  • Create compound logical tests using AND, OR, NOT
  • Check for errors using the IFERROR and IFNA (Excel 2013) functions
  • Work with the Math and Statistical functions
  • Return values with the database functions
  • SUMPRODUCT v SUMIF
  • Extract subtotal data
  • Use mixed references in formulas
  • Update values without formulas
  • Create functions to find data
  • Extend formulas with arrays
  • Return data with VLOOKUP and HLOOKUP
  • Find approximate matches with LOOKUPS
  • Use nested lookups to find data from multiple tables
  • Recover data without VLOOKUPS
  • Nest information functions to highlight errors
  • Understand date and time functions
  • Calculate a period of time between two dates

Data Security

  • Overview of Microsoft Excel’s workbook/worksheet protection
  • Protect the structure and layout of worksheets
  • Show only the results not the formula

What our users say

 

  • A very good course, trainer had good knowledge and delivered well
  • I like the fact that you have selected topics/functions on the basis of their interest/possible use - course is much more than pertinent and a better use of time
  • Very friendly and helpful trainer
  • Ray is the only IT Trainer who has ever inspired me with enough confidence to learn effectively
  • It was very good, I really enjoyed it and learnt a lot
  • I will definitely use the Pivot Tables at work in the future! Thank you for such a great training day!
  • Will ensure my team all attend (they all work remotely)
  • Exactly what I was hoping to learn, thanks

  • Contact Us
  • 44 (0) 208 681 1485
  • 44 (0) 7794 0689 6985

E Mail Us