Merging queries (joins) in Excel - Dashboard Guide

Dashboard Mode - Merging queries (joins)

Goal

Combine customer orders with customer details to see who ordered what and when.

Sample Data

Customers

Customer ID	Name	City
101	Alice	New York
102	Bob	Chicago
103	Charlie	Los Angeles
104	Diana	Miami

Orders

Order ID	Customer ID	Order Date	Amount
5001	101	2024-01-10	250
5002	103	2024-01-15	450
5003	102	2024-01-20	300
5004	105	2024-01-25	150

Dashboard Components

• Merged Table: Shows orders with customer names and cities. Formula in cell G2:
  =IFERROR(VLOOKUP(B2,$A$2:$C$5,2,FALSE),"Unknown") to get customer name.
  Formula in cell H2:
  =IFERROR(VLOOKUP(B2,$A$2:$C$5,3,FALSE),"Unknown") to get city.
  Drag down for all orders.
• Total Sales: Sum of all order amounts.
  Formula:
  =SUM(D2:D5)
  Result: 1150
• Sales by City: Summary table using SUMIFS.
  Formula for New York sales:
  =SUMIFS(D2:D5,H2:H5,"New York")
  Similarly for other cities.

Dashboard Layout

+----------------------+---------------------+
|      Merged Table    |    Total Sales KPI   |
|  (Orders + Customers)|                     |
+----------------------+---------------------+
|    Sales by City Summary Table              |
+---------------------------------------------+

Interactivity

Add a filter for City. When you select a city, the Merged Table and Sales by City summary update to show only orders from that city. Total Sales KPI updates to sum only those filtered orders.

Self Check

If you filter the dashboard to show only orders from "Chicago", which orders appear in the Merged Table? What is the Total Sales value?

Key Result

Dashboard merges customer and order data to show detailed orders with customer info and sales summaries by city.