Trading Services Dashboard in Google Sheets

Introduction

30 Second Summary

Every trading floor generates mountains of data. The difference between a good analyst and a great one is turning those raw numbers into a single screen that tells leadership exactly where to focus.

In this project, you will build an interactive trading services performance dashboard in Google Sheets. You will take raw trading data, surface performance trends with pivot tables and charts, and deliver strategic recommendations that a Head of Trading Services can act on.

What You'll Build

Open the finished dashboard to see color-coded KPI signals at a glance, click a slicer to filter every chart to a single trading desk, and read strategic recommendations you wrote from the data.

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

• A polished dashboard where you see at a glance which KPI targets are being hit and which are flagged red, without reading a single raw number.
• Conditional formatting and slicers that let any viewer filter every chart to a specific trading desk or region with one click.
• Three data-backed strategic recommendations that demonstrate the analytical "so what" separating strong analyst candidates from chart-makers.
• Secret Mission: Add a QUERY-powered dropdown that dynamically rebuilds a summary table for any desk a viewer selects.

Are there any prerequisites?

You just need a free Google account with access to Google Sheets. No downloads, installations, or paid tools required.

Before We Start

Before we start building, let's lock in what you're about to create and why it matters for the Trading Services role you're preparing for.

Set Up Your Spreadsheet and Trading Dataset

Every dashboard starts with data. Before you can build charts, pivot tables, or KPI scorecards, you need a clean, structured dataset as the foundation.

In this step, you'll create a new Google Sheets spreadsheet and populate it with a realistic trading operations dataset. This data covers 6 months of performance across four trading desks, three regions, and three client tiers.

In this step, get ready to:

• Create a new Google Sheets spreadsheet with a RawData tab.
• Enter 36 rows of trading operations data with 8 performance columns.
• Verify the dataset is clean and ready for analysis.

Create your spreadsheet and name the RawData tab

The first thing every analyst does is separate raw data from the analysis layer. You'll create a dedicated tab for the raw dataset so your formulas and charts never accidentally overwrite the source.

• Open your web browser and go to sheets.google.com. Sign in with your Google account if prompted.
• Click Blank to create a new empty spreadsheet.
• Double-click the Sheet1 tab at the bottom left of the screen.
• Type RawData and press Enter to rename the tab.

The tab at the bottom now reads RawData.

Don't see the tab at the bottom?

Make sure you're looking at the very bottom of the spreadsheet window, not the top toolbar. The sheet tabs appear as small labels at the bottom edge of the screen.

If you accidentally created a second tab instead of renaming the first one, right-click the extra tab and select Delete.

Help me find and rename my sheet tab.

Paste the trading dataset

Your dataset models 6 months of trading activity across four desks (Equities, Fixed Income, FX, and Commodities), three regions (Americas, EMEA, APAC), and three client tiers. There are 36 rows total, with built-in performance patterns you'll uncover as you analyze.

• Click on cell A1 in your RawData tab.
• Copy all of the following data (headers and January through March rows) and paste it into cell A1:

Date	Trading Desk	Region	Client Tier	Trade Volume	Revenue	Settlement Error Rate	Client Satisfaction Score
2026-01-15	Equities	Americas	Institutional	1250	485000	0.012	8.7
2026-01-15	Fixed Income	EMEA	Corporate	980	312000	0.018	7.9
2026-01-15	FX	APAC	Retail	1540	178000	0.022	7.2
2026-01-15	Commodities	Americas	Corporate	870	267000	0.015	8.1
2026-01-15	Equities	EMEA	Institutional	1100	425000	0.014	8.5
2026-01-15	FX	Americas	Institutional	1680	392000	0.019	7.6
2026-02-15	Fixed Income	Americas	Institutional	1050	338000	0.016	8.0
2026-02-15	Commodities	EMEA	Retail	760	198000	0.021	7.4
2026-02-15	Equities	APAC	Corporate	920	295000	0.013	8.6
2026-02-15	FX	EMEA	Corporate	1420	345000	0.025	7.1
2026-02-15	Fixed Income	APAC	Retail	680	165000	0.019	7.7
2026-02-15	Commodities	Americas	Institutional	910	285000	0.014	8.2
2026-03-15	Equities	Americas	Corporate	1180	412000	0.011	8.8
2026-03-15	FX	APAC	Institutional	1350	298000	0.028	7.0
2026-03-15	Fixed Income	EMEA	Institutional	1020	355000	0.017	8.1
2026-03-15	Commodities	APAC	Retail	640	152000	0.023	7.3
2026-03-15	Equities	EMEA	Retail	890	278000	0.015	8.4
2026-03-15	FX	Americas	Corporate	1580	410000	0.026	7.3

What do these columns represent?

• Date is the reporting date for that month's data.
• Trading Desk is the business unit (Equities, Fixed Income, FX, or Commodities).
• Region is the geographic area (Americas, EMEA, or APAC).
• Client Tier is the customer segment (Institutional, Corporate, or Retail).
• Trade Volume is the number of trades executed.
• Revenue is the dollar value generated from those trades.
• Settlement Error Rate is the fraction of trades with processing errors (0.012 means 1.2%).
• Client Satisfaction Score is a rating out of 10.

