Microsoft Excel
Data Analysis
Objectives
In this session you will learn some of the features Excel offers for data analysis, such as consolidating and summarising data and Pivot Tables. You will also learn how to use Excel’s table and database capabilities to extract, filter and subtotal information.
By the end of this course you will be able to:
Work with tables and ranges to summarise, analyse and filter information
Build and modify Pivot Tables for easy data analysis
Create and work with names for data ranges
Consolidate data from multiple worksheets and workbooks
Prerequisities
This course is suitable for experienced users of Excel. Those attending should be confident in creating and editing worksheets and writing basic formulas.
Content
Working With Data Ranges
Creating A Database
Sorting An Ordinary Data Range
Creating A Sub Total Outline
Removing Duplicates
Analysing A Data Range
Using AutoFilter
Setting Custom AutoFilter Options
Using The Search Filter
Setting The Database Back To Show All
Turning Off AutoFilter
Using The Subtotal Function
Organising And Analysing Data Using Tables
Creating A Table From Scratch
Creating A Table From Existing Data
Table Formatting
Adding Calculations Into Table Columns
Adding A Table Totals Row
Filtering And Sorting Data In A Table
Converting A Table To An Ordinary Data Range
Using The Advanced Filter To Analyse Data
Using Advanced Filter
Setting Criteria
Filtering Data Within The Database
Filtering Data To A Separate Area
Filtering Unique Records
Analysing Data With A Pivot Table
Creating A PivotTable
The PivotTable Tools
Recalculating A PivotTable
Report Filter
Filtering In Row And Column Fields
Searching A PivotTable
Creating Subsequent PivotTables
More With Pivot Tables
Drilling Into The Detail Of A Data Field
Show Report Filter Pages
Customising Field Names
Changing Field Formatting
Creating A PivotChart
The PivotChart Tools
Filtering The PivotChart
Using Slicers & Timelines
Creating Named Ranges
What Is A Named Range?
Naming A Range Of Cells
Finding And Highlighting A Named Range
The Name Manager
Keeping Track Of Named Ranges
Summarising Data Using Data Consolidation
Consolidating Data
Creating Links To Source Data
Outline Format
Deleting A Reference
Adding A Source Area To An Existing Consolidation
Editing A Reference