Marketing Campaign Dashboard in Excel

Build an interactive marketing dashboard with advanced Excel formulas.

Introduction

30 Second Summary

Marketing teams generate mountains of campaign data across Google Ads, social media, email, and display channels. Without a clear way to visualize performance, that data just sits in spreadsheets collecting dust.

In this project, you will build an interactive marketing dashboard in Excel that transforms raw multi-channel campaign data into executive-ready insights with KPI cards, charts, and point-and-click filtering.

What You'll Build

An interactive Marketing Campaign Performance Dashboard in Excel 2021 that uses advanced formulas, Pivot Tables, and Slicers to surface actionable insights from 50+ rows of campaign data.

By the end of this project, you'll have:

  • A fully interactive dashboard with KPI cards, channel comparison charts, monthly trend lines, and Slicers that filter everything with a single click.
  • Hands-on experience with XLOOKUP, INDEX-MATCH, and dynamic array functions (FILTER, SORT, UNIQUE) applied to a real marketing dataset.
  • A polished, portfolio-ready Excel workbook demonstrating intermediate-to-advanced skills that marketing hiring managers actively look for.
  • Secret Mission: Build a dynamic Campaign Lookup Tool and an auto-updating Underperforming Campaigns alert table.

Are there any prerequisites?

This project assumes you are comfortable with basic Excel formulas like SUM, AVERAGE, and IF, as well as basic chart creation. You do not need any prior experience with XLOOKUP, dynamic arrays, or Pivot Tables.

Set Up the Raw Data Sheet

Every dashboard starts with clean, structured data. Before you can build charts, write formulas, or add interactivity, you need a solid data layer that your entire workbook can reference.

In this step, you'll create a structured dataset of 50 marketing campaigns across four channels and six months. Formatting it as an Excel Table gives your formulas the ability to auto-expand whenever new data is added.

In this step, get ready to:
  • Create a new workbook and set up the data sheet with column headers.
  • Enter 50 rows of multi-channel campaign data.
  • Format the data as a named Excel Table for dynamic referencing.
Create a new workbook and build the data structure

Your workbook needs a dedicated sheet just for raw data. Keeping data separate from calculations and visuals is a dashboard best practice that makes maintenance easier.

  • In Excel, press Ctrl+N to create a new blank workbook.
  • Save the workbook immediately by pressing Ctrl+S.
  • In the Save As dialog, navigate to your Desktop.
  • Type Marketing_Dashboard.xlsx as the file name and click Save.
  • Double-click the Sheet1 tab at the bottom of the screen.
  • Type Data and press Enter to rename the sheet.

Why rename the sheet?

When your workbook grows to multiple sheets (Calculations, Dashboard), descriptive names help you find things instantly. Formulas that reference other sheets also become much easier to read.

  • Click cell A1 and type the following headers across the row, pressing Tab after each one:
Campaign_ID    Campaign_Name    Channel    Month    Impressions    Clicks    Conversions    Spend    Revenue

What do these columns represent?

  • Campaign_ID: A unique identifier for each campaign (C001, C002, etc.).
  • Channel: The marketing platform (Google Ads, Social Media, Email Marketing, Display Ads).
  • Impressions, Clicks, Conversions: The engagement funnel from views to actions.
  • Spend and Revenue: The financial metrics you'll use to calculate ROI and CPA later.
  • Verify that your header row spans from A1 to I1 with all 9 column names visible.

Headers not fitting in cells?

If text is cut off, select the header row, go to Home tab, click Format in the Cells group, then select AutoFit Column Width.

Help me fix column widths in my Excel workbook.

Enter sample marketing campaign data

You need realistic data with a good mix of high-performing and underperforming campaigns. This variety is what makes the dashboard interesting to analyze later.

  • Click cell A2 to start entering data in the row below your headers.
  • Enter all 50 rows of campaign data using the reference table below. Press Tab to move between columns and Enter to move to the next row.
