On top of that, we will cover several attributes of pivot tables which are the primary coverage device in Excel. We will additionally take a look at PowerPivot, one more effective device in Excel that will put your pivot tables on turbo. While pivot tables have some restrictions, PowerPivots can be used when you have substantially large information. The tail end of the program is devoted to automating your Excel reports and settlement using macros.
Educating Objectives
Table format
Lookup functions
Text functions
Naming cells
Advanced techniques in creating and customizing pivot tables
Number and cell format
Report layout
Calculation in the value field
Grouping and un-grouping fields
Default and customized sorting and filtering
Sorting using a custom list
Creating calculated field
Filtering using slicers and timelines
Connecting multiple pivot tables to one set of slicers
Customizing reports using the GetPivotData option
Introduction to this new feature
Where does Power Query fit in the Power family?
Get and transform: Link your Excel to external other data sources
Excel files
Text files
Web
SQL
Creating and editing the Query
Get data from Tables, files, and folders
Power Query to clean up data
Practical examples:
UnPivotting data
Working with nested column headers and merged cells
Naming, merging, splitting, and removing columns
Filtering rows in different ways
Transforming and formatting data
Combining queries: Merge and Append
The different types of joining data
Utilizing pivot table wizard
Using an internal data model
Building pivot tables using external data sources
Benefits and drawbacks of PowerPivot
Merging data from multiple tables without using Vlookup
Creating better calculations using the DAX Formulas
Using DAX to create calculated fields
Calculate and Related Functions
Where you cannot use Power Query, use macros
Planning your macro
Creating and recording macros
Editing macros
Introduction to Visual Basic for Application (VBA)
Dates | Venues | Price | Details |
---|
Write a public review