Using a spreadsheet template

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which best describes a spreadsheet template?

A A single formula that adds up a column of numbers

B A pre-built file with layout, formulas and formatting already set up

2. A teacher wants to enter marks for a new class each term. What is the best approach?

A Open a template and enter the new marks

B Rebuild the whole spreadsheet from scratch each term

3. Why should you lock the cells that contain formulas in a template?

A So users cannot accidentally overwrite or delete them

B So the formulas run faster

4. Which cells should be left unlocked in a template?

A The input cells where new data is typed

B The cells that hold the total formulas

5. How do you save a file so it can be reused as a template?

A Delete the data and hope nobody changes the layout

B Save a copy of the file in a template format, such as .xltx

Match the Pairs

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

Left column		Right column
1Save As > Excel Template (.xltx)		AType new values into B2:B9; =SUM(B2:B9) in B10 recalculates
2Review > Protect Sheet (formula cells locked)		BA1: Name \| B1: Score \| C1: Grade
3Reusing a template for new data: clear the old inputs but keep the formulas, so the totals update automatically		CA typed value replaced the formula, so the total will not update when new data is entered into the template.
4Keeping labels and layout consistent: every reuse has the same headings in the same cells		DLocks the formula cells so users can only type into the input cells and cannot overwrite the formulas
5Saving the template as report.xlsx and typing the new data over the old file		ESaves the finished layout, formulas and formatting as a reusable template file, so each new use starts from a clean copy
6B10 holds the typed number 540 instead of =SUM(B2:B9)		FSaved as a normal workbook (.xlsx) instead of a template (.xltx), so the original gets overwritten and the template is lost.

Definitions

template

Fill in the Blanks

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

=___(B2:B6)

=C2*_B$1

=__(B2="","",B2*C2)

Save as type: Excel Template (*.____)

="Report for "_A1

Spot the Error

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

Question 1

A A1: Item

B D2: =B2+C2

C D3: =B3*C3

D D4: =B4*C4

Question 2

A Select formula cells D2:D9

B Format Cells > untick Locked

C Review > Protect Sheet

Question 3

A File > Save As

B Save as type: CSV (Comma)

C File name: Sales_Template

Question 4

A Open Sales_Template.xltx

B Type new data in B2:C9

C Save over Sales_Template.xltx

D Formulas in D2:D9 recalculate

Question 5

A B1: 0.1 (GST rate)

B C2: =B2*$B$1

C C3: =B3*$B$1

D C4: =B4*B1

Answer Key

Using a spreadsheet template

Section A: Quick Questions

B — A pre-built file with layout, formulas and formatting already set up

A — Open a template and enter the new marks

A — So users cannot accidentally overwrite or delete them

A — The input cells where new data is typed

B — Save a copy of the file in a template format, such as .xltx

Section B: Match the Pairs

Save As > Excel Template (.xltx) → Saves the finished layout, formulas and formatting as a reusable template file, so each new use starts from a clean copy

Review > Protect Sheet (formula cells locked) → Locks the formula cells so users can only type into the input cells and cannot overwrite the formulas

Reusing a template for new data: clear the old inputs but keep the formulas, so the totals update automatically → Type new values into B2:B9; =SUM(B2:B9) in B10 recalculates

Keeping labels and layout consistent: every reuse has the same headings in the same cells → A1: Name | B1: Score | C1: Grade

Saving the template as report.xlsx and typing the new data over the old file → Saved as a normal workbook (.xlsx) instead of a template (.xltx), so the original gets overwritten and the template is lost.

B10 holds the typed number 540 instead of =SUM(B2:B9) → A typed value replaced the formula, so the total will not update when new data is entered into the template.

Section C: Definitions

template — A pre-built file with the layout, formulas and formatting already set up, so new data can be entered quickly and consistently every time it is reused.

Section D: Fill in the Blanks

=SUM(B2:B6)

=C2*$B$1

=IF(B2="","",B2*C2)

Save as type: Excel Template (*.xltx)

="Report for "&A1

Section E: Spot the Error

B — The template's Total column should multiply price by quantity. Line 2 adds them, so it should be =B2*C2, matching the other rows.

B — Formula cells must stay Locked so protecting the sheet stops people overwriting them. Unticking Locked leaves them editable, so line 2 should tick it.

B — To save a file as a template, choose the Excel Template type (.xltx). A CSV file keeps no formulas, formatting or locking.

C — When reusing a template, save the filled-in copy under a new name with Save As. Saving over the template destroys the blank original.

D — The GST rate must be an absolute reference ($B$1) so every row uses the same cell. B1 is relative and would shift when copied. It should be =B4*$B$1.