Excel Building Bi With Pivottables

Excel Building BI with PivotTables

What you'll learn

Learn how to master Excel’s PivotTables to analyze, summarize, and present large datasets for smarter, data-driven decision-making.

Discover how to use Excel PivotTables to effortlessly organize, analyze, and report on complex data, turning raw information into actionable insights.

Gain the skills to leverage PivotTables in Excel, enabling you to transform large volumes of data into clear, concise reports for effective decision-making.

Master Excel’s PivotTables to quickly analyze and summarize data, empowering you to present insights that drive informed business decisions.

Requirements

No advanced Excel skills are required, as the course will guide you step by step in mastering PivotTables and data reporting.

Basic Excel Knowledge: Familiarity with Excel’s interface and basic functions like entering data, creating simple formulas, and navigating worksheets.

Understanding of Basic Data Concepts: A basic understanding of data structures, such as tables, and concepts like sorting and filtering will be helpful.

Access to Excel: Make sure you have a working version of Excel installed (Excel 2016 or later versions recommended) to follow along with the course.

Description

Master the art of data analysis and reporting in Excel with our comprehensive course, Data Analysis and Reporting with Excel, led by expert instructor Ken Puls. This course is designed to help you unlock the full potential of Excel's most powerful tools, with a special focus on PivotTables.PivotTables are a game-changer when it comes to managing and analyzing large volumes of data. They allow you to effortlessly organize, summarize, and present complex datasets in a way that is easy to understand, enabling you to draw valuable insights quickly. With just a few clicks, you can transform raw data into actionable reports that support informed, data-driven decisions.Ken Puls, a recognized authority in Excel and data analysis, will guide you through every step of the process, ensuring you not only understand the technical aspects but also how to apply them in real-world business scenarios. Whether you’re looking to track key performance metrics, identify trends, or forecast outcomes, this course provides the tools and knowledge you need to make the most of Excel's capabilities.By the end of the course, you’ll have mastered the skills needed to create dynamic, automated reports and perform advanced data analysis, empowering you to become more efficient and effective in your role. Don’t miss this opportunity to enhance your Excel expertise and transform how you work with data.

Overview

Section 1: Intro - The Value of PivotTables

Lecture 1 Business Intelligence Lifecycle

Lecture 2 Why You Care About PivotTables

Lecture 3 Creating Your First PivotTable

Lecture 4 DEMO - Creating Your First PivotTable

Section 2: Pivot Compliant Data Sets

Lecture 5 Required Data Layout

Lecture 6 Connecting to Your Source Data

Lecture 7 Issues Using Excel Ranges

Lecture 8 Using Excel Tables

Lecture 9 Building PivotTables Against Excel Tables

Lecture 10 Why Excel Tables are Better Than Ranges

Lecture 11 Using Data Direct From Databases

Lecture 12 Sourcing Data Direct From Databases to PivotTables

Lecture 13 Sourcing Data Direct From Databases to a Table

Lecture 14 Sourcing Data Direct From Databases to the Data Model

Section 3: Controlling PivotTable Look & Feel

Lecture 15 Value Field Formats

Lecture 16 Modifying Column Names & Number Formats

Lecture 17 Report Layouts

Lecture 18 Changing PivotTable Layouts

Lecture 19 Grouping Data

Lecture 20 Grouping Data in PivotTables

Lecture 21 PivotTable Styles

Lecture 22 Creating Custom PivotTable Styles

Lecture 23 Sorting PivotTables

Lecture 24 Sorting Methods in PivotTables

Section 4: Creating Compliant Data Sets

Lecture 25 Using Power Query to Get and Transform Your Data

Lecture 26 Cleaning Up A Text File With Power Query

Lecture 27 Appending Data Tables

Lecture 28 Simple Append Operations

Lecture 29 Consolidating Flat Files From Folder

Lecture 30 Consolidating A Folder of CSV Files

Lecture 31 Consolidating Excel Data

Lecture 32 Consolidating External Excel Data Files

Lecture 33 Get Data From The Active Workbook

Lecture 34 Consolidating Data From Within The Active Work File

Lecture 35 UnPivoting Data

Lecture 0 UnPivoting Simple Data Sets

Lecture 0 UnPivoting Data With Subheadings

Lecture 0 UnPivoting Subcategorized Data Sets

Lecture 0 Flattening Data Sets

Lecture 0 Flattening Tables With VLookup

Lecture 0 6 Ways To Join Foundation

Lecture 0 Flattening Tables With Power Query - The Basics

Lecture 0 Join Types

Lecture 0 6 Ways To Join With Power Query

Lecture 0 Sum Count And Others

Lecture 0 Introduction To Changing Aggregations

Lecture 0 Show Values As

Lecture 0 Running Totals

Lecture 0 Difference And % Difference From

Lecture 0 Difference From X And % Difference From X

Lecture 0 Ranking Items

Lecture 0 Ranking Items in PivotTables

Lecture 0 Showing the Top/Bottom X Records

Lecture 0 Displaying the Top/Bottom X

Lecture 0 Calculated Fields

Lecture 0 Working with Calculated Fields

Lecture 0 Calculated Items

Lecture 0 Working with Calculated Items

Section 5: Slicing and Filtering

Lecture 36 Filtering with Classic Controls

Lecture 37 Working with PivotTable Filter Fields

Lecture 38 Filtering With Slicers

Lecture 39 Working With Slicers

Lecture 40 Filtering The Timelines

Lecture 41 Working With The Timelines

Lecture 42 The Show Details Feature

Lecture 43 The Show Details Feature - DEMO

Lecture 44 Keep Your Pivots Looking Nice

Lecture 45 Toggling PivotTable Options

Section 6: Building Pivot Based Dashboard

Lecture 46 Conditional Formatting on Pivots

Lecture 47 Conditional Formatting DEMO

Lecture 48 Keeping Pivots in Sync

Lecture 49 Linking One Slicer to Multiple Pivots

Lecture 50 Tricks For Extracting Key Info

Lecture 51 Extracting Data From Pivots And Slicers

Lecture 52 Working With Pivot Charts

Lecture 53 Building Pivot Charts

Section 7: Data Refresh & Security

Lecture 54 Driving PivotTable Refresh

Lecture 55 Refresh Options External DataSets

Lecture 56 Refresh Options Local DataSets

Lecture 57 How Secure Is Your Data?

Lecture 58 DEMO of Exposing Data

Business Analysts: Professionals looking to enhance their Excel skills for more efficient data analysis and reporting using PivotTables.,Data Analysts: Individuals who want to streamline their data processing and reporting by mastering Excel’s powerful PivotTable feature.,Managers and Decision Makers: Leaders who need to quickly analyze and summarize large datasets to make informed, data-driven business decisions.,Excel Users: Anyone who regularly works with data in Excel and wants to take their skills to the next level by learning how to efficiently analyze and report on data with PivotTables.,Students and Professionals: Individuals aiming to improve their Excel proficiency for career advancement in fields that require data management and reporting.