• No results found

Excel Data Management (Two Days)

N/A
N/A
Protected

Academic year: 2021

Share "Excel Data Management (Two Days)"

Copied!
5
0
0

Loading.... (view fulltext now)

Full text

(1)

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 - Inputting

Understanding Types of Data The Rules of Data Lists

Restricting input with Data Validation Importing Data

(2)

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

(3)

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

(4)

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

(5)

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

References

Related documents

In each case, panel A graphs the smoothed median, panel B plots the associated contour lines, panel C shows median weekly wages by age for various years, panel D gives wages by year

They contain functions with the model data sources is power pivot database that pivot is selected set and service international certified excel tables in our traffic.. Grouping

• Geodata, model data and unstructured data (documents) can be searched by ArcGIS metadata functionality (using ArcCatalog) respectively by webbased metadata service or File Explorer

* Students are encouraged to pursue specialist industry placements in an internship which provides national and global opportunities to experience a hands-on business

– locality-based least-connection with replication scheduling – destination hashing scheduling. – source

A Wood Destroying Pest & Organism Inspection Report contains findings as to the presence or absence of evidence of wood destroying pests and organisms in visible and

Leisure Cocktail: JL Tangerine Flavored Vodka, Pineapple Juice, Splash Midori, Lemon Juice, and Sweet and Sour. Johnny Love Passion

If you import your data from a Microsoft Excel workbook, a SharePoint site, an Open Database Connectivity (ODBC) database, or an existing Access database, Access will quickly