Loading Events

« All Events

  • This event has passed.

Advanced Excel 2010

31st October 2017 @ 9:00 am - 4:00 pm

£160
Objectives: This course is designed to show delegates tools and techniques at the higher end of Excel’s capabilities. It should provide them with new skills and an in-depth awareness of the advanced formulas and features that Excel has to offer, helping to maximise its full potential.
Style: A structured, hands-on, tutor-led session using practical examples and exercises.
Prerequisites: Delegates attending this course must have good practical experience using most of Excel’s popular formulas and formatting tools (or have previously attended an intermediate course).
Content:

 

Quick review of essentials

Formulae – quickest & best practice methods
BODMAS rule / Advanced Date functions Absolute Cell Referencing / F4 variations

Formula Auditing

Trace Dependants / Trace Precedents Show / Hide formulas – [F2] key to edit

Goals Seeking

Using Goal Seek to find a required value

More Statistical Functions

COUNTIF & SUMIF (multiple ranges)
COUNT COUNTA COUNTBLANK
MIN MAX AVERAGE

Logical Functions

IF Functions (using Absolute cell references)
Nested IF Functions
AND / OR IF Functions

Named Ranges

Creating & managing Named ranges

Lookup Functions

H & VLOOKUP (using Named ranges)
H & VLOOKUP (using Absolute Cell Refs)

Text Functions

UPPER / LOWER / PROPER
TRIM / EXACT
LEFT / RIGHT / MID
CONCATENATE Text to Columns

Manipulating Data

Navigation shortcuts for databases
Data input best practices – Data Validation Advanced Filtering / Removing Duplicates
Pivot Tables – Creating and modifying
Pivot Tables – Creating calculated fields

Macros

Recording a Macro
Creating Macro buttons

Data Consolidation

Grouping / ungrouping worksheets
Consolidating data on multiple sheets

Protection

Protecting workbooks & worksheets
Protecting cells on a worksheet

Rounding Functions

ROUND / ROUNDUP / ROUNDDOWN / INT

Benefits to the organisation

  • Get the job done quickly and efficiently
  • Avoid time wasting and costly mistakes
  • Portray a professional image

NB: This course is also available as in house course tailored to the needs of your organisation if you have a minimum of 5 delegates (Please enquire for details)

Details

Date:
31st October 2017
Time:
9:00 am - 4:00 pm
Cost:
£160
Event Category:

Organiser

BPI
Phone:
01685 884175
Email:
info@bpigroup.co.uk

Venue

BPI
Venture Wales Building, Depot Rd
Aberdare, RCT CF44 8DL United Kingdom
+ Google Map
Phone:
01685 886357
Website:
http://www.bpigroup.co.uk