Microsoft Excel specialists

Excel training courses

See dates of all courses
Contact us

  


Related Courses
Wringing the most out of PivotTables & MS Query for Finance




  WHAT YOU GET

Course manual
& Excel files

40  minutes’
hotline time

Excel expertise
28 years’
specialisation

Job-related courses

Power Pivot & Power BI
for Finance


     Duration: 2 days  
     Format: Hands-on, computer provided  
   Arrival and refreshments: 8:45am  
   Start 9:00am  
   Finish: 4:30pm  
  What is Power Pivot?  Play video
  YOUR POWER-PACKED AGENDA

Get started with Power Pivot

  • Install and set up Microsoft's Power Pivot add-in.
  • Import various data sources into the Power Pivot Data Model.
  • Import millions of records into the Power Pivot Data Model.
  • Apply filters to import only a subset of the data.
  • Understand the Data Model and the benefits of in-memory data storage.

Summarise data quickly and effectively in Power Pivot

  • Create a Power Pivot PivotTable from multiple source data tables.
  • Use related columns to side-step VLOOKUP formulas.
  • Quickly add Slicers to PivotTables.

Open new analytical horizons with the DAX formula language in Power Pivot

  • Create calculated columns with DAX functions.
  • Add Measures (calculated fields) to a PivotTable.
  • Learn how Measures are calculated.
  • Use one Measure across multiple PivotTables.
  • Discover new functions such as CALCULATE which turns any formula into a conditional calculation.
  • Use time intelligence functions to calculate YTD for financial year.
  • Compare values from various time periods with DATEADD.

Combine PivotTable power with formula flexibility

  • Quickly convert a Power Pivot PivotTable to CUBE formulas.
  • Write simple SUBTOTALs and reformat your report.
  • Extract Slicer selections using CUBE formulas.
  • Return KPI values with CUBE formulas.

Manipulate and consolidate data with Power Query

  • Reshape and un-pivot data for Power Pivot analysis.
  • Consolidate multiple sheets within a single file.
  • Automate the consolidation of all files within a particular folder-no macros required!
  • Allow user input with function parameters.

Construct an informative Power Pivot Cash Flow forecast

  • Use separate heading tables to structure a report.
  • Create subtotals correctly in a Power Pivot PivotTable.
  • Use Slicers to toggle a report between thousands or millions.
  • Create dynamic DAX Measures to calculate differently on certain report rows.
  • Make an interactive waterfall chart for data insights.

Note that PivotTables and MS Query are standard features of all versions of Excel. The Power BI add-ins, although free, are available only for certain editions of Excel 2010 and 2013; please check the complete list of system requirements for specific details. Microsoft SharePoint is a for-purchase web application platform designed to facilitate collaboration and file management, and provide a central enterprise intranet portal. Although useful as a host for Power BI reports, SharePoint is not required and is used only in brief demonstrations during the course.


Prerequisites

You are familiar with PivotTables and constructing Excel formulas. Knowledge of criteria functions, such as SUMIF and DSUM may help you to get more out of the DAX formula language, but is not required.


  LEARN IN A POSITIVE ENVIRONMENT
 
  • Be assured the advice you get is well-proven and utilised.
  • Feel free to ask questions.
  • Get time away from the office and constant interruptions.
  • Enjoy a friendly environment where you don’t feel you’re holding others up.
  • Instantly apply what you’ve learned on return to work.

Presenters

AbleOwl has a number of different presenters, all of whom are Excel specialists.

See dates of all courses  

  COURSE CANCELLATION POLICY
 
A course booking is considered final when received in writing (online, email, fax or post) by AbleOwl, and can be cancelled up to ten working days before the course. After that, payment is expected in full and no refund will be given. All cancellations must be notified in writing, i.e. post, fax or email. Another delegate may be substituted at any time.