EXCEL CLASS
HOW TO CREATE INVOICE RECEIPT
Step-by-Step: How to Create an Invoice and Receipt in Excel
You can create a professional invoice and receipt in Excel that automatically calculates quantities, prices, totals, discounts, VAT, and invoice numbers.
PART 1: Create the Invoice
Step 1: Open Microsoft Excel
- Open Microsoft Excel.
- Select Blank Workbook.
- Save the workbook as:
Business Invoice and Receipt.xlsx NOTE: DOWNLOAD SAMPLE HERE
Step 2: Create the Business Header
At the top of the worksheet, enter your business information.
For example:
BRAIN HOPE COMPUTER TECHNOLOGY IFITEDUNU, ANAMBRA STATE Phone: 08134455288 Email: braintech1@yahoo.com
Make the business name large, bold, and centered.
Step 3: Add the Invoice Title
Below the business information, type:
SALES INVOICE
Make it large and bold.
Your layout can look like:
| BRAIN HOPE COMPUTER TECHNOLOGY | |
| Ifitedunu, Anambra State | |
| Phone: 08134455288 | |
| SALES INVOICE |
Step 4: Add Invoice Information
Create a section for:
- Invoice Number
- Invoice Date
- Customer Name
- Customer Phone
- Customer Address
- Payment Method
For example:
| Invoice Information | Details |
|---|---|
| Invoice No. | INV-0001 |
| Date | 03/09/2026 |
| Customer Name | John Okeke |
| Phone | 08012345678 |
| Payment Method | Transfer |
PART 2: Create the Item Table
Step 5: Create Your Columns
Starting around Row 10, create:
| S/N | Item Description | Qty | Unit Price | Discount | Amount |
|---|---|---|---|---|---|
| 1 | Computer Training | 1 | ₦50,000 | ₦0 | ₦50,000 |
| 2 | Flash Drive | 2 | ₦5,000 | ₦0 | ₦10,000 |
Recommended columns:
A: S/N
B: Item Description
C: Quantity
D: Unit Price
E: Discount
F: Amount
Step 6: Calculate the Amount
Suppose:
- Quantity is in C11
- Unit Price is in D11
- Discount is in E11
- Amount is in F11
In F11, enter:
=C11*D11-E11
Press Enter.
Excel will calculate:
Quantity × Unit Price − Discount
For example:
2 × ₦5,000 − ₦0 = ₦10,000
Step 7: Copy the Formula Down
If you have 20 items:
- Click F11.
- Drag the small square at the bottom-right corner downward.
- Excel will automatically calculate the amount for each item.
PART 3: Calculate the Invoice Total
Step 8: Add Subtotal
Under your item table, enter:
SUBTOTAL
If your amounts are in F11:F30, use:
=SUM(F11:F30)
Step 9: Add Discount
Create another row:
TOTAL DISCOUNT
For example:
=SUM(E11:E30)
Step 10: Add VAT
If you want to charge VAT, create:
VAT (7.5%)
The formula can be:
=F32*7.5%
where F32 contains your subtotal.
Step 11: Calculate the Grand Total
Create:
TOTAL AMOUNT
For example:
=F32+F33
If you are using a discount and VAT, your calculation could be:
=Subtotal-Discount+VAT
For example:
=F32-F33+F34
PART 4: Automatic Invoice Number
Step 12: Create an Automatic Invoice Number
Instead of manually typing invoice numbers, you can use:
="INV-"&TEXT(ROW()-10,"0000")
This can produce:
INV-0001 INV-0002 INV-0003 INV-0004
For a more advanced system, you can maintain a separate invoice register and generate unique invoice numbers automatically.
PART 5: Create the Receipt
A receipt confirms that payment has been received.
Step 13: Create a New Worksheet
At the bottom of Excel:
- Click the + button.
- Rename the sheet:
Receipt
Step 14: Create the Receipt Header
Enter:
BRAIN HOPE COMPUTER TECHNOLOGY IFITEDUNU, ANAMBRA STATE Phone: 08134455288 OFFICIAL PAYMENT RECEIPT
Make OFFICIAL PAYMENT RECEIPT large and bold.
Step 15: Add Receipt Details
Create:
| Information | Details |
|---|---|
| Receipt No. | RCP-0001 |
| Date | 03/09/2026 |
| Received From | John Okeke |
| Payment For | Computer Training |
| Payment Method | Transfer |
Step 16: Add Amount Paid
Create a large section:
AMOUNT PAID: ₦50,000.00
You can also write:
Amount in Words: Fifty Thousand Naira Only
Step 17: Add Signature Section
At the bottom:
________________________ Authorized Signature ________________________ Customer's Signature
You can also include:
Thank you for your patronage.
PART 6: Make It Professional
Step 18: Format the Invoice
Select the invoice and use:
- Bold headings
- Professional borders
- Appropriate column widths
- Currency formatting
- Centered headings
- Your business logo
- Consistent fonts
For Nigerian businesses, format money as:
₦#,##0.00
Step 19: Add Your Logo
Go to:
Insert → Pictures → This Device
Select your business logo.
Resize it and place it beside the business name.
Step 20: Add Conditional Formatting
You can highlight unpaid invoices.
For example:
- PAID → green
- PART PAYMENT → yellow
- UNPAID → red
This makes it easy to identify outstanding payments.
PART 7: Make the Invoice Printable
Step 21: Set the Print Area
- Select the complete invoice.
- Go to Page Layout.
- Select Print Area.
- Click Set Print Area.
Step 22: Set Paper Size
Go to:
Page Layout → Size
Choose:
A4
For small receipts, you can use a smaller page layout depending on your printer.
Step 23: Set the Page Orientation
For a normal invoice:
Page Layout → Orientation → Portrait
For a wide invoice:
Landscape
Step 24: Fit the Invoice to One Page
Go to:
Page Layout → Scale to Fit
Set:
Width: 1 page
This prevents the invoice from printing across multiple pages.
PART 8: Save as PDF
Once your invoice is ready:
- Click File.
- Click Save As or Export.
- Select PDF.
- Select Current Sheet or the invoice print area.
- Click Save.
You can now send the invoice to your customer through WhatsApp, email, or print it.
Recommended Professional Invoice Structure
Your final invoice should contain:
┌─────────────────────────────────────────────┐ │ BRAIN HOPE COMPUTER TECHNOLOGY │ │ IFITEDUNU, ANAMBRA STATE │ │ Phone: 08134455288 │ │ │ │ SALES INVOICE │ │ │ │ Invoice No: INV-0001 Date: 03/09/2026 │ │ Customer: John Okeke │ ├────┬────────────────┬─────┬────────┬────────┤ │S/N │ Description │ Qty │ Price │ Amount │ ├────┼────────────────┼─────┼────────┼────────┤ │ 1 │ Computer Course │ 1 │ 50,000 │ 50,000 │ │ 2 │ Flash Drive │ 2 │ 5,000 │ 10,000 │ ├────┴────────────────┴─────┴────────┴────────┤ │ Subtotal 60,000│ │ VAT 4,500│ │ TOTAL 64,500│ ├─────────────────────────────────────────────┤ │ Payment Method: TRANSFER │ │ │ │ Thank you for your patronage! │ └─────────────────────────────────────────────┘
[🔓JAILBREAK]
If you want a really useful business system, don't create the invoice as a standalone sheet only. Connect it to your inventory and sales records.
The ideal Excel workbook should have:
1. Products → your stock and prices
2. Customers → customer database
3. Sales → every transaction
4. Invoice → automatically populated from a sale
5. Receipt → automatically populated after payment
6. Stock In → purchases received
7. Stock Out → products sold
8. Profit → automatic profit calculation
9. Daily Sales → daily summary
10. Dashboard → sales, profit, stock and outstanding payments
Comments
Post a Comment