top of page

Training Manual How to Create the Invoice Template (Step-by-Step)

  • 3 days ago
  • 3 min read

Updated: 2 days ago

Overview


In this session, we will build a fully functional invoice template in Excel.

By the end of this exercise, you will be able to:

  • Design a professional invoice layout

  • Use formulas to calculate totals and VAT

  • Apply conditional logic

  • Format a clean business document

  • Prepare a reusable invoice template

STEP 1 – Set Up the Workbook

  1. Open Excel.

  2. Click Blank Workbook.

  3. Save the file as:

    Invoice Template Training.xlsm

    (If using macros, save as .xlsm. If not, .xlsx is fine.)

  4. Rename Sheet1 to:

    Invoice Template


STEP 2 – Create the Invoice Header


Company Title

  1. Click cell A1.

  2. Type:

    Your Company Name

  3. Increase the font size (16–20pt).

  4. Make it Bold.


Invoice Title

  1. Go to cell H1.

  2. Type:

    Invoice

  3. Increase font size.

  4. Make it Bold.


Formatting training1: Create a box from Cell B3 to D4 colour in Cell B3 to Cell B6 blue or your chosen colour.

Formatting training1: Create a line under the text from Cell A1 to Cell I1


STEP 3 – Create Invoice Information Section

Enter the following labels and example values:

Cell

Text

B3

Invoice No.

B4

PO No.

B5

Date

B6

Terms (days)

C6

30, 60, 90 (Using Data Validation List function)

B7

VAT Registered (This mean is the company VAT registered or not

C7

Yes, No (Using Data Validation List function)

Format:


  • Make labels bold

  • Format Date as Short Date


STEP 4 – Create “Invoice To” Section


In cell B8, type:

Invoice to:

Then enter example customer details below:

B10: ABC Ltd (Customer Name)
B11: Unit 12 (Address 1)
B12: Crown Industrial Estate (Address 1)
B13: Swindon (Address 1)
B14: Wiltshire (Address 1)
B15: BA1 1GQ (Address 1)
B16: abc@email.com (Email)

You can adjust alignment and spacing for a clean layout.


Extra skill (Do you want me to teach you how to auto populate the company details of your customer after using Data Validation on cell B10 to select the customer your looking for)


STEP 5 – Create the Invoice Table Structure

Go to Row 18 and enter:

Column

Header

B19

Description

F19

Qty

G19

Price

H19

VAT

I19

Total (exc VAT)

Make headers:

  • Bold

  • Add borders

  • Add light background colour


STEP 6 – Create Line Calculations


Starting in row 19:


VAT Formula (Column H)

In cell H19, enter:

==IF($C$7="Yes",IF(F20<>"",F20*G20*20%,""),"")

This formula:

  • Checks if quantity is entered

  • Calculates VAT at 20%

  • Leaves blank if no quantity


Copy this formula down to row 35.


Line Total Formula (Column I)


In cell I19, enter:

=IF(F20<>"",F20*G20,"")

This:

  • Multiplies Quantity × Price

  • Leaves blank if empty


Copy down to row 35.


STEP 7 – Create Summary Section

Move to Row 39.


Enter:

Cell

Text

G40

Subtotal:

G41

VAT:

G42

Total:

Subtotal Formula


In cell I39, enter:

=SUM(I20:I36)

VAT Total Formula

In cell I40, enter:

=SUM(H20:H36)

Final Invoice Total

In cell I41, enter:

=SUM(I40:I41)

Make the final total:

  • Bold

  • Larger font

  • Add border


STEP 8 – Add Customer Message Section


In cell B37, type:

Customer Message

Leave space underneath for notes.


STEP 9 – Add Company & Payment Details


Add:

Cell

Text

B44

Company Details

F44

Payment Details

B45

Company No.

B46

VAT No.

F45

Acct No.

F46

Bank

Make section headers bold.


STEP 10 – Formatting for Professional Look


To make it look like a real invoice:

✔ Adjust column widths

✔ Add borders to invoice table

✔ Align numbers to the right

✔ Format currency columns as £ (or local currency)

✔ Remove gridlines (View → Untick Gridlines)

✔ Add logo in top right if desired


STEP 11 – Protect Formula Cells (Optional Advanced Step)


To prevent users overwriting formulas:


  1. Select formula cells.

  2. Right-click → Format Cells → Protection.

  3. Tick Locked.

  4. Go to Review → Protect Sheet.


This keeps your template secure.


What This Template Teaches


This invoice demonstrates:


  • IF logic

  • Conditional calculations

  • VAT calculation

  • SUM functions

  • Professional formatting

  • Real-world finance workflow


Real-World Application

This template reflects how finance teams:


  • Raise invoices

  • Calculate VAT

  • Track totals

  • Prepare documents for customers

  • Structure financial records


This is a practical, interview-ready finance task.


This Is For The Teacher (Final Teaching Tip)


When delivering this:


  • Build it live with learners.

  • Explain every formula clearly.

  • Show what happens when quantity is deleted.

  • Demonstrate how totals update automatically.


Then ask learners to recreate it independently.

Comments

bottom of page