Microsoft Excel - Dashboards & Data Analytics

Download the customer orders.xlsx file and clean raw data with Excel functions for the customer info and order info tabs. Build a dashboard in the customer dashboard tab.

Learn to clean inconsistent data by using Excel's proper() function to convert names to proper case, create a new column, and autofill to apply changes across the customer info sheet.

Use Excel's upper function to convert customer IDs to uppercase, ensuring consistent, presentable data for dashboards; create a new column, apply =upper(B2), and autofill down.

Learn how to clean up data in Excel by using paste special to paste only values, convert text to uppercase or proper case, and remove redundant columns without breaking formulas.

Apply the choose array function to map 1, 2, 3 to Speed Express, National Package, and Inland Shipping, creating a shipper name column on the Order Info tab for dashboards.

Learn how to use the text function to extract the month from order dates and format them as month values, enabling dashboard filters and monthly analytics.

Clean and organize data from the customer info and order info tabs, remove redundancies with pest special, and build a dashboard using vlookup to populate contact details from a drop-down.

Format as table in Excel streamlines working with customer info data, creates a named dynamic table like customer info, and allows formulas such as Vlookup to reference the data.

Create a drop-down on the customer dashboard by using data validation set to a list sourced from the customer info tab column A, applied to cell B3.

Explore data retrieval with Excel's VLOOKUP by linking a dropdown to the customer info table, returning contact details such as name and city using an exact-match lookup.

Learn data cleansing in Excel by nesting vlookup inside an if function to replace zeros with hyphens or not applicable, driven by a data validation dropdown.

Explore the limitations of vlookup, such as the first-column lookup requirement and performance, then use index and match together as an alternative to retrieve data like contact names.

Move and clean order data from the Order Info tab to the customer dashboard, using paste special to fix values, prune columns, and rename ship name to customer name.

Format a large order list as an Excel table, name the range (no spaces) for easy reference in calculations or VBA, customize color, and disable filters to streamline data.

Learn how to use Excel's advanced filter to display a selected customer's orders in a dashboard, then extend it with VBA to auto-filter when the customer dropdown changes.

Create a macro to automate advanced filtering in Excel by recording steps, then edit VBA in module to run the filter and update order history when a customer is selected.

Learn how to implement customer-triggered order record filtering with VBA, using the advanced filter, event-driven worksheet change, and a dynamic, interactive Excel dashboard.

Adjust the worksheet change procedure in vba to trigger the dashboard filter only when cell b3 changes, using target.address for absolute referencing and improving performance.

Discover how to add summary calculations to an Excel dashboard, including order count, average order total, and last order date, using subtotal to respect filters.

Master how to use subtotal to summarize only visible data in filtered Excel lists, ignoring hidden rows, and apply 101, 102, and 104 for average, count, and max.

Learn how pivot tables quickly summarize data and power charts in dashboards, with step-by-step guidance to analyze total order amounts by customer, year, and month.

Build a pivot table from the orders data, format the order amount as currency, and create a pivot chart on the dashboard to visualize yearly order amounts.

Set up pivot table filters to drive interactivity with pivot charts. Learn to add a customer name filter, link it to the pivot table, and automate updates with VBA.

Open the VBA editor with Alt+F11, create a public sub named update_customer_order_info, and connect the dashboard's customer name to the pivot table filter.

Dim learn how to declare variables in excel vba with dim, storing a pivot table, pivot field, and customer name, then link a dashboard dropdown to update the pivot filter.

Declare and set VBA variables for a pivot table on the yearly orders pivot worksheet, reference the customer name field, and read the selected value from the dashboard cell B3.

Use VBA to link pivot table filters for dashboards: clear the customer name filter, set the current page to a new customer, and refresh the table.

Automate pivot chart updates in Excel with VBA by linking B3 customer selection to the pivot table and chart via an advanced filter, adding error handling for empty orders.

Learn to handle missing order data in Excel VBA by adding error handling with on error goto, bookmarks, and a message box to prevent pivot table breaks.

Add interactive filters to an Excel dashboard by creating slicers bound to the order month field, enabling month-specific or multi-month views of pivot charts.

Customize the chart slicer to boost interactivity and presentation by selecting slicer styles, adjusting the number of columns, and resizing button height and width.

Hide extra worksheets and columns to streamline the dashboard, then set the chart to ignore cell size changes and apply consistent formatting.

Optimize dashboards by turning off defaults like the formula bar, headings, and grid lines in the view tab for a clean white sheet. Customize the display to improve presentation.

Learn to use VBA to automate Excel dashboards by recording macros that hide charts and slicers when a customer has no orders, and reset filters when a customer is selected.

Protect the dashboard by locking the worksheet to prevent edits, while keeping the customer name cell editable and the slicer and chart interactive. Use a password to unlock when needed.

Apply Excel data analysis and dashboard techniques to real work by using filtering, VLOOKUP and INDEX MATCH functions, subtotals, advanced filters, and pivot tables.

Explore Power Query in Excel to cleanse and transform data from diverse sources, preparing clean, structured data for intelligent reports.

Explore how this course builds from basics to advanced Power Query features, with structured videos, downloadable materials, assignments, and lifetime access to help you become a Power Query expert.

Download the exercise files and downloadable materials from the resources pane, access installation guidelines, and use the completed and bonus files to follow along and earn your certificate.