Column transformations in Excel - Dashboard Guide

Dashboard Mode - Column transformations

Dashboard Goal

This dashboard helps you transform and analyze sales data by creating new columns with formulas. You will see total sales, sales tax, and sales after tax for each product.

Sample Data

Product	Price	Quantity Sold
Notebook	5	100
Pen	2	300
Backpack	20	50
Calculator	15	40
Ruler	3	150

Dashboard Components

• Total Sales Column: Formula in D2: =B2*C2 (multiplies Price by Quantity Sold)
• Sales Tax Column (10%): Formula in E2: =D2*0.1 (calculates 10% tax on Total Sales)
• Sales After Tax Column: Formula in F2: =D2+E2 (adds Total Sales and Sales Tax)
• Summary Table: Shows total revenue before tax, total tax collected, and total revenue after tax using these formulas:
  • Total Revenue Before Tax (cell B8): =SUM(D2:D6)
  • Total Tax Collected (cell B9): =SUM(E2:E6)
  • Total Revenue After Tax (cell B10): =SUM(F2:F6)

Dashboard Layout

+-----------------------------+
|        Sales Data Table      |
|  (Columns A to C, rows 1-6) |
+-----------------------------+
| Total Sales | Sales Tax | Sales After Tax |
| (Cols D-F, rows 1-6)          |
+-----------------------------+
|        Summary Table          |
| (Cells A8:B10)               |
+-----------------------------+

Interactivity

You can change the Quantity Sold or Price values in the data table. When you do, the Total Sales, Sales Tax, and Sales After Tax columns update automatically. The summary table below also updates to show new totals.

Self Check

If you change the Quantity Sold for the Pen from 300 to 400, which components update?

• The Total Sales for Pen updates (D3).
• The Sales Tax for Pen updates (E3).
• The Sales After Tax for Pen updates (F3).
• The summary totals in cells B8, B9, and B10 update to reflect the new totals.

Key Result

Dashboard showing sales data with transformed columns for total sales, tax, and after-tax sales plus summary totals.