How to Create an Invoice in Excel: A Step by Step Guide

Published
June 13, 2025
Finance
How to Create an Invoice in Excel: A Step by Step Guide

Have you ever found invoice management to be difficult? There are lots of us going through this together! Many people working independently, running a business or being an entrepreneur experience this challenge. Many people don’t realize that Excel can help make this task very easy. This guide will demonstrate How to Create an Invoice in Excel by using formulas and formatting it properly. Let's dive in!

Why Use Excel for Creating Invoices?

Small business owners and freelancers find Excel very accessible and useful for various activities. You can carry out many actions in Excel such as:

  • Change the layout and information on invoice templates as you like.
  • Let the spreadsheet automatically add totals using its own formulas.
  • Make sure your billing is well organized and easy to handle.

If you're curious about how to make bill in Excel, the steps are nearly identical to invoicing and can be adjusted based on your billing requirements.

How to Create an Invoice in Excel Template

Learning the right format how to make invoice in Excel helps present your invoices clearly, enhancing readability and professionalism.

Step 1: Open Microsoft Excel

Begin by opening Excel on your computer.

Step 2: Search for an Invoice Template

Use Excel's search feature by typing "Invoice template" in the search bar.

Step 3: Choose Your Template

Browse through available invoice templates and select one that suits your needs and style.

Step 4: Open the Invoice Template

Click on your chosen template to open it in Excel.

Step 5: Customize the Invoice

Customize the invoice template with essential details:

  • Company's name and logo
  • Contact information
  • Invoice number and Invoice date
  • Client information
  • Description of goods or services
  • Quantity, unit price, and total amount

Clearly format your invoice for readability:

  • Use headings for clarity.
  • Adjust column widths to fit content.
  • Apply borders for cleaner presentation.
  • Select colors that match your branding.

Step 6: Save the Invoice

To reuse your customized invoice:

  • Go to 'File' → 'Save As'.
  • Choose "Excel Template (.xltx)" as the file type.

Step 7: Send the Invoice

Once saved, your invoice is ready to send electronically or print for physical distribution.

Looking for an effortless way to streamline your invoicing without the cost? Try Otto AI Invoice Generator—completely free, easy to use, and perfect for your business!

send the invoice

How to Create an Invoice in Excel from Scratch

Creating an invoice from scratch is simpler than you might think. Follow these straightforward steps:

Step 1: Set Up the Workbook

Launch Excel and open a new blank workbook. This fresh sheet will be your invoice canvas.

Step 2: Build the Header Section

At the top, clearly state your business name, address, contact information, and the word "Invoice" prominently.

Step 3: Add Client Information

Below your header, enter your client’s name, address, phone number, and email to clearly identify who the invoice is for.

Step 4: Create Columns for Itemized Charges

Label your columns clearly:

  • Description
  • Quantity
  • Price per unit
  • Total (use Excel’s built-in formula =Quantity*Price)

Step 5: Calculate Totals Using Excel Formulas

Sum up the charges with Excel formulas and ensure taxes or discounts are part of the total if required. You can quickly get totals by using a formula like =SUM(cell range).

How we can do Multi-Currency Formatting & Automatic FX Conversion

Automatic FX Conversion

If you bill clients in different countries, you’ll need to display both your base amounts and their local currency equivalents. With a simple FX-Rate cell and properly formatted columns, you can create a single invoice that recalculates totals whenever exchange rates change.

1: Add an “Exchange Rate” Cell

On your invoice sheet (for instance, in the top-right corner), reserve one cell for the current FX rate. For example:

F2: FX Rate (USD → EUR)

G2: 0.92

  •  Here, 0.92 is the USD→EUR rate. Labeling the cell makes it easy for readers to know where to update when rates fluctuate.

2: Format Currency Columns

  • Select your Unit Price column (e.g., column C) and go to Home → Number Format → Currency. Choose your home currency symbol (₹, $, £, etc.).

  • Select your Total column (e.g., column D) and format it for the client’s currency symbol (€, ¥, etc.).

3: Adjust the Line-Item Formula

If your original formula in the “Total” column was:
=B2 * C2

 (where B2 = Quantity, C2 = Unit Price), change it to:
=B2 * C2 * $G$2

  •  Here, $G$2 is the absolute reference to your FX Rate cell. When you update G2, every line item automatically converts to the client’s currency.

4: Show Both Home & Client Currencies (Optional)

If you want to display both amounts side-by-side, add a second “Converted Total” column (e.g., column E). In D2, keep your base total:

=B2 * C2

  •  Format column D as your home currency.

In E2, use:

=D2 * $G$2

  •  and format column E as the client’s currency.

5: Update the FX Rate as Needed

Whenever exchange rates change, simply overwrite the value in G2. All totals recalc instantly, and your invoice stays accurate.

6: Payment Details and Terms

Include your payment methods, due date, and any Payment Terms or policies clearly to avoid confusion.

How to Manage Invoices with Excel

It is very important to keep your records orderly. These are a few basic tips:

  • Make sure there is a special tab in the spreadsheet for keeping track of issued invoices. Properly organized bill entry in Excel ensures accuracy in your financial records and makes invoice management more straightforward.
  • Log numbers for all your invoices, the names of clients, when to bill and whether the invoice is or isn’t paid.
  • Updating your records often will make tracking finances easier.

