Excel Office

Advanced Excel – Data Analysis, Power Query and Pivot Tables

Advanced Excel: Power Query, Pivot Tables, lookup functions, conditional formatting, and dynamic dashboards.

Course description

Advanced Excel: professional data analysis with Power Query and Pivot Tables

If you already use Excel for the basics but know you can do much more, this is the course you've been waiting for. In 31 complete lessons I take you from advanced CSV imports all the way to Pivot Tables, covering Power Query, advanced lookup functions, and professional conditional formatting.

What You'll Learn

  • Power Query: importing CSV, Excel, web tables and merging multiple files automatically
  • Data cleaning: removing duplicates, validating input, handling empty cells and dropdown lists
  • Advanced functions: VLOOKUP, INDEX, MATCH, HLOOKUP
  • Logical functions: IF, IFERROR, AND, OR in combination
  • Aggregations: SUMIF, COUNTIF with complex criteria
  • Conditional formatting: data bars, custom rules, duplicate highlighting
  • Pivot Tables: creation, complex aggregations, calculated fields, groupings
  • Pivot Charts for dynamic dashboards
  • Structures, advanced filters, custom sorts and circular references

31 Lessons, a Complete Learning Path

Each lesson has a precise objective and a concrete example. You can follow the course from start to finish or use it as a reference when you need a specific feature. By the end you'll have the tools to build reports and dashboards that make a real difference in business.

Who This Course Is For

Those who know Excel but want to move to the next level — analysts, controllers, sales teams, HR, and anyone who works with spreadsheets every day.

Lessons list

I'll explain what a CSV (Comma-Separated Values) file is, why it's become the de facto standard for data exchange, and what variations exist, from UTF-8 encoding to different delimiter types.

Guarda lezione

Let's walk through importing a CSV table into Excel with Power Query step by step: I'll show you how to load the file, preview the data, and prepare it for analysis in just a few clicks.

Guarda lezione

I'll show you how to automatically import and merge multiple CSV files into a single Excel table using Power Query: an essential technique for anyone managing data from multiple sources who wants to keep everything always in sync.

Guarda lezione

Let's see how to dynamically link an external Excel file with Power Query: I'll show you how to select which sheets and tables to import and how to refresh the data with one click whenever the source file changes.

Guarda lezione

I'll show you how to import an HTML table directly into Excel from a web page using Power Query: you'll learn how to access online data, select the right table, and refresh it automatically going forward.

Guarda lezione

I'll show you how to remove duplicates from your data safely, using both Excel's classic built-in tools and Power Query, helping you avoid the most common mistakes and keep your datasets clean and reliable.

Guarda lezione

I'll show you how to apply numeric data validation to an Excel table to block incorrect entries and keep your datasets consistent, using simple and customizable rules.

Guarda lezione

Let's learn how to create a dropdown list with Excel's data validation: I'll show you how to limit cell entries to a predefined list, making forms far more reliable and easy to fill in.

Guarda lezione

Let's see how to create custom validation rules with formulas to handle complex inputs: I'll explain how to block empty cells, strip unwanted spaces with TRIM, and verify that a field contains a valid email format.

Guarda lezione

I'll show you how to create custom sort orders in Excel to handle qualitative variables like 'Low', 'Medium', 'High' or 'Urgent', 'Normal' — for when alphabetical order isn't enough and you need to define the logical sequence yourself.

Guarda lezione

Let's explore Excel tables and advanced filters: I'll show you how to use them like SQL queries to extract exactly the data you need with multiple criteria, without writing a single line of code.

Guarda lezione

Let's go deeper into Excel table filters with multiple criteria and complex operations: we'll see how to combine conditions, filter on multiple columns simultaneously, and find exactly the records you're looking for.

Guarda lezione

Let's continue with advanced Excel table filters, this time with even more complex criteria: I'll show you how to combine conditions across multiple columns, use logical operators, and handle results professionally.

Guarda lezione

I'll explain VLOOKUP, INDEX, and MATCH — the three most widely used lookup functions in Excel: let's see how to use them individually and how to combine them to build flexible lookups that VLOOKUP alone can't handle.