C001    Spring Sale Search        Google Ads         Jan    45000    2250    135    3200    8100
C002    Brand Awareness FB        Social Media       Jan    62000    1860    56     1800    2800
C003    Newsletter Promo          Email Marketing    Jan    15000    2100    189    400     5670
C004    Banner Retarget           Display Ads        Jan    95000    950     38     1400    1900
C005    Product Launch Search     Google Ads         Jan    38000    1900    114    2850    7980
C006    Instagram Stories         Social Media       Jan    55000    1650    33     1200    1650
C007    Abandoned Cart Email      Email Marketing    Feb    12000    1920    230    350     6900
C008    YouTube Pre-roll          Display Ads        Feb    120000   1200    24     2400    1200
C009    Competitor Keywords       Google Ads         Feb    32000    1280    64     2560    3840
C010    LinkedIn Thought Lead     Social Media       Feb    28000    840     42     1500    3150
C011    Flash Sale Email          Email Marketing    Feb    18000    2520    202    300     6060
C012    Programmatic Display      Display Ads        Feb    150000   1500    30     2700    1500
C013    Spring Collection Ads     Google Ads         Feb    52000    2600    156    3900    10920
C014    TikTok Campaign           Social Media       Mar    78000    2340    47     1600    2350
C015    Welcome Series            Email Marketing    Mar    8000     1200    144    200     4320
C016    Native Ads                Display Ads        Mar    88000    880     35     1760    2100
C017    Brand Terms Search        Google Ads         Mar    25000    2000    180    1500    10800
C018    Facebook Lookalike        Social Media       Mar    48000    1440    58     2100    3480
C019    Re-engagement Email       Email Marketing    Mar    10000    1400    112    250     3360
C020    Podcast Sponsor Display   Display Ads        Mar    65000    650     26     1300    1560
C021    Summer Preview Search     Google Ads         Mar    41000    2050    103    3075    6180
C022    Twitter Promoted          Social Media       Apr    35000    700     21     900     1050
C023    Win-back Email            Email Marketing    Apr    9000     1170    105    275     3150
C024    Billboard Digital         Display Ads        Apr    110000   1100    22     2200    1100
C025    Exact Match Campaign      Google Ads         Apr    29000    1740    122    2610    8540
C026    Pinterest Promoted        Social Media       Apr    42000    1260    50     1400    2500
C027    Loyalty Rewards Email     Email Marketing    Apr    14000    1960    176    350     5280
C028    Sidebar Ads               Display Ads        Apr    75000    750     23     1500    1150
C029    Shopping Ads              Google Ads         Apr    55000    3300    198    4950    13860
C030    Facebook Video            Social Media       May    68000    2040    61     1900    3050
C031    Product Update Email      Email Marketing    May    11000    1540    123    300     3690
C032    Interstitial Ads          Display Ads        May    130000   1300    26     2600    1300
C033    Long-tail Keywords        Google Ads         May    22000    1320    92     1980    6440
C034    Instagram Reels           Social Media       May    72000    2160    43     1500    2150
C035    Seasonal Promo Email      Email Marketing    May    16000    2240    179    380     5370
C036    Rich Media Display        Display Ads        May    98000    980     29     1960    1450
C037    DSA Campaign              Google Ads         May    35000    1750    88     2625    5280
C038    LinkedIn Sponsored        Social Media       May    30000    900     36     1600    2700
C039    VIP Early Access Email    Email Marketing    Jun    7000     1050    126    200     3780
C040    Video Pre-roll            Display Ads        Jun    105000   1050    21     2100    1050
C041    Summer Sale Search        Google Ads         Jun    48000    2400    144    3600    10080
C042    Facebook Carousel         Social Media       Jun    58000    1740    52     1700    2600
C043    Anniversary Email         Email Marketing    Jun    13000    1820    164    325     4920
C044    Responsive Display        Display Ads        Jun    82000    820     25     1640    1250
C045    Performance Max           Google Ads         Jun    60000    3000    180    4500    12600
C046    Snapchat Ads              Social Media       Jun    50000    1500    30     1100    1500
C047    Cross-sell Email          Email Marketing    Jun    9500     1330    133    275     3990
C048    Pop-under Ads             Display Ads        Jun    140000   1400    14     2800    700
C049    Remarketing Search        Google Ads         Jun    33000    1980    119    2970    8330
C050    Facebook Lead Gen         Social Media       Jun    45000    1350    41     1450    2050

What patterns are in this data?

The data includes a deliberate mix of performance levels. Google Ads and Email Marketing campaigns tend to have strong ROI, while Display Ads campaigns frequently underperform. This variety creates interesting contrasts when you build charts and filters later.

Each channel has 12-13 campaigns spread across all six months. The numbers are designed to produce realistic marketing metrics like click-through rates between 1-15% and conversion rates between 1-12%.

Speed up data entry

You can type each row's values separated by Tab presses, then press Enter to jump to column A of the next row. If you make a mistake, press Ctrl+Z to undo.

  • After entering all 50 rows, verify your data ends at row 51 (row 1 is headers, rows 2-51 are data).
  • Press Ctrl+End to jump to the last used cell. It should be cell I51.

Last cell is not I51?

If your last cell is in a different location, you may have skipped rows or added extra blank rows. Scroll through your data and look for gaps. Make sure every row from 2 to 51 has data in all 9 columns.

Help me check my data range is correct.

Format as an Excel Table

An Excel Table is more than just formatting. It gives your data structured references (like CampaignData[Channel]) that make formulas readable, and it auto-expands when you add new rows.

  • Click any cell inside your data (for example, A1).
  • Press Ctrl+T to open the Create Table dialog.
  • Confirm that the My table has headers checkbox is checked.
  • Click OK.

What is Ctrl+T doing?

Ctrl+T converts a regular range into an Excel Table. Excel automatically detects the boundaries of your data and applies banded row formatting. The key benefit is that formulas using structured references (like CampaignData[Spend]) will always refer to the correct column, even if you rearrange or add data.

Your data should now have alternating row colors and filter dropdown arrows on each header. Next, you need to give the table a meaningful name.

  • With your cursor still inside the table, look at the Table Design tab in the ribbon (it appears automatically when a table is selected).
  • Click the Table Design tab.
  • In the Properties group on the left, click inside the Table Name field.
  • Clear the existing name and type CampaignData.
  • Press Enter to confirm the name.

Why name the table CampaignData?

Every formula you write in the Calculations sheet will reference this table by name. Instead of writing =SUM(E2:E51), you'll write =SUM(CampaignData[Impressions]). The second version is self-documenting and won't break if rows are added or deleted.

  • Save your workbook by pressing Ctrl+S.
  • Verify that filter dropdown arrows appear on every header and that alternating row colors are applied across all 50 rows.

Don't see the Table Design tab?

The Table Design tab only appears when your cursor is inside a table. Click any cell within your data range (between A1 and I51) and the tab should appear in the ribbon.

Help me find the Table Design tab in Excel.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file with Ctrl+S.

ⓧ I'd like to double check the full data

Your Data sheet should have a header row in row 1 and 50 rows of campaign data in rows 2-51. The table should be named CampaignData (visible in the Table Name field under the Table Design tab). Here is the complete dataset for comparison:

Campaign_ID    Campaign_Name            Channel            Month    Impressions    Clicks    Conversions    Spend    Revenue
C001           Spring Sale Search       Google Ads         Jan      45000          2250      135            3200     8100
C002           Brand Awareness FB       Social Media       Jan      62000          1860      56             1800     2800
C003           Newsletter Promo         Email Marketing    Jan      15000          2100      189            400      5670
C004           Banner Retarget          Display Ads        Jan      95000          950       38             1400     1900
C005           Product Launch Search    Google Ads         Jan      38000          1900      114            2850     7980
C006           Instagram Stories        Social Media       Jan      55000          1650      33             1200     1650
C007           Abandoned Cart Email     Email Marketing    Feb      12000          1920      230            350      6900
C008           YouTube Pre-roll         Display Ads        Feb      120000         1200      24             2400     1200
C009           Competitor Keywords      Google Ads         Feb      32000          1280      64             2560     3840
C010           LinkedIn Thought Lead    Social Media       Feb      28000          840       42             1500     3150
C011           Flash Sale Email         Email Marketing    Feb      18000          2520      202            300      6060
C012           Programmatic Display     Display Ads        Feb      150000         1500      30             2700     1500
C013           Spring Collection Ads    Google Ads         Feb      52000          2600      156            3900     10920
C014           TikTok Campaign          Social Media       Mar      78000          2340      47             1600     2350
C015           Welcome Series           Email Marketing    Mar      8000           1200      144            200      4320
C016           Native Ads               Display Ads        Mar      88000          880       35             1760     2100
C017           Brand Terms Search       Google Ads         Mar      25000          2000      180            1500     10800
C018           Facebook Lookalike       Social Media       Mar      48000          1440      58             2100     3480
C019           Re-engagement Email      Email Marketing    Mar      10000          1400      112            250      3360
C020           Podcast Sponsor Display  Display Ads        Mar      65000          650       26             1300     1560
C021           Summer Preview Search    Google Ads         Mar      41000          2050      103            3075     6180
C022           Twitter Promoted         Social Media       Apr      35000          700       21             900      1050
C023           Win-back Email           Email Marketing    Apr      9000           1170      105            275      3150
C024           Billboard Digital        Display Ads        Apr      110000         1100      22             2200     1100
C025           Exact Match Campaign     Google Ads         Apr      29000          1740      122            2610     8540
C026           Pinterest Promoted       Social Media       Apr      42000          1260      50             1400     2500
C027           Loyalty Rewards Email    Email Marketing    Apr      14000          1960      176            350      5280
C028           Sidebar Ads              Display Ads        Apr      75000          750       23             1500     1150
C029           Shopping Ads             Google Ads         Apr      55000          3300      198            4950     13860
C030           Facebook Video           Social Media       May      68000          2040      61             1900     3050
C031           Product Update Email     Email Marketing    May      11000          1540      123            300      3690
C032           Interstitial Ads         Display Ads        May      130000         1300      26             2600     1300
C033           Long-tail Keywords       Google Ads         May      22000          1320      92             1980     6440
C034           Instagram Reels          Social Media       May      72000          2160      43             1500     2150
C035           Seasonal Promo Email     Email Marketing    May      16000          2240      179            380      5370
C036           Rich Media Display       Display Ads        May      98000          980       29             1960     1450
C037           DSA Campaign             Google Ads         May      35000          1750      88             2625     5280
C038           LinkedIn Sponsored       Social Media       May      30000          900       36             1600     2700
C039           VIP Early Access Email   Email Marketing    Jun      7000           1050      126            200      3780
C040           Video Pre-roll           Display Ads        Jun      105000         1050      21             2100     1050
C041           Summer Sale Search       Google Ads         Jun      48000          2400      144            3600     10080
C042           Facebook Carousel        Social Media       Jun      58000          1740      52             1700     2600
C043           Anniversary Email        Email Marketing    Jun      13000          1820      164            325      4920
C044           Responsive Display       Display Ads        Jun      82000          820       25             1640     1250
C045           Performance Max          Google Ads         Jun      60000          3000      180            4500     12600
C046           Snapchat Ads             Social Media       Jun      50000          1500      30             1100     1500
C047           Cross-sell Email         Email Marketing    Jun      9500           1330      133            275      3990
C048           Pop-under Ads            Display Ads        Jun      140000         1400      14             2800     700
C049           Remarketing Search       Google Ads         Jun      33000          1980      119            2970     8330
C050           Facebook Lead Gen        Social Media       Jun      45000          1350      41             1450     2050

Your data foundation is complete. Next up, you'll build a Calculations sheet that uses XLOOKUP, INDEX-MATCH, and dynamic array functions to extract insights from this raw data.

Build the Calculations Sheet with Advanced Formulas

Your raw data is structured and locked into an Excel Table. Now it's time to build the engine that powers your dashboard.

The Calculations sheet acts as a middle layer between raw data and the final dashboard. It transforms 50 rows of campaign numbers into clean KPIs, lookup tools, and dynamic lists that the dashboard can reference directly.

In this step, get ready to:
  • Create KPI formulas and per-channel breakdowns using SUMIFS.
  • Build a campaign lookup section with XLOOKUP and INDEX-MATCH.
  • Generate dynamic lists using UNIQUE, SORT, and FILTER.
Create KPI calculations using SUMIFS and arithmetic

Every marketing dashboard needs headline numbers. You will build five KPIs that summarize campaign performance at a glance, then break those numbers down by channel.

  • Right-click the Data sheet tab at the bottom of your workbook.
  • Click Insert, then select Worksheet.
  • Double-click the new sheet tab and rename it to Calculations.

Set up labels for your KPI section by entering these values:

  • In cell A1, type KPI Summary.
  • In cells A2 through A6, type these labels one per row: Total Spend, Total Revenue, ROI, CPA, CTR.
  • Enter the KPI formulas in column B by clicking each cell and typing the formula shown below:
Cell B2: =SUM(CampaignData[Spend])
Cell B3: =SUM(CampaignData[Revenue])
Cell B4: =(B3-B2)/B2
Cell B5: =B2/SUM(CampaignData[Conversions])
Cell B6: =SUM(CampaignData[Clicks])/SUM(CampaignData[Impressions])

What do these formulas do?

  • Total Spend and Total Revenue sum all values from the CampaignData table using structured references like CampaignData[Spend]. These auto-update if you add rows to the table.
  • ROI calculates return on investment as (Revenue minus Spend) divided by Spend. A value of 1.5 means 150% return.
  • CPA (Cost Per Acquisition) divides total spend by total conversions to show how much each conversion costs.
  • CTR (Click-Through Rate) divides total clicks by total impressions to measure ad engagement.

You should see numbers appear in cells B2 through B6. ROI and CTR will display as decimals for now.

Seeing a #REF! or #NAME? error?

  • Check that your Data sheet table is named exactly CampaignData. Click any cell in the table on the Data sheet, then look at the Table Design tab for the table name.
  • Make sure column names in your structured references match the exact header text in your table. For example, CampaignData[Spend] only works if the column header says Spend, not spend or Spending.

Need more help? Why are my KPI formulas returning errors?

Now build the per-channel breakdown using SUMIFS. This function sums values based on criteria you specify, letting you calculate spend, revenue, and conversions for each individual channel.

  • In cell A8, type Channel Breakdown.
  • In cells A9:D9, type headers: Channel, Spend, Revenue, Conversions.
  • In cells A10:A13, type each channel name: Google Ads, Social Media, Email Marketing, Display Ads.
  • Enter the SUMIFS formulas starting in cell B10:
Cell B10: =SUMIFS(CampaignData[Spend],CampaignData[Channel],A10)
Cell C10: =SUMIFS(CampaignData[Revenue],CampaignData[Channel],A10)
Cell D10: =SUMIFS(CampaignData[Conversions],CampaignData[Channel],A10)
  • Select cells B10:D10, then drag the fill handle (the small square at the bottom-right corner of the selection) down to row 13 to copy the formulas for all four channels.

What does SUMIFS do here?

SUMIFS adds up values from one column only where a condition is met in another column. The formula =SUMIFS(CampaignData[Spend],CampaignData[Channel],A10) says: sum all Spend values, but only where the Channel column matches the text in A10.

Because A10 contains Google Ads, you get the total spend for Google Ads only. When you drag the formula down, A10 shifts to A11, A12, A13, pulling each channel's totals automatically.

  • Press Ctrl+S to save your workbook.
  • Check that rows 10 through 13 show numbers for each channel. Each row should display different totals.

All channels showing the same number?

Make sure the channel names in cells A10 through A13 exactly match the text in your Data table. Check for trailing spaces or capitalization differences. SUMIFS text matching is not case-sensitive, but extra spaces will cause a mismatch.

Still stuck? Help me fix my SUMIFS channel breakdown.

Use XLOOKUP to pull campaign-specific data

Sometimes you need to look up a single campaign's details quickly. XLOOKUP lets you type a Campaign ID into one cell and instantly retrieve any related data from the table. It replaces the older VLOOKUP with a simpler syntax and no column-number counting.

  • In cell A16, type Campaign Lookup.
  • In cell A17, type Campaign_ID:.
  • In cell B17, type any Campaign_ID from your Data sheet (for example, C001). This is your lookup input cell.
  • In cells A18:A22, type labels: Campaign_Name, Channel, Spend, Revenue, ROI.
  • Enter the XLOOKUP formulas in column B to retrieve each value:
Cell B18: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Campaign_Name],"Not found")
Cell B19: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Channel],"Not found")
Cell B20: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Spend],"Not found")
Cell B21: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Revenue],"Not found")
Cell B22: =(B21-B20)/B20

How does XLOOKUP work?

XLOOKUP takes three main arguments: the value to search for, where to search, and what to return. The formula =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Campaign_Name],"Not found") searches for whatever ID is in B17, looks through the Campaign_ID column, and returns the matching Campaign_Name.

The fourth argument ("Not found") is a fallback message that appears if the ID doesn't exist. This prevents ugly #N/A errors.

Now add an equivalent lookup using INDEX-MATCH so you can see how the older approach compares. INDEX-MATCH is still common in interviews and works in older Excel versions.

  • In cell A23, type INDEX-MATCH (Name):.
  • In cell B23, enter this formula:
=INDEX(CampaignData[Campaign_Name],MATCH(B17,CampaignData[Campaign_ID],0))

XLOOKUP vs INDEX-MATCH

Both formulas return the same result. XLOOKUP reads left-to-right and uses simpler syntax. INDEX-MATCH works in two steps: MATCH finds the row position, then INDEX returns the value at that position.

XLOOKUP is cleaner for straightforward lookups. INDEX-MATCH is more flexible because you can change which column INDEX returns without rewriting the formula. Knowing both gives you maximum versatility in interviews and collaborative workbooks.

  • Press Ctrl+S to save your workbook.
  • Verify that cells B18 through B21 show values matching the campaign ID you typed into B17. Cell B23 should display the same campaign name as B18.
  • Try changing the value in B17 to a different Campaign_ID. All lookup cells should update instantly.

Seeing "Not found" when you know the ID exists?

Check that the value in B17 matches your Data sheet exactly. Even an extra space after the ID will cause a mismatch. Click the cell in the Data sheet and look at the formula bar to see the exact text.

Need help? Why is my XLOOKUP returning "Not found"?

Use dynamic arrays (UNIQUE, SORT, FILTER) to generate dynamic lists

Dynamic array functions automatically spill results into multiple cells. Instead of writing one formula per row, you enter a single formula and Excel fills adjacent cells with all the results. These are some of the most powerful features in Excel 2021.

What are dynamic arrays?

Traditional Excel formulas return one value to one cell. Dynamic array formulas return multiple values that "spill" into neighboring cells automatically. You only enter the formula once in the top cell.

The cells where results spill must be empty. If anything blocks the spill range, you will get a #SPILL! error.

  • In cell A25, type Dynamic Lists.
  • In cell A26, type Channels:.
  • In cell D25, type Months (sorted):.
  • Enter the UNIQUE and SORT formulas to extract distinct values from your data:
Cell B26: =UNIQUE(CampaignData[Channel])
Cell D26: =SORT(UNIQUE(CampaignData[Month]))

What do UNIQUE and SORT do?

  • =UNIQUE(CampaignData[Channel]) scans the entire Channel column and returns each distinct value once. It spills downward into B27, B28, and B29.
  • =SORT(UNIQUE(CampaignData[Month])) first extracts unique months, then sorts them alphabetically. Nesting functions like this is a key dynamic array technique.

Cell B26 should spill into 4 cells (one per channel). Cell D26 should spill into 6 cells (one per month, sorted).

Now add a FILTER formula that extracts all rows for a specific channel. This is useful for isolating one channel's campaigns for analysis.

  • In cell A32, type Google Ads Campaigns:.
  • In cell A33, enter this FILTER formula:
=FILTER(CampaignData,CampaignData[Channel]="Google Ads")

What does FILTER do?

FILTER returns every row from CampaignData where the Channel column equals "Google Ads". The result spills into as many rows and columns as needed to display all matching data.

Make sure the cells below and to the right of A33 are empty. FILTER needs room to spill the full result. If any cell in that range contains data, you will see a #SPILL! error.

  • Press Ctrl+S to save your workbook.
  • Confirm that cell A33 has spilled into multiple rows showing only Google Ads campaigns with all their columns (Campaign_ID through Revenue).

Seeing a #SPILL! error?

The cells where the formula needs to spill contain data or are inside an Excel Table. Dynamic array formulas cannot spill inside a Table. Make sure your Calculations sheet formulas are in regular cells, not inside a formatted Table.

Delete any content in the cells below and to the right of your FILTER formula, then press Enter again.

Still stuck? Help me fix my #SPILL! error on the Calculations sheet.

✔️ Awesome, I've got everything!

Great. Double check you have saved your workbook with Ctrl+S.

ⓧ I'd like to double check the full code

Here is a summary of every formula on your Calculations sheet:

=== KPI Summary (Column A labels, Column B formulas) ===
A1: KPI Summary
A2: Total Spend         | B2: =SUM(CampaignData[Spend])
A3: Total Revenue       | B3: =SUM(CampaignData[Revenue])
A4: ROI                 | B4: =(B3-B2)/B2
A5: CPA                 | B5: =B2/SUM(CampaignData[Conversions])
A6: CTR                 | B6: =SUM(CampaignData[Clicks])/SUM(CampaignData[Impressions])

=== Channel Breakdown ===
A8: Channel Breakdown
A9: Channel | B9: Spend | C9: Revenue | D9: Conversions
A10: Google Ads       | B10: =SUMIFS(CampaignData[Spend],CampaignData[Channel],A10)
                      | C10: =SUMIFS(CampaignData[Revenue],CampaignData[Channel],A10)
                      | D10: =SUMIFS(CampaignData[Conversions],CampaignData[Channel],A10)
A11: Social Media     | B11-D11: (drag formulas down from row 10)
A12: Email Marketing  | B12-D12: (drag formulas down from row 10)
A13: Display Ads      | B13-D13: (drag formulas down from row 10)

=== Campaign Lookup ===
A16: Campaign Lookup
A17: Campaign_ID:     | B17: (your test ID, e.g. C001)
A18: Campaign_Name    | B18: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Campaign_Name],"Not found")
A19: Channel          | B19: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Channel],"Not found")
A20: Spend            | B20: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Spend],"Not found")
A21: Revenue          | B21: =XLOOKUP(B17,CampaignData[Campaign_ID],CampaignData[Revenue],"Not found")
A22: ROI              | B22: =(B21-B20)/B20
A23: INDEX-MATCH (Name): | B23: =INDEX(CampaignData[Campaign_Name],MATCH(B17,CampaignData[Campaign_ID],0))

=== Dynamic Lists ===
A25: Dynamic Lists
A26: Channels:        | B26: =UNIQUE(CampaignData[Channel])
D25: Months (sorted): | D26: =SORT(UNIQUE(CampaignData[Month]))
A32: Google Ads Campaigns:
A33: =FILTER(CampaignData,CampaignData[Channel]="Google Ads")

Your Calculations sheet is now powering all the data your dashboard needs. Next up, you will turn these numbers into visual KPI cards, charts, and interactive slicers on a polished Dashboard sheet.

Build the Interactive Dashboard

Your Calculations sheet is doing the heavy lifting behind the scenes. Now it's time to build the visual layer that turns those numbers into something an executive can understand at a glance.

In this step, you'll create a dedicated Dashboard sheet with KPI cards, charts, and interactive filters. By the end, clicking a single button will filter every chart and metric on the page simultaneously.

In this step, get ready to:
  • Create KPI cards with conditional formatting that turns green, yellow, or red based on performance.
  • Build Pivot Tables and Pivot Charts for channel comparison and monthly trends.
  • Add Slicers that let you filter the entire dashboard with one click.
Create KPI cards with conditional formatting

KPI cards are the first thing someone looks at on a dashboard. They answer the big questions instantly: How much did we spend? What's our return?

  • Right-click any sheet tab at the bottom of Excel and select Insert.
  • Select Worksheet and click OK.
  • Double-click the new sheet tab and rename it to Dashboard.

Now set up the KPI labels and value cells. You'll create a row of card-style cells that pull their values from the Calculations sheet.

  • In cell B2, type Total Spend. In D2, type Total Revenue. In F2, type ROI %. In H2, type CPA. In J2, type Conversion Rate.
  • In cell B3, enter the formula that links to your Total Spend value on the Calculations sheet:
=Calculations!B2
  • In cell D3, enter a similar formula pointing to your Total Revenue cell on the Calculations sheet.
  • In cell F3, link to your ROI formula on the Calculations sheet. Format this cell as a percentage by pressing Ctrl+1, selecting Percentage, and clicking OK.
  • In cell H3, link to your CPA value on the Calculations sheet.
  • In cell J3, enter a Conversion Rate formula. This is total conversions divided by total clicks:
=SUM(CampaignData[Conversions])/SUM(CampaignData[Clicks])

What do these KPI cards show?

Each card answers a key question for marketing leadership. Total Spend shows budget usage. Total Revenue shows what came back. ROI % tells you if campaigns are profitable overall.

CPA (Cost Per Acquisition) reveals how much you pay for each conversion. Conversion Rate shows what percentage of people who clicked actually converted.

Now add Conditional Formatting to make the ROI card change color based on performance. This creates a traffic-light indicator that's instantly readable.

  • Select cell F3 (your ROI % value).
  • Click Home in the ribbon, then click Conditional Formatting.
  • Select New Rule.
  • Choose Format only cells that contain.
  • Set the rule to Cell Value > greater than > 1 (which represents 100%).
  • Click Format, go to the Fill tab, select a green color, and click OK twice.
  • Repeat the process to add a second rule: Cell Value > between > 0.5 and 1. Set the fill to yellow.
  • Add a third rule: Cell Value > less than > 0.5. Set the fill to red.

Why use 1 and 0.5 instead of 100 and 50?

Excel stores percentage-formatted values as decimals internally. A cell showing 120% actually contains the value 1.2. Your conditional formatting rules need to reference the underlying decimal, not the displayed percentage.

Your ROI card should now show a colored background. Green means campaigns are more than doubling their spend. Yellow means they're profitable but below 2x. Red means they're losing money relative to the 50% threshold.

KPI card showing 0 or a wrong value?

Double-check that your formula references the correct cell on the Calculations sheet. Click on the KPI cell, look at the formula bar, and verify the sheet name and cell address match where your KPI value lives.

If the conditional formatting color isn't showing, open Conditional Formatting > Manage Rules and confirm the rules apply to cell F3.

Help me fix my KPI cards or conditional formatting rules.

Build Pivot Tables and Pivot Charts

KPI cards show totals, but charts reveal patterns. A Pivot Table lets Excel summarize your raw data automatically, and a Pivot Chart visualizes that summary without any manual formula work.

  • Click anywhere inside the CampaignData table on the Data sheet.
  • Click Insert in the ribbon, then click PivotTable.
  • In the dialog, Excel should auto-detect CampaignData as the source. Select Existing Worksheet.
  • Click inside the Location field, then switch to your Dashboard sheet and click cell B6.
  • Click OK.

The PivotTable Fields pane appears on the right. This is where you tell Excel how to summarize your data.

  • Drag Channel to the Rows area.
  • Drag Revenue to the Values area. It should appear as Sum of Revenue.

Now create a bar chart from this Pivot Table to visualize Revenue by Channel.

  • Click anywhere inside the Pivot Table you just created.
  • Click PivotTable Analyze in the ribbon, then click PivotChart.
  • Select Clustered Bar and click OK.
  • Click the chart title and change it to Revenue by Channel.
  • Right-click any bar in the chart and select Add Data Labels to show exact revenue values.

Next, build a monthly trend line chart. This requires a second Pivot Table because each chart needs its own data summary.

  • Switch to the Data sheet and click inside the CampaignData table again.
  • Click Insert > PivotTable.
  • Select Existing Worksheet and place it on the Dashboard sheet at cell H6.
  • In the PivotTable Fields pane, drag Month to Rows.
  • Drag Spend to Values.
  • Drag Revenue to Values as well. You should now see both Sum of Spend and Sum of Revenue in the Values area.
  • Click inside this second Pivot Table.
  • Click PivotTable Analyze > PivotChart.
  • Select Line and click OK.
  • Click the chart title and change it to Spend and Revenue by Month.
  • Right-click a data line and select Add Data Labels. Repeat for the second line.

Why two separate Pivot Tables?

Each Pivot Chart is tied to exactly one Pivot Table. The bar chart summarizes data by Channel, while the line chart summarizes by Month. These are two different views of the same data, so they need separate Pivot Tables.

The good news is that both Pivot Tables use the same CampaignData source. This means Slicers can filter both of them simultaneously.

You should now see two charts on your Dashboard sheet. The bar chart compares total revenue across channels. The line chart shows how spend and revenue trend across months.

Chart showing wrong data or no data?

Make sure you dragged fields to the correct areas in the PivotTable Fields pane. Rows determines the axis labels. Values determines what gets plotted.

If a field shows "Count of" instead of "Sum of", right-click it in the Values area, select Value Field Settings, and change the summarization to Sum.

Help me troubleshoot my Pivot Table or Pivot Chart.

Add Slicers for interactive filtering

Charts are great, but a truly interactive dashboard lets the viewer ask their own questions. Slicers are clickable filter buttons that update every connected chart and table at once.

  • Click anywhere inside your first Pivot Table (the Revenue by Channel one).
  • Click PivotTable Analyze in the ribbon.
  • Click Insert Slicer.
  • Check the boxes for Channel and Month, then click OK.

Two slicer panels appear on your sheet. Right now they only filter the first Pivot Table. You need to connect them to the second Pivot Table as well.

  • Right-click the Channel slicer.
  • Select Report Connections.
  • Check the box for the second Pivot Table (the monthly trend one) and click OK.
  • Repeat for the Month slicer. Right-click it, select Report Connections, and check the second Pivot Table.

What are Report Connections?

Report Connections let a single Slicer filter multiple Pivot Tables at once. The only requirement is that all connected Pivot Tables share the same data source. Since both your Pivot Tables come from CampaignData, connecting them works perfectly.

Finally, position and format the slicers so they sit neatly at the top of the dashboard.

  • Drag both slicers to the top area of your Dashboard (above or beside the KPI cards in row 1-2).
  • Click a slicer to select it, then click the Slicer tab in the ribbon.
  • Use the Columns setting in the Buttons group to increase the number of columns. Set the Channel slicer to 4 columns so all four channels display side by side.
  • Set the Month slicer to 6 columns so all six months display in a single row.
  • Resize each slicer by dragging its edges so it fits compactly at the top of the sheet.
  • Test the interactivity. Click Google Ads in the Channel slicer.
  • Both charts should update to show only Google Ads data.
  • Click the clear filter icon (funnel with an X) in the top-right corner of the slicer to reset it.
  • Press Ctrl+S to save your workbook.

Multi-select with Ctrl

Hold Ctrl while clicking slicer buttons to select multiple channels or months at once. This lets you compare subsets of your data without writing any formulas.

Slicer only filtering one chart?

This means the Report Connections step was missed. Right-click the slicer, select Report Connections, and make sure both Pivot Tables are checked. Both must share the same data source (CampaignData) for this to work.

Help me connect my slicers to all Pivot Tables.

Your dashboard now has all the core pieces working together. KPI cards show the big picture, charts reveal patterns across channels and time, and slicers let anyone explore the data without touching a formula. Next up, you'll polish the layout and lock it down for professional presentation.

Polish and Professional Formatting

Your dashboard already does the heavy lifting. Charts visualize channel performance, KPI cards surface key metrics at a glance, and slicers make everything interactive.

A dashboard that functions well but looks unpolished loses credibility with stakeholders. In this step, you'll transform it from a working prototype into a presentation-ready deliverable by cleaning up the layout, applying consistent formatting, and locking the sheet so no one accidentally breaks it.

In this step, get ready to:
  • Remove gridlines and apply a consistent color scheme to the dashboard.
  • Align all charts and slicers using the Align tools and add a dynamic title.
  • Protect the sheet so only the slicers and lookup input cell remain editable.
Apply dashboard layout best practices

Professional dashboards separate data from presentation. Gridlines, misaligned objects, and inconsistent colors signal "work in progress" to anyone viewing your file.

  • Switch to the Dashboard sheet in your workbook.
  • Click the View tab in the ribbon.
  • In the Show group, uncheck Gridlines.

The cell grid disappears, giving your dashboard a clean canvas look.

  • Select a blank cell near the top-left corner of your dashboard (for example, cell A1).
  • Type a dashboard title such as Marketing Campaign Dashboard and press Tab.
  • In the cell immediately to the right of your title, enter the following formula to display today's date:
=TODAY()

What does this formula do?

The =TODAY() function returns the current date and updates automatically every time you open the workbook. This keeps your dashboard header accurate without manual edits.

  • Select your title cell and format it with a larger font size (for example, 16pt) and bold using the Home tab.
  • Apply a consistent color scheme to the dashboard by selecting your KPI card cells, then setting a dark fill color (such as dark blue or charcoal) with white font using Fill Color and Font Color on the Home tab.
  • Ensure your chart backgrounds match. Right-click each chart, select Format Chart Area, and set the fill to white or No Fill so charts sit cleanly against the sheet background.

Keep colors minimal

Stick to 2-3 accent colors maximum. One color for headers and KPI card backgrounds, one for chart highlights, and white for the overall background. Overusing colors makes a dashboard harder to read.