A separate Invoice Log sheet makes it easy to track every billing activity in one place. Rather than hunting through individual files, you’ll have a consolidated register showing issue dates, due dates, payment status, and amounts received.

Invoice Log sheet

Step-by-Step Instructions:

  1. Create a new sheet: Rename it to Invoice Log.

  2. Set up column headers exactly as shown above in row 1.
  3. On your main invoice sheet, use a formula like

    =MAX('Invoice Log'!A:A) + 1

             in the “Invoice Number” cell. That way, each new invoice tab automatically picks up the next sequential number.

  1. After generating each invoice, copy the following details into the next blank row of Invoice Log:
    • Invoice Number

    • Client Name

    • Issue Date (e.g., 2025-06-03)

    • Due Date (e.g., 2025-06-17)

    • Status (mark “Unpaid” or “Paid”)

    • Payment Date (leave blank until payment arrives)

    • Amount Due (the grand total)

    • Amount Paid (fill in once you receive the payment)

  1. To highlight overdue invoices, select all cells in the “Due Date” column, then choose Conditional Formatting → New Rule → Use a formula, and enter:

    =AND($E2="Unpaid", TODAY() > $D2)
  2.  Format these rows to fill red or light red so late bills stand out.

  3. Optionally, apply an AutoFilter on the headers so you can quickly sort by Status (Paid vs. Unpaid) or filter by client name.

This Invoice Log sheet ensures you always know which invoices are still outstanding and when they were issued.

How to Automate Recurring Invoices

Automate Recurring Invoices

Create a “Settings” Sheet

  • Add a new sheet named Settings.

  • In cell A1, label it Last Invoice Date, and in A2 enter your most recent issue date (e.g., 2025-06-03).

  • In cell B1, label it Last Invoice Number, and in B2 enter that invoice’s number (e.g., 2025-002).

Link “Issue Date” & “Invoice Number” on Your Template

  • On your InvoiceTemplate sheet, replace the static “Issue Date” entry with a formula:

    =Settings!A2 + 30

(This adds 30 days for a monthly recurrence. Adjust to 7 for weekly, 90 for quarterly, etc.)

  • For “Invoice Number,” use:

    =Settings!B2 + 1

Set “Due Date” Automatically

  • If your normal terms are Net 15, then make your “Due Date” formula:

    =IssueDate + 15
    • where IssueDate refers to whatever cell you used in step 2.

  • Duplicate the Template for Each Cycle


    • Right-click the InvoiceTemplate tab → Move or Copy… → check Create a copy → click OK.

    • Rename the new tab to something like Invoice 2025-07 (or just leave it as “InvoiceTemplate (2)” if you prefer). All formulas will auto-update because they point to the Settings sheet.

  • Update Your Settings After Generating the New Invoice


    • In the new invoice sheet, confirm that:


      • Issue Date = (previous issue date) + 30

      • Invoice Number = (previous invoice number) + 1

    • Once satisfied, copy the new Issue Date cell and paste it into Settings!A2.

    • Copy the new Invoice Number cell and paste it into Settings!B2.

    • Now Settings holds the latest values, so next month you repeat steps 4–5 without manually adjusting any formulas.

  • Fill in Client Details & Line Items


    • Since you duplicated an existing invoice, all client info, line-item descriptions, and rates will already be in place. You only need to update quantities or dates if anything changed.

Tips for a Smooth Workflow:

  • If you invoice multiple clients on the same schedule, consider creating one “master” template per client (e.g., InvoiceTemplate_Acme). Then follow the same Settings process for each.

  • Use cell-protection to lock formula cells on your template:


    • Select all formula cells → Review → Protect Sheet → Allow only unlocked cells to be edited. This prevents accidental deletion of formulas.

  • Keep the Invoice Log sheet updated each time you generate a new invoice

How to Create Invoice in Excel with Formulas

Formulas like =VLOOKUP() or =IF() automate repetitive invoice tasks:

  • Quickly retrieve prices from a price list.
  • Automatically calculate discounts or apply tax rates.

This level of automation makes invoice management smoother and error-free.

Conclusion

Handling finances easily and with professionalism becomes possible when you rely on Excel to make invoices. Now that you know exactly how to make an invoice on Excel, invoicing tasks can become quick and hassle-free. 

Frequently Asked Questions (FAQs)

1. Can Excel invoices automatically calculate taxes?

Yes, you can set Excel to automatically calculate taxes using formulas. For example, if you have a 10% tax rate, use the formula =Subtotal*0.10.

2. Is it possible to add my logo to Excel invoices?

Absolutely! Simply insert your logo image via the "Insert" tab and position it within your invoice template.

3. How do I number invoices sequentially in Excel?

You can manually enter numbers or automate sequential invoice numbering by using a formula like =MAX(range)+1 for auto-increment.

4. Can I use Excel invoices for different currencies?

Yes, Excel lets you easily customize currency formatting through the "Format Cells" feature, allowing you to bill clients worldwide.

5. Are Excel invoice templates free?

Yes, Excel offers numerous free invoice templates accessible directly within Excel or through online resources such as Deskera, FreshBooks, and InvoiceSimple.

Nikko

Nikko