|
MICROSOFT EXCEL XP – Data Analysis and Reporting
Course Aims:
The course will focus on the capability of Excel to handle very large amounts of data which can be analysed, manipulated and then presented into professional looking reports. Excel can be used to download data from many applications such as bespoke databases and many of its features such as AutoFilters, Advanced Filters, SubTotals, Outlines and MS Query can be used to analyze the result.
Contents:
Databases
Copy and Paste from External Source Files
Exporting Data from MS Access
Importing Text Files
Text to Columns Wizard
Applying AutoFilters
Advanced Filters
Extracting Data with set Criteria
AND and OR Logic
Selecting Visible Data Only
Sorting Data
Multi-level Sorting
Applying automatic SubTotals
Using SubTotal Functions with AutoFilters
Pivot Tables
What are Pivot Tables
Pivot Table and Pivot Chart Wizard
Pivot Table Layout
Update and Refresh
Modifying the Appearance
Applying Number Formats
Calculation Types and Field Settings
Calculated Fields
Group and Sort Data
Extracting Detail
Hiding and Showing Detail
Pivot Charts
MS-Query
Linking to External Data
Link Management
Adding, Moving and Deleting Fields
Creating and Saving Queries
Changing the Look of Data in the Data Pane
Sorting Data
Specifying Criteria
AND and OR Logic
Specifying Criteria using Expressions
Create OLAP Cubes
Working with Data
Search for Values within a list
VLOOKUP( )
HLOOKUP( )
Database Functions DSum( ), DMax( ) etc..
Array Functions
Group and Outline
Link with MS Access
MS Access Queries
Query Joins
Target Audience:
This course is essential for anyone needing to analyse data. It explores the database features of Excel that simplify the analysis of large amounts of information.
Assumed Knowledge:
Attendees must have a thorough understanding of file handling, spreadsheet fundamentals and be confident creating simple functions and formulas.
Duration: 1 Day
Suggested follow-up course:
Excel XP - Specialists
printer friendly version
|
