Retail Command Center with Excel

Build a verified retail performance dashboard with Excel and Copilot.

Introduction

30 Second Summary

Strong sales numbers look reassuring on a monthly report. Returns and heavy discounts can tell a very different profit story.

In this project, you will turn an adapted transaction-level retail dataset into a one-page Retail Performance Command Center in Excel for Microsoft 365. You will use Copilot in Excel for speed before a native PivotTable verifies the key totals.

What You'll Build

Your finished workbook gives a retail manager one screen to uncover where strong sales hide weak profit.

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

A decision-ready dashboard that exposes monthly performance. The same view highlights categories and products with weak economics.
A repeatable Copilot prompting workflow that turns broad retail questions into precise analysis requests.
A native PivotTable audit with clickable slicers that lets you trace dashboard totals back to the source transactions.
Secret Mission: Find a high-sales product whose discounts and returns conceal a profit problem.

Are there any prerequisites?

You need a Windows device with Excel for Microsoft 365 and Copilot in Excel already visible. No previous Excel analysis experience is required.

Before We Start

Your finished dashboard should help a retail manager make a clear decision using sales, profitability, discounts, returns, customer segments, and product performance. Before you begin working in Excel for Microsoft 365 with Copilot in Excel, commit to the decision your Retail Performance Command Center should support.

Prepare the Retail Workbook

Your dashboard depends on every transaction being interpreted consistently. A clean Excel table gives each date, order, customer, product, and return field a dependable structure.

In this step, you will protect the supplied source by saving a working copy. That copy becomes the structured input Copilot in Excel can interpret.

In this step, get ready to:

Save the supplied workbook as Retail_Performance_Command_Center.xlsx.
Format the transaction range as a table named RetailSales.
Confirm that Copilot recognizes the retail columns without changing the workbook.

Save a working copy

The supplied workbook is your untouched baseline. Saving a separate copy first keeps every project edit inside the command center workbook.

In Excel, click File in the upper-left corner.
Select Open.
Use the file picker to open the supplied NextWork_Retail_Sales.xlsx workbook from its saved location.

You should see the workbook's Retail Data worksheet tab. This confirms that you opened the supplied transaction dataset.

Click File.
Select Save As.
Keep the current folder as the save location.
Enter Retail_Performance_Command_Center.xlsx as the file name.
Select Excel Workbook (.xlsx) as the file format.
Click Save.

Your safe working copy is ready. The title bar should now show Retail_Performance_Command_Center.xlsx.

Still seeing the source filename?

Repeat the Save As workflow if the title bar still shows NextWork_Retail_Sales.xlsx. Confirm that the new file name is Retail_Performance_Command_Center.xlsx before continuing.

Use Excel Workbook (.xlsx) if Excel reports an unsupported file state.

Help me protect the source workbook while saving an Excel working copy.

Convert the transactions into RetailSales

An Excel table turns the transaction range into one named structure. The name RetailSales gives Copilot a precise source to analyze.

Select the Retail Data worksheet tab.
Select any cell inside the supplied transaction range.
Press Ctrl+T.
Confirm that the highlighted range includes the header row through the final transaction row.
Select My table has headers.
Click OK.

You have turned the loose transaction rows into one analyzable table. You should see formatted rows with filter controls in the headers.

Why use a named table?

Structured references use table names with column names. These references adjust when table data is added or removed.

A clear table name also anchors Copilot to the intended transaction source. This reduces ambiguity when later prompts request charts or calculations.

The Table Design tab only appears while a table cell is selected. This control can be easy to miss.

Select any cell inside the formatted table.
Click the Table Design tab in the ribbon.
Select the Table Name box.
Replace the existing name with RetailSales.
Press Enter.

Your transaction table now has a stable name. With a table cell selected, the Table Name box should display RetailSales.

Table name not showing?

Select a cell inside the formatted area if the Table Design tab is missing. A cell outside the table keeps that tab hidden.

Retype RetailSales in the Table Name box if the old name remains. Press Enter to apply it.

Help me check why my Excel table is not named RetailSales.

Verify RetailSales with Copilot

Copilot can inspect the table structure before it makes any workbook edits. Chat mode keeps its response in the conversation pane.

Select any transaction cell inside RetailSales.
Select the Copilot icon in the lower-right corner of Excel.
Switch Copilot to chat mode.
Ask Copilot to inspect the table without editing the workbook by entering this prompt:

List the columns in RetailSales and describe what each could help a retail manager investigate. Do not change the workbook.

What does this prompt check?

Naming RetailSales directs Copilot to the correct transaction table.
Requesting a management use for each column tests whether Copilot understands the data's business meaning.
The final sentence keeps this check inside chat mode without changing the workbook.

Before you send it, which retail fields do you expect Copilot to connect to a management decision?

Submit the prompt using Copilot's chat control.
Confirm that the reply stays inside the Copilot pane.

What should I see?

Dates are represented by Order Date.
Orders are represented by Order ID.
Customers are represented by Customer ID.
Customer groups are represented by Segment.
Individual products are represented by Product Name.
Product groupings include Category.
Detailed product groupings include Sub-Category.
Revenue is represented by Sales.
Initial earnings are represented by Profit.
Price reductions are represented by Discount.
Return status is represented by Returned.
Return expenses are represented by Return Cost.

Copilot's wording can vary. Complete field coverage is the success signal.

Select a transaction cell in RetailSales.
Select the Table Design tab.
Confirm that the Table Name box displays RetailSales.
Return to the Copilot pane from earlier.
Confirm that Copilot identified the supplied retail fields without changing the workbook.

Copilot cannot identify RetailSales?

Confirm that a cell inside the table is selected. Check that the Table Name box displays RetailSales.

Confirm that the working copy uses the Excel Workbook (.xlsx) format. Submit the same prompt again after correcting the table name or file format.

Help me find out why Copilot cannot recognize my RetailSales table.

Your source workbook remains untouched. Copilot can now read RetailSales from the working copy.

Your working copy is structured as RetailSales. Next, you will build a sales-only category chart that looks convincing but leaves important evidence out.

Build a Sales-Only Snapshot

The prepared RetailSales table gives Copilot in Excel a consistent source for analysis. Your next goal is a visible category sales ranking.

A sales ranking can look decisive before it earns that confidence. This step preserves that early answer so you can inspect what its single measure leaves out.

In this step, get ready to:

Create a Category ranking based only on total Sales.
Visualize the ranking as a descending bar chart.
Challenge the sales-only conclusion without changing the chart.

Create the sales-only ranking

A single-metric snapshot isolates the story that revenue tells. Seeing that story on its own makes the limits of the evidence easier to spot.

Return to the Copilot chat from earlier.
Select edit mode.

Before you send the prompt, do you think the category with the most Sales must also be the strongest business performer?

Create the sales-only snapshot by sending this exact prompt:

From RetailSales, rank Category by total Sales. Create a bar chart on a new worksheet named Sales Snapshot, sorted from highest to lowest.

What does this prompt do?

The RetailSales name anchors Copilot to the prepared transaction table.
The Category field defines the groups in the ranking.
The Sales field provides the only performance measure.
The descending sort places the sales-leading category at the top.

Wait for Copilot to finish creating the worksheet.
Select the Sales Snapshot worksheet tab.
Check the ranking order from top to bottom.
Check that the bar chart uses total Sales as its only measure.
Save Retail_Performance_Command_Center.xlsx.

You should see a Category ranking sorted from highest to lowest by total Sales. You should also see a bar chart that follows the same order.

Worksheet or chart missing?

Confirm that edit mode is selected if Copilot responds without creating the worksheet.
Confirm that the prompt names RetailSales if Copilot uses the wrong source.
Set Calculation Options to Automatic if Copilot can chat but cannot edit the workbook.

Help me create the Sales Snapshot worksheet and chart.

Challenge the sales-only conclusion

The chart now looks convincing enough to invite a quick conclusion. Its visual confidence comes from Sales alone.

Identify the sales-leading category at the top of the ranking.
Select an empty cell beside the chart.
Type the name of the sales-leading category into the selected cell.
Switch back to the Copilot chat from earlier.
Select chat mode.

Before you ask Copilot, do you think this chart contains enough evidence to prove that its leader is the best business performer?

Challenge the conclusion by sending this exact prompt:

Does this sales-only chart prove that the leading category is the best business performer? Name the evidence that is still missing.

What does this prompt do?

The chat mode keeps the response in the conversation without changing the workbook.
The first question separates a sales ranking from a broader performance conclusion.
The second sentence asks Copilot to identify the evidence that the chart cannot show.

