Advanced Microsoft Interactive and Dynamic Excel Dashboard

Learn to build advanced Excel dashboards by creating relationships in Power Pivot, using DAX, alongside Power Query, and traditional functions to transform data across multiple tables.

Learn to activate Power Pivot in Excel by enabling COM add-ins through file, options, add-ins, then click go to check the add-in, and save the workbook.

Learn how to connect to data sources in Excel using Get and Transform in Power Query, import CSV and workbook data, and transform and clean it before loading.

Learn how to transform data in Power Query using add column versus transform, including merging columns, preserving originals, and loading as a connection or into the model.

Explore how to import multiple tables into Power Query, transform data, and load clean tables from CSV sources, including product and region lookups, with data type adjustments.

Adjust the current workbook's regional settings to United States in Power Query, fix date value errors, and refresh to ensure proper dates and times are loaded without errors.

Master transforming data with Power Query by checking column quality, removing top and bottom rows, promoting headers, setting data types, and choosing columns for clean dashboards.

Learn how to add an index number in Power Query, create a brand lookup, generate a unique brand list, and merge queries with a left outer join to link tables.

Merge queries in Power Query to join product data with sales data via product ID, then compute revenue with a custom column multiplying quantity by retail price.

Append 2020–2022 transactional data into a single fact table and learn how append grows rows versus merge which grows columns. Explore a dynamic folder approach to routinely incorporate new data.

Discover how to append queries by using the folder option, loading only csv files, and automatically updating the report when new files arrive.

Create a custom calendar table in Power Query to analyze data monthly, weekly, and quarterly. Import a calendar lookup, transform data, and add day, weekday, month, year, and weekend columns.

Master dynamic date table creation in Power Query to auto update with the system date, building a rolling calendar with year and month insights for your data analysis.

Move data from a worksheet into Power Pivot, verify data types, and load via create connection only to prevent loading into the worksheet, enabling clean data modeling.

Enable Power Pivot, switch between data view and diagram view, and clean data by promoting headers and removing blanks; then load to Power Pivot to create relationships and lookups.

Discover data normalization in relational databases, understand normal forms to reduce data redundancy, and build robust lookup tables with one-to-many relationships for PowerPivot dashboards and advanced analysis.

Learn to build Power Pivot data models by creating relationships between fact tables and lookup tables, using primary and foreign keys, one-to-many joins, and snowflake schemas for dashboards.

Edit and manage Power Pivot relationships by deleting incorrect links, creating correct mappings between fact and lookup tables, and activating or deactivating relationships.

Discover dax, the data analysis expressions language powering Power Pivot in Excel, and learn to add calculated columns and measures. Explore implicit and explicit measures through practical in Excel demonstrations.

Learn to create a DAX calculated column in Power Pivot, using date functions and today to compute customer ages and buckets, alongside measures, for interactive dashboards.

Create a new calculated column to bucket customer ages using the DAX if function, grouping 14 to 60 into 0 to 30, 31 to 50, and 51+.

Master the DAX switch function to create a calculated column that classifies customer age into groups, such as 0 to 30.

Explore how to compute revenue with a calculated column by multiplying quantity with price retrieved via related, and compare it to using a measure for row versus filter context.

Learn to write DAX measures in power pivot using the measure box, activate power pivot, create and name total revenue, and build the calculation with X, related, and currency formatting.

Compare implicit measures, which auto-sum when you drag fields into a pivot, with explicit measures built in power pivot using count rows and distinct count to control results.

Learn to create a separate measures table in Excel, consolidate measures in a single table using Power Pivot and Power Query, and optimize your data model by hiding unused tables.

Create a cost of goods sold measure using sumx on the fact table with related to bring in the product cost, then derive gross profit by subtracting cogs from revenue.

Use the Dax all function with calculate to remove filter context from gross profit and compute its percentage with divide across age bucket ranges.

Explore using the DAX filter function in PowerPivot DAX to compute gross profit by gender with CALCULATE and a gender filter. See male and female profits show filter segmenting data.

Mark your date table in Power Pivot by selecting the calendar table’s date column and marking it as a date table for accurate modeling and analysis.

Calculate month to date, quarter to date, and year to date in Excel using DAX with a calendar table, creating revenue measures in a pivot table and applying conditional formatting.

Create a ten-day rolling total measure for transactions using calculate and date in period with a calendar, filtered by year. It also works for any measure.

Learn to calculate a ten-day moving average for transactions using DAX, visualize with color scales, and build dashboards and reports that tell a data-driven story.

Explore how to count customers and products using DAX in a Power BI and Excel dashboard, and why counting in the fact table with distinct values yields accurate totals.

Explore a customer profitability dashboard that analyzes top customers, profit by age group, country, and city, with interactive filters to reveal country-specific insights and key metrics.

Turn on view elements, rename canvas to dashboard, insert and resize a colored card, add a header reading customer detail dashboard, adjust font and spacing, and center with no borders.

Create a blank Power Query, paste M-code to generate the last refresh date, load it to the data model, and display a formatted last update timestamp in a dashboard.

Add five dashboard cards by inserting rounded-rectangle shapes, aligning and distributing them, and configuring text for quantity ordered, average order, and total revenue using pivot tables and DAX.

Add icons to the Excel dashboard to visually represent analytics, money, goals, revenue, and gross profit. Adjust colors and layout, select icons, and invest time to tell a data story.

Learn to create and format gradient color cards for a dashboard in Excel, using gradient fills, custom hex colors, alignment, and data-driven cards tied to transactions, products, stores, and customers.

Learn to build a dynamic top N dashboard in Excel by ranking quantities, selecting metric (quantity, revenue, gross profit) with option buttons, and updating charts and titles in real time.

Learn to build a dynamic title in excel dashboards using ifs, concatenate, and counta to reflect top n customers by quantity order, total revenue, or gross profit.

Create and format doughnut charts in Excel, duplicating visuals, applying shadows, labeling profit and customer age group, using DAX age buckets in a pivot table for percentage of gross profit.

Create a map chart in Excel by arranging country data, exporting data from pivot tables, and formatting colors and labels to show total profit by customer country.

Create a dynamic top five profitable and bottom five least profitable cities by country, using a country slicer and pivot tables driven by ground gross profit.

Connect month and year slicers to the pivot table, link multiple slicers across all pivot tables, and adjust show items with no data to ensure accurate filtering.

Explore how to build an interactive excel dashboard from scratch, using slicers, reset buttons, and dynamic visuals to compare monthly revenue and identify top regions and channels.

Build a dynamic Excel dashboard with a total revenue header and month slicer, and use Power Query to add month numbers and year, then refresh to show the percentage difference.

Build an Excel dashboard that compares current month revenue to the previous month using IFS or nested IF, showing percentage change with conditional formatting (green for positive, red for negative).

Learn to build dynamic quantity ordered and transaction cards in an interactive Excel dashboard, formatting and coloring charts, linking slicers, and comparing total revenue across current and previous months.

Build pivot tables to reveal which products contributed most to revenue, including gender-based analyses, and use max, index, and match to identify top products, then visualize findings in a dashboard.

Identify top states to push products to by using a pivot table, dynamic state selection, and index-match with locking, culminating in a map chart that shows revenue contribution by state.

Construct a pivot table to identify the top region and top sales channel. Format revenue, apply currency, and highlight west region with online sales as the leading channel.

Add slicers for product and sales method, connect them to the relevant pivot tables, and arrange slicers in the dashboard to control views while handling monthly view constraints.

Create a single button to clear all slicer filters in an advanced Microsoft Excel dashboard by recording a macro and assigning it to an icon.

Learn how to save your Excel dashboard after using macros, including the warning that VBA workbooks cannot be saved as macro-free, and how to save as a macro version.

Format slicers by hiding headers, setting colors, borders, and fonts; learn hover and selected versus unselected item styles, multi-select, and aligning slicers for a clean dashboard.

Apply final formatting to an Excel sales dashboard by adjusting fonts, creating headers, adding shapes and gradients, and configuring year-based filters to visualize online sales, revenue, and growth.

Clean and transform raw data with power query to build an attrition dashboard from scratch, then load it back to the workbook, showcasing color-consistent visuals, animations, and gender icons.

Transform raw data into a clean, readable format by converting to a table and importing via Power Query, then set data types and review applied steps toward building a dashboard.

learn to remove unwanted columns in power query using choose columns and manage columns, then load the transformed data back to excel with close and load options.

Use Excel Power Query to transform HR data by adding conditional columns for performance status, age bucket, distance status, job satisfaction, and attrition, preparing data for analysis and dashboard creation.

Shape the dashboard canvas by removing gridlines and headings, applying a subtle background, inserting a rounded rectangle with no border, centering with AI aid, and saving your work.

Format your DataTable in Excel by adjusting header width, applying custom colors, borders, and bolding, then copy formatting to other cells for a consistent, polished dashboard.

Create and customize a pivot table to count employees by department, track attrition with yes/no ex-employee indicators, compute department totals and attrition percentage, and display results with cards.

Add and customize shapes to the dashboard environment, align three rounded cards, apply precise colors and shadows, and prepare the attrition dashboard visuals.

Enhance an Excel dashboard by downloading animated GIFs from flaticon.com, inserting them into a worksheet, and using pivot tables and formatting to style visuals.

Create an interactive Excel dashboard to analyze employees by performance status, using Power Query transformed data, filters for attrition, and visuals that show high vs low performance.

Explore building a dynamic attrition dashboard by distance from work using pivot tables and filters, creating a responsive legend that hides when values are absent.

Create an attrition by job role visual in Excel by selecting, sorting from largest to smallest, and formatting charts while keeping the back end clean and dynamic labels.

Format legends in Excel dashboards by selecting the legend, adjusting font type and boldness, changing color, aligning left, and testing updates to enhance readability and dashboard quality.

Explore building an Excel dashboard that visualizes attrition by employee education and by business travel, using pivot tables, chart formatting, category in reverse order, and data labels for clear insights.

Learn to build a custom Excel chart not offered by default using if conditions, vlookup or xlookup, and max, then lock cells, switch rows and columns, and craft a legend.

Create a gender-based attrition chart in Excel dashboards using a custom bar and pie chart, with data labels, color styling, and filter-connected visuals for interactive analysis.

Master calculating the average age of all employees using the average function, handling attrition and current staff data, formatting the result, and placing it on a dynamic Excel dashboard.

Learn to format Excel slicers end-to-end, including captions, heights, colors, borders, fonts, hover and selected item effects, and duplicating and modifying slicers for polished dashboards.

Apply final formatting to your interactive Excel dashboard by adding icons and an arrow, adjusting colors to match charts, and balancing visuals to tell a clear story without clutter.

Import AdventureWorks data with Power Query, build a data model with relationships, and create a dashboard using DAX measures and a custom date table.

download the bonus template and follow along with a dynamic Excel dashboard built in Excel using Power Query and Power Pivot, not Power BI, with a downloadable dataset.