Custom functions in Google Sheets - Dashboard Guide

Dashboard Mode - Custom functions

Goal

Calculate the area of different shapes using a custom function in Google Sheets to simplify repeated calculations.

Sample Data

Shape	Dimension 1	Dimension 2	Area
Rectangle	5	10
Triangle	6	8
Circle	7
Rectangle	3	4
Triangle	10	5

Dashboard Components

• KPI Card: Total Area of all shapes combined.
  Formula: =SUM(D2:D6)
• Custom Function: =CALCULATE_AREA(A2, B2, C2) used in cells D2 to D6.
  Function logic:
  - Rectangle: area = dimension1 * dimension2
  - Triangle: area = 0.5 * dimension1 * dimension2
  - Circle: area = PI() * (dimension1 ^ 2) (dimension1 is radius)
  Example formula in D2: =CALCULATE_AREA(A2, B2, C2)
• Table: Shows shapes, dimensions, and calculated area using the custom function.

Dashboard Layout

+---------------------------+
|       Total Area KPI      |
|         (Cell F1)         |
+---------------------------+
| Shape | Dim1 | Dim2 | Area |
|---------------------------|
| Data Table with custom fn |
+---------------------------+

Interactivity

Users can change the dimensions or shape names in columns A, B, and C. The custom function recalculates the area automatically in column D. The Total Area KPI updates to reflect the sum of all areas.

Self Check

If you change the shape in A3 from "Circle" to "Rectangle" and set Dimension 2 to 9, what happens to the area in D3 and the total area KPI?

Key Result

Dashboard calculates areas of shapes using a custom function and sums total area.