Read Copilot's explanation of the sales-only chart.
Confirm that the response identifies Profit, Return Cost, Net Profit, Profit Margin, Discount, and Returned as missing evidence.
Ask one focused follow-up about any missing item that Copilot skips.
Return to the Sales Snapshot worksheet.
Select another empty cell beside the chart.
Add a note stating that the view deliberately excludes profitability, discounts, and returns.
Keep the sales-only chart unchanged.

Before the final check, do you expect the chart to contain any measure other than Sales?

Inspect the ranking to confirm that it runs from highest to lowest total Sales.
Inspect the chart to confirm that total Sales is its only measure.
Save Retail_Performance_Command_Center.xlsx.

You should see the category ranking beside a matching sales-only bar chart. The sales-leading category should be recorded beside the chart.

You should see no Net Profit, Profit Margin, Discount, or Returned measure in the chart. You have now exposed the intended gap.

Response missing important evidence?

Confirm that chat mode is selected if Copilot tries to change the workbook.
Ask a focused follow-up about each missing metric if the first response is incomplete.
Keep the original bar chart unchanged while you clarify the explanation.

Help me review Copilot's explanation of the missing business evidence.

Your sales-only snapshot now gives you a clear baseline for comparison. Next, you will measure the economics hidden behind its ranking.

Calculate Verified Profitability

Your Sales Snapshot now makes one thing clear: the category with the most revenue has won a narrow contest. The next goal is to test whether that leader still looks strong after return costs affect profit.

This step uses Copilot in Excel to plan two calculated columns. Excel structured references keep each formula tied to the current table row.

The sales-only view cannot reveal whether discounts or return costs erase profit. You will add Net Profit and Profit Margin before asking Copilot for a fairer category comparison.

In this step, get ready to:

Plan the Net Profit and Profit Margin calculations before changing RetailSales.
Apply both calculated columns across every table row.
Compare categories across five performance measures.

Plan the profitability columns

Plan mode lets you inspect Copilot's proposed formulas before the workbook changes. This creates a review point between your business definition and the finished calculations.

Switch back to the Retail Data worksheet in Retail_Performance_Command_Center.xlsx.
Select any transaction cell inside RetailSales.
Return to Copilot by selecting the Copilot icon in the lower-right corner of Excel.
Switch Copilot to plan mode.
Ask Copilot to explain the two calculations by submitting this prompt:

In RetailSales, plan two calculated columns. Net Profit should subtract Return Cost from Profit. Profit Margin should divide Net Profit by Sales, return 0 if the calculation errors, and display as a percentage. Explain the formulas before changing the workbook.

What Does This Plan Test?

Net Profit removes Return Cost from the supplied Profit value.
Profit Margin measures return-adjusted profit against Sales.
IFERROR supplies a fallback value when the division produces an error.
Plan mode keeps both definitions reviewable before Copilot edits the table.

Review Copilot's proposed plan.
Confirm the plan names both calculated columns.
Confirm the plan explains the percentage formatting for Profit Margin.

You should see a plan that subtracts Return Cost from Profit. You should also see Net Profit divided by Sales with errors replaced by zero.

Plan Missing a Requirement?

Resubmit the same prompt if Copilot omits either calculated column.
Keep plan mode selected until both formulas are explained.

Ask for help with a plan that does not match the required calculations: Help me review Copilot's profitability formula plan.

Verify the profitability formulas

Excel tables copy a structured-reference formula through an entire calculated column. Each transaction therefore receives the same calculation pattern with values from its own row.

Check Copilot's plan against these required formulas:

=[@Profit]-[@[Return Cost]]
=IFERROR([@[Net Profit]]/[@Sales],0)

How Do These Formulas Work?

The first formula calculates Net Profit for the current row by subtracting Return Cost from Profit.
The second formula divides the current row's Net Profit by Sales.
The second formula returns zero when the division produces an error.

Switch Copilot to edit mode.
Apply the reviewed plan to RetailSales.
Confirm Profit Margin displays as a percentage.

You should see Net Profit and Profit Margin appear as new columns inside RetailSales.

Before you inspect the last rows, do you expect the formulas to appear only near the top or throughout RetailSales?

