Learning Excel: Tables and structured references

Format as Table

Every quarter brings new orders, and every time Isla has to change her formulas: B2:B21 becomes B2:B26. There's no need. A table knows how big it is.

A table

This list is formatted as a table, named Sales: a coloured header, banded rows and a filter button in every header. Type a row just below the table and it belongs to it straight away.

Look at the formula in G2: =[@Litres]*[@Price]. In a table you refer to the names of the columns. [@Litres] is the litres in the same row.

=[@Litres]*[@Price]

	A	B	C	D	E	F	G	I
1	Quarter	Beer	Customer	Packaging	Litres	Price	Revenue	Result
2	Q1	Wise Owl	Crown Inn	Keg	300	£2.40	£720.00
3	Q1	Tripel Hoot	Bay Tree Bistro	Bottle	80	£4.50	£360.00
4	Q1	Night Owl	Curlew Inn	Keg	150	£3.20	£480.00
5	Q1	Wise Owl	Festival Hall	Keg	600	£2.20	£1,320.00
6	Q1	Barn Owl	Bay Tree Bistro	Bottle	60	£4.00	£240.00
7	Q2	Wise Owl	Crown Inn	Keg	350	£2.40	£840.00
8	Q2	Tripel Hoot	Crown Inn	Bottle	90	£4.50	£405.00
9	Q2	Night Owl	Bay Tree Bistro	Bottle	120	£3.40	£408.00
10	Q2	Wise Owl	Curlew Inn	Keg	200	£2.40	£480.00
11	Q2	Barn Owl	Festival Hall	Keg	250	£3.60	£900.00
12	Q3	Wise Owl	Festival Hall	Keg	800	£2.20	£1,760.00
13	Q3	Tripel Hoot	Bay Tree Bistro	Bottle	110	£4.50	£495.00
14	Q3	Barn Owl	Crown Inn	Keg	180	£3.80	£684.00
15	Q3	Wise Owl	Curlew Inn	Keg	250	£2.40	£600.00
16	Q3	Night Owl	Crown Inn	Keg	100	£3.20	£320.00
17	Q4	Night Owl	Festival Hall	Keg	400	£3.00	£1,200.00
18	Q4	Tripel Hoot	Curlew Inn	Bottle	70	£4.50	£315.00
19	Q4	Wise Owl	Crown Inn	Keg	320	£2.40	£768.00
20	Q4	Barn Owl	Bay Tree Bistro	Bottle	90	£4.00	£360.00
21	Q4	Tripel Hoot	Festival Hall	Keg	300	£4.20	£1,260.00
22

A whole column

Sales[Litres] is the whole Litres column of the table, without the header. =SUM(Sales[Litres]) adds up all the litres.

When a new order comes in, the column grows with it and the formula counts it in by itself.

=SUM(Sales[Litres])

	A	B	C	D	E	F	G	I
1	Quarter	Beer	Customer	Packaging	Litres	Price	Revenue	Result
2	Q1	Wise Owl	Crown Inn	Keg	300	£2.40	£720.00	4820
3	Q1	Tripel Hoot	Bay Tree Bistro	Bottle	80	£4.50	£360.00
4	Q1	Night Owl	Curlew Inn	Keg	150	£3.20	£480.00
5	Q1	Wise Owl	Festival Hall	Keg	600	£2.20	£1,320.00
6	Q1	Barn Owl	Bay Tree Bistro	Bottle	60	£4.00	£240.00
7	Q2	Wise Owl	Crown Inn	Keg	350	£2.40	£840.00
8	Q2	Tripel Hoot	Crown Inn	Bottle	90	£4.50	£405.00
9	Q2	Night Owl	Bay Tree Bistro	Bottle	120	£3.40	£408.00
10	Q2	Wise Owl	Curlew Inn	Keg	200	£2.40	£480.00
11	Q2	Barn Owl	Festival Hall	Keg	250	£3.60	£900.00
12	Q3	Wise Owl	Festival Hall	Keg	800	£2.20	£1,760.00
13	Q3	Tripel Hoot	Bay Tree Bistro	Bottle	110	£4.50	£495.00
14	Q3	Barn Owl	Crown Inn	Keg	180	£3.80	£684.00
15	Q3	Wise Owl	Curlew Inn	Keg	250	£2.40	£600.00
16	Q3	Night Owl	Crown Inn	Keg	100	£3.20	£320.00
17	Q4	Night Owl	Festival Hall	Keg	400	£3.00	£1,200.00
18	Q4	Tripel Hoot	Curlew Inn	Bottle	70	£4.50	£315.00
19	Q4	Wise Owl	Crown Inn	Keg	320	£2.40	£768.00
20	Q4	Barn Owl	Bay Tree Bistro	Bottle	90	£4.00	£360.00
21	Q4	Tripel Hoot	Festival Hall	Keg	300	£4.20	£1,260.00
22

