Advanced Microsoft Excel Dashboards with ChartGPT

Discover how to build dynamic Excel dashboards with GPT that mirror Power BI visuals, using slicers and filters on department, gender, and salary.

Create an advanced transportation dashboard in Excel using pub query, Power Pivot, M code, and DAX, with dimensional and fact tables, and GPT-assisted data modeling.

Transform a raw age column into an age group using Power Query M code, grouping into 0–19, 20–29, 30–39, and above 60; explore custom and conditional columns, prompts to GPT.

Transform the fact table by extracting am/pm from the time column and creating time groupings, such as 3 pm to 5 pm, to analyze bus capacity utilization categories.

Use GPT to create a time group and a utilization segment in Power Query; classify routes as under utilized, well utilized, or over utilized and compute utilization percentage.

Create a dynamic custom date table in power query with year, month, weekday, and weekend flags, linked to the fact table, plus a dax calculation table loaded as a connection.

Advance into Power Pivot to store millions of rows, build data models with related tables, use DAX for time intelligence, and create efficient charts and dashboards in Excel.

Learn to create relationships in Power Pivot by linking tables with IDs to form one-to-many connections in the data model, then build pivot tables.

Master pivot tables in Excel with drag and drop, learn when to use sum or count, and use DAX for advanced calculations.

Learn how to create your first dax measures in Excel using PowerPivot, focusing on distinct counts on the transactional (fact) table and understanding table relationships for reliable analysis.

Design and interact with a dynamic Excel dashboard using slicers to filter by criteria, visualize kpis like busiest routes and peak usage, and draw year-over-year insights.

Create and evaluate key performance indicators in Excel by building DAX measures for total riders and average riders per trip, then identify busiest and least busy routes for a dashboard.

Learn to build a dark Excel dashboard background with KPI and charts, remove grid lines, and use shapes, color fills, and precise spacing to create a polished layout.

Create KPI cards for total passengers, average riders per trip, and busiest route, then align, format, and decorate them with shapes and gradients to build a polished dashboard.

Learn to visualize bus utilization by time range in Excel dashboards, creating and customizing cards and bar charts, applying color schemes, sorting data, and adding data labels for peak times.

Identify peak and off-peak hours to optimize resource allocation in an Excel dashboard. Group time, use icons, and build charts to visualize demand and guide bus deployment.

Verify and improve dashboard visuals with GPT-4 by capturing screenshots, then assess readability, color contrast, time representation, data ink ratio, and interactivity against best practices.

Learn yearly analysis in Excel dashboards by calculating year over year change, adding captions, and visualizing with pivot tables and charts for riders vs passengers.

Choose the best visual for year, month, and weekdays in an Excel dashboard using a line chart. Refine with axis removal, data labels, titles, and year-over-year change.

Analyze the weekday trend of total passengers and highlight weeks above the average with a color-coded chart tied to a pivot table and date table for a dashboard.

Create dynamic weekdays charts in advanced excel dashboards by calculating the passengers' average, highlighting weekdays, and displaying above-average percentages using a pivot table and chart.

Analyze the bus utilization rate by classifying buses as overutilized, underutilized, or well utilized, using distinct counts in a fact table and a PowerPivot and DAX visualization.

Analyze riders by am and pm using a pivot table to show percentage of grand total, and design a simple dashboard visualization with shapes and colors.

Explore the second dashboard to analyze average riders per trip, total revenue and trips, riders by gender and routes, plus top and bottom buses, and age, occupation, and peak hours.

Create a background and navigation for the second transportation dashboard in Excel dashboards. Use shapes, icons, color, transparency, and alignment, then link between dashboards for seamless switching.

Explore crafting DAX measures for total revenue in Excel, using sumx, related, and divide to connect fact ridership and trip fee, and calculate total trips and average riders per trip.

Use the free GPT to write a dax total revenue measure with sumx and related, verify it against fact ridership and dim routes trip fee data, and learn the syntax.

Build a total riders distribution by route in an advanced excel dashboard, using formula bar techniques, concatenation, text formatting, and named ranges to highlight top routes with dynamic percentage labels.

Create a custom gender distribution chart from the demographic data by referencing gender, computing percentage shares, and formatting for a dashboard with switching between charts.

Learn how a circular percentage chart represents data, with 10% increments, color-coded fields, and gender breakdowns, and how to apply it to your dashboards.