Select the first populated cell in Net Profit.
Confirm the formula bar shows =[@Profit]-[@[Return Cost]].
Compare that row's Profit minus Return Cost with its Net Profit value.
Select the first populated cell in Profit Margin.
Confirm the formula bar shows =IFERROR([@[Net Profit]]/[@Sales],0).
Compare that row's Net Profit divided by Sales with its displayed percentage.
Scroll to the final rows of RetailSales.
Repeat the same checks on several final rows.

You should find both formulas filled through the entire table. Every Profit Margin result should use percentage formatting.

That is the calculation gap closed: every transaction now carries return-adjusted profit plus a comparable margin.

Copilot Cannot Apply the Columns?

Set Calculation Options to Automatic if Copilot can chat but cannot edit the table.
Ask Copilot to format Profit Margin as a percentage if the values appear as decimals.
Ask Copilot to reapply each calculated column inside RetailSales if the final rows are blank.

Get help with a column that will not calculate correctly: Help me troubleshoot my RetailSales calculated columns.

Test the sales leader again

A multi-metric category comparison puts revenue beside return-adjusted profit. Discount levels plus returned-order counts provide the missing risk context.

Before you submit the comparison, do you expect the sales leader to remain strongest after return costs enter the analysis?

Return to the Copilot pane from earlier.
Keep Copilot in edit mode.
Build the multi-metric category comparison by submitting this prompt:

Create a category comparison with total Sales, total Net Profit, Profit Margin, average Discount, and returned-order count. Explain where the original sales-only ranking was incomplete or misleading.

What Makes This Comparison Fairer?

Total Sales preserves the original revenue ranking as a baseline.
Total Net Profit reveals the effect of Return Cost on category performance.
Profit Margin makes categories with different sales totals easier to compare.
Average Discount plus returned-order count exposes pressures hidden by revenue.

Review the generated category comparison.
Confirm it includes total Sales.
Confirm it includes total Net Profit.
Confirm it includes Profit Margin.
Confirm it includes average Discount.
Confirm it includes returned-order count.
Confirm the commentary identifies where the original ranking was incomplete or misleading.
Leave the Sales Snapshot worksheet unchanged.

You should now see all five measures in Copilot's category comparison. The unchanged Sales Snapshot remains available as evidence of the original sales-only limitation.

Comparison Missing a Measure?

Request only the missing measure in one focused follow-up.
Check that Copilot used RetailSales as the source table.
Refresh the comparison after confirming both calculated columns contain values.

Get help checking an incomplete result: Help me fix my category comparison.

Your profitability calculations are filled through RetailSales. Next, you will audit the same totals with a transparent native Excel check.

Audit Results with a PivotTable

Your Copilot in Excel comparison now covers the metrics that the sales-only snapshot missed. The result is persuasive, but its totals still need an independent check.

A native PivotTable makes every field placement visible. Its summary methods create a reproducible check of the totals and category rankings.

In this step, get ready to:

Create an Audit Pivot worksheet from RetailSales.
Build the category audit with the required row fields and value fields.
Complete the final audit with interactive slicers and matched totals.

Create the audit PivotTable

The audit must use the complete RetailSales table as its source. Starting from a table cell lets Excel carry the full structured dataset into the PivotTable.

Return to the Retail Data worksheet in Retail_Performance_Command_Center.xlsx.
Select any cell inside RetailSales.
Select Insert on the ribbon.
Select PivotTable.
Confirm that the source is RetailSales.
Choose New Worksheet.
Select OK.

You should see a blank PivotTable area on a new worksheet. A field list should appear beside it.

Why combine Copilot and a PivotTable?

Copilot speeds up exploration across several retail metrics. This helps you reach a useful comparison quickly.

The PivotTable exposes the source fields and aggregation choices behind each number. That visibility gives your analysis a repeatable audit trail.

Double-click the new worksheet tab.
Type Audit Pivot.
Press Enter.

The worksheet tab should now read Audit Pivot.

Is the source incorrect?

Return to Retail Data.
Select a cell inside RetailSales.
Repeat the PivotTable insertion steps.

If the field list is hidden or the table source still looks wrong, help me create the PivotTable from RetailSales.

Build the audit view

Excel sums numeric value fields by default. The Discount field needs an average so the audit reflects the typical discount instead of adding percentages together.

Drag Category into the Rows area.
Drag Sub-Category beneath Category in the Rows area.
Drag Sales into the Values area.
Drag Net Profit into the Values area.
Drag Discount into the Values area.

The PivotTable should now display category rows with three value columns. Excel may initially summarize Discount as a sum.

Select the arrow beside Sum of Discount in the Values area.
Select Value Field Settings.
Select Average under Summarize Values By.
Select OK.

You should now see Sum of Sales, Sum of Net Profit, and Average of Discount in the PivotTable.

What does the audit layout show?

The Category field creates the top-level retail view.
The Sub-Category field exposes the detail behind each category.
The Sales field measures revenue at each level.
The Net Profit field includes the effect of return costs.
The Discount field shows the average discount applied to the underlying transactions.

Return to the Copilot category comparison from the previous step.
Compare its category Sales values with Sum of Sales in Audit Pivot.
Compare its category Net Profit values with Sum of Net Profit in Audit Pivot.
Check that the leading category for each metric matches across both views.

The category values should agree with the Copilot comparison. That is your first audit passed: the AI-generated summary now has a native calculation trail.

Seeing the wrong summaries?

Confirm that Sales appears as Sum of Sales.
Confirm that Net Profit appears as Sum of Net Profit.
Confirm that Discount appears as Average of Discount.

If a field uses the wrong summary method, help me correct my PivotTable value settings.

Add and test slicers

Slicers provide visible controls for narrowing the same PivotTable. Testing each one proves that the audit responds to region, customer segment, and return status.

Select any cell inside the PivotTable on Audit Pivot.
Select Insert on the ribbon.
Select Slicer.
Select Region in the field list.
Select Segment in the field list.
Select Returned in the field list.
Select OK.

You should see separate slicers for Region, Segment, and Returned beside the PivotTable.

Select one visible option in the Region slicer.

The PivotTable should recalculate to show only transactions from the selected region.

Select Clear Filter in the Region slicer.
Select one visible option in the Segment slicer.

The category totals should change to reflect the selected customer segment.

Select Clear Filter in the Segment slicer.

Before you test the final slicer, do you expect the totals to stay fixed or respond to the return filter?

Select Yes in the Returned slicer.

The PivotTable totals should change to include returned orders only. This confirms that the return filter is connected to the audit.

Select Clear Filter in the Returned slicer.

Before the final comparison, do you expect the native totals to agree with the Copilot result now that every slicer is clear?

Return to the Copilot category comparison from earlier.
Compare its total Sales with the unfiltered Sales grand total on Audit Pivot.
Compare its total Net Profit with the unfiltered Net Profit grand total on Audit Pivot.
Compare its category ranking with the category values on Audit Pivot.
Save Retail_Performance_Command_Center.xlsx.

You should find that the Sales totals, Net Profit totals, and category ranking agree across both views. Your audit trail is complete: the Copilot comparison now has a reproducible native Excel check.

Slicer unchanged or totals different?

Select Clear Filter in the Region slicer.
Select Clear Filter in the Segment slicer.
Select Clear Filter in the Returned slicer.
Right-click inside the PivotTable.
Select Refresh.
Compare the unfiltered totals again.

If a slicer still leaves the PivotTable unchanged, repeat the slicer insertion with a PivotTable cell selected. You can also ask for help diagnosing the mismatch.

Your workbook now has a transparent audit for its category analysis. Next, you will turn these verified metrics into the Retail Performance Command Center.

Build the Performance Dashboard

Your native PivotTable audit now agrees with the Copilot category comparison. That gives your working workbook a trustworthy audit trail.

Verified metrics still sit across separate worksheets. In this step, you'll use Copilot in Excel to turn them into one decision-focused Dashboard without hiding the source checks.

In this step, get ready to:

Plan a one-page dashboard with every required decision element.
Build the Dashboard worksheet through focused Copilot edits.
Verify three retail findings against the workbook's audit trail.

Plan the decision-focused dashboard

A useful dashboard connects the retail decision to the smallest set of supporting metrics. Copilot's plan mode lets you review that structure before the workbook changes.

Return to the open Retail_Performance_Command_Center.xlsx working workbook.
Select the Copilot icon in the lower-right corner of Excel.
Choose plan mode in the Copilot pane.
Draft the one-page layout by pasting this prompt:

