Since 1988, Blue Novation has designed and delivered exceptionally high standard training solutions to businesses in both the private and public sector. The quality of training we deliver is of paramount importance to us.
Our approach ensures that delegates enjoy their training whilst gaining the additional skills and knowledge to become more productive in the workplace.
Excel Pivot Tables and Power Pivot
This course will cover the many features of Pivot Tables and then move onto the more powerful use of PowerPivot and the DAX functions that are used to manipulate the data. Users will be shown how to create data models both within and outside of the PowerPivot environment to enable multiple data sources to be manipulated.
Using PowerPivot involves the use of DAX functions which are written differently from standard Excel Functions. In addition to core Excel functions that users may be familiar with many new functions are available and introduced during this course in order that the enhanced power of PowerPivot can be fully utilised.
Pivot Table Essentials
Layout • Limitations • Data preparation • Slicers and Timelines • Refreshing Data • Pivot Table Cache
Customising Pivot Tables
Changing Report Layouts • Working with Sub Totals • Converting a Pivot Table to Values • Changing Function Type • Changing the Calculation Type
Grouping, Sorting and Filtering
Grouping Dates • Grouping Numeric Data • Creating Bespoke Groups • Sorting and Filtering • Sorting with a Custom Group • Manual Sorts • Special Filters such as Top Five
Calculated Fields and Items
Calculated Fields • Calculated Items • Modifying Calculated Fields or Items • Deleting Calculated Fields or Items • Changing the Order of Calculation • Documenting Formulas • Calculation Constraints
Pivot Charts and Conditional
Creating Pivot Charts • Formatting Pivot Charts • Adding Slicers and Timelines • Creating a Dashboard • Using Conditional Formatting
Analysing Multiple Data Sources
Using Multiple Consolidation Ranges • Using the Internal Data Model • Creating a Relationship • Managing Relationships • Adding/Removing Tables from the Data Model • Creating Pivot Tables from an Existing Data Model
Using Power Pivot
Enabling PowerPivot • Benefits and Limitations of PowerPivot • Connecting to Data Sources • Defining Relationships • Building the Pivot Table
What are DAX Functions? • Getting Started with DAX • Avoiding Implicit Calculated Fields (Measures) • Creating and Amending Calculated Fields (Measures) • Basic DAX Functions • Other Functions – DISTINCTCOUNT & DIVIDE • Calculated Columns • Using CALCULATE, IF, SWITCH FILTER and FIND and other DAX Functions
On Request – Please call for available dates
Scheduled Course Fee
£190 + VAT*
*Public Sector, please email us for pricing information.
Delegates must be experienced with using a mouse and working in the Windows environment. They should have a good understanding of Excel fundamentals, including formatting, navigation, and basic formulas.
Scheduled course · private course · tailored · 1 to 1 (all instructor led)
A comprehensive course manual provides support throughout the course and upon your return to the office.
Not found the course you are looking for?