An accounts payable spreadsheet is as vital to your business as your revenue sources. It specifies who you owe money to, how much you owe and when payments are due. One phase of creating an accounts payable spreadsheet involves inserting column headings that identify an invoice's source, and the dates and amounts associated with the invoice. A formatting phase makes the accounts payable list easy to read and add new data to. The most important phase consists of entering each invoice's data. Use Microsoft Excel 2010 to assist you in performing these tasks quickly.
Create a new workbook, and then type or paste the following column headings into the spreadsheet. Press "Tab" after entering each heading:
Invoice Source, Account Number, Expense Category, Memo, Date on Invoice, Date Received, Date Due, Date Paid, Amount Due, Amount Paid, Past Due
Select approximately 10 rows under all four Date headings, and then click the Home tab's "Number Format" control to display a list of numeric formats. Click the "Short date" item so that Excel displays your date entries in the concise format of MM/DD/YY.
Select about 10 rows under the two Amount headings and the Past Due heading, and then click the dollar symbol on the Home tab. This action applies currency formatting to numeric entries in the selected cells.
Click the first cell under the Past Due heading, and then enter the following formula:
Type over the "<>" placeholders with the addresses of the current row's Amount Paid and Amount Due cells. For example, if the first Past Due cell has the address N6, your formula for that address is "=L6-M6".
Select all column headings, and approximately 10 rows under those headings. Click the Home tab's "Format as Table" button to display a gallery of table styles. Click a style you want, and then check the "Has headers" check box in the Format dialog box that appears. Click "OK" to apply the style you selected to the accounts payable cells. The alternating color scheme of the style enables you to easily distinguish a row from nearby rows.
Type the data for each of your invoices in the appropriate column of the completed accounts payable table. For example, if the printed date on one of your invoices is "Jan 1, 2013," Enter "1/1/2013" under the Date on Invoice heading. Type the name of the company that sent the invoice under the Invoice Source heading. Enter a category like "Office Equipment" or "Computer Maintenance" under the Expense Category heading. Type anything you want to note about the current invoice under the Memo heading -- for example, enter a phone number or other contact information printed on the invoice.
- Try out existing accounts payable templates through Excel's New command.
Darrin Koltow wrote about computer software until graphics programs reawakened his lifelong passion of becoming a master designer and draftsman. He has now committed to acquiring the training for a position designing characters, creatures and environments for video games, movies and other entertainment media.