Converting a range to a Table (Ctrl+T)

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which shortcut converts a selected data range into a Table in Excel?

A Ctrl+F

B Ctrl+T

2. Your data has column headings in row 1. What should you do in the Create Table box?

A Tick 'My table has headers'

B Leave 'My table has headers' unticked

3. You add a new row directly under a Table. What happens to the Table?

A It grows to include the new row

B It stays the same size and ignores the row

4. Which of these does a Table give you automatically?

A Filter buttons on every column heading

B A pivot chart added to a new sheet

5. Where do you rename a Table, for example to SalesData?

A In the Name Box at the top of the Home tab

B In the Table Name box on the Table Design tab

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Ctrl+T		AConverts the selected data range into a Table with named columns and filter buttons
2Insert > Table		BExcel adds its own headings (Column1, Column2) and treats your real headings as a data row. Tick the box first.
3Name your Table so formulas and charts are easy to read and find		CTable Name box: SalesData
4Tell Excel the first row holds your column headings before you create the Table		DA Table only grows when the new row is typed directly under its last row, with no blank row gap.
5Press Ctrl+T without ticking 'My table has headers' when row 1 holds headings		EThe menu route that does the same job as Ctrl+T and asks if your table has headers
6Type a new row two lines below the Table and expect the Table to grow		F[x] My table has headers

Definitions

Ctrl+T

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Select A1:D9, then press ______

Table name: Marks =SUM(_____[Maths])

Tick 'My table has _______' in the dialog

Google Sheets: Format > ________________

Table name: Marks Mean of Science, updates when rows are added =_______(Marks[Science])

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Click any cell in A1:D9

B Press Ctrl+T

C Untick My table has headers

D Click OK

Question 2

A Select A1:D9

B Press Ctrl+T

C Tick My table has headers

D Click Cancel to make the Table

Question 3

A Click inside the Table

B Open the Page Layout tab

C Type Sales in Table Name

Question 4

A Table name: Sales

B Column: Amount

C Formula in F1:

D =SUM(Sales(Amount))

Question 5

A Select the data range

B Format > Wrap text

C Check the column headers

Answer Key

Converting a range to a Table (Ctrl+T)

Section A: Quick Questions

B — Ctrl+T

A — Tick 'My table has headers'

A — It grows to include the new row

A — Filter buttons on every column heading

B — In the Table Name box on the Table Design tab

Section B: Match the Pairs

Ctrl+T → Converts the selected data range into a Table with named columns and filter buttons

Insert > Table → The menu route that does the same job as Ctrl+T and asks if your table has headers

Name your Table so formulas and charts are easy to read and find → Table Name box: SalesData

Tell Excel the first row holds your column headings before you create the Table → [x] My table has headers

Press Ctrl+T without ticking 'My table has headers' when row 1 holds headings → Excel adds its own headings (Column1, Column2) and treats your real headings as a data row. Tick the box first.

Type a new row two lines below the Table and expect the Table to grow → A Table only grows when the new row is typed directly under its last row, with no blank row gap.

Section C: Definitions

Ctrl+T — Shortcut that converts a data range into a Table with named columns, filter buttons and automatic growth when new rows are added. Tick 'My table has headers' first.

Section D: Fill in the Blanks

Select A1:D9, then press Ctrl+T

Table name: Marks
=SUM(Marks[Maths])

Tick 'My table has headers' in the dialog

Google Sheets: Format > Convert to table

Table name: Marks
Mean of Science, updates when rows are added
=AVERAGE(Marks[Science])

Section E: Spot the Error

C — The first row holds the column headings, so 'My table has headers' must be ticked. If it is unticked, Excel adds extra Column1, Column2 headings and treats your real headings as data.

D — Cancel closes the Create Table box without changing anything. You must click OK to convert the range into a Table.

B — The Table Name box is on the Table Design tab, which appears when you click inside a Table. Page Layout does not have it.

D — Structured references use square brackets around the column name, so it should be Sales[Amount]. Round brackets are only for the function. This formula grows when new rows are added.

B — In Google Sheets the Table is made with Format > Convert to table. Wrap text only changes how long text is displayed and does not create a Table.