Power Excel - Level 3

@ Netassist International

+94 11 2 375346, +94 77 0347777

August 20, 2019

12 Hours

LKR 15,500

Suitable for - An existing Excel user who has to manipulate, analyze and report on massive amounts of data.
Prerequisites - A very good knowledge of Excel, including a working knowledge of PivotTables and an understanding of nested functions.

Syllabus

Module 1 - Getting started with PowerPivot

  • Start Excel & launch the PowerPivot window
  • Navigating the PowerPivot window
  • Exploring the PowerPivot tab and field list in Excel

Module 2 - Adding data to PowerPivot

  • Data sources and types supported in PowerPivot workbooks
  • Importing data (from pre-prepared file)
  • Add Data by using Excel Linked Tables

Module 3 - Preparing data for analysis

  • Working with Tables and Columns
  • Filtering and Sorting Data
  • Creating Relationships Between Tables
  • Creating and Working with calculations

Module 4 - Data; formatting and layout

  • Creating, deleting a table
  • Rename a Table or Column
  • Set the Data Type of a Column
  • Hide or Freeze Columns
  • Undo or Redo an Action
  • Sorting and filtering data in a table

Module 5 - PowerPivot and relationships

  • Understanding Relationships
  • Create a Relationship Between Two Tables
  • View and Edit Relationships
  • Delete Relationships
  • Troubleshoot Relationships

Module 6 - Calculations in PowerPivot

  • Overview of Data Analysis Expressions (DAX) language
  • Building Formulas for Calculated Columns and Measures
  • Understanding the Use of Relationships and Lookups in Formulas
  • Understanding Aggregations in Formulas
  • Filtering Data in Formulas
  • Recalculating Formulas

Module 7 - Creating PivotTables, Charts & Reports

  • Create a PivotTable or PivotChart Report
  • Create a Reporting Services Report with PowerPivot Data
  • Create a Measure in a PivotTable or PivotChart
  • Create and Change the Field Layout in a PivotTable or PivotChart Report
  • Delete a PivotTable or PivotChart Report
  • Filter Data using Slicers
  • Work with Relationships in PivotTables
  • Change the Display Language

Module 8 - Overview of Key Performance Indicators (KPI's) in PowerPivot

  • Retail sales KPI case study
  • Sales per hour, average
  • Conversion rates
  • Wage to sales ratio

Module 9 - Dashboards basics

  • Ideas on sourcing the relevant financial and non-financial information
  • Excel tools and functions that are vital for dashboards (create templates)
  • Using Pivot table as the dashboard

Module 10 - Key functions for dashboards

  • Offset/VLookup/Aggregate
  • Linking cells to text box/Linking cells to chart labels
  • Comparing time series
  • Assessing seasonality in a business
  • Mixed metrics and KPI's
  • Comparing year on year results
  • Showing actuals versus budgets versus forecasts
  • Budget Forecast

Level
Commencement
August 20, 2019
Closing Date
August 20, 2019
Location
Colombo , Kandy
Category
Address
No 308 - 310 , R A De Mel Mw , Colombo 03

Send Inquiry



Scholarship Alerts!

Type "REG coursenet" and SEND to 77000

Get latest scholarships, offers & academic event alerts to your phone via SMS.

Service is available for Dialog, Mobitel, Hutch & Airtel users. Monthly (Rs. 30 + tax).


Related Courses