HOW TO Create a selective pull-out sheet
1
Create a spreadsheet
Open Numbers and tap Create
Spreadsheet, then Blank. This creates
a single sheet with one table. Unlike in
Excel, you don’t get infinite cells. Tap
the formatting brush, then Table to set
the size – here it’s 13 columns.2
Enter some data
We want to summarise the data for
each person, so start by entering names
in the first column. (If you wanted your
summary sheet to show data for each
month, you could list months vertically
and people horizontally.)3
Autofill month headings
Type ‘January’ as the next column
heading, then tap Cell at the top right of
the on-screen keyboard (or bottom right
of the screen), tap Autofill Cells, and drag
the right handle of the yellow box across
to fill in 12 months.4
Enter dummy data
We don’t know what scores out of
100 everyone will get, so just for now,
double-tap the first cell (B2), tap the ‘=’
icon and enter RANDBETWEEN(0,100).
Pinch to zoom out, and autofill this across
all cells, then down.5
Add tick boxes
Tap ‘A’ to select the first column,
pick Column Actions from the menu and
tap Add Column Before. Drag to select
Rows 2 to 22 of the new column (A2:22),
tap the formatting paintbrush and set
Format to Tickbox.6
Add a second sheet
Tap the plus sign at the top left and
pick New Sheet. Size the default table to
2 rows and 14 columns. We now want to
fill this with the data series for whichever
participant is ticked on Sheet 1. Time for
a VLOOKUP!7
Write a VLOOKUP
Double-tap cell B2, tap the ‘=’ icon
and type VLOOKUP(true followed by a
comma, then go to Sheet 1 and drag-
select all the cells. Tap the up arrow at
the right of the range and turn on all
the ‘Preserve’ switches.8
Repeat for all columns
Add a comma, then COLUMN(),0)
and click the green tick. In Sheet 2,
autofill this function across the row.
You’ll see red triangles. Go back to Sheet
1 and tick one of the boxes. Sheet 2 will
now fill with that row’s data.9
Add a chart
Double-tap cell C1 in Sheet 2, tap
‘=’, go to Sheet 1 and tap the same cell
(‘January’). Autofill this across the row.
Drag-select all the columns from C
onwards, tap ‘+’ at the top right and
pick a chart type to add.Summarise in Numbers APPLE APPS
HOW TO Create a selective pull-out sheet
1
Create a spreadsheet
Open Numbers and tap Create
Spreadsheet, then Blank. This creates
a single sheet with one table. Unlike in
Excel, you don’t get infinite cells. Tap
the formatting brush, then Table to set
the size – here it’s 13 columns.2
Enter some data
We want to summarise the data for
each person, so start by entering names
in the first column. (If you wanted your
summary sheet to show data for each
month, you could list months vertically
and people horizontally.)3
Autofill month headings
Type ‘January’ as the next column
heading, then tap Cell at the top right of
the on-screen keyboard (or bottom right
of the screen), tap Autofill Cells, and drag
the right handle of the yellow box across
to fill in 12 months.4
Enter dummy data
We don’t know what scores out of
100 everyone will get, so just for now,
double-tap the first cell (B2), tap the ‘=’
icon and enter RANDBETWEEN(0,100).
Pinch to zoom out, and autofill this across
all cells, then down.5
Add tick boxes
Tap ‘A’ to select the first column,
pick Column Actions from the menu and
tap Add Column Before. Drag to select
Rows 2 to 22 of the new column (A2:22),
tap the formatting paintbrush and set
Format to Tickbox.6
Add a second sheet
Tap the plus sign at the top left and
pick New Sheet. Size the default table to
2 rows and 14 columns. We now want to
fill this with the data series for whichever
participant is ticked on Sheet 1. Time for
a VLOOKUP!7
Write a VLOOKUP
Double-tap cell B2, tap the ‘=’ icon
and type VLOOKUP(true followed by a
comma, then go to Sheet 1 and drag-
select all the cells. Tap the up arrow at
the right of the range and turn on all
the ‘Preserve’ switches.8
Repeat for all columns
Add a comma, then COLUMN(),0)
and click the green tick. In Sheet 2,
autofill this function across the row.
You’ll see red triangles. Go back to Sheet
1 and tick one of the boxes. Sheet 2 will
now fill with that row’s data.9
Add a chart
Double-tap cell C1 in Sheet 2, tap
‘=’, go to Sheet 1 and tap the same cell
(‘January’). Autofill this across the row.
Drag-select all the columns from C
onwards, tap ‘+’ at the top right and
pick a chart type to add.Summarise in Numbers APPLE APPS