Microsoft Excel – Managing Big Data with Pivot Tables & Power Pivot
Overview
This programme seeks to lessen significantly the burden that comes with managing large data sets. It presents users with several options ranging from simple pivot tables to built-in business intelligence (BI) tools and features in Excel. The programme teaches users to make presentations using the most relevant data through a dynamic mix of reports.
Objectives
At the end of the programme, participants should be able to use simple pivot tables, graduate to power pivot and progressively build on the knowledge to collect and process data from multiple sources; discover and combine data in data mash ups; create data models, explore, analyse and visualize data.
-
Intermediate
-
18 Hours
GHC 1000
Per Participant
Content
Pivoting Data
- What is a PivotTable?
- Creating a PivotTable
- Using the PivotTable Tools Tabs
- Adding and Removing Data with the Field List
- Changing the Field List Layout
- Pivoting Data
- Expanding and Collapsing Data
- Filtering Data
- Sorting Data
- Grouping Data
- Refreshing Data
- Editing the Data Source
- Modifying Fields and Labels
- Modifying Values
- Using the Layout Group on the Design Tab
- Applying a Style to a PivotTable
- Changing PivotTable Style Options
- Manually Formatting a PivotTable
- Using the PivotTable Options Dialog
- Creating an Empty (Classic) PivotTable Frame
- Switching an Existing PivotTable to a Classic Layout
- Adding Data
- Pivoting Data
- Creating a PivotTable Based on External Data
- Refreshing External Data
- Creating a Slicer
- Using the Slicer Tools Tab
Charting Pivoted Data
- Creating a PivotChart from Scratch
- Creating a PivotChart from Existing Data
- Adding Data to your Chart
- Pivoting Data
- Using the Design Tab
- Using the Layout Tab
- Using the Format Tab
- Using the Analyse Tab
- Renaming Fields
- Changing the Chart Type
- Applying a Chart Style
- Manually Formatting Chart Elements
- Changing the Layout of Chart Elements
- Creating a PivotChart Based on External Data
- Creating a Slicer
- Creating a PivotTable and PivotChart from a Scenario
- Creating PivotCharts with PowerPivot Data
- System Requirements
- Downloading and Installing PowerPivot
- Importing Access Data
- Importing Excel Data
- Integrating Data with Relationships
- Creating a PivotTable with PowerPivot Data
- Learn about queries (Power Query add-in in Excel 2013 and Excel 2010), and build an Excel data model from a single flat table.
- Learn how to import multiple tables from a SQL database, and create an Excel data model from the imported data.
- Create a mash-up between data from text-files and data from a SQL database.
- Get the details on how to create measures to calculate for each cell, filter context for calculation, and explore several advanced DAX functions.
- Find out how to use advanced text query to import data from a formatted Excel report.
- Perform queries beyond the standard user interface.
- Explore ways to create stunning visualizations in Excel.
- Use the cube functions to perform year-over-year comparisons.
- Create timelines, hierarchies, and slicers to enhance your visualizations.
- Learn how Excel can work together with Power BI.
- Upload an Excel workbook to the Power BI service.
- Explore the use of Excel on the mobile platform.
Course Reviews
Facilitator(s)
Richard Lamptey
Richard Odartey Lamptey has worked in the private sector for a period spanning over eight (8) years. He has consulted...
