Creating Interactive Excel Dashboards

Master Interactive Dashboard Design and Data Analysis

Data Analysis 4.9(59)5h 27m21 lectures2 sections

What you'll learn

Create Pivot tables and link the relationships between them
Use a Slicer to filter data
Count with and without repetition using Distinct Count
Design a dashboard
Use column and line charts
Add navigation buttons and icons
Show year over year changes and Actual Vs Target
Prepare data with Power Query
Build a complete final project

About this course

This course teaches you how to create interactive dashboards and analyze data with Excel. It starts with an introduction, then Pivot tables and the relationships between them, using the Slicer filter, and counting with and without repetition with Count and Distinct Count. Next you move to dashboard design in two parts, column and bar charts, navigation buttons and icons, charts for comparisons and percentages, Pareto and Combo charts, year over year changes known as YOY, and showing results as Actual Vs Target. You then learn the Power Query data editor in two parts to prepare your data, and you apply everything in a final project of four parts, with the supporting files available to download for practice. The course suits accountants, data analysts, sales and reporting staff and anyone who presents numbers at work, and it helps to have basic knowledge of Excel. By the end, you will be able to turn data tables into a clear interactive dashboard that helps you read numbers and make decisions.

Expected outcomes

Create professional dashboards using Excel.
Analyze data using PivotTables and Power Query.
Design interactive charts that support presentations and reports.
Implement comprehensive final projects using real data.

Learner reviews

These reviews are from learners who bought the content and completed at least 50% of it — there are no reviews from anyone who hasn't gone through the material.

4.9

★★★★★

59 reviews

5★

4★

3★

2★

1★

★★★★★محمد س.·منذ 1 شهرشاهد 100٪
محاضرة مهمة جدا ومفيدة
★★★★★MR S.·منذ 2 شهرشاهد 100٪
GOOD FORMATION
★★★★★Yasser K.·منذ 2 شهرشاهد 50٪
★★★★★خالد ص.·منذ 3 شهرشاهد 50٪
كورس هايل شرح ممتاز واسلوب تحفه
★★★★★Samer N.·منذ 3 شهرشاهد 54٪

5 / 9 تقييم

Course content

Excel Dashboards

20 lectures

The IntroductionGives an overview of the course: why Excel matters for customer data analysis, and the topics covered from sorting and filtering to Pivot Tables, chart types, building a Dashboard, practical case studies and Power Query.6:23
Pivot TableExplains creating a Pivot Table in Excel: inspecting the data first, converting it to a Table via Table Design, adding fields and totals, using Count Distinct with the Data Model, and formatting the table layout.16:39
Pivot Tables & RelationshipsExplains building relationships between tables in a Pivot Table: converting each sheet to a Table, adding them to the Data Model, and creating a Relationship on shared columns to get correct calculations across several tables.9:57
Pivot Table & SlicerExplains the Show Values As options in a Pivot Table for calculating percentages, such as percentage of grand total and of column total, and comparing year-over-year change, with formatting and the differences between each option.6:49
Count & Distinct Count in Pivot TablesExplains the difference between Count and Distinct Count in a Pivot Table, and why the data must be a Table added to the Data Model for Distinct Count to appear and count items without duplicates.4:54
Dashboard design (Part 1)Starts designing an Excel Dashboard: creating a data-preparation sheet from a Pivot Table, showing total numbers as cards using Shapes linked to cells, and formatting font, size and number format.11:05
Dashboard design (Part 2)Continues Dashboard design: inspecting the data and adding it to the Data Model, creating a Pivot Table, showing big numbers in cards linked to cells, and choosing consistent, eye-friendly colors and formatting.25:21
Column - Bar chartExplains adding Column and Bar charts to a Dashboard from a Pivot Chart: sorting the data, reversing the axis order, formatting the chart, adding a title and aligning the elements.35:41
Buttons - IconsCovers two Dashboard tricks: showing a Pivot Table in a collapsible compact layout with control over totals, and adding navigation buttons and icons that jump between pages via hyperlinks, while avoiding too many colors.15:41
PercentageExplains showing percentages in a Dashboard from cells outside the Pivot Table using Paste Link, adding Data Labels that show value and percentage, and applying a custom number format.11:09
Pareto - Combo ChartExplains the Pareto chart and the 20/80 rule in sales analysis: computing the cumulative percentage with Running Total, drawing the Pareto chart, and the alternative Combo Chart that reads directly from a Pivot Table.6:26
YOYExplains calculating year-over-year (YOY) change between two years with a percentage formula and showing it with Donut charts so color reflects increase or decrease, with decimal formatting and arranging the shapes.29:00
Percentage Charts and ValuesExplains representing percentages in a Pivot Table using Percentage of Grand Total and Percentage of Column Total, the difference between them, and defining the question to answer before choosing the method.6:47
Actual Vs TargetExplains showing sales by category and region with a Stacked Column Pivot Chart, and converting values to percentages using Percentage of Column Total to show each category's distribution across regions.5:11
Power Query (Part 1)Explains the Power Query editor: importing data from various sources, cleaning it and splitting columns by delimiter, combining queries with Merge and Append, and adding data to the Data Model so steps apply automatically.22:28
Power Query (Part 2)Shows how to combine and clean two years of quarterly sales data in Power Query: unmerging cells, renaming columns as dates in the correct format, and ordering the months to prepare the data for analysis.33:04
Final Project (Part 1)Explains starting the dashboard from the final data table: defining analysis questions, adding titles with text boxes, reversing axis order, showing each company's sales share in a pie chart, and adding navigation buttons between pages.38:33
Final Project (Part 2)Explains adding a second dashboard page and comparing two years of sales with a percentage change using a calculated field and a combo chart, applying the 80/20 rule with a Pareto chart, and linking pages together.22:09
Final Project (Part 3)Explains building a company comparison dashboard by year using a PivotTable and charts with colored arrows, reordering and formatting the data, and linking the new dashboard to the other pages with navigation buttons.8:18
Final Project (Part 4)Explains sorting sales agents' data numerically by adding an index column in Power Query, displaying it in a PivotChart with data labels and formatting, and finishing the project with pages linked by navigation buttons.11:47