Microsoft Excel Data Analysis and Dashboard Reporting

Download the file customer orders-01 and follow along as we clean raw data and build a dashboard in the customer dashboard tab using the customer info and order info sheets.

Learn to create consistency in data by using Excel's proper function to convert names to proper case, add a new column, apply =proper(C2), and autofill down.

Learn how to use Excel's upper function to convert text to uppercase, creating a new column for consistent, presentable data in dashboards and reports.

Learn how to use paste special to paste only values, replacing formulas and removing redundant columns, so data becomes uppercase and uses the proper function results, preserving calculations.

Explore the choose function, an array function in Excel, to replace data by mapping 1, 2, 3 to Speedy Express, United Package, and Federal Shipping in the shipper column.

Extract the month from the order date using Excel's text function, formatting values as month abbreviations for a dashboard. Filter by month and compute monthly totals, counts, and averages.

Build a dynamic Excel dashboard by creating a dropdown of customer names and using VLOOKUP to populate contact details from the customer info tab, after cleaning data and removing redundancies.

Format as table in Excel creates a named table from a range, like customer info, so formulas reference the name and auto adjust as data grows.

Create an interactive drop-down menu in the customer dashboard using data validation. Build a list from the customer info tab, merge header cells, and enforce selection from existing customers.

Use vlookup to populate a dashboard with a selected customer's contact name, phone, and address from the Customer Info table, using lookup value, table array, column index, and exact match.

Clean up customer data by nesting VLOOKUP inside an IF function to replace missing region or fax with hyphens, using a data validation dropdown to drive clearer reports.

Use index and match as an alternative to VLOOKUP to overcome column order limitations and performance risks. Combine index with match to retrieve contact names from a data table.

Move and clean order data from the order info tab into the dashboard, remove unused fields, rename ship name to customer name, and keep order month for filtering.

Format the order data as a named table to simplify references in calculations and vba. Name it customerorderinfo, customize color, and turn off the header dropdown filter buttons.

Learn to filter a dashboard's orders by customer using Excel's advanced filter, with criteria ranges and a VBA macro linked to a dropdown.

Automate the advanced filter in Excel by recording a macro that captures selecting a customer and applying the filter to orders, using VBA to support a dashboard.

Learn how to place VBA code in a worksheet change event to run the advanced filter on an order data range via a dropdown, creating an interactive Excel dashboard.

Learn to tailor VBA filter logic in Excel dashboards by triggering an advanced filter only when cell B3 changes, using a targeted worksheet_change event and absolute address checks.

Learn to use Excel's subtotal function for accurate, filtered summaries in a dashboard, comparing it with count, average, and max for order count and last order date.

Learn to use the subtotal function to calculate on visible data, ignore hidden records, and choose function numbers for average, count, and max while referencing a table column during filtering.

Master pivot tables to quickly summarize order data and create charts for dashboards. Drill down by year and month to see customer totals and insights.

Create a pivot table from the customer order info, group by year, and format the sum as currency. Then create a pivot chart and move it to the dashboard.

Prepare the pivot table to receive a customer filter by placing the customer name into the filter field, then automate the filter transfer with VBA to update the chart.

Create a VBA procedure to connect your dashboard filters with pivot tables, enabling automated chart updates for the selected customer.

Declare and use variables in Excel VBA to interact with a pivot table, using dim to define a pivot table, field, and string for the selected customer name.

Learn to assign values to VBA variables by linking them to the yearly orders pivot table, its customer name field, and dashboard cells, using the set keyword for object references.

Apply a VBA workflow to connect the customer name filter to the yearly orders pivot table, clearing filters, setting the current page to a new customer, and refreshing the data.

Update the pivot chart automatically when a customer is selected by wiring the customer dashboard VBA to trigger the advanced filter on B3 and refresh the pivot table and chart.

Implement robust error handling in Excel VBA to manage customers with no orders in a pivot table. Use on error goto and labeled error blocks with a message box.

Add interactive slicers to filter a pivot chart by month, using a new order month column and the Analyze tab for a dynamic, dashboard-ready experience.

Modify the chart slicer in Excel to boost interactivity and presentation by using the Options tab to adjust style, columns, and button dimensions so all months fit on one page.

Hide unused worksheets and columns to streamline your Excel dashboard, improve interactivity, and clean design; fix chart behavior by setting don't move or size with cells.

Turn off default Excel elements: column headers, row headers, grid lines, and the formula bar to create a clean dashboard; use the View tab to collapse the Ribbon.

Automate Excel dashboards by recording a macro to clear filters and hide the chart and slicer when a customer has no orders.

Protect your dashboard by locking worksheet cells while allowing users to change the customer and use the slicer to update charts.

Celebrate completing the Microsoft Excel data analysis and dashboard reporting course by applying concepts like filtering, VLOOKUPs, index and matching, subtotals, advanced filters, and pivot tables to your work.