Wednesday, December 03, 2014

Organizational Tip #15: Tracking Holiday Spending & Preventing Repeat Gifts using Excel

As anyone shopping their holiday list knows, holiday spending can get expensive quickly.  Children, family members, friends and coworkers, the list goes on and on.  Remember each gift you gave last year?  Not likely.  Tracking expenses can be tedious and time consuming.  However, using an Excel spreadsheet to track these expenses is easy, can be reused year after year, helps prevent repeat gifts, and can help anyone stay within their desired budget.

To create a spreadsheet to track expenses, first, open Excel to create a blank workbook.  Type "Recipient" in the first column at the top.  In the next column type "Gift", and in the third column type "Cost".  To make these headings stand out, center them and make the font bold.

Next, start adding the people you plan to give gifts to in the recipient column leaving a few blank lines between each person. 

Add in any gifts already purchased in the gift column and their prices in the cost column. Click the $ symbol in the menu bar to format the cells as money.  Including tax is a preference, just be sure to be consistent in adding or excluding tax for each gift.

For individual totals:
Add a column named "Totals" after the Cost column.  Use this column to total the amount spent on each person and everyone combined using easy to add formulas.  To input a simple formula to add cells click the cell to input the formula and use any one of the three following ways:

  • Click the Greek E in the ribbon menu bar to add the Sum function.  The sum function will try to pick which cells you want to add.  If the correct cells are selected, press the enter key.  If the cells highlighted are not correct, highlight all the cells to add and press the enter key.  NOTE:  In Excel, a contiguous set of data is represented succinctly like this:  E5:E34, which would add all these cells, rather than E5+E6+E7, etc.
  • OR Type "=" then select the first cell to add, click "+", then click each remaining cell to add being sure to type "+" between each cell selected and press enter.
  • OR Click on the function button next to the cell contents bar.  Select SUM from the
    function list or type SUM at the top to search for it if it is not listed.  Press OK to select the sum function.  Type out the cell numbers to sum in the box next to Number1, or select the cells on the spreadsheet which will automatically enter them in the Number1 box.  Click OK to save the function.

To total all costs:
To summarize how much you have spent on each gift listed, insert a sum function at the bottom of the cost column after all the items listed.  Press the Sum button, the Greek E and verify the correct cells are selected and press OK.  Adjust the function if necessary by selecting the cells or changing the cells listed in the function to make sure all costs are included.  The function should look something like this:  "=Sum(C2:C25)".

To reuse the spreadsheet:
Copy all of the data in the worksheet.  Select a different worksheet and paste all the cells.  Replace gifts and prices as needed and the formulas will adjust automatically.  For organizational purposes, name each worksheet by the event, or year, or some other way to make the data in each clear.

Using spreadsheets to document holiday spending can help make sure you stay within your budget regardless of what it is, is reusable year after year, and once set up calculates the information you need quickly and easily. Spreadsheets are a great way to organize data, to calculate data, and to document and analyze data.  Once set up, formulas can be used over and over again to save while providing the information needed.

No comments:

Post a Comment