Excel Module 2 - Data Analysis and Pivot Tables

Description

Duration: This one day course is run over 2 half days virtually using Teams.

Overview

Our Excel Module 2 – Data Analysis and Pivot Tables course looks at how to handle your data more efficiently.  It looks at creating data to give a more visual appearance and shows tips and tricks for creating your own dashboards.  It looks at how to manage large amounts of data and the best ways to handle and analyse data.

This course is aimed at existing users of Excel who want to increase their skills.  Participants should be able to create and amend worksheets and build simple formulas.

Objectives

By the end of the course you will be able to:

• Manage worksheets and workbooks
• Link information between workbooks and worksheets
• Setup conditional formatting
• Create and manage data
• Filter and sort information
• Setup data validation
• Create and amend pivot tables
• Customise pivot tables

Detailed Content

Formulas review and working with multiple worksheets

• Building formulas and ordering calculations
• Absolute cell references
• Using functions
• Moving and copying worksheets
• Inserting and deleting worksheets
• Grouping worksheets
• Changing the worksheet tab colour

Transferring and linking data

• Linking within a workbook
• Linking between workbooks
• Updating and managing links

Working with data

• Organising data using Excel tables
• Table formatting
• Adding calculations into table columns
• Adding total rows
• Converting to an ordinary range

Sorting and Filtering Data

• Sorting and filtering information
• Using date, number and text filters
• Using the search filter
• Adding slicers

Conditional formatting

• Changing the cell format depending on set criteria
• Databars, Top/Bottom Rules, Colour Scales and Icon Sets
• Creating custom formula rules

Setting up data validation

• Setting number and date validation
• Setting drop down list validation
• Setting custom validation

Analysing information using Pivot Tables

• Creating and modifying a Pivot Table
• Recalculating the Pivot Table
• Filtering the Pivot Table
• Searching the Pivot Table
• Drilling down to underlying data

Pivot Table customisation

• Customising field names
• Changing field formatting
• Pivot Charts
• Using Slicers