Create a Dashboard worksheet with four KPIs: Total Sales, Net Profit, Profit Margin, and Returned Orders. Add a monthly Sales versus Net Profit trend. Add a Category profitability chart. Add a customer Segment comparison. Add a product risk table showing Product Name, Sales, Net Profit, average Discount, and returned-order count.

What does this prompt do?

The four KPIs provide an immediate performance summary.
The monthly trend reveals how revenue moves alongside return-adjusted profit.
The Category chart exposes differences between sales performance and profitability.
The Segment comparison connects results to customer groups.
The product risk table surfaces strong sellers with weak economics.

Confirm the plan names all four KPIs.
Confirm the plan includes the monthly Sales versus Net Profit trend.
Confirm the plan includes the Category profitability chart.
Confirm the plan includes the customer Segment comparison.
Confirm the plan includes every required product risk table field.

You'll see a proposed dashboard structure that covers each required decision angle. The exact chart styling may vary because Copilot generates workbook content from your request.

Is the plan missing a requirement?

Reply with the exact missing requirement before leaving plan mode. Ask Copilot to update the plan without removing any existing requirement.

Keep each follow-up focused on one gap. This makes the revised plan easier to review.

Help me complete my dashboard plan.

Build and refine the worksheet

Edit mode turns the reviewed plan into workbook content. A requirement-by-requirement check keeps an incomplete first result from becoming your final dashboard.

Switch Copilot from plan mode to edit mode.
Submit the reviewed dashboard plan for Copilot to apply.
Allow Copilot to finish the workbook edit.
Select the new Dashboard worksheet tab.

You'll see the first version of your Retail Performance Command Center. Its arrangement can differ while still meeting the required structure.

Confirm that Total Sales, Net Profit, Profit Margin, and Returned Orders appear as four KPIs.
Confirm that a monthly Sales versus Net Profit trend appears.
Confirm that a Category profitability chart appears.
Confirm that a customer Segment comparison appears.
Confirm that the product risk table contains Product Name, Sales, Net Profit, average Discount, and returned-order count.

Record one missing requirement here when needed: the missing dashboard element.
Request only that correction by pasting this prompt:

Add [[MISSING_DASHBOARD_ELEMENT="the missing dashboard element"]] to the Dashboard worksheet. Keep every existing dashboard element unchanged.

Why use a focused correction?

A focused request limits the next edit to one missing requirement. It protects the dashboard elements that already work.

Repeat the focused correction separately for each remaining gap.
Save Retail_Performance_Command_Center.xlsx after the dashboard contains every required element.
Confirm that the saved workbook still shows the complete Dashboard worksheet.

Can Copilot chat but not edit?

Copilot editing is only supported when Calculation Options are set to Automatic. Change that setting before retrying the focused edit.

If one element keeps disappearing, name that element explicitly in the correction. Tell Copilot to preserve the rest of the Dashboard worksheet.

Help me fix a Copilot workbook edit.

That is the main build complete. Your workbook now condenses the verified retail metrics into one decision-focused view.

Verify and publish three findings

A polished dashboard can still carry an unsupported conclusion. You'll treat the AI wording as a draft until each figure agrees with the native audit or the filtered source rows.

Switch Copilot to chat mode.
Generate the three draft findings by pasting this prompt:

Give me three concise findings covering the sales trend, discount or return risk, and a customer or product action.

What does this prompt produce?

The first finding describes a meaningful movement in the sales trend.
The second finding identifies a discount or return risk.
The third finding proposes an action for a customer segment or product.

Confirm that Copilot returns exactly three concise findings.
Confirm that one finding covers the sales trend.
Confirm that one finding covers discount or return risk.
Confirm that one finding recommends a customer or product action.
Identify every number or ranking that needs verification.

Are the findings too vague?

Ask Copilot to rewrite one vague finding with the specific metric it used. Keep the other two findings unchanged.

Do not add an unsupported figure to make the wording sound more precise. Verification decides which details belong on the dashboard.

Help me make one retail finding verifiable.

Choose the right audit source

Use Audit Pivot for Category rankings plus Category Sales or Net Profit figures. Its cleared slicers preserve the unfiltered comparison.

Use filtered RetailSales rows for dates, customer segments, products, discounts, or returned orders. This route lets you test the exact conditions named in a finding.

