Category

ICT

Course type

Microsoft

Cost per individual

£300 +VAT
Public course

Cost per course

£990 +VAT
Client site, max 10 individuals

Duration

2 Days

Full/Part time

Part Time

Provider

ITS SmartStyle Training

Dates and Locations

24/07/2018 Bradford
27/07/2018 Liverpool
09/08/2018 Barnsley
30/08/2018 Bradford
13/09/2018 Liverpool
27/09/2018 Bradford
11/10/2018 Barnsley
25/10/2018 Bradford
27/11/2018 Bradford
04/12/2018 Liverpool
06/12/2018 Barnsley
06/12/2018 Bradford

Book course

This course is intended for users who need to be able to exploit the full potential of Excel to build powerful spreadsheets, including a wide array of formulae and functions and who also need to use Excel modelling tools to report on results.

Objectives:

By the end of the course users will be able to create sophisticated spreadsheets, which include complex functions. They will be able to utilise analysis tools to summarise and extract data, use pivot tables, scenarios and auditing tools. The use of macros will facilitate the automation of tasks.

Prerequisites: A thorough grounding in building multiple sheet workbooks which include functions, such as Min, Max, Average and simple Ifs. Users should also be able to create simple charts and use filter and sorting options.

Download Outline (PDF)

Naming Cells & Ranges
  • Creating Named Ranges
  • Using Names in Formulas
  • Editing & Deleting Named Cells & Ranges
Powerful Formulae & Functions
  • Statistical Functions (COUNTIF & SUMIF)
  • Logical Functions (IF, IFERROR, AND & OR)
  • Lookup & Reference Functions (VLOOKUP, HLOOKUP, INDEX & MATCH)
  • Date & Time Functions
  • Math & Trig Functions (ROUND & INT)
  • Text Functions
Formula Auditing
  • Tracing Dependants & Precedents
  • Formula Error Checking
  • Formula Evaluation Techniques
Protection
  • Protecting Files
  • Protecting Worksheets
  • Protecting Cells
What-If Analysis & Forecasting
  • Creating, Editing & Deleting Scenarios
  • Scenario Summary Reports
  • Data Tables
  • Goal Seek & Solver
  • Predicting Values with Forecast Sheet
Data Validation
  • Different Types of Validation
  • Input Messages
  • Error Messages
  • Locating Invalid Data
Introducing Pivot Tables
  • Creating a Pivot Table
  • Drilling Down on Pivot Data
  • Sorting,Grouping & Filtering Pivot Data
  • Filter & Timeline Slicers
  • Summary Values & Calculations
  • Formatting Pivot Tables
  • Pivot Chart Reports
Automating Spreadsheets with Macros
  • Recording & Running Macros
  • Deleting a Macro
  • Editing Macros
  • Macro Virus Protection

Book a course

Fill in your information to book this course. The information you provide on this booking form may be passed to funding bodies, in line with the Data Protection Act 1998, and may be shared with other training providers and possible employers for the purpose of administration, statistical and research purposes.

Booking Form

Please select a date and location from the dropdown to add attendees

There are not enough spaces at this location for this many attendees. Please remove 0 attendees before continuing.

spaces remaining

If you just wish to enquire about this course click here