• Confirm the data filled columns A through H, with headers in row 1 and 18 data rows below. You should see data in rows 1 through 19.

All the data ended up in column A?

This happens when the tab characters between columns are lost during copy-paste. Select all the pasted data, delete it, then try copying again. Make sure you select the entire block of text from the code box above before pasting.

If the data still lands in a single column, try this: select the pasted column, go to Data in the top menu, then click Split text to columns. Choose Tab as the separator.

Help me split my pasted data into separate columns.

• Now click on cell A20 (the first empty row after your pasted data).
• Copy the remaining April through June data below and paste it into cell A20:

2026-04-15	Fixed Income	Americas	Corporate	1080	342000	0.018	7.8
2026-04-15	Commodities	EMEA	Institutional	850	275000	0.016	8.0
2026-04-15	Equities	APAC	Institutional	960	310000	0.012	8.9
2026-04-15	FX	EMEA	Retail	1290	285000	0.031	6.9
2026-04-15	Fixed Income	APAC	Corporate	720	178000	0.020	7.5
2026-04-15	Commodities	Americas	Retail	780	225000	0.017	7.8
2026-05-15	Equities	Americas	Institutional	1300	498000	0.010	9.0
2026-05-15	FX	APAC	Corporate	1480	315000	0.034	6.8
2026-05-15	Fixed Income	EMEA	Retail	850	245000	0.019	7.6
2026-05-15	Commodities	APAC	Institutional	690	195000	0.022	7.5
2026-05-15	Equities	EMEA	Corporate	1050	385000	0.013	8.6
2026-05-15	FX	Americas	Institutional	1720	425000	0.029	7.1
2026-06-15	Fixed Income	Americas	Institutional	1120	365000	0.015	8.2
2026-06-15	Commodities	EMEA	Corporate	820	248000	0.019	7.6
2026-06-15	Equities	APAC	Retail	880	268000	0.014	8.5
2026-06-15	FX	EMEA	Institutional	1390	338000	0.036	6.7
2026-06-15	Fixed Income	APAC	Institutional	750	192000	0.021	7.4
2026-06-15	Commodities	Americas	Corporate	900	278000	0.016	8.3

What patterns are hiding in this data?

This dataset is not random. There are intentional performance patterns baked in. One desk has error rates that climb over time. One region consistently underperforms on revenue. One desk stands out for client satisfaction.

You don't need to spot them now. The pivot tables, charts, and conditional formatting you build in the next steps will make these patterns jump off the screen.

• Confirm your data now fills rows 2 through 37, for a total of 36 data rows plus the header row in row 1.

✔️ Awesome, I've got everything!

Great. Double check that your last row of data is in row 37 and that you can see Commodities in cell B37.

ⓧ I'd like to double check the full data

Scroll up and confirm you have both data chunks pasted correctly:

• The header row and first 18 data rows (January through March) should be in rows 1 through 19.
• The remaining 18 data rows (April through June) should be in rows 20 through 37.
• Row 1 should contain Date in column A and Client Satisfaction Score in column H.
• Row 37 (the last row) should show 2026-06-15 in column A, Commodities in column B, and 278000 in column F.

Verify your dataset is clean

Clean data means every formula, pivot table, and chart you build later will work without unexpected errors. Dirty data (blank rows, text in numeric columns, inconsistent headers) is the number one cause of broken dashboards.

• Click on cell E2 (the first Trade Volume value). Confirm the cell contains a number, not text. You can check this in the formula bar at the top of the screen. Numbers align to the right in their cell by default.
• Click on cell A2 (the first Date value). Confirm it shows a date. Google Sheets may format it differently depending on your locale (for example, 1/15/2026 or 15/01/2026). Either is fine as long as it's recognized as a date.
• Scroll through the data from row 2 to row 37. Confirm there are no blank rows in the middle and no merged cells.

Before you check the final result, take a guess: how many total rows should your spreadsheet have, counting the header row?

• Click on cell A37. This should be your last row of data, confirming 36 data rows plus 1 header row for 37 rows total.

Your RawData tab now displays a clean table with 8 column headers in row 1 and 36 rows of trading data below, with no blank rows or merged cells.

Seeing blank rows or misaligned data?

If you have blank rows between your data, click on the row number at the far left to select the entire blank row. Right-click and select Delete row. Repeat for any other blanks.

If your data ends before row 37 or goes past it, check that the second paste started at cell A20 and that no rows were accidentally duplicated or missed.

Help me fix gaps or misaligned rows in my trading dataset.

Your raw dataset is locked and loaded. Next up, you'll use pivot tables to compress these 36 rows into summaries that reveal which desks drive the most revenue and where performance gaps are hiding.

Slice the Data with Pivot Tables

Your spreadsheet has 36 rows of clean trading data ready to analyze. Scrolling through the raw numbers, though, you cannot tell which trading desk drives the most revenue or whether any region is falling behind.

