A visitor asked how to set up a simple invoicing system in Excel.
This is a common problem in many small businesses, divisions, and sales offices. And it applies to both invoices and purchase orders.
The following figure of an Excel Table shows the general idea. In columns B, C, and D, you have key product information. This table could have additional information, including a description, vendor, and so on.
This figure also includes a Count column, which I’ll explain shortly.
To create an invoice or a purchase order, you first go to the Qty column and erase the previous values.
To erase them all at once, first press F5 or Ctrl + G. Or, in the Home, Editing group choose Find & Select, Go To. Each of these actions launches the Go To dialog. Select the Qty name or type it into the Reference edit box, then choose OK.
With the entire Qty column selected, press Delete.
Then enter the quantities to be purchased.
When Excel recalculates, the Invoice area displays the price, quantity, and extended total for each of the items purchased.
The table that supports the invoice could use at least two different methods to calculate this figure. I’ll explain both of them.
Set Up the Data Table
After you enter and format the information shown in the Product Data Table, shown again below, you should assign range names. To do so, select the range A2:D11. Then, in the Formulas, Defined Names group, choose Create from Selection. Make sure that only Top Row is checked; then choose OK.
(I know, Tables are self-naming. But it’s often easier to assign your own names to the columns you plan to reference in your Tables.)
The VLOOKUP function will be easier to use if the full data table is named. Select the range A3:D11; in the Formulas, Defined Names group, choose Define Name, Define Name; then assign the name Data to this range.
The Count column increments its value whenever a new quantity (Qty) is entered. Here’s the first formula:
A3: =IF( $D3>0, N(A2)+1, N(A2) )
The formula in cell A3 adds the value of 1 to the previous row if a quantity greater than zero has been entered in cell D3; otherwise, the formula returns the previous value. In the whole column, therefore, the count increments whenever a new quantity is added.
In cell A3 specifically, we need cell A2 to be evaluated as 0, not as “Count”. Therefore, we use the N() function to reference cell A2. Because N() returns zeros when it references text, and returns numbers when it references numbers, it returns the values we need throughout the column.
Use the VLOOKUP Function to Create Purchases Orders and Invoices
The figure below shows the summary table, which represents a purchase order or invoice. It shows extended prices for the four items selected in the first figure.
Although the table has only five items, you could add enough rows to make it as large as you want.
The nice thing is that the table appears to consist of a variable-length list. No matter how many items are chosen within the limits set, the remainder of the table appears to be empty.
Here are the key formulas for the table:
A3: 1
A4: =A3+1
Cell A4 increments the count begun in cell A3. Copy cell A4 down the column as needed.
C3: =IFERROR( VLOOKUP($A3, Data, 2, FALSE), “”)
This formula returns the product name for the item number that appears in cell A3. If that item number isn’t found, VLOOKUP returns an error, causing the IFERROR function to return a null string.
Here’s the general form for the VLOOKUP function:
=VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
- lookup_value…The current line number, in this case.
- table_array…The Data range assigned above.
- col_index_num…Because Product is in the second column of the Data range, shown here, we use the value 2.
- [range_lookup]…This is an optional parameter that uses TRUE as its default. But if we enter FALSE as its value the function acquires two useful characteristics. First, it returns #N/A if the lookup_value isn’t found. Second, it returns data associated with the first lookup_value found…which is precisely what we want.
D3: =IF($C3=””,””,VLOOKUP($A3,Data,3,FALSE))
If cell C3 has a has a null string, this formula returns a null string, which effectively hides the formula from view.
To be clear, the range C7:F7 contains formulas that return null strings. This is why that area appears to be empty.
E3: =IF($C3=””,””,VLOOKUP($A3,Data,4,FALSE))
If cell C3 doesn’t contain a null string, this formula returns the quantity.
F3: =IF($C3=””, “”, $E3*$D3)
If cell C3 doesn’t contain a null string, this formula returns the total cost for the product in its row.
After you enter these formulas, copy the range B3:F3 down the column as needed.
F8: =SUM( F$2 : OFFSET(F8, -1, 0 ) )
You’ve probably never seen a SUM formula like this before. You probably expected to see a formula like this:
=SUM(F2:F7)
The problem with this short formula is that if you insert a row between F7 and F8, Excel would continue to reference F7, not the data you set up in the new F8 row. There are various ways around this problem, but using the OFFSET function is a quick and easy solution.
This formula always returns a reference to the cell immediately above the cell with the formula. And then, because of the reference operator “:”, Excel returns the sum of the range from F2 through the cell immediately above the cell where the formula is used.
To see how this works, here’s OFFSET’s syntax:
=OFFSET( reference, rows, cols, [height], [width] )
And here’s the formula again…
F8: =SUM( F$2 : OFFSET(F8, -1, 0 ) )
…where…
- The reference is to cell F8. I know, this feels like it should cause a circular calculation error, but doesn’t. This is because the range that the OFFSET function returns doesn’t include the cell that the function is in.
- The rows value is a minus 1, because we want the reference to be one row above the cell with the formula.
- The columns value is zero, because we don’t want the reference to be offset to the left or to the right.
- The optional height and width value are ignored, because F8 is only one cell, which—by default—causes OFFSET to return only one cell.
To complete this table, define the print area. To do so, first select the area to be printed, then in the Page Layout, Page Setup group, select Print Area, Set Print Area.
One disadvantage to using VLOOKUP is that we need to specify which column number to return data from. Therefore, if we rearrange the table, the VLOOKUP function could return data from the wrong column. There are ways to eliminate this problem, but it creates a more complex formula. Instead, let’s look at the INDEX-MATCH approach.
Use INDEX-MATCH to Create Purchases Orders and Invoices
The combination of the INDEX function MATCH functions probably is the most powerful and flexible lookup method that Excel offers. You can see it illustrated in the following figure.
The figure below is virtually identical to the previous figure. The only difference is that it uses column B to contain an index number.
Column A is unchanged:
A3: 1
A4: =A3+1
This formula is in the new Index# column:
B3: =IFERROR(MATCH($A3,Count,0),””)
MATCH looks up the specified data in a row or column and returns the index number for the item found. For example, if MATCH identifies the 23rd item in the list, it returns the value 23.
Here’s the general form of this function:
=MATCH(lookup_value, lookup_array, match_type)
- lookup_value…The value you’re searching for.
- lookup_array…Typically, a single row or column to search through.
- match_type…This can be the value -1, 0, or 1. Each value causes MATCH to behave differently. You will use 0 most of time. When you do, the lookup_array doesn’t need to be sorted; MATCH returns the index for the first instance found; and MATCH returns #N/A if it doesn’t find an exact match. That is exactly the performance we need here, because we can use IFERROR to return a null string when MATCH returns #N/A.
The formulas in columns D and E then use INDEX to return the values we need from the various columns, as shown in the following formulas.
D3: =IF($B3=””, “”, INDEX(Price,$B3))
E3: =IF($B3=””, “”, INDEX(Qty,$B3))
F3: =IF($B3=””, “”, F3*E3)
Finally, notice the strategy here. In column B, we used one lookup function to find the row index we needed, and then we used the INDEX function to return data from various columns in that row.
In short, using the INDEX-MATCH combination is the best strategy by far, for two reasons. First, it’s more flexible than VLOOKUP. Second, the INDEX function works very quickly!
