Complete Introduction to Excel Power Pivot

Learn Power Pivot — Essential Training

About The Course

What you'll learn

  • Importing Data
  • Transforming Data
  • Combine Data from multiple sources
  • Create Relationship using Data Model
  • Create calculated columns
  • Handling Large Data Sets
  • Using External Data sources withing Excel
  • Load the Result

This course includes:

  • 4 hours on-demand video
  • 19 articles
  • 19 downloadable resources
  • Access on mobile and TV
  • Full lifetime access
  • Certificate of completion

Unlock Insights

Do you find yourself struggling with large datasets in Excel? Are you tired of getting stuck with VLOOKUPs in massive spreadsheets? Fear not! Discover the power of Power Pivot to overcome these challenges and simplify your life.

What is Power Pivot?

Power Pivot is an advanced data analysis tool that is integrated into Microsoft Excel. It enables users to analyze large volumes of data from various sources with ease. With Power Pivot, users can create sophisticated data models, perform complex calculations, and generate insightful reports and visualizations.

Course Header with 12 words

A tool from Microsoft, helps to analyze large volumes data from various sources.

The Course Curriculum

- Delimited

- Fixed Width

- Column data format

- Destination

- Skip columns

- Text qualifier

- Merging Queries

- Appending Queries

- Splitting Columns

- Grouping and Aggregating Data

- Filtering Data

- Removing Duplicates

- Formatting Column Values

- Renaming Columns and Tables

- Working with Date and Time Functions

- Pivoting and Un-pivoting Data

- AutoFilter

- Advanced Filtering with Text, Numbers, and Dates

- Using Advanced Filter with Wildcards

- Copying Filtered Data to Another Location

- Combining Advanced Filter with OR and AND Logic

- Filtering Data Based on Date Ranges

- Top/Bottom N Filter

- Filter by Color

- Sorting Data by Multiple Columns

- Sorting Data in Custom Order

- Advanced Sorting Techniques

- Creating Named Ranges

- Managing Named Ranges

- Use Named Range with

- Formulas

- Data Validation

- Conditional Formatting

- Tables

- PivotTables

- Chart

- Macros

- INDEX and MATCH Functions

- Deleting Named Ranges

- Scope of Named Ranges

- Creating Static & Dynamic Drop-down Lists

- Limiting Input to a List of Values

- Limiting Input to Specific Text Patterns

- Validating Dates and Times

- Restricting Input to Whole Numbers

- Validating Email Addresses or URLs

- Using Custom Formulas for Validation

- Creating Dependent Drop-down Lists

- Advanced Technique

- Ensuring Unique Entries

- Handling Input Errors

- Allowing or Disallowing Blank Cells

- Using Data Validation with Named Ranges

- Handling Input Message

- Remove Data Validation

- Text Functions

- CONCATENATE

- LEFT, RIGHT, MID

- LEN

- UPPER, LOWER, PROPER

- SUBSTITUTE, REPLACE

- Logical Functions

- AND, OR

- NOT

- Date and Time Functions

- TODAY, NOW

- DATE, TIME

- DAY,WEEKDAY, WEEKNUM, MONTH, YEAR

- Lookup and Reference Functions

- VLOOKUP

- HLOOKUP

- XLOOKUP

- INDEX

- MATCH

- INDIRECT

- Array Functions

- SUMPRODUCT

- TRANSPOSE

- SUM, AVERAGE

- FREQUENCY

- OFFSET

- Conditional Functions

- SUMIF, SUMIFS

- COUNTIF, COUNTIFS

- MAXIF, MINIF

- IF / IFERROR / IFNA

- CHOOSE

- IFS

- ISBLANK, ISNUMBER, ISTEXT

- XLOOKUP

- FILTER

- UNIQUE

- SEQUENCE

- TEXTJOIN

- CONCAT

- CONCATENATE

- IFS

- MAXIFS

- MINIFS

- FlashFill

- Creating a Pivot Table

- Basic Functionality

- Adding Data to a Pivot Table

- Sorting Data in a Pivot Table

- Filtering Data in a Pivot Table

- Changing Pivot Table Layout

- Refreshing Pivot Table Data

- Renmaing Pivot Tables

- Adding Sub-total

- Advanced Technique in Pivot Table

- Grouping and Ungrouping Data in a Pivot Table

- Creating Calculated Fields/Items

- Creating Pivot Charts from Pivot Tables

- Removing Blank Cells in Pivot Table

- Use of Data Model

- Creating Pivot Tables from External Data Sources

- Creating Multiple Pivot Tables from a Single Data Source

- Connecting Slicers to Pivot Tables

- Using Timeline Filter with Pivot Tables

- Dynamic Data Range

- Coming Soon

- Evaluate Formula

- Show Formula

- Calculate Formula Manual/Automatic

- Refresh Formula

- Cell

- Range

- Sheet

- Workbook

- VBA

- Share Workbook

- Cell

- Range

- Sheet

- Workbook

- VBA

- Share Workbook

- Cell

- Range

