Names in Sheet 2 appear in the second column – to still use
these as your header, tap the formatting brush, go to Table
> Headers & Footer and increase Header Columns to 2.Summary sheet in Numbers
Extract data from a table with some handy multi-purpose tricks
IT WILL TAKE
40 minutesYOU WILL LEARN
How to use
VLOOKUP and tick
boxes to auto-generate
a summary sheetYOU’LL NEED
iOS 12 or higher and
Numbers installed
and updatedSummary sheets are commonly
used to pull together useful
information. In this example,
we’re pulling out a subset of data
from a table rather than compiling a new table
from multiple sources, using a table of scores
for a set of participants in a monthly test. How
can we show each participant’s personal
progress? Creating a separate table or sheet
for every participant would mean a lot of
manual duplication. Instead, we can make asingle summary sheet that will update with
the data for any participant we select.
We’ve used a separate sheet for two
reasons: firstly, it’s something you might
want to print or export by itself; secondly, we
can preserve a distinction between Sheet 1,
which is only for entering data, and Sheet 2,
which only outputs data. You could use the
same techniques with a second table on
Sheet 1, if you preferred, and you can always
lock any table (Paintbrush > Arrange > Lock)
to prevent editing.Looking up
The key function is VLOOKUP, which returns
data from a row selected by searching the
leftmost column of a range. We’ll also use
RANDBETWEEN to enter some dummy data,
which is helpful if you’re creating a template
for entering data you don’t have yet. Just
select the cells and tap Delete to clear the
table for real data when you have it.
You could also add more data to the chart
in Sheet 2, such as a line showing someone’s
average score so far or the average across all
participants in each test. Adam BanksGenius tip!
To remind people to
only tick one box,
double-click cell A1 in
Sheet 1, tap ‘=’ and enter
IF(COUNTIF($A2:A22),tr
ue)=1,””,”Tick one box”.APPLE APPS Summarise in Numbers
Namesin Sheet2 appearin thesecondcolumn– tostilluse
theseasyourheader,taptheformattingbrush,gotoTable
> Headers& FooterandincreaseHeaderColumnsto2.Summary sheet in Numbers
Extract datafroma tablewith somehandymulti-purposetricks
ITWILLTAKE
40 minutesYOUWILLLEARN
Howtouse
VLOOKUPandtick
boxestoauto-generate
a summarysheetYOU’LLNEED
iOS 12 or higher and
Numbers installed
and updatedSummary sheets are commonly
used to pull together useful
information. In this example,
we’re pulling out a subset of data
from a table rather than compiling a new table
from multiple sources, using a table of scores
for a set of participants in a monthly test. How
can we show each participant’s personal
progress? Creating a separate table or sheet
for every participant would mean a lot of
manual duplication. Instead, we can make asingle summary sheet that will update with
the data for any participant we select.
We’ve used a separate sheet for two
reasons: firstly, it’s something you might
want to print or export by itself; secondly, we
can preserve a distinction between Sheet 1,
which is only for entering data, and Sheet 2,
which only outputs data. You could use the
same techniques with a second table on
Sheet 1, if you preferred, and you can always
lock any table (Paintbrush > Arrange > Lock)
to prevent editing.Looking up
The key function is VLOOKUP, which returns
data from a row selected by searching the
leftmost column of a range. We’ll also use
RANDBETWEEN to enter some dummy data,
which is helpful if you’re creating a template
for entering data you don’t have yet. Just
select the cells and tap Delete to clear the
table for real data when you have it.
You could also add more data to the chart
in Sheet 2, such as a line showing someone’s
average score so far or the average across all
participants in each test. Adam BanksGeniustip!
To remind people to
only tick one box,
double-click cell A1 in
Sheet 1, tap ‘=’ and enter
IF(COUNTIF($A2:A22),tr
ue)=1,””,”Tick one box”.APPLE APPS Summarise in Numbers