New Logo White mail

Microsoft Excel

Data Analysis

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

Objectives

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

This course is suitable for experienced users of Excel.  Those attending should be confident in creating and editing worksheets and writing basic formulas.

Prerequisities

Content

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