Description

Since its 2013 version, Excel has become a comprehensive tool for data management and analysis. It integrates a decision-making approach and makes it possible to produce professional and interactive dashboards. Four free add-ins to Excel 2013 and Excel 2016 work together to perform all the necessary operations: PowerQuery, PowerPivot, PowerView and PowerMap. Beyond getting started, this course will allow you to facilitate data manipulation, increase your analytical power tenfold and create appropriate visual reports. Thus the exploitation of data will be at the heart of Business.

Who is this training for ?

For whom ?

Management controller, analyst, research manager, any experienced Excel user having to exploit and analyze mass data in Excel 2013 or Excel 2016. This training does not use advanced concepts of databases or programming.

Prerequisites

Training objectives

Import, connect and manipulate a large volume of data from heterogeneous sources using PowerQuery and PowerPivot

Analyze data and build a visual and interactive dashboard using PowerView and PowerMap

Broadcast and share the dashboard

Training program

• Import and transform data with
  • PowerQuery Import data from: a file (txt, csv, xls, ...); a folder; a database (Access, SQL Server, Oracle, SAP, ...); a site Web.
  • Combine data tables by merging or appending.
  • Transform data: Remove rows or columns.
  • Replace, split, merge.
  • Transpose, uncross a table.
• Build the data model with
  • PowerPivot Import data: Apply filters.
  • Set connection properties.
  • Build the relational data model: Define relationships and hierarchies.
  • Complete the data: Use the main DAX functions Add new measures or calculated fields.
  • Add a key performance indicator (KPI).
• Analyze data and build the dashboard
  • Build tables and pivot charts based on PowerPivot.
  • Edit forecast sheets.
  • Create a visual and interactive report in Power View: Insert tables synthesis, multiple graphs.
  • Add filters.
  • Create a geographic map D in Power Map Geolocate the data.
  • Identify the different visualizations.
  • Create the map and animate it using guided scenes.
• 3Broadcast and share the dashboard
  • Protect and share the workbook.
  • Generate the restitution PDF or PowerPoint file.
  • Publish the workbook in SharePoint or Office.