Pivot tables solve this by grouping rows into summaries automatically. In this step, you will build two pivot tables that slice your data by desk and by region, turning 36 hard-to-read rows into clear, scannable totals.

In this step, get ready to:

• Create a pivot table summarizing revenue and trade volume by trading desk.
• Build a cross-tabulation showing monthly revenue trends across regions.
• Identify initial performance patterns in the data.

Insert a pivot table and summarize by trading desk

The first question to answer is simple: which trading desk generates the most revenue? A pivot table groups every row in your dataset by a category you choose and calculates totals automatically.

• Switch to the RawData tab at the bottom of your spreadsheet.
• Click any cell inside your data (for example, cell A1).
• Click Insert in the top menu bar.
• Click Pivot table.
• In the dialog that appears, confirm the data range shows RawData!A1:H37. If it does not, type it in manually.
• Select New sheet and click Create.

A blank pivot table appears on a new tab. The Pivot table editor panel is open on the right side of the screen.

• Double-click the new tab name at the bottom of the screen and rename it to Pivots.
• Press Enter to confirm the name.

Why rename the tab?

Naming your tabs keeps the spreadsheet organized as you add more sheets. Anyone opening this file can tell at a glance which tab holds raw data and which holds analysis.

Now configure the pivot table to summarize your trading data by desk.

• In the Pivot table editor panel on the right, click Add next to Rows.
• Select Trading Desk from the dropdown.
• Click Add next to Values.
• Select Revenue. It defaults to SUM, which is what you want.
• Click Add next to Values again.
• Select Trade Volume.

The pivot table now shows 4 rows, one for each trading desk, with columns for SUM of Revenue and SUM of Trade Volume. A Grand Total row appears at the bottom.

• In the Rows section of the editor, click the dropdown arrow next to Trading Desk.
• Change Sort by to SUM of Revenue.
• Set Order to Descending.

The desks reorder so the highest-revenue desk sits at the top. You can now see at a glance which desk dominates.

What is a pivot table doing here?

Instead of writing formulas to add up revenue for each desk manually, the pivot table scans all 36 rows, groups them by the Trading Desk column, and calculates the SUM for you. When the source data changes, the pivot table updates automatically.

Pivot table editor not visible?

If the side panel disappeared, click the Edit button that appears at the bottom of the pivot table to reopen it.

If you see suggested pivot tables pre-filled, click Clear all in the editor panel and start fresh by adding the fields listed above.

Why can't I see Trading Desk in the field list?

Add a second pivot table for monthly revenue by region

The first pivot table answers "which desk earns the most." The second answers a different question: is revenue trending up or down over time, and does performance vary by region?

• Click an empty cell on the Pivots tab, well below the first pivot table (around row 15 or lower).
• Click Insert in the top menu bar.
• Click Pivot table.
• Confirm the data range shows RawData!A1:H37.
• Select Existing sheet this time so the pivot table lands on the same Pivots tab.
• Click Create.

A second blank pivot table appears below the first. The editor panel opens for this new pivot table.

• In the Pivot table editor, click Add next to Rows.
• Select Date.
• Click Add next to Columns.
• Select Region.
• Click Add next to Values.
• Select Revenue.

The pivot table now shows individual dates as rows with Americas, APAC, and EMEA as column headers. You need to group these dates by month to see the monthly trend.

• Right-click any date value in the pivot table's row labels.
• Click Create pivot date group.
• Choose the monthly grouping option from the list that appears.

The individual dates collapse into 6 monthly rows. Each cell now shows total revenue for that month and region combination.

Why group by month?

With individual dates as rows, the pivot table has too many rows to scan quickly. Grouping by month compresses the data into 6 rows, one per month. Now you can spot trends at a glance: is revenue climbing, flat, or declining over the 6-month period?

Don't see Create pivot date group?

Make sure you right-click directly on a date value in the pivot table, not on a column header or an empty cell. The option only appears when right-clicking a cell that contains date-formatted data.

If the dates show as plain numbers instead of date values, switch back to the RawData tab and check that column A is formatted as dates. Select the column, click Format > Number > Date.

Help me fix pivot table date grouping in Google Sheets.

Spot your first patterns

Both pivot tables are now live. Before moving on, take a moment to read what the data is telling you.

• Look at Pivot Table 1 (Trading Desk summary). Note which desk sits at the top, meaning it generates the most revenue.
• Scan the Trade Volume column. Does the highest-revenue desk also have the highest volume, or is a different desk processing more trades for less money?
• Look at Pivot Table 2 (Monthly Revenue by Region). Check which region column has the largest totals.
• Scan the monthly rows from top to bottom. Is overall revenue trending up, staying flat, or declining?

Why do observations matter at this stage?

You will write strategic recommendations in Step 5 based on patterns in this data. Spotting trends now gives you a head start. The dashboard you build next will make these patterns visually obvious, but knowing what to look for is the analyst skill that separates you from someone who just makes charts.

