Excel on Steroids Overview

Microsoft Excel 2007 and 2010 topics

Formulas and Functions

Formulas are instructions or expressions that you define according to what you need to calculate; functions are predefined formulas that are readily available in Excel. In this workshop you will learn how to customise and manipulate worksheets and how to calculate values in cells using basic and complex formulas.

By the end of this workshop you will be able to:

  • Create your own ribbon’s
  • Copy and move worksheets between workbooks
  • Capture data in multiple worksheets by using Grouping
  • Consolidate worksheets using Paste Special
  • Quickly identify certain transactions and highlight duplicates using Conditional Formatting including Data Bars, Icons, etc.
  • Ensure certain cells and formulae are protected
  • Simplify formulae using Named Ranges
  • Develop reports using Lookup & Reference, Logical, Financial and other Functions

Data Lists and Macros

Data lists are ideal for storing large amount of similar information. In this workshop you will learn how to sort your data lists, how to view and analyse different variables of your data, and how to automate tasks that you perform repeatedly in Excel to save you time.

By the end of this workshop you will be able to:

  • Sort your list in any specific order using Custom List
  • Use Subtotals to analyse your list to view totals by category
  • Identify certain data based on criteria using Filtering
  • Ensure data is captured consistently using Data Validation
  • Summarise rows and columns using Group and Outline and create custom views
  • Forecast outcomes by creating scenarios
  • Simplify repetitive tasks by recording, viewing, running, and editing Macros

PivotTables and PivotCharts

PivotTables and PivotCharts enable you to make informed decisions about critical data in your enterprise. In this workshop you will learn how to summarise, analyse, explore, and present summary data using PivotTables, and how to graphically represent summary data using PivotCharts which allow you to easily see comparisons, patterns, and trends.

By the end of this workshop you will be able to:

  • Understand the definitions and layout of a PivotTable
  • Summarise data by creating a PivotTable
  • Modify and format PivotTables to improve their look and feel
  • Save time creating new PivotTables by moving or copying existing PivotTables
  • Use Pivot Tools like slicers to manage your PivotTable effectively
  • Create and customize Sparkline’s
  • Create specific formulae within PivotTables using calculated fields
  • Summarise dates into months, quarters and years using Grouping
  • Graphically represent PivotTables by creating PivotCharts

Microsoft Excel 2013 topics

Microsoft Excel 2013 offers new, powerful functionality for intuitive and intelligence business reporting. Learn how to take advantage of these ground-breaking features and functions in the three workshops within Excel on Steroids 2013:

Data and Worksheets

In this workshop you will learn how to view and analyse different variables of your data, and how to customise and manipulate cells, worksheets and workbooks.

By the end of this workshop you will be able to:

  • Split and combine cells
  • Use conditional formatting to analyse data easier
  • Create hyperlinks
  • Group, view or hide multiple worksheets and workbooks
  • Protect worksheets and workbooks

Formulas and Data Analysis

In this workshop you will learn how to calculate values in cells using basic and complex formulas which are instructions that you define according to what you want to calculate. If you use Office Professional Plus, you will learn how to take advantage of Power View to discover insights about your data with interactive data visualization and presentation features that are easy to use.

By the end of this workshop you will be able to:

  • Understand formulas and functions
  • Use different methods to sum up totals
  • Use cell references and names in formulas
  • Use lookup functions, text functions, logical functions, financial functions, statistical functions and mathematical functions
  • Analyse data
  • Create a summary worksheet
  • Use Power View to create a visual worksheet

PivotTables and PivotCharts

In this workshop you will learn how to summarise, analyse, explore, and present summary data using PivotTables, and how to graphically represent summary data using PivotCharts which allow you to easily see comparisons, patterns, and trends.

By the end of this workshop you will be able to:

  • Give a definition of a PivotTable and describe its layout
  • Create a PivotTable
  • Modify and format PivotTables to improve their look and feel
  • Graphically represent PivotTables by creating PivotCharts