- Sheet

- Workbook

- VBA

- Share Workbook

- Cell

- Range

- Sheet

- Workbook

- VBA

- Share Workbook

- Cell

- Range

- Sheet

- Workbook

- VBA

- Share Workbook

- Cell

- Range

- Sheet

- Workbook

- VBA

- Share Workbook

Instructor Image

SONU PANDAY

Microsoft Certified Trainer - 1 Million+ Students

4.6 Instructor Rating
579,498 Reviews
1,823,172 Students
26 Courses

Summary:

  • Over 16 years of extensive experience in Data Analysis, Data Visualization, MIS Reporting, and developing macro systems across multiple tools.
  • Certified Tableau Developer with more than 9 years of expertise in Tableau Desktop and Tableau Site Admin.
  • Coordination at various levels to ensure timely project delivery.
  • Proven track record of initiating and implementing process improvement initiatives to enhance related processes.
  • Possess strong critical thinking abilities and communication skills, with proficiency in quickly grasping and utilizing new concepts effectively.

Overall Career Overview:

Experienced Tableau specialist adept at converting business requirements into impactful visualizations. Skilled in Tableau Server administration, performance optimization, and report publishing. Proficient in SQL queries across MySQL, SQL Server, and Oracle. Expertise in creating Data Lakes to centralize offline data management. Strong ability to define data flows and templates in Excel and MySQL environments. Knowledgeable in implementing user filters for row-level security (RLS) and proposing suitable visualization views aligned with KPIs. Proficient in Tableau features like LOD, Parameter, Sets, and Actions. Experienced in end-to-end automation of data and dashboard processes. Well-versed in training team members on new technologies and BI tools. Expertise in converting excel-based reports to BI visualizations and writing VBA Macros/Python Script for data preparation. Analyzes data requirements and designs visualization solutions to meet client needs. Skilled in data analysis and preparing ad-hoc reports.

Additionally, proficient in Python for data manipulation and analysis, including feature engineering, data mapping, data cleaning, and aggregation. Skilled in creating online platforms using Python, HTML/CSS, and JQuery for data file management with LDAP Authentication. Experienced in Exploratory Data Analysis (EDA) and assisting team members with machine learning algorithms for predictions. Utilized Python libraries such as Pandas, NumPy, Sci-Kit, Scikit-learn, OpenPyXL, and ldap3.

Certificate of Recognition:

  • Received SPOT Recognition Award for exceptional performance in project delivery.
  • Recognized with Certificate of Recognition for demonstrating cooperative and collaborative behavior.
  • Received a Thank-You Letter from the Head of Department for dedication, technical expertise, and successful implementation of new initiatives.
  • Peer Recognition for Outstanding Teamwork Contributing to our success.
  • Recognized by Peers as a valuable colleague.
  • Appreciated for Developing Macro Systems to streamline processes and increase efficiency.
  • Consistently praised by higher management for achieving 100% accuracy in dashboards and ad-hoc reports.

Areas of Expertise:

Data Analytics MIS Data Visualization Excel Power Query Power Pivot VBA Macros Python Tableau Power BI SQL

Professional Experience:

  • ScatterPie Analytics Pvt. Ltd. – Delhi, India
  • Ericsson Global India Ltd. – Noida, India
  • OTSi Pvt. Ltd. – Noida, India
  • Future Retail Pvt. Ltd. – Gurgaon, India
  • Overseas Outsourcing Solutions Pvt. Ltd. – Gurgaon, India
  • Salient Business Solution Pvt. Ltd. – Gurgaon, India
  • Tech Mahindra – Noida, U.P.

Professional Experience in Diverse Industries:

Retail (Apparels/Food Chain/FMCG/Footwear), E-Commerce, NBFC, Electric Vehicle (EV) Industry, Manufacturing.

Academic Credentials:

The highest educational qualifications include a Master of Computer Application (MCA) and a Master's degree in English Literature (MA).

Student Reviews

5.0

Total 3 Ratings

  • 5
    3 Ratings
  • 4
    0 Rating
  • 3
    0 Rating
  • 2
    0 Rating
  • 1
    0 Rating

A
Anonymous
1 year ago

This was my first time taking an online course. The content is excellent, and the instructors are also excellent. How much you learn from this course is pretty much what you put into it.

A
Anonymous
5 years ago

This was my first time taking an online course. The content is excellent, and the instructors are also excellent. How much you learn from this course is pretty much what you put into it.


Leave a Comment

Course Information

₹ 3000
  • Course level:
    Intermediate
  • Instructor:
    Sonu Pandey
  • Lesson: 19
  • Duration: 3 h
  • Last Updated:
    Nov 17, 2024

Material Includes

  • Videos
  • Articles
  • Downloadable Resources

Requirements

  • Basic computer skills
  • Interest in Power Query

Tags

  • Excel
  • Advance Excel
  • Power Query

Target Audience

  • Beginners/Intermediate in Power Query
2024 © All Rights Reserved | Designed and Developed by NewAceAnalytics