8 Not So Secret Formulas to Get Your Sheet Together
Invoices always boil down to basic math. And math is formulaic. So, what better way to manage your finances than to use spreadsheets that include easy-to-apply formulas that are always correctly calculated?
Using spreadsheets to create your invoices is a great way to get them set up quickly and easily. They can also help you to be as prepared as possible when tax season hits.
Here are eight formulas to help you get going with your spreadsheets:
1. Multiplying Line Items
With most invoices, you’ll have individual line items that have a unit price and then the quantity bought. You’ll then want a total for that line item. Say that line item is in row 8 on your spreadsheet, the unit price is in column C, the quantity in column D and the total should go in column E. In E, put =C8*D8. It will automatically create a total for you.
2. Using The SUM Formula For Column Addition
This is the most useful formula because it will do all the major additions on your invoice for you. Make sure that your invoice is set up in rows and columns, and that all of your line item totals are in the same column. Then, simply select everything in that column and click on the SUM icon. This will bring up a formula like this in the cell below the last selected item: =SUM(E8:E15)
3. Adding In Sales Tax
To add sales tax automatically to your invoice, it’s a simple case of inserting a formula that relies on the subtotal you’ve created from your line items. If your sales take is 5%, then your formula should look like this: =E16*0.05
E16 is the cell where your subtotal would’ve been created in the example above. The amount shown in the cell where you put this formula will be the tax amount. You then need to add that to your subtotal to get your final total.
4. Using The IF Function For Shipping
Shipping is another part of your invoice that is often reliant on a subtotal. For example, maybe your business only charges shipping on orders under $100. You can use the IF Function to make your life a lot easier here. This function looks at the value in a designated cell and applies an action based on the criteria you set.
In the shipping total cell, your formula should look like this: =IF(E6>=100,0,3.99)
The $3.99 is an example of your shipping cost. The 0 that appears there is the shipping cost value that will appear if the amount is $100 or more.
5. Create Percentage Discount Options
This formula will be similar to your sales tax formula and can be applied to either your individual line items or your subtotal. Underneath your subtotal, add in a row for the discount and input this formula: =E16*0.1
This will show a 10% discount amount, which you can then subtract from your subtotal to get your new total amount payable. You can do the same thing on a line item total if you only want to apply the discount to a specific item and not the entire sale.
6. Using the IF Function For Subtracting Discounts
Now, let’s go back to row 8 and apply a discount to your line item because a customer has bought a large quantity of the product. Perhaps you want to put in a rule that if the customer buys more than 10 of that particular product, they get a 5% discount. Your formula will look like this: =IF(D8>=10(E8*0.05),0)
The same formula can be applied to the subtotal for an overall discount. You can say IF the subtotal is greater than $100, for example, the customer gets a 5% discount.
Just make sure this doesn’t impact any other IF formulas you may have for shipping. It also mustn’t impact your sales tax negatively. That figure must be set according to the proper regulations.
7. Using The SUM Formula To Add Up Various Totals
For some invoices, you might not have a single column to add up to your final total. Sometimes you might have various sections of your invoice, depending on the services you render or the products you sell. This will lead to several subtotals that you can’t simply select and add up as you would in tip number 2.
For a scenario like this, you can use the SUM formula, but a little differently. Into your subtotal cell right at the bottom of your invoice—we’ll use E30 for this—start with =. The next step is to manually select your different subtotals, but be sure to put a + in between each one.
As you select the subtotals in the spreadsheet, their cells will be highlighted in a different color and the corresponding color and cell reference will show up in E30. Your final formula will look something like this: =E16+E20+E25+E29
You can put SUM after the = but it isn’t necessary in a case like this.
8. Using $ To Create Absolute Cell References
When copying and pasting formulae in a spreadsheet, the program will automatically change the reference cells. This is incredibly useful if you want to have the same formula for adding up line items on your invoice. For example, when you copy and paste =C8*D8 into row 9, it will automatically come up as =C9*D9.
However, there are times when you want one or both parts of a formula to reference a constant cell and not shift row or column. To do this, you can add in a $ before the letter, number or both. If you originally had =$C$8*D8, when you copy and paste the formula into the next row down, you’ll get =$C$8*D9. This is called an absolute reference.
If you only have the $ before either the letter or the number of the cell, for example, =C$8*D8, you get a mixed reference. When you copy and paste it, you’ll get =D$8*D9.
Build Your Invoices With Confidence
Having these formulae set up and ready to go in your invoice template will save you plenty of time and energy. You’ll find that creating and sending out invoices is a simple process now, and hopefully, you won’t end up as another of these shocking tax stories we hear about all too often.
