Template : QBO Invoices & Bills - Post and Edit

Learn how to Create, Edit, Void and Delete QBO Invoices and Bills directly from Excel.

This article covers the following:

:bookmark: It is recommended to familiarise yourself with the following articles:

Getting Started

Important notes to be aware of when using this template:

  1. In the POST column, select ‘Yes’ if you want to post directly to Quickbooks. Select ‘Delete’ if you would like to to delete or ‘Void’ if you want to void an Invoice/Bill.

  2. You can upload a supporting working as an attachment for each Invoice/Bill. To effect this, select ‘Yes’ under Upload Sheet column and make sure that the respective supporting sheet name is identical to the Invoice/Bill NUMBER field in the template.

  3. This template does not allow the use of Tags, Message on Invoice or Message on Statement.

  4. The template can be used for Part or Full payment of Invoices. However part payment figures and full payment figures cannot be retrieved from Quickbooks to Excel.

  5. The Template returns ALL Invoices whether fully paid, partly paid and pending payment.

Columns Explained

Mandatory fields must be filled in to successfully push a row to the accounting software - these are also marked with a white star :star: in the template. The other columns are optional and can be used depending on what information needs to be posted/imported.

Column.Name Required Description
Post? :heavy_check_mark: Change to ‘Yes’ if you want to post the row details to your accounting software. Don’t forget to mark as ‘No’ for journals which are not required during this reporting period. Select ‘Delete’ or ‘Void’ as needed if you want to delete or void an Invoice/Bill
Upload Sheet? :heavy_check_mark: You can upload supporting workings as an attachment to each row. To effect this, please select ‘Yes’ under the Upload Sheet? column, then create a new tab and make sure the sheet name is the same as the Invoice/Bill number value. In the newly created sheet, paste in any documentation or workings you need. This sheet alone, will be added as an attachment to the Invoice/Bill in your accounting software.
Entry Date :heavy_check_mark: This is the date that you would like the Invoice/Bill to be created. This can be past, present or future.
Due Date :heavy_check_mark: This is the date you expect the Invoice/Bill to be paid.
Invoice / Bill Number :heavy_check_mark: Every Invoice and Bill must have a unique number.
Transaction Type :heavy_check_mark: From the drop down list choose between Invoice, Bill, Credit Note or Supplier Credit note.
Customer :ballot_box_with_check: Only required for Transaction Type , Invoice and Credit Notes. Users are required to use the dropdown to input the Customer this entry relates to.
Supplier :ballot_box_with_check: Only required for Transaction Type , Bill and Supplier Credit Notes. Users are required to use the dropdown to input the Supplier this entry relates to.
Tax Type Choose if tax is Exclusive, Inclusive or No Tax. Choosing Exclusive will add the Tax rate chosen to your total amount to your accounting software. Inclusive will assume that your amounts have already taken Tax calculations into consideration.
Currency Code Choose the currency you would like to associate to the entry from the dropdown.
Product / Service :ballot_box_with_check: Only required for Transaction Type , Invoice and Credit Notes. Users are required to use the dropdown to input the product or service this entry relates to.
Description :heavy_check_mark: A Description for each Invoice/Bill line is required.
Qty :heavy_check_mark: The Quantity for this line of entry has to be inputed.
Discount % A Discount can be applied on the relevant entry.
Unit Price :heavy_check_mark: The Unit Price per line of entry has to be input.
Account Users can choose which Account they want the entry to be posted to.
Location The relevant Location can be chosen for the entry by using the drop down.
Class The relevant Class can be chosen for the entry by using the drop down.
Tax Rate :heavy_check_mark: Choose the tax rate that needs to be applied to this entry.
Balance This column is still being developed

Columns for Full or Part repayment

Column.Name Required Description
Payment Reference User can choose to input a reference related to the payment.
Payment Date :heavy_check_mark: This is the date that you would like the Invoice/Bill to be created. This can be past, present or future.
Payment from Account :heavy_check_mark: The payment account can be chosen from the dropdown.
Payment Amount :heavy_check_mark: This is the amount that you want to pay from this invoice. This will effect your Balance figure.

Grouping of Invoices and Bills

The template caters for creating Invoices and Bills with multiple lines. When the following columns are identical, DataDear will automatically collate them as a single Invoice or Bill with multiple lines - there is no limit (unless imposed by the accounting software) on the number of lines that can be used:

Column Name
Entry Date
Due Date
Invoice / Bill Number
Transaction Type
Customer
Supplier
Tax Type
Currency Code
Location

:information_source: When grouping Invoices and Bills, it is recommended to put these under each other in the template.

Posting, Editing, Deleting and Voiding Invoices and bills

This template is a Post and Edit template, meaning it allows for invoice and bills to be downloaded (from Quickbooks) , edited and posted back to the accounting software.

:information_source: If you are not familiar with this process please go through the article below.
How do I push & edit data into the accounting software?

Posting

To push a new posting to QBO simply input the required data in the relevant columns. Once happy with your entries simply click Post online. Once DataDear is done posting a validation window will appear.

ezgif.Post

Editing

Editing entries that are already in QuickBooks is very similar to posting. However users would first have to click " Get Data to edit" to retreive all the Invoice, Bills, Credit notes and Supplier Credit notes for a given period chosen by the user.

ezgif.Get

Deleting and Voiding

By deleting the invoice, users will be permanently removing it from their company file. To do this simply choose Delete from the “Post?” dropdown and click Post online. For entries with multiple lines make sure to select Delete on all lines.

ezgif.Void

On the other hand, voiding an invoice cancels a particular payment or invoice. Voiding an invoice will keep the invoice number and list it in reports but changes the amounts to zero. Similarly choose Void from the “Post?” dropdown and click Post online. For entries with multiple lines make sure to select Void on all lines.

Scenarios When This Template Could be Used

Inputting Invoice, Bills, Credit notes and Supplier Credit notes periodically.

The template can be populated using formulae from a secondary excel tab. Since the template utilises excel functionality like formulae, they can be used to populate the cells of the template from a different excel source and used multiple times.

Editing Invoice, Bills, Credit notes and Supplier Credit note in bulk.

Let us assume an error was made or a decision was taken that effects a previous months Invoices that are already on QBO. User can use this template to pull the data from QBO and edit it accordingly. Using the Criteria window that pops up after clicking “Get Data to Edit” users can select a particular period and DataDear would retrieve data for that given period alone.

Posting payments against Invoice and Bills.

The Template can be used to retrieve all invoice and bills that haven’t been paid or only partly paid. Payments can be settled in bulk by populating the last four columns of the template. Once payment has been done, the Balance column will reflect the payments done and will make identifying which invoices and bills are pending very easy.