Excel Professional

Excel header
Contact Us
Price
£100.00
Start Date:
TBC
Duration
1 day 9am - 16.30pm

Course Sheets

Who the course is for
The course is aimed at employees whose job role includes the regular use of Excel to complete sometimes complex work tasks.
What you will learn
This course will involve a variety of tasks including Flash Fill, Index and Match, Advanced Pivot Tables and Pivot Charts, Power Pivot and Sparklines.
How you will learn
This one day session will encompass learning through both theoretical and practical activity.
Progression
Completion of this one-day course will allow for improved efficiency, knowledge and skills when using Excel and its functions and features on a professional level.

What will I learn?

The course will include a wide variety of tasks including:

Flash Fill

  • Automate data extraction with Flash Fill (CTRL+E)
  • Data Mining

Index and Match

  • INDEX()
  • MATCH()
  • One-way
  • Two-way
  • Closest match
  • Multiple Criteria

Advanced Pivot Tables & Pivot Charts

  • Quick Analysis (CTRL +Q)
  • Setting up a pivot table that counts data
  • All About Slicers
  • Filtering data using slicer / chart / pivot table
  • Add new data to data source – Inserting new row
  • Creating a table from data (CTRL +T)
  • Calculated Fields
  • Using the Timeline tool
  • Report Filter Pages
  • Customising the Design / Creating a new Style

Power Pivot

  • Activating the Power Pivot Tab
  • Adding Tables to Power Pivot Data Model
  • Creating Table Relationships
  • Creating columns with DAX Expressions
  • Move Column
  • Undertaking Calculations
  • Creating a Pivot Table from Related Tables
  • Displaying New Source Data in Power Pivot Tables

Sparklines

  • High & Low points
  • Columns

Array Formulas

  • Linking to a cell
  • Linking a cell to two cells
  • Linking a cell to a column
  • Using & to link to 2 arrays
  • Using CTRL+SHIFT+ENTER
  • Array Formula = e.g. multiply two columns
  • Arrays in XLOOKUP()
  • Array Functions – UNIQUE(), SORT(), RANDARRY()

Macros and VBA Basics

  • Adding the developer Tab to Excel
  • Difference between absolute referencing and relative referencing
  • Step into Macro – VBA Editor

Consolidate

  • Consolidating multiple tables into one table
  • Summarise a table of data
  • Consolidate multiple worksheets into one worksheet

Power Query

  • Importing Data from other sources
  • Transforming Data
  • Loading Data
  • Merge Query

Filled Maps from Imported Data

  • Importing Data from web page
  • Creating a filled map

Basic Forecasting

  • Using SLOPE() function to get gradient
  • Using INTERCEPT() function to get y-intercept
  • Forecasting using Slope and Intercept
  • FORECAST.LINEAR() Function
  • Forecast with Named Ranges
  • TREND() Function

Forecast Sheet

  • Producing a forecast sheet from historical data
  • Adding a Trend Line