A Data table puts a grid on your page — rows going
across, columns going down, like a small spreadsheet.
Use one when a single number is not the whole story. If a visitor asks
“what will this cost?”, sometimes the honest answer is
£4,200 — and sometimes it is a list showing where those £4,200
came from.
Tables are good for things like:
- A quote broken down line by line, so people can see what they are paying
for.
- A year-by-year projection — savings, output, growth.
- A payment schedule.
- A price list or a comparison of your packages.
Adding one
In the left-hand panel, open Elements, find the
Charts & tables group, and drag Data table
onto your calculator.
Then, in the settings panel on the right, open the Data tab.
This is where you tell the table what to put in it. Nothing will appear until
you do.
Step one: where do the rows come from?
The Data from dropdown offers three choices. Pick the one
that matches where your information already lives.
- This calculator’s fields and results — the
easiest. You tick which questions and answers to include, and the table
lists them. Use this for a summary of what the visitor filled in. You
can also flip it so each one becomes a column instead of a row.
- A range from the connected Google Sheet — if you
keep a price list in a spreadsheet, type where it sits, like
A1:D20, and that block appears on your page. Tell it
whether your first row contains the column headings.
- Table Builder — for rows that do not exist yet.
Nobody has typed them anywhere, because they depend on what the visitor
just entered. You describe the rows once, and the calculator works them
out. This is explained below.
There is also a switch called Reload when the visitor changes an
input. Leave it on and the table keeps up as people type. The only
reason to turn it off is a table that never changes, like a fixed price
list.
Table Builder, in plain terms
The first two choices show information you already have. Table Builder is
different: you write a short instruction, and the calculator builds the rows
from it.
The instruction is called a TABLE() formula. It always starts
the same way — your column headings in square brackets — and then
you add rows underneath in one of two ways:
- Type a row out yourself. Good when you know what the
rows are and only the numbers change.
- Use
GENERATE(). Good when you do not know
how many rows there will be, because that depends on the visitor’s
answer — 5 years or 25, 12 months or 360.
You can use both in one table, and you can use one without ever touching the
other.
About the three examples below. They are complete, working
examples — not steps to follow in order, and not a set you need all
of. Each one is a different way of using Table Builder. Find the one closest
to what you are building, copy it into the Table box, and
change the wording and the numbers to suit you. The names in square brackets
like [guests] are your own fields, so swap those for yours.
Example #1 — An itemised quote (rows you type yourself)
A caterer wants to show what an event will cost, line by line. There are
always the same four lines — only the quantities and totals move,
depending on how many guests were entered.
TABLE(
["Item", "Quantity", "Each", "Line total"],
["Canapes on arrival", [guests], 4.50, [guests] * 4.50],
["Three-course dinner", [guests], 32.00, [guests] * 32.00],
["Waiting staff", [staff], 145, [staff] * 145],
["Delivery and setup", 1, 180, 180]
)
With 80 guests and 4 staff, the visitor sees:
| Item | Quantity | Each | Line total |
|---|
| Canapes on arrival | 80 | 4.50 | 360.00 |
| Three-course dinner | 80 | 32.00 | 2,560.00 |
| Waiting staff | 4 | 145.00 | 580.00 |
| Delivery and setup | 1 | 180.00 | 180.00 |
Reading the formula:
- The first line,
["Item", "Quantity", "Each", "Line total"],
is the headings across the top.
- Every line after it is one row of the table. Each gives four values,
because there are four columns, in the same order.
- Words go in quotes. Numbers do not.
[guests] means “whatever the visitor typed in the
Guests field”, so [guests] * 4.50 is that number
multiplied by 4.50.
To add a line, copy one and edit it. To remove a line, delete it. That is the
whole idea — there is nothing else going on here.
Example #2 — A 25-year projection (rows the calculator makes)
A solar installer wants to show what a system saves over its lifetime. Typing
out 25 rows would be miserable, and the visitor might choose 10 years or 30
— so instead you describe one row, and say how many to make.
TABLE(
["Year", "Output (kWh)", "Saving", "Saved so far"],
GENERATE([years], ROW(
INDEX,
IF(INDEX = 1, [system_kw] * 950, PREV("Output (kWh)") * 0.995),
CURRENT("Output (kWh)") * [tariff],
IF(INDEX = 1, CURRENT("Saving"), PREV("Saved so far") + CURRENT("Saving"))
)),
["Total", "", "", PREV("Saved so far")]
)
The visitor sees this (shortened — it really runs to 25 rows):
| Year | Output (kWh) | Saving | Saved so far |
|---|
| 1 | 4,275.00 | 1,026.00 | 1,026.00 |
| 2 | 4,253.63 | 1,020.87 | 2,046.87 |
| 3 | 4,232.36 | 1,015.77 | 3,062.64 |
| … |
| Total | | | 24,168.41 |
GENERATE([years], ROW(...)) means “make this many rows,
and here is what one row looks like”. Inside it, three special words let a
row know about the rest of the table:
INDEX — which row this is, counting 1, 2, 3…
Above, it fills the Year column, and it is also how the first row is
told apart from the others.PREV("Output (kWh)") — the value in that column on the
row above this one. That is how each year comes out slightly
lower than the last: it is 99.5% of whatever the year before was.CURRENT("Saving") — a value already worked out
on this same row, in a column further left. Columns fill in
left to right, so you can only look leftwards.
You will see IF(INDEX = 1, ..., ...) twice. It means “if
this is the very first row, use this; otherwise use that”. The first row
needs it because there is no row above it to look at.
The last line is worth noticing. A typed row placed after a
GENERATE() becomes a total at the bottom, because
PREV() there reads the final row the calculator made. Put a typed
row before the GENERATE() instead and it becomes an opening
balance at the top.
Example #3 — A repayment schedule (each row builds on the last)
This is Example #2’s idea pushed a little further. Every row depends on
what was left over by the row above it, which is what makes a loan schedule
work.
TABLE(
["Month", "Payment", "Interest", "Balance"],
GENERATE([term_months], ROW(
INDEX,
ABS(PMT([rate] / 12, [term_months], [amount])),
IF(INDEX = 1, [amount], PREV("Balance")) * [rate] / 12,
IF(INDEX = 1, [amount], PREV("Balance"))
- (CURRENT("Payment") - CURRENT("Interest"))
)))
On £10,000 over 12 months at 6%:
| Month | Payment | Interest | Balance |
|---|
| 1 | 860.66 | 50.00 | 9,189.34 |
| 2 | 860.66 | 45.95 | 8,374.62 |
| 3 | 860.66 | 41.87 | 7,555.83 |
| … |
| 12 | 860.66 | 4.28 | 0.00 |
PMT() hands back a negative number — that is the
spreadsheet convention for money going out. So it is wrapped in
ABS(), which strips the minus sign off. Forget that and the
balance climbs instead of falling, which looks like a broken calculator.
This same shape covers far more than loans. Anything with a running total
works the same way: equipment losing value year by year, stock counting down
week by week, holiday days accrued and taken, a project spending through its
budget. Only the arithmetic in the middle changes.
Tidying up the numbers
Each column can be formatted on its own — as money, as a percentage, or
as a plain whole number. That is how a Year column stays 1, 2, 3
while the column next to it reads £1,026.00.
Do not go mad with row counts. A 30-year monthly schedule is 360 rows, which
is already more than anybody reads. If you find yourself making thousands,
switch to yearly rows instead — it will be quicker and far easier to
look at.
Making the table easier to read
Open the Design tab once there is real data in your table.
These are the settings that make the difference:
- Let visitors sort columns — they can click a
heading to reorder the rows.
- Show a search box — filters the rows as they type.
Worth turning on past about ten rows.
- Keep the header visible when scrolling — the
column headings stay put on a long table, so people do not lose track of
which column is which.
- Shade alternate rows — on already, and it helps
more than anything else here. Leave it on.
- Maximum height — set a height and the rows scroll
inside the table, instead of a long table shoving the rest of your page
down the screen. Almost always what you want for a schedule.
- Text alignment — choose Labels left, numbers
right. It lines the digits up underneath each other, and columns of
money are very hard to compare otherwise.
If your table is wide, set the width to Fixed maximum. It will then
scroll sideways rather than squashing the columns, which is what you want on a
phone.
If your table looks wrong
- It is empty — you probably have not chosen
anything in the Data tab yet, or the results it shows
have not been calculated. Preview the calculator and fill the form in.
- “a row has 3 values but there are 4 columns”
— you have one heading more, or fewer, than the values in a row.
Count both and make them match.
- Everything shows as 0 — a field name in square
brackets probably does not match a real field. Check the spelling.