Compare every Category Sales or Net Profit figure with the unfiltered Audit Pivot worksheet.
Switch to Retail Data for each figure that depends on dates, segments, products, discounts, or returns.
Select the filter arrow for the relevant RetailSales column.
Filter the table to the period, segment, product, or return state named in the finding.

You'll now see only the transactions that support the claim you are checking.

Enable Total Row from the Table Design tab.
Choose Sum under Sales or Net Profit when checking totals.
Choose Average under Discount when checking discount risk.
Choose Count under Order ID after filtering Returned to Yes when checking returned-order count.
Compare the resulting value with the corresponding Copilot finding.

A matching value gives the finding an auditable source. A mismatch means the draft needs correction before it reaches the dashboard.

Clear every filter from RetailSales after completing the checks.
Turn off Total Row to restore the table's original layout.
Tell Copilot the confirmed value when a draft finding needs rewriting.
Add only the three verified findings to the Dashboard worksheet.
Save Retail_Performance_Command_Center.xlsx.

Before you inspect the finished sheet, do you think every headline claim now has a route back to a visible source?

Return to the Dashboard worksheet.
Confirm that Total Sales, Net Profit, Profit Margin, and Returned Orders appear as four KPIs.
Confirm that the monthly trend, Category profitability chart, and Segment comparison appear on the same worksheet.
Confirm that the product risk table contains all five required fields.
Confirm that each of the three findings uses only figures checked against Audit Pivot or filtered RetailSales rows.

You should see one Dashboard worksheet containing all four KPIs, all three visual sections, the product risk table, and three verified findings. Each conclusion now traces back to the workbook's native audit trail.

You have built the full command center. A retail manager can now move from headline performance to a defensible action without searching across separate sheets.

Secret mission

Secret Mission: Find the Hidden Profit Trap

Use Copilot to find a high-sales product whose weak profitability is hidden by headline revenue. Prove that the product meets every condition with a native PivotTable audit before adding a recommendation to your Dashboard.

Clean Up Your Resources

Your project stays in two local Excel workbooks, so it creates no new cloud resources or ongoing costs. Choose whether to keep both files, close Excel for later, or delete the copies you no longer need.

Resources you used:

The untouched NextWork_Retail_Sales.xlsx source workbook for restarting or comparing your analysis.
The Retail_Performance_Command_Center.xlsx working workbook containing your retail table, analysis sheets, audit, dashboard, findings, and product recommendation.

Keep everything running

No action is needed. Choose this option if you want to revisit your dashboard or use the untouched dataset for another analysis.

Keep NextWork_Retail_Sales.xlsx unchanged for future comparisons.
Keep Retail_Performance_Command_Center.xlsx as your editable Retail Performance Command Center.
Resume future analysis from Dashboard or Audit Pivot.

Pause - I'll come back to this later

Pausing leaves both workbooks on your computer without keeping Excel open. Your completed analysis remains available at no additional cost.

Save Retail_Performance_Command_Center.xlsx as an Excel Workbook.
Close Excel.
Reopen Retail_Performance_Command_Center.xlsx when you are ready to continue.
Select Dashboard to review the command center.
Select Audit Pivot to repeat the native audit.

Delete - I don't want to use this again

Remove your working workbook if you want a clean restart. Deleting your analysis can be hard to undo, so keep a backup if you may revisit the dashboard.

Close Excel.
Return to the Windows folder containing NextWork_Retail_Sales.xlsx and Retail_Performance_Command_Center.xlsx.
Select Retail_Performance_Command_Center.xlsx.
Press the Delete key.
Select NextWork_Retail_Sales.xlsx if you do not want to repeat the project.
Press the Delete key again.

You should no longer see Retail_Performance_Command_Center.xlsx in the folder. If you removed the source dataset too, neither workbook remains there.

Nice Work!

You did it! Your Retail Performance Command Center now turns raw transactions into retail decisions backed by verified evidence.

You've learned how to:

Built a structured Excel table with return-adjusted Net Profit plus Profit Margin.
Used Copilot in Excel to move from broad sales questions to a decision-focused dashboard covering trends, customers, returns, discounts, and product risk.
Verified AI-generated conclusions through a native PivotTable with Region, Segment, and Returned slicers.
Completed the Secret Mission by validating a high-sales product whose returns or discounting conceal weak profitability.

Ready to quiz yourself?