www.platinum-cpd.com Tel: +971 4 44 68 68 5 info@platinum-cpd.com 1
Excel Data Management
(Two Days)
Microsoft Excel is the world’s premier spreadsheet software. We use Excel to analyze numbers, keep track of data, and graphically represent our information. It has an array of powerful tools to help us turn data into usable information – better information leads to better decision making.
This unique and in-depth advanced Excel workshop will take its participants through three phases; the collection phase, the process phase, and the reporting phase. It covers the journey from understanding data import, data validation, and data cleansing to database manipulation, calculation, lookups, what-ifs, database functions, pivot tables, and finally presenting and outputting the results, including worksheet formatting, chart creation, chart editing, exporting information to other applications and managing the printing of large sheets.
The exercises utilize real-life worksheets containing many day-to-day challenges to be resolved. In addition, participants can bring their own worksheets to the workshop to share their challenges and find solutions.
Participants are encouraged to ask as many questions as required to make sure that there are no more doubts on the topics covered.
This advanced workshop endeavours to find the right solutions to real-life Excel scenarios in order to accomplish better work accuracy and efficiency.
All versions of Excel from 97/2003 to 2007/2013 can be utilised in this workshop with references to the different techniques and new features available in each version.
Workshop Objectives
Phase 1 - InputtingUnderstanding Types of Data The Rules of Data Lists
Restricting input with Data Validation Importing Data
www.platinum-cpd.com Tel: +971 4 44 68 68 5 info@platinum-cpd.com 2
Phase 2a - Processing
Mastering Sorting Using Advanced Filter Automating Subtotal Lists
Understanding Database Functions Using Lookup Functions
Using Logical Functions
Phase 2b- Analyzing
Understanding Pivot Tables Working with Pivot Tables Adding Calculated Fields Formatting Pivot Tables
Phase 2c - Presenting
Creating and Modifying Charts Working with Chart Elements
Phase 2d - Auditing
How to Audit Reports Using Goal Seek and Solver Multiple Sheet Consolidation Analyzing Data Tables
Phase 3 - Outputting
How to Export Data Working with AutoFormat Using Page Breaks
Printing large sheets
Workshop Outlines
Day One Introducing Excel
o Starting Excel
o Types of Information o “What’s right is RIGHT!” o Entering Information o The Problem with Dates o Editing Information in Cells o Keyboard Shortcuts
www.platinum-cpd.com Tel: +971 4 44 68 68 5 info@platinum-cpd.com 3
Performing Calculations
o Selecting and Naming Ranges o Using AutoCalculate
o Using Functions and Formulas o Using AutoSum and AutoFill
Coffee Break
Managing Workbook Information
o Managing Information
Clearing Information
Deleting and Inserting Ranges o Moving and Copying Information o Copying Formulas
Understanding Relative, Absolute, and Mixed Formulas
Creating Absolute and Mixed References
Copying Values Only o Using AutoComplete
Importing Information
o Importing Documents
o Pasting Information from Other Programs
Lunch Break
Working with Data in Lists o Understanding Data Lists o Sorting Data
o Creating and Removing Subtotals o Using Data Validation
Restricting Cells to Numbers, Dates, or Times
Restricting Cells to a List of Values
Filtering Data
o Filtering Data
Using AutoFilter and Removing AutoFilter
Working with Filtered Data
Using AutoFilter with Top 10
Creating a Custom AutoFilter
Filtering with a Single Comparison Criterion
Filtering with Two Comparison Criteria o Working with the Advanced Filter
Setting Up the Criteria Range
www.platinum-cpd.com Tel: +971 4 44 68 68 5 info@platinum-cpd.com 4
Filtering Data with the Advanced Filter
Displaying All Data
Coffee Break
Using Lookup and Database Functions
o Looking Up Values in a Table o Working with Database Functions
Counting the Occurrences of a Value
Finding the Sum & Average of Specific Records
Working with Multiple Worksheets and Workbooks
o Working Across Worksheets and Workbooks o Working with Multiple Sheet Formulas o Consolidating Information
o Linking Information Between Workbooks
Day Two
Day One Review
Working with PivotTables
o Creating PivotTables o Editing PivotTables
Updating Information in a PivotTable
Adding Fields to a PivotTable
Removing Fields from a PivotTable
Changing the Layout of a PivotTable
Selecting PivotTable Data o Modifying PivotTables
Grouping PivotTable Data
Hiding and Showing Source Details
Understanding Calculated Fields
Adding Page Fields
Displaying a Specific Page
Displaying Page Fields on Separate Sheets Working with Charts
o Creating a Chart o Creating a Pie Chart
Exploding a Wedge in a Pie Chart o Modifying a Chart
Modifying Chart Options
o Modifying Titles and Labels
www.platinum-cpd.com Tel: +971 4 44 68 68 5 info@platinum-cpd.com 5 o Working with Data Tables
o Changing the Data Range o Working with Data Series o Deleting a Chart
Lunch Break
Auditing Worksheets
o Finding Referenced Cells
o Finding Formulas that Refer to a Cell
Analyzing Data
o Working with the Goal Seek Feature o Working with Solver
Making Projections About Data
o Solving What-If Problems
Using Data Tables
Working with One-Input Data Tables
Working with Two-Input Data Tables
Exporting Documents
o Exporting Workbooks
o Copying Information to Other Programs
Formatting Worksheet Information
o Formatting Numbers and Characters o Formatting Cells and Ranges
o Merging Cell Information
o Copying and Deleting Formatting o Using AutoFormat
Checking and Printing Information
o Checking Spelling in a Worksheet o Finding and Replacing Information o Previewing a Worksheet
o Setting Up the Page
Setting the Page Options and Margins
Adding Headers and Footers
Inserting and Removing a Page Break
Setting Worksheet Options o Printing a Worksheet