The Total Row

At the bottom of a table you can switch on a Total Row. It calculates with SUBTOTAL: =SUBTOTAL(109,[Total]) in D7 adds up the Total column.

The first argument picks the calculation: 109 is the sum, 101 the average, 102 the count, 104 the maximum and 105 the minimum.

=SUBTOTAL(109,[Total])

	A	B	C	D	E
1	Product	Quantity	Price	Total	VAT
2	Malt	40	£18.50	£740.00
3	Hops	12	£32.00	£384.00
4	Yeast	8	£14.75	£118.00
5	Bottles	30	£9.60	£288.00
6	Labels	25	£4.20	£105.00
7	Total			£1,635.00
8

Try it yourself

In I2, put the total revenue, using the Revenue column of the Sales table.

	A	B	C	D	E	F	G	I
1	Quarter	Beer	Customer	Packaging	Litres	Price	Revenue	Result
2	Q1	Wise Owl	Crown Inn	Keg	300	£2.40	£720.00
3	Q1	Tripel Hoot	Bay Tree Bistro	Bottle	80	£4.50	£360.00
4	Q1	Night Owl	Curlew Inn	Keg	150	£3.20	£480.00
5	Q1	Wise Owl	Festival Hall	Keg	600	£2.20	£1,320.00
6	Q1	Barn Owl	Bay Tree Bistro	Bottle	60	£4.00	£240.00
7	Q2	Wise Owl	Crown Inn	Keg	350	£2.40	£840.00
8	Q2	Tripel Hoot	Crown Inn	Bottle	90	£4.50	£405.00
9	Q2	Night Owl	Bay Tree Bistro	Bottle	120	£3.40	£408.00
10	Q2	Wise Owl	Curlew Inn	Keg	200	£2.40	£480.00
11	Q2	Barn Owl	Festival Hall	Keg	250	£3.60	£900.00
12	Q3	Wise Owl	Festival Hall	Keg	800	£2.20	£1,760.00
13	Q3	Tripel Hoot	Bay Tree Bistro	Bottle	110	£4.50	£495.00
14	Q3	Barn Owl	Crown Inn	Keg	180	£3.80	£684.00
15	Q3	Wise Owl	Curlew Inn	Keg	250	£2.40	£600.00
16	Q3	Night Owl	Crown Inn	Keg	100	£3.20	£320.00
17	Q4	Night Owl	Festival Hall	Keg	400	£3.00	£1,200.00
18	Q4	Tripel Hoot	Curlew Inn	Bottle	70	£4.50	£315.00
19	Q4	Wise Owl	Crown Inn	Keg	320	£2.40	£768.00
20	Q4	Barn Owl	Bay Tree Bistro	Bottle	90	£4.00	£360.00
21	Q4	Tripel Hoot	Festival Hall	Keg	300	£4.20	£1,260.00
22

My Excel writes

Ready for more?

The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson