Power Pivot, Relaciones de Uno a Varios y KPIs | Excel Empresarial desde Cero

Welcome to Digital Skills! In this fourth lesson of our Business Excel course, we'll delve into business intelligence and the design of high-impact interactive dashboards, building an automated dashboard step by step. In this session, you'll learn how to connect multiple tables without using slow formulas, create a robust relational data model with Power Pivot, and structure visual cards to present key performance indicators (KPIs). We'll also solve common problems with duplicate databases and blank values ​​live. 📌 What will you learn in this step-by-step tutorial? Activating Power Pivot: How to enable the hidden tab from the Excel COM add-ins menu. Preparing Master Tables: Properly formatting and naming data ranges to integrate them into the model (Sales, Customers, Products, Salespeople, Cities, and Categories). Relational Modeling (Diagram View): The art of connecting tables using "One-to-Many" relationships with common key fields. KPI Structuring: Creating independent pivot tables to isolate metrics for Total Sales, Profit, Quantity Sold, and Average Margin. Dynamic Dashboard Charts: Implementing line charts for time trends and column charts for category analysis. Live Data Auditing and Cleaning: How to correct consistency errors (non-existent dates like February 29th in non-leap years), duplicate records, and the appearance of the annoying "(blank)" field in your final reports. 📂 Download the course materials: [Here you can insert the Drive/Mega link with the sales database file for the session so your audience can practice along with you]. 🔔 Subscribe to Digital Competencies to master the tools that transform a company's data into smart strategic decisions. Video content: 00:00 Introduction: Today's objective and the key KPIs to present 00:30 Why use a relational model with Power Pivot? 01:03 How to activate the Power Pivot add-in from COM options 04:07 Convert data ranges to Table Format and assign clear names 08:28 Load multiple independent tables into the Power Pivot Data Model 14:07 Review: Quick troubleshooting activation issues 18:06 Analyzing the database architecture: The Sales parent table 19:40 Extracting unique values ​​from a column and removing duplicates 21:20 Updating changes from the Excel source to Power Pivot 26:01 Introduction to the Diagram View in Power Pivot 29:39 Theoretical and practical explanation of "One-to-Many" relationships 32:21 Solution to the duplicate error when relating Categories and Subcategories 36:12 Duplicating sheets and restructuring data dimensions in Excel 40:26 How to delete an incorrect relationship in the Diagram View 41:57 Navigation and ordering of the Tables within the Power Pivot canvas 46:48 Difference between regular pivot tables and multi-table pivot tables 51:20 Creating the Dashboard spreadsheet from Power Pivot 57:30 Designing KPI cards: Setting up the Total Sales metric 58:54 Productivity shortcut: Copying pivot tables to isolate KPIs 59:33 Setting up the Profit and Quantity Sold KPIs 01:00:37 Operation change: Setting up Average Margin instead of the sum 01:03:05 The strategic reason for building cards with loose pivot data 01:05:09 Fixing the grouping error: Detecting false dates (February 29, 2025) 01:09:05 Correctly applying the Date format and grouping by Years and Months 01:12:32 Inserting the Dynamic Line Chart for trends Temporary 01:14:40 Inserting a Column Chart for Sales by Category 01:16:32 Data Audit: Detecting why the category "(blank)" appears 01:17:51 Bulk Correction of Erroneous Variables in the Source Sheet 01:21:10 Analysis of Top Products and Trace of Codes Without Master Equivalents 01:25:07 Normalization and Final Cleanup of the Product Database and Closure #BusinessExcel #PowerPivot #ExcelDashboard #DataModeling #PivotTables #AdvancedExcel #KPIs #DigitalSkills