Before you move on, predict what you expect to see on the Pivots tab. Do you have two distinct pivot tables, each showing different slices of the same dataset?

Your Pivots tab should show two pivot tables. The first displays Revenue and Trade Volume broken down by Trading Desk (4 rows). The second shows Revenue by month (6 rows) with regions as columns (Americas, APAC, EMEA). Both tables source from the same RawData tab.

Your raw data now has structure. You can see which desks lead in revenue, how regions compare, and whether the trend is moving in the right direction. Next up, you will turn these numbers into a polished dashboard with KPI headlines and charts that tell the story visually.

Build Your Dashboard with KPIs and Charts

Your pivot tables from the last step already crunch the trading data into summaries you can scan. But a Head of Trading Services isn't going to scroll through pivot table rows during a review meeting.

In this step, you'll build a dedicated Dashboard tab that puts KPI headlines and charts on a single screen. This is the deliverable you'd actually hand to a stakeholder.

In this step, get ready to:

• Set up a clean Dashboard tab with hidden gridlines and a white background.
• Build four KPI summary cells that pull live data from your RawData tab.
• Add a bar chart and a line chart that visualize your pivot table data.

Set up your Dashboard tab

Before any data goes on the dashboard, you need a clean canvas. Hiding gridlines and setting a white background transforms a spreadsheet tab into something that looks like a polished report.

• Click the + button at the bottom left of your spreadsheet to add a new tab.
• Double-click the new tab's name at the bottom of the screen and rename it to Dashboard.
• Click View in the top menu bar.
• Hover over Show and uncheck Gridlines.

The tab instantly looks cleaner. Now set the background and layout.

• Press Cmd+A (macOS) or Ctrl+A (Windows) to select all cells.
• Click the Fill color button (paint bucket icon) in the toolbar.
• Select white.
• Click View in the top menu bar again.
• Select Freeze, then click 1 row.
• Drag the right edge of the column A header to the left to make it narrow (about 30 pixels wide). This creates a margin so your dashboard content doesn't sit flush against the left edge.

Your Dashboard tab should now look like a blank white page with no gridlines. Row 1 is locked in place at the top.

Still seeing gridlines?

Make sure you unchecked Gridlines under View > Show, not one of the other options in that menu. Gridlines should have no checkmark next to it.

help me figure out why gridlines are still showing on my Dashboard tab

Build your KPI summary cells

Four KPI values will anchor the top of your dashboard. Each one uses a formula that pulls live data from the RawData tab, so the numbers always stay current.

• Click on cell B2 and type Total Revenue.
• Click on cell B3 and enter this formula:

=SUM(RawData!F2:F37)

What does this formula do?

SUM adds up every value in a range. RawData!F2:F37 tells Google Sheets to look at column F (Revenue) on the RawData tab, from row 2 to row 37.

The exclamation mark is the cross-tab reference. Whenever you see TabName! in a formula, it means the data lives on a different tab in the same spreadsheet.

• Press Enter. A revenue total appears in cell B3.

One KPI down. Now add the remaining three following the same label-above, formula-below pattern.

• Click on cell D2 and type Total Trade Volume.
• In cell D3, enter this formula:

=SUM(RawData!E2:E37)

• Press Enter. A trade volume number appears.
• Click on cell F2 and type Average Settlement Error Rate.
• In cell F3, enter this formula:

=AVERAGE(RawData!G2:G37)

• Press Enter. A decimal value appears (this is the error rate as a raw decimal, not a percentage yet).
• Click on cell H2 and type Average Client Satisfaction Score.
• In cell H3, enter this formula:

=AVERAGE(RawData!H2:H37)

• Press Enter. A satisfaction score appears in the cell.

You should now see four numbers across row 3: a revenue total, a volume total, an error rate, and a satisfaction score. The numbers are there, but they're raw and unformatted.

Seeing a #REF! or #VALUE! error?

Double-check that your RawData tab is named exactly RawData with no spaces or extra characters. The formula's RawData! prefix must match the tab name exactly (case-sensitive).

Also confirm that your data runs from row 2 to row 37 with no blank rows in between. A gap in the data can cause unexpected formula results.

help me fix a formula error in my KPI cells on the Dashboard tab

Now format each KPI so the numbers are readable at a glance.

• Click on cell B3 (Total Revenue).
• Click Format in the top menu bar.
• Select Number, then click Custom number format.
• Type $#,##0 in the format field and click Apply. The revenue now displays as a clean dollar amount with no decimal places.

What does $#,##0 mean?

This is a number format code. The $ adds a dollar sign, the #,## adds comma separators for thousands, and the 0 means always show at least one digit. No decimal point means whole dollars only.

• Click on cell D3 (Total Trade Volume).
• Apply the custom number format #,##0 using the same Format > Number > Custom number format path.
• Click on cell F3 (Average Settlement Error Rate) and apply the custom number format 0.0%.
• Click on cell H3 (Average Client Satisfaction Score) and apply the custom number format 0.0.

