Subscribe to the new YouTube channel dedicated for English content
This series contains 14 videos, from how to create and design pivot tables, to the advanced level. The series includes basic skills for data analysts through Excel, including using data visualization tools and preparing reports. You will find the working files in the description box for each video to facilitate the training process.
Watch the first clip to find out the contents of the series
Proper Data Set
Create and name pivot tables
Pivot Table Field List
Aggregations in Values Section
Report planning and design (Design and Layout)
Change values inside the data set
Copy and change pivot table design
Add column/change column header
Update Data Source - add new data & using a Name instead of a Range
Use excel tables instead of ranges or names
How to use keyboard shortcuts to (Create pivot table - Create excel tables - Select large set of data)
Grouping is one of the most important features of pivot tables. Watch this clip to learn how to:
Group dates
Preparing sales reports: weekly, monthly and quarterly
In this clip, we continue to present the advantages of grouping data in pivot tables, with a focus on grouping numeric values
Example 1: Products report by sales bucket
Example 2: Evaluating the performance of salespeople, see also how to use:
Show value as (%)
Conditional formatting with pivot tables
Group similar items to summarize your report
Use data categories created Inside the Pivot Table
Get rid of traditional helper columns technique
Reduce the file size massively
Calculated Fields:
Add/Subtract 2 or more fields
Multiply a Calculated field by a fixed value
Multiply/Divide 2 or more fields
Calculated Items:
Add/Subtract 2 or more items
Multiply/Divide 2 or more items
In the clip you will see how to create a Refreshable Accounts Receivable System using sheet Formulas & Pivot Tables and you will learn how to
use Data Validation to control manual data entry & use sheet functions to auto generate serial number
use Functions to retrieve customer name and to decide if the amount is Dr or Cr
Pivot Tables to build customer statement report and to Arrange the rows order
Use tabular layout for the Pivot Table, and hide subtotals if not needed with one click
Use the filter option to view individual customer statements
Generate 75 Statement: “Report Filter Pages” tool, and single click to refresh and update new data
In this video, you will see how to add calculations outside pivot tables depending on data inside a pivot table, you will learn how to:
Activate “Getpivotdata” function
How “Getpivotdata” function works
Copy & Drag “Getpivotdata” function
Impact on the calculation when “Pivot Table” layout change
In this section, we will review a feature that is not widely used within pivot tables despite its great benefit, which is the "Show Value As". In this lecture, we will explain the following:
Running Total:
From the beginning of the period or from the beginning of the year
As absolute value or As % of the total
Show Value As:
As % of grand total or % of parent
As a difference of, or As a rank
Double Click on a value to analyze
Pivot Tables filters
Sort Labels & Values
Filed List Sort & Search
Activate/Deactivate Autofit Column Width
Show/hide Labels
Custom Styles
Create a Pivot chart form a Pivot Table:
Directly create a Pivot Chart
Save and reuse Chart Template
Slicers & Timeline
Build, organize & format interactive an Dashboard
In this video, you will see how to make pivot tables based on Power Query, this section is a comprehensive introduction to the Power Query feature in Excel:
An overview of how Power Query/Power Query Editor work
From a CSV file: Upload 3 million rows to Power Query
Load excel tables to Power Query
Using Power Query: Clasify data with a helper table (Merge)
Download the query into a pivot table and generate a sales report
The pivot table will be automatically updated when new data added
In this video, you will see how to pivot tables based on Power Pivot. This section is also a comprehensive introduction to the Power Pivot feature, data model, and the DAX language in Excel:
Create relationships between tables
Create a pivot table based on the Excel Data Model
Choose fields from two tables to generate a pivot table report
DAX Formula to create measures inside the Data Model
Attach number format to measures and use them multiple times
