bookmark_border
Category
What Will I Learn?
Excel advanced is a specialised training programme focusing on key Excel functionality for powerful business reporting and decision – making!
Directors, Managers, and anyone else who uses Excel extensively for report writing. Delegates will return to the office after the workshops with an advanced understanding of relevant Excel functionality and practical know-how that will result in significant time-savings each month.
Learning Objectives
Workshop 1: Business Reporting using Formulas and Functions
- Create your own ribbon/s (in MS Excel 2010).
- By viewing multiple windows you can Copy and Move worksheets between workbooks.
- Save time on data capturing in multiple worksheets by using Grouping.
- Consolidating worksheets by using Paste Special.
- Quickly identifying certain transactions and highlighting duplicates by using Conditional Formatting including Data Bars, Icons, etc.
- Ensuring certain cells cannot be selected and formulae protected by using worksheet protection.
- Simplify formulae by using Named Ranges.
- Using powerful functions to develop your report such as Lookup Functions, Text Functions, Logical Functions, Information Functions, Statistical Functions and Mathematical Functions.
Workshop 2: Managing Data lists and Basic Macros
- Sorting your list in any specific order by using a Custom List.
- Using Subtotals to analyze your list to view totals by category.
- Identifying certain data based on a criteria by using Filtering.
- Ensuring you have consistent data capturing by using Data Validation.
- Summarizing your rows and columns by using Group and Outline and create Custom Views.
- Forecast outcomes by creating Scenarios.
- Simplify repetitive tasks by recording, viewing, running, and editing Macros
Workshop 3: Data analysis using PivotTables and Pivot Charts
- Understanding the definitions and layout of a PivotTable.
- Summarizing data by creating a PivotTable.
- Improving the look and feel of the PivotTable by Modifying and Formatting an existing PivotTable.
- Saving time creating a new PivotTable by moving or copying an existing PivotTable.
- Using Pivot Tools to manage your PivotTable effectively (MS Excel 2010 includes using slicers to filter and for connection to another PivotTable).
- Create and customize Sparkline’s (only in MS Excel 2010)
Course Overview - Day 1
1. Data Lists lecture
- Sort Data
- Single Level Sort
- Multi-Level Sort
- Set a Custom Sort Order
- Subtotals
- Sum Function VS Subtotal Function
- Create Subtotals
- More than One Set of Subtotals
- Remove all Subtotals
- Copying Subtotals
- SUBTOTAL Function
- Filter Data
- Apply AutoFilter
- Clear Filter
- Show all Data
- Remove AutoFilter
- AutoFilter Features
- Custom AutoFilter
- Top 10
- Advanced Filter Functions
- Extract Unique Records
- Data Validation
- Text Length
- List
- Decimal
- Copy Data Validation
- Group and Outline
- Group Rows and Columns
- Ungroup Parts of an Outline
- Remove Outline
- Custom View
- Create a Custom View
- Show a Custom View
- Delete a Custom View
- Scenarios
- Create a Scenario
2. Macros lecture
- MACROS
- Security Levels
- Record a Macro
- Record a Macro using the Ribbon
- Record a Macro using the Status Bar Shortcut
- View a List of Macros
- Run a Macro
- Delete a Macro (This Workbook)
- Delete a Macro (Personal Workbook)
- Relative Macro
- Edit a Macro
- Assign a Macro to the Quick Access Toolbar
- Assign a Macro to a Button / Object
Course Overview - Day 2
1. Setup Pivot Tables lecture
- Concept and Layout
- PivotTable Field List
- Turn the PivotTable Field List On/Off
- PivotTable Ribbon
- The PivotTable Tools Options Tab
- The PivotTable Tools Design Tab
- Create a PivotTable Report
- PivotTable Options
- Layout & Format
- Totals & Filters
- Display
- Printing
- Data
- PivotTable Layout
2. Modify Pivot Tables lecture
- Format a PivotTable
- Selecting Elements of a Pivot Table
- Enable Selection
- To Select all Instances
- PivotTable Styles
- Style Options
- Remove / Add and Move Fields
- Remove a Field
- Add a Field
- Move Fields within the Table
- Changes to Source Data
- Changes to the Location / Area of the Source Data
- Field Settings in Row / Column Labels
- Subtotals & Filters
- Layout & Print
- Field Settings in Value Labels
- Summarize Values By & Number Format
- Show Values As
- Create a Custom Name
- Create Calculated Fields
- Add a Calculated Field
- Adjust the Order of Calculation for Multiple Formulae
- View all Formulae that are used in a PivotTable Report
- Edit a Calculated Field Formula
- Delete a PivotTable Formula
- Group and Ungroup Fields
- Group Dates
- Ungroup
- Crating the Custom Group
3. Pivot Tables lecture
- Expand and Collapse Detail
- Show or Hide Expand & Collapse Buttons in a PivotTable Report
- Expand or Collapse Levels of Detai
- Show Details for a Value
- Disable or Enable Show Value Field Details
- Show Value Field Details
- Filter a PivotTable
- Label & Value Filters
- Use Slicers to Filter
- Elements of a Slicer
- Create a Slicer
- Share a Slicer by Connecting to another PivotTable
- Make a Slicer Available in another PivotTable
- Format a Slicer
- Delete a Slicer
- Use Sparklines to Show Data Trends
- Create a Sparkline
- Customise Sparklines
- Control which Value Points are shown
- Change the Style or Format Sparklines
- Handle Empty Cells or Zero Values
- Edit the Sparkline Range
- Clear Sparklines
- Sort Data in a PivotTable
- Quick Sort
- More Sort Options
- Sort Based on the Values
- Report Filter Area
- Multiple Selection
- Show Report Filter Pages
- PivotTable as a Flat Sheet
- Copy / Remove / Move a PivotTable
4. Pivot Charts lecture
- PivotCharts
- PivotChart Terminology
- PivotCharts and Regular Charts
- PivotChart Tools Ribbon
- The PivotChart Design / Layout / Format Tab
- The PivotChart Analyze Tab
- Create a PivotChart
- Start with a PivotTable Report
- Start with a Data List
- Edit a Chart
- Add / Remove Fields
- Change Chart Settings
- Filter Data on the PivotChart
5. Course Conclusion lecture
- Worksheet and Workbook Specifications and Limits
- Calculation Specifications and limits
- Charting Specifications and Limits
- PivotTable and PivotChart Report Specifications and Limits
- Shared Workbook Specifications and Limits