Each KPI value should now display with proper formatting. The revenue shows a dollar sign and commas, the volume shows commas, the error rate shows a percentage, and the satisfaction score shows one decimal place.

• Select all four KPI value cells (B3, D3, F3, H3) by clicking each one while holding Cmd (macOS) or Ctrl (Windows).
• Increase the font size to 18 or 20 using the font size dropdown in the toolbar.
• Click the Bold button in the toolbar (or press Cmd+B / Ctrl+B) to make the numbers stand out.

Your four KPI values now sit across the top of the dashboard in large, bold, formatted numbers with descriptive labels above each one.

Add your charts

Numbers alone don't tell the full story. A bar chart will show how revenue compares across trading desks, and a line chart will show whether revenue is trending up or down over time.

• Click on the Pivots tab at the bottom of your spreadsheet.
• In Pivot Table 1 (Revenue and Trade Volume by Trading Desk), select only the Trading Desk names and their Revenue values. Do not include the Grand Total row.
• Click Insert in the top menu bar.
• Click Chart.

Why exclude the Grand Total row?

The Grand Total is the sum of all four desks. Including it in the chart would add a single massive bar that dwarfs the individual desk bars, making it hard to compare desk-level performance. You want to compare desks against each other, not against the total.

• The chart editor panel opens on the right. Under the Setup tab, set the chart type to Bar chart if it isn't already selected.
• Switch to the Customize tab in the chart editor.
• Expand Chart style.
• Set Background color to None.
• Set Chart border color to None.
• Expand Chart & axis titles and set the chart title to Revenue by Trading Desk.

A bar chart now shows revenue across your four trading desks. You can see at a glance which desk generates the most revenue.

• Click outside the chart to deselect it.
• Click and drag the chart from the Pivots tab to reposition it. Instead, cut the chart by clicking on it, pressing Cmd+X (macOS) or Ctrl+X (Windows).
• Click the Dashboard tab at the bottom of the spreadsheet.
• Click on a cell below your KPIs (around row 5 or 6) and paste with Cmd+V (macOS) or Ctrl+V (Windows).
• Drag the chart's edges to resize it so it sits neatly below the KPIs on the left side of the dashboard.

Your bar chart is now on the Dashboard tab. Time to add the second chart.

• Click the Pivots tab at the bottom of your spreadsheet.
• In Pivot Table 2 (Revenue by month and region), select the month labels and the revenue values. Include all three region columns.
• Click Insert, then click Chart.
• In the chart editor under the Setup tab, set the chart type to Line chart.
• Switch to the Customize tab.
• Under Chart style, set Background color to None and Chart border color to None.
• Under Chart & axis titles, set the chart title to Monthly Revenue Trend.

• Cut the line chart with Cmd+X (macOS) or Ctrl+X (Windows).
• Switch to the Dashboard tab.
• Click on a cell next to or below the bar chart and paste with Cmd+V (macOS) or Ctrl+V (Windows).
• Resize and position the line chart so it sits alongside or below the bar chart.

Your Dashboard tab now has four formatted KPI values across the top, a bar chart comparing desk revenue, and a line chart showing the monthly revenue trend.

Charts not showing the right data?

If the chart shows unexpected columns or includes the Grand Total, double-click the chart and check the Data range field under the Setup tab. Adjust the range to select only the rows and columns you need from the Pivots tab.

If the chart type looks wrong (e.g. a pie chart instead of a bar chart), click the Chart type dropdown in the Setup tab and choose the correct one.

help me fix my charts on the Google Sheets Dashboard tab

Take a step back and look at your dashboard as a whole. Before moving on, ask yourself: can you tell at a glance which KPIs are on track and which need attention?

You can't. The Total Revenue number is there, but is it good or bad? The error rate is a percentage, but is it acceptable or alarming? Every number is presented without a target or comparison. A viewer has to already know what "good" looks like to make any judgment. That gap between showing data and showing meaning is exactly what the next step fixes.

Your dashboard displays the raw data beautifully. In the next step, you'll add targets, deltas, and conditional formatting that turn those neutral numbers into red-or-green signals a stakeholder can read in seconds.

Turn Numbers into Signals with Targets and Formatting

Your dashboard is live. Four KPIs and two charts give a full picture of trading operations at a glance.

But try answering this: is a 3.2% settlement error rate acceptable or alarming? Is $2.3M total revenue on track or falling short? Right now, every number on the dashboard forces the viewer to mentally compare it against a benchmark they carry in their head. In this step, you'll add targets and color-coded formatting that answer those questions automatically.

In this step, get ready to:

• Add Target and Delta values next to each KPI.
• Apply conditional formatting to flag KPIs that miss their targets.
• Add a revenue heatmap to your pivot table.

Add target benchmarks to each KPI

A Target is the benchmark that defines "good" for each metric. Without it, a number like $2,500,000 in revenue is just a number. With a target next to it, a viewer instantly knows whether that number is on track.