Now align your charts and slicers so everything looks intentional.

  • Click your bar chart to select it.
  • Hold Ctrl and click your line chart to add it to the selection.
  • With both charts selected, click the Shape Format tab in the ribbon.
  • In the Arrange group, click Align, then select Align Top.
  • Click Align again and select Distribute Horizontally to space them evenly.
  • Repeat this process for your Channel and Month slicers. Select both slicers with Ctrl+click, then use Shape Format > Align > Align Top and Distribute Horizontally.

Why use the Align tools?

Dragging objects by hand rarely produces pixel-perfect alignment. The Align tools in the Shape Format tab snap objects to the same edge or distribute them with equal spacing. This is what separates a polished dashboard from a rough one.

Save your workbook. Your dashboard should now have no gridlines, a clear title with today's date, consistent colors, and neatly aligned charts and slicers.

Align options greyed out?

The Align menu only works when two or more objects are selected. Make sure you are holding Ctrl while clicking each chart or slicer so they are all highlighted before you open the Align dropdown.

Help me fix my alignment tools in Excel.

Lock the dashboard for presentation

Protecting a worksheet prevents accidental edits to your carefully arranged layout. The key is to unlock the cells that still need interaction before you turn protection on.

  • Click on your Channel slicer to select it.
  • Hold Ctrl and click your Month slicer so both are selected.
  • Right-click one of the selected slicers and choose Size and Properties.
  • In the Format pane that appears on the right, expand Properties and uncheck Locked.
  • Close the Format pane.

Why unlock the slicers?

By default, all objects on a worksheet (charts, slicers, shapes) are marked as Locked. Once you protect the sheet, locked objects become unclickable. Unlocking the slicers before protection ensures they remain interactive for anyone viewing the dashboard.

  • Now select the cell where users type a Campaign_ID for the lookup (the input cell on your Calculations sheet that your XLOOKUP references). If you placed a lookup input area on the Dashboard sheet, select that cell instead.
  • Press Ctrl+1 to open the Format Cells dialog.
  • Click the Protection tab.
  • Uncheck Locked, then click OK.

With the interactive elements unlocked, you can now protect the rest of the sheet.

  • Click the Review tab in the ribbon.
  • Click Protect Sheet in the Protect group.
  • In the Protect Sheet dialog, leave the password field blank (for this project, you do not need a password).
  • Make sure Select locked cells and Select unlocked cells are both checked.
  • Click OK.

What does Protect Sheet actually do?

Worksheet protection locks all cells marked as "Locked" so no one can edit them. It is not a security feature. It simply prevents accidental changes to your layout, formulas, and formatting while keeping the unlocked slicers and input cells fully interactive.

  • Test your protected dashboard by clicking a slicer button. The charts and KPI cards should still update when you filter.
  • Try clicking a KPI card cell and typing. Excel should block the edit with a protection warning.
  • Save your workbook by pressing Ctrl+S.

Slicers not responding after protection?

If clicking a slicer does nothing, you likely forgot to uncheck Locked on the slicer objects before protecting. Click Review > Unprotect Sheet, then right-click each slicer, open Size and Properties, and uncheck Locked. Then re-protect the sheet.

Help me fix my slicers on a protected sheet.

Secret mission

Campaign Lookup Tool and Underperforming Alert Table

Your dashboard already filters and visualizes campaign data. But what if a stakeholder wants to instantly look up a specific campaign's full metrics by name? Or what if you could surface underperforming campaigns automatically, without anyone having to dig through the data? Build two advanced features that combine XLOOKUP and dynamic arrays into a self-service reporting tool.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally in Excel 2021, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your workbook as-is, or remove it from your computer.

Resources you used:

  • Marketing_Dashboard.xlsx workbook (local file on your computer).

Keep everything running

No action needed. Choose this if you want to keep refining your dashboard, adding new campaign data, or using it as a portfolio piece.

  • Your Marketing_Dashboard.xlsx file stays wherever you saved it.
  • You can reopen it anytime in Excel 2021 to update data or adjust the dashboard.

Pause - I'll come back to this later

Since this is a local file with no running processes, there is nothing to pause. Your workbook is already saved and idle.

  • Close Excel if it is still open.
  • Your Marketing_Dashboard.xlsx file remains on disk and ready to reopen whenever you return.

Delete - I don't want to use this again

Remove the project file from your computer entirely.

  • Close Excel if it is still open.
  • Open File Explorer and navigate to the folder where you saved Marketing_Dashboard.xlsx.
  • Right-click the file and select Delete.
  • Empty your Recycle Bin if you want to permanently remove it.

Nice Work!

Nice Work!

You just built a professional marketing campaign performance dashboard in Excel 2021. From raw multi-channel data to interactive charts, KPI cards, and dynamic formulas, you now have a portfolio-ready workbook that demonstrates the exact skills marketing hiring managers look for.

Here's what you learned:

  • Mastered XLOOKUP for intuitive lookups and INDEX-MATCH for flexible lookups, two approaches that come up constantly in interviews and on the job.
  • Used dynamic array functions (FILTER, SORT, UNIQUE) to generate auto-updating lists and filtered views without complex legacy array formulas.
  • Built an interactive dashboard with Pivot Charts, Slicers, and conditional formatting that filters the entire view with a single click.
  • Built a dynamic Campaign Lookup Tool and an auto-updating Underperforming Campaigns alert table using XLOOKUP and FILTER to surface campaigns below target ROI.

Ready to quiz yourself?