SPLIT function in Google Sheets - Dashboard Guide

Dashboard Mode - SPLIT function

Goal

Split full names into first and last names automatically for easier sorting and analysis.

Sample Data

Full Name	First Name	Last Name	Department	Salary
John Smith	John	Smith	Sales	50000
Mary Johnson	Mary	Johnson	Marketing	55000
Linda Lee	Linda	Lee	HR	48000
James Brown	James	Brown	IT	60000
Patricia Davis	Patricia	Davis	Finance	62000

Dashboard Components

• First Name Column: Formula =INDEX(SPLIT(A2," "),1) entered in B2 and dragged down. This extracts the first name from the full name in A2.
• Last Name Column: Formula =INDEX(SPLIT(A2," "),2) entered in C2 and dragged down. This extracts the last name from the full name in A2.
• Total Salary: Formula =SUBTOTAL(9,E2:E6) in cell E7 to show total salary of visible employees.
• Average Salary: Formula =SUBTOTAL(1,E2:E6) in cell E8 to show average salary of visible employees.
• Department Count: Formula =COUNTA(UNIQUE(FILTER(D2:D6,SUBTOTAL(3,OFFSET(D2,ROW(D2:D6)-ROW(D2),0,1))))) in cell E9 to count unique visible departments.

Dashboard Layout

+----------------+----------------+----------------+----------------+----------------+
| Full Name (A)  | First (B)      | Last (C)       | Dept (D)       | Salary (E)     |
+----------------+----------------+----------------+----------------+----------------+
| John Smith     | John           | Smith          | Sales          | 50000          |
| Mary Johnson   | Mary           | Johnson        | Marketing      | 55000          |
| Linda Lee      | Linda          | Lee            | HR             | 48000          |
| James Brown    | James          | Brown          | IT             | 60000          |
| Patricia Davis | Patricia       | Davis          | Finance        | 62000          |
+----------------+----------------+----------------+----------------+----------------+
|                |                |                | Total Salary   | 275000         |
|                |                |                | Average Salary | 55000          |
|                |                |                | Dept. Count    | 5              |
+----------------+----------------+----------------+----------------+----------------+

Interactivity

Adding a filter on the Department column will update the Total Salary, Average Salary, and Department Count to reflect only the filtered employees. The First Name and Last Name columns will show split names only for visible rows.

Self Check

If you add a filter to show only employees from the 'Sales' and 'IT' departments, which components update?

• The Total Salary will update to sum salaries of Sales and IT employees only (110000).
• The Average Salary will update to average salaries of Sales and IT employees only (55000).
• The Department Count will update to count only Sales and IT departments (2).
• The First Name and Last Name columns will show split names only for visible rows.

Key Result

Dashboard splits full names into first and last names and summarizes salary and department data.