Guarda lezione

Let's explore HLOOKUP and LOOKUP, Excel's functions for row-based and unstructured range searches: I'll show you how to use them in real scenarios and when they're preferable to VLOOKUP or the INDEX/MATCH combination.

Guarda lezione

I'll show you how to combine IF and IFERROR with VLOOKUP to handle exceptions, conditions, and missing results: a powerful combination for building formulas that never throw errors, even when the lookup value doesn't exist.

Guarda lezione

Let's continue with the AND and OR logical functions used together with IF to build compound conditions: I'll show you how to evaluate multiple criteria at once and write increasingly precise conditional formulas.

Guarda lezione

Let's explore SUM, SUMIF, and COUNTIF — the most widely used aggregation functions in Excel: I'll show you how to total or count values based on specific criteria, building reports that update automatically as data changes.

Guarda lezione

I'll explain how to freeze rows and columns in Excel so you don't lose headers while scrolling through large tables, and which display options to use to make your spreadsheets more readable and professional.

Guarda lezione

Let's explore Excel outlines for organizing data hierarchically: I'll show you how to create levels, collapse or expand groups of rows and columns with one click, and use automatic outlines to simplify complex reports.

Guarda lezione

Let's start with conditional formatting: I'll show you how to automatically apply colors, styles, and icons based on cell values, making your Excel sheets far more readable and visually clear.

Guarda lezione

Let's look at data bars in conditional formatting: I'll show you how to add inline mini bar charts directly inside cells to instantly visualize value distribution and compare numbers without creating a separate chart.

Guarda lezione

I'll show you how to create custom conditional formatting rules using formulas: an advanced feature that lets you highlight complex data based on conditions no predefined rule could ever catch.

Guarda lezione

I'll show you how to highlight entire rows based on one or more cell values using advanced conditional formatting, and how to automatically identify and call out duplicates within a table.

Guarda lezione

Let's explore Excel's AutoFill and smart series: I'll show you how to speed up repetitive data entry, create custom sequences of dates, numbers, and text, and use Flash Fill to transform data by recognizing a pattern.

Guarda lezione

Let's learn how Excel handles relative, absolute, and mixed references in AutoFill: I'll explain the difference between $A$1, A$1, and $A1 with practical examples, so your formulas adapt correctly when copied to other cells.

Guarda lezione

I'll explain what circular references are in Excel, why they occur, and how to handle them: they appear when a formula refers to its own cell, and knowing how to spot and resolve them will save you a lot of trouble.

Guarda lezione

Let's start with PivotTables, Excel's most powerful data analysis tool: I'll show you how to create a Pivot from scratch and how to arrange rows, columns, and values to aggregate data in a meaningful way.

Guarda lezione

Let's go beyond simple SUM in PivotTables: I'll show you how to calculate averages, counts, percentage of total, period-over-period differences, and rankings to extract real insights from your data.

Guarda lezione

Let's explore three key PivotTable features: groupings for dates and categories, calculated fields for adding custom formulas, and layout options for presenting results as clearly as possible.

Guarda lezione

Let's enter the world of PivotCharts, the ideal tool for dynamically visualizing PivotTable data: I'll show you how to create a chart linked to a Pivot, how to change the chart type, and how to use it to build interactive dashboards.

Guarda lezione
Giovanni Pace

Giovanni Pace

Ingegnere Informatico · Pratola Peligna

  • 107 Courses
About the teacher

Sono un ingegnere informatico con oltre 15 anni di esperienza nel settore ICT. Mi occupo di sviluppo software, cybersecurity, data science e intelligenza artificiale, con particolare attenzione all’innovazione e all’automazione dei processi aziendali. Da anni svolgo anche attività di docenza e formazione, creando percorsi pratici e coinvolgenti su programmazione, DevOps, AI e sicurezza informatica.

This course includes

  • Lessons 31
  • Duration 5h 1m
  • Difficulty intermedio
  • Language it
  • Certificate No

Recently viewed