• Switch to the Dashboard tab and find your four KPI values near the top.
• In the row directly below your Total Revenue value, click a cell and type Target as a label. Bold it.
• In the cell below that label, type 2500000. Format it as currency ($#,##0) using Format > Number.
• Below your Total Trade Volume value, add a bold Target label and type 50000 in the cell below it. Format it as #,##0.
• Below your Average Settlement Error Rate value, add a bold Target label and type 0.025 in the cell below it. Format it as 0.0% so it displays as 2.5%.
• Below your Average Client Satisfaction Score value, add a bold Target label and type 4.0 in the cell below it. Format it as 0.0.

You should now see a target value sitting below each of your four KPI numbers. Each target is formatted the same way as its KPI so they're easy to compare at a glance.

Target showing as plain text?

If your target displays as raw text (e.g. 0.025 instead of 2.5%), select the cell and reapply the number format through Format > Number. Make sure you pick the matching format for that KPI.

Help me fix my target formatting

Add delta formulas that measure the gap

A Delta is the difference between the actual value and the target. Positive means on track. Negative means falling short. You'll add a Delta cell below each Target.

• In the row below your Revenue Target value, click a cell and type Delta as a label. Bold it.
• Click the empty cell below that label. Type =, click your actual Total Revenue value cell, type -, click your Revenue Target cell, and press Enter.
• Format the Delta cell as $#,##0 to match the Revenue formatting.

You should see a positive or negative dollar amount. A positive number means actual revenue exceeds the target.

• Add a bold Delta label and formula below your Trade Volume Target using the same pattern: = Actual cell - Target cell. Format as #,##0.
• Add a bold Delta label and formula below your Client Satisfaction Score Target: = Actual cell - Target cell. Format as 0.0.
• For Settlement Error Rate, the formula is reversed. Add a bold Delta label below the Error Rate Target. Click the cell below it, type =, click the Target cell first, type -, then click the Actual cell, and press Enter. Format as 0.0%.

Why is the error rate formula reversed?

For Revenue, Trade Volume, and Satisfaction, higher is better. The formula Actual - Target gives a positive number when you're beating the benchmark.

For Settlement Error Rate, lower is better. Flipping the formula to Target - Actual keeps the same logic: positive means on track, negative means trouble. This way, green always means good and red always means bad across every KPI.

You should now see a Delta number below each KPI. Some are positive, some might be negative. The signs tell the story, but you still have to read the numbers to spot problems.

Apply conditional formatting to flag off-target KPIs

Conditional formatting automatically changes a cell's appearance based on its value. You'll set it up so positive deltas turn green and negative deltas turn red. A quick glance at color tells the whole story.

• Click on your Revenue Delta value cell.
• Go to Format > Conditional formatting.
• Under Format rules, set the condition to Greater than or equal to and enter 0 as the value.
• Click the fill color icon (paint bucket) under Formatting style and choose a green background.
• Click Add another rule.
• Set the condition to Less than with value 0. Set the fill color to red.
• Click Done.

Your Revenue Delta cell should now have a green or red background. Green means revenue is meeting or exceeding the target. Red means it's falling short.

• Click your Trade Volume Delta cell and apply the same two conditional formatting rules: green background for >= 0, red background for < 0.
• Repeat for your Settlement Error Rate Delta cell: green for >= 0, red for < 0.
• Repeat for your Client Satisfaction Score Delta cell: green for >= 0, red for < 0.

Why the same rules work for every KPI

Because you reversed the error rate Delta formula earlier (Target - Actual instead of Actual - Target), the conditional formatting rules are identical across all four KPIs. Positive always means "on track" and negative always means "needs attention." One rule, four metrics, zero confusion.

All four Delta cells should now be color-coded. Scan the row and you can instantly tell which KPIs are healthy and which need attention, without reading a single number.

All four deltas the same color?

If every Delta is green, double-check that your target values are realistic benchmarks (not rounded-down versions of your actuals). If every Delta is red, your targets might be set too high. Adjust a target value up or down slightly and confirm the Delta recalculates and the color changes.

If no colors appear at all, click the Delta cell and check that the conditional formatting rules are applied to that specific cell's range. The "Apply to range" field in the formatting panel should match the cell you selected.

Help me debug my conditional formatting

Add a revenue heatmap to your pivot table

Conditional formatting can also span an entire range of cells to create a heatmap. A heatmap uses a smooth color gradient so the highest and lowest values in a set jump out visually. You'll apply one to the Revenue column in your first pivot table.

• Switch to the Pivots tab.
• In Pivot Table 1, select only the Revenue values for the four trading desks. Do not include the header row or the Grand Total.
• Go to Format > Conditional formatting.
• At the top of the formatting panel, click the Color scale tab.
• Set Minpoint to red and Maxpoint to green.
• Click Done.

Before you look at the result, take a guess: based on what you saw in the bar chart, which trading desk do you think will be the deepest red? Which will be the brightest green?

The Revenue column in Pivot Table 1 now shows a color gradient. The highest-revenue desk glows green, the lowest is shaded red, and the desks in between show intermediate colors. You can scan the entire table and spot the weakest performer without reading a single dollar amount.

Heatmap only showing one color?

Make sure you selected only the four revenue value cells, not the header or Grand Total row. Including the Grand Total (which is much larger) compresses the color scale so all four desks look nearly the same.

Also check that you clicked the Color scale tab, not the Single color tab. The single-color tab applies a flat conditional rule, not a gradient.

Help me fix my heatmap colors

✔️ Awesome, I've got everything!

Great. Your Dashboard should have four KPIs, each with a Target value, a Delta value, and green or red coloring on the Delta. Your Pivots tab should have a red-to-green heatmap on the Revenue column in Pivot Table 1.

ⓧ I'd like to double check my work

Here is what each part of your spreadsheet should look like at this point:

• Dashboard tab: Four KPI values (Total Revenue, Total Trade Volume, Average Settlement Error Rate, Average Client Satisfaction Score) each with a Target value below and a Delta value below the Target. Delta formulas use Actual - Target for Revenue, Trade Volume, and Satisfaction. Error Rate uses Target - Actual. Every Delta cell has conditional formatting: green fill for >= 0, red fill for < 0. Bar chart and line chart are still present below the KPIs.
• Pivots tab: Pivot Table 1 has a color-scale heatmap on the Revenue values (red for the lowest desk, green for the highest). Pivot Table 2 is unchanged.
• RawData tab: Unchanged. 36 rows of clean data with 8 columns.

Your dashboard now flags problems automatically. A Head of Trading Services can open it and know in seconds which KPIs are healthy and which need attention. Next up, you'll make the dashboard interactive with slicers and write the strategic recommendations that turn your analysis into action.

Make It Interactive and Write Recommendations

Your dashboard now highlights which KPIs are on target and which are falling short through color-coded deltas. But the view is locked to the full dataset, so a viewer who wants to zoom into one trading desk or one region has no way to filter down.

In this step, you'll add slicers that let anyone filter every chart on the dashboard with a single click. You'll also write strategic recommendations that explain what the data means and what to do about it.

The recommendations section is what turns this dashboard from a report into a decision tool. Your analysis tells leadership what the numbers mean and what action to take.

In this step, get ready to:

• Add slicers that filter the dashboard by Trading Desk and Region.
• Explore the filtered data to spot performance patterns across desks and regions.
• Write 3 data-backed strategic recommendations with observations, implications, and actions.

Add slicers to the Dashboard

A slicer is an interactive filter control that sits directly on your spreadsheet. When a viewer clicks a value in the slicer, every chart and pivot table on the same sheet that shares the data source updates instantly.

• Click the Revenue by Trading Desk bar chart on your Dashboard tab.
• Click Data in the top menu bar.
• Click Add a slicer.
• In the slicer settings panel on the right, click the Column dropdown and select Trading Desk.
• Drag the slicer to the top of your dashboard, near the KPI cells.

Your Trading Desk slicer is in place. Now add a second slicer for the Region column.

• Click the Monthly Revenue Trend line chart.
• Click Data in the top menu bar.
• Click Add a slicer.
• Set the Column to Region.
• Drag this slicer next to the Trading Desk slicer at the top of the dashboard.

Before you test, think about what will happen when you click a desk name in the Trading Desk slicer. Will the KPI numbers at the top change along with the charts?

• Click the Trading Desk slicer.
• Under Filter by values, uncheck all desks except Equities, then click OK.

The bar chart and line chart update to show only Equities data. But look at your KPI numbers at the top. They haven't changed.

Why don't the KPI numbers change?

Slicers only filter charts and pivot tables. Your KPI cells use formulas like =SUM(RawData!F2:F37) that reference the RawData tab directly.

Those formulas always calculate across the full dataset, regardless of slicer selections. Your KPI numbers show the overall picture, while the charts let viewers drill into specifics.

• Click the Trading Desk slicer again, re-check all values to restore the full view, and click OK.

Charts not filtering when you click the slicer?

• Make sure you clicked on a chart before adding the slicer. If you added a slicer without clicking a chart first, it may not be connected to your charts.
• Check that both your charts source data from the same RawData range. Slicers only affect charts and pivot tables that share the same data source.
• Try deleting the slicer (click it, press Delete) and adding it again by clicking a chart first.

Still stuck? Help me troubleshoot why my Google Sheets slicer isn't filtering my charts.

Explore the data to gather insights

You now have the tools to explore your trading data interactively. Your goal is to gather specific observations that will fuel the recommendations you write next.

• Click through each value in the Trading Desk slicer one at a time. Watch how the Monthly Revenue Trend line chart changes shape for each desk.
• Clear the Trading Desk filter, then click through each value in the Region slicer. Notice which region shows the strongest revenue across desks in the bar chart.
• Clear both slicers, then switch to the RawData tab. Scan the Settlement Error Rate column to spot which desks have noticeably higher error rates.
• Return to the Dashboard tab.

What patterns should I look for?

• Which trading desk generates the most revenue? Which generates the least?
• Which region is the strongest performer on revenue?
• Which desk has the highest settlement error rate?

Write down the specific numbers you notice. You'll reference them in your recommendations.

Write strategic recommendations

The dashboard shows what happened. Your recommendations tell leadership what to do about it.

Each recommendation follows a three-part pattern that grounds your advice in the data.

• Scroll below the charts on your Dashboard tab.
• In an empty cell, type Strategic Recommendations as a section header.
• Format the header in bold with a larger font size to match the dashboard style.

Below the header, write 3 numbered recommendations. Each recommendation has three parts:

• Observation: What the data shows, with a specific number from your analysis.
• Implication: Why this matters for Trading Services.
• Recommended Action: What you'd suggest doing about it.

Ready to write your recommendations, or would you like to see an example first?

✔️ I'll give it a go!

Write your 3 recommendations in separate cells below the header. Use the specific numbers you gathered during your exploration to ground each observation.

ⓧ Maybe a little help...

Here is an example of what one recommendation might look like:

• Observation: The FX desk's settlement error rate is the highest across all four desks, exceeding the target by a noticeable margin.
• Implication: Elevated error rates increase operational risk and can erode institutional client trust over time.
• Recommended Action: Review FX post-trade settlement workflows to identify process gaps driving the higher error rate.

Write your three recommendations following this pattern. Pull specific numbers from your own data to ground each observation.

• Confirm your Strategic Recommendations section is visible below the charts with all 3 numbered recommendations.

Not sure what to recommend?

• Revisit your slicer exploration. Filter to each desk and each region, noting specific numbers that stand out.
• Focus on the gaps between actual performance and the targets you set in the previous step. Any delta that shows red is a strong candidate for a recommendation.

Need more guidance? Help me identify patterns in my trading data for strategic recommendations.

Take one final look at your complete dashboard. Click the Trading Desk slicer and select a single desk.

Every chart on the dashboard reshapes to show only that desk's data. Clear the filter and scroll down to confirm your three recommendations are visible with specific data points and suggested actions.

✔️ Awesome, I've got everything!

Your dashboard is complete. It's interactive, visually clear, and backed by your own written analysis.

ⓧ I'd like to double check

Confirm these elements are on your Dashboard tab:

• A Trading Desk slicer near the top of the dashboard.
• A Region slicer next to it.
• Both slicers filter the bar chart and line chart when clicked.
• A Strategic Recommendations section below the charts with 3 numbered recommendations, each containing an Observation, Implication, and Recommended Action.

Secret mission

Add a QUERY-Powered Dynamic Summary

Slicers filter charts, but they can't reshape a summary table on the fly. In this mission, you'll build a dropdown-driven panel that uses the QUERY function to dynamically rebuild a regional summary for any trading desk a viewer selects. This is the foundation of parameterized reporting in Google Sheets.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in Google Sheets, so there are no ongoing costs or paid resources to worry about. Your spreadsheet is saved automatically in your Google Drive.

Decide whether to keep your spreadsheet, use it as a portfolio piece, or delete it entirely.

Resources you used:

• Google Sheets spreadsheet with 3 tabs (RawData, Pivots, Dashboard) stored in your Google Drive.

Keep everything running

No action needed. Choose this if you want to keep refining your dashboard or use it as a reference for future projects.

• Your spreadsheet stays in Google Drive at no cost.
• To share it as a portfolio piece, click Share in the top right corner and set access to Anyone with the link as a Viewer. Copy the link to include in job applications or interviews.
• To download a copy as an Excel file, go to File > Download > Microsoft Excel (.xlsx).

Pause - I'll come back to this later

Your spreadsheet is already saved automatically in Google Drive. There is nothing to pause or shut down.

• Your work is saved in Google Drive and accessible from any browser whenever you want to return.
• To find it later, go to drive.google.com and search for your spreadsheet by name.

Delete - I don't want to use this again

Remove the spreadsheet from your Google Drive entirely.

• Open drive.google.com in your browser.
• Find your trading dashboard spreadsheet in your recent files or by searching for its name.
• Right-click the file and select Move to trash.
• To permanently delete it, open Trash in the left sidebar, right-click the file, and select Delete forever.

That's a Wrap!

That's a Wrap!

Nice work! You just built a stakeholder-ready trading services performance dashboard from scratch in Google Sheets. What started as 36 rows of raw data is now an interactive tool that surfaces trends, flags underperformance, and delivers actionable recommendations.

You've learned how to:

• Built a three-tab interactive dashboard with KPI scorecards, pivot tables, and charts that present trading operations performance on a single screen.
• Applied targets, deltas, and conditional formatting to turn raw numbers into visual signals that flag underperformance automatically, then added slicers so stakeholders can filter the entire dashboard by desk or region with a single click.
• Wrote three data-backed strategic recommendations with specific numbers, implications, and suggested actions, demonstrating the analytical "so what" that separates strong analyst candidates from chart-makers.
• Secret Mission: Built a QUERY-powered dynamic summary driven by a dropdown, the foundation of parameterized reporting in Google Sheets.

Ready to quiz yourself?