Create a dynamic Excel dashboard that toggles a single chart between top five and bottom five bosses using form control options, with linked filters and clear axis labeling.

Rename the demographics age group to 60 plus in Power Query, fix the GPT-generated code, and build a chart showing age-group percentages with data labels.

Design a custom curved chart in Excel by creating and formatting shapes, adding data labels from cells, and highlighting the maximum value on a secondary axis.

Format and align a custom chart to achieve balanced spacing and clean visuals, then create a dynamic average age measure from demographics for your dashboard.

Visualize riders by occupation with a pivot table and custom charts to reveal peak times for students, retirees, unemployed, and employed, guiding resource allocation on a dashboard.

Extracts the peak hour for every occupation by filtering by occupation and time, defining dynamic visuals, and formatting results for an Excel dashboard.

Name all the pivot tables created and connect slicers to filter the dashboard by year, weekday, month, and route name and gender, using VBA to enable interactive controls.

Learn how to use GPT to generate VBA code that hides and shows dashboard objects like slicers, by recording macros, assigning macros, and toggling slicer visibility.

Connect and format slicers in advanced Excel dashboards by linking to KPI, month, year, weekday, and utilization category, then customize colors and headers for an interactive, polished dashboard.

Save after using VBA by saving as a macro-enabled workbook; when prompted, browse and choose the macro-enabled file type to preserve the VBA project.

Learn to generate an unfiltered dashboard report by clearing filters, using GPT-4 to extract five top recommendations from dashboard screenshots, and present them in Excel or PowerPoint.

Use GPT to evaluate dashboards against design principles, assess clarity, hierarchy, color contrast, data visualization, and interactivity, then implement improvements with slicers and feedback.

Generate a data dictionary for your data using AI, leveraging GPT-4 or GPT-3.5 and Googlebot, by crafting prompts, processing sample data, and producing a downloadable CSV.

clean and format data, insert a tenure column, and calculate each employee’s years with the company using today for blanks, group tenure into 0–1, 1–3, 3–5, 5–10, and beyond.

create a gpt-driven excel prompt to calculate each employee’s years in the company using date joined in column g and date left in column h, with blanks treated as active.

Learn to group numbers into buckets using GPT, applying a three-interval scheme and zero-specific handling, then compare GPT-4 and GPT-3.5 prompts to generate flexible segments in Excel dashboards.

Create pivot tables by dragging fields to the value area to compute counts, sums, and averages; format numbers with a gpt prompt and design a kpi with a slicer.

Learn to design an Excel dashboard layout with shapes, gradients, icons, pivot tables, charts, and KPI cards for total employees, salary, bonus, and averages.

Create a custom chart to display active and inactive employees, showing their percentages, while refining a pivot table into a clean KPI dashboard.

Learn to use icons to convey gender and orders more effectively than charts, including selecting icons from flat icon, inserting illustrations, formatting colors, and linking icons with pivot table analysis.

Explore using GPT to bucket employee ages into 10-year age groups, create an age group column, refresh data, and build a polished Excel dashboard with charts and cards.

Visualize employee attrition over time with a line chart that separates active and inactive employees, uses year filters, and highlights the maximum point on a clean Excel dashboard.

Segment employees by year of service and highlight neo, who left in less than a year. Use index and match to compute their share and visualize it on a dashboard.

Transform numeric performance and job satisfaction ratings (1–5) into text labels via a GPT prompt, creating a new column, then map with Excel's choose function and visualize with charts.

Learn to switch between charts in Excel to compare department salary payments and employee counts using form controls, pivot tables, vlookup, and dynamic dashboard labels.

Add slicers to a dashboard by selecting pivot table fields like status (active/inactive), gender, and department, then configure report connections to control pivot tables and clear filters at once.

Create a single button to automatically clear all filters on a dashboard by recording a macro and assigning it to a button, using the developer tab and Visual Basic.

Format and arrange slicers in Excel dashboards by hiding headers, adjusting column layouts, and applying color and font styles for a clean interactive filter.

Format slicers by applying fonts to selected and unselected items, italicize unselected and use white for the selected, then adjust fills to match the dashboard and turn off formula bar.

Download the template and follow along to build a dynamic Excel sales dashboard, connecting data with Power Query and Power Pivot to manage relationships and create an updateable Excel dashboard.

Build a simple sales and marketing dashboard in Excel with a hidden filter, chart or map view, and backend calculations to analyze call agents and success rates.