Taxes, bills, purchases made with your ATM or credit card : We organize everything in a worksheet, in order, to manage our family budget.
The computers were invented primarily to perform complex mathematical operations and still managing the numbers is the task that perform better. With the help of Excel, the spreadsheet in Microsoft Office , you can create a tool to manage and control the family budget . Expenses , income and expenditure from the current account , and bills to pay taxes can be simple letters and receipts scattered throughout the house, and without any meaning , or strictly ordered values in the spreadsheet . In this case, help us to assess how we can avoid waste and take away some more satisfaction at the end of the month!
In these pages we suggest , first of all , a method for organizing your home finances with Excel : For reasons of simplicity, we do not have “exaggerated” the amount of items to be included , but you have at your disposal all the instructions to create a spreadsheet individually tailored to suit your needs. This article is dedicated to those who are already experienced enough of Excel, but with patience, even a beginner can not just solve some simple steps .
Preparation of the spreadsheet Set up a worksheet with four columns, one for the date, the reason for a transaction ( ” causal ” ), one for revenue and ultimaper outputs . These last two columns must be formatted with the Currency format : Select the headings of each column and click on the euro symbol in the toolbar . For the column for the date , you can set a custom format : select the column “A” on the menu bar and click on “Format” and then ” Cells”.
In the Number tab, click “Custom” in the Type box and type ” ggg gg” . In this way you type the date in numeric format ” month / day ” (” 8/1 ” for January 8 ), but this will be displayed in the format ” day of the week / day ” (” Sat 08 “). Please copy the worksheet eleven times and rename all the sheets with the names of the months. Finally aggiungetene a thirteenth that will be used to collect a summary of monthly data and call it “Summary” . Nominate the columns of the Summary sheet as follows: ” Month”, ” Total revenue “, ” Total expenses “, ” bills “, ” Taxes “, ” spending ” ( applied to the last five columns the currency format ) .
Once prepared columns and assigned colors , insert functions that automatically calculate the various totals . Let’s start with the calculation of the sum of the income and expenditure of every month : make a click in cell B2 and type the ” = SUM ( gen! C: C) ” . In the active cell will be calculated the sum of all the values in column C (the output ) of the worksheet called ” gen” . The same formula can be copied in cells B3 to B13 , but of course we must , for each cell , change the reference to the sheet, replacing “gen ” text strings that identify the other sheets in the workbook ( Tues feb! ! April ! , and so on ) . The exclamation point following the name of each sheet and is used just to “inform” Excel that the string of text that precedes it identifies a worksheet.
When you have finished selecting all the cells from B2 to B13 and click on “Edit” and then click ” Copy.” Click in cell C2, and select Edit / Paste Special : will open a window titled ” Paste Special ” where you have to click on “Forms .” Confirm the operation by clicking ” OK.”
Now click in cell B15 and type ” = SUM (B2: B13) ,” while in the cell C15 enter ” = SUM ( C2: C13) .”
In this way the two columns for entrances and exits are ready and will be updated automatically when the worksheet data will be inserted .
Fees , expenses, bills Taxes, electricity bills , telephone, gas cost items in the budget to which we devote our attention to discover, at the end of the year , exactly how much we spend . The function that allows you to calculate these sub- totals is quite complex , follow the next steps very carefully , copying all the good features that will indicate ( not shown but the quotes including the functions are enclosed by convention) . Also in the Summary worksheet , click in the cell F2 and then click Insert / Function .
In the selection of the categories set ” All ” and scroll down to ” SOMMA.SE .” Select it and then confirm by clicking ” OK.” It will thus see the ” Function Arguments ” , consists of three items. Click the button to the right of the range . From this moment on, you will have the opportunity to select the cells where you have to indicate the conditions of the function. Click the worksheet “gen ” and then on the column B. You will notice that a small part of the window ” Function Arguments ” is still visible by clicking the button to the right you will return to the original window , but the Range box will be defined as : “gen ! B : B”. The program will then examine the entire column B of sheet “gen ” to determine whether the condition is that we are going to specify true or false. Click in the box, and type ” = spending .” Now activate the checkbox ” sum_range ” in which the cells to be added will be shown .
Following the same procedure as explained in step 1 , select the column D of the worksheet ” gen” . Now click on the OK button to close the dialog box : in the formula bar , you can see how the function is switched on. In essence we have ” ordered ” the program to add a value in the column D of the worksheet ” gen” if , and only if, the cell two columns to the left you have typed the text string ” spending .” Once again you will need to copy the formula into all cells from F3 to F13 remembering to change the name of the function for each reference month.
In columns D and E of the Summary sheet totals are calculated , respectively, of all the bills and taxes paid during the year. When , from month to month , you input your data , you must enter the reason for specific items ” light bill “, ” phone bill ” and so on. The formula to be entered in cells D2 and E2 is still based on the ” SOMMA.SE ” but with an important modification : the comparison operator is no longer the equal sign , but greater than and equal to (> =) .
Here are the correct formulas :
cell D2 = SOMMA.SE ( gen! $ B : $ B , “> = bill ” ; January ! $ D : $ D ) cell E2 = SOMMA.SE ( gen! $ B : $ B ; ” > = tax ” ; January ! $ D : $ D )
In the first case, Excel will evaluate the cells in column B in the worksheet ” gen” and will add the relevant data in column D when you meet the bill word preceded or followed by other characters.
Then you can enter as causal ” light bill ” and ” phone bill ” which amounts will be calculated by the program . Typing ” > =” is included in the calculation is the only cells that contain the string ” bill ” , and those that do precede or follow a ” bill ” even more text. To complete the columns D, E and F have to just copy the formulas inserted into cells D3 through F13 , remembering to change the indication of the reference sheets ( ” February ” in line 3 , “apr ” in line 4 , and so on ) . Finally, line 15 will be included in the formulas for calculating the annual totals of each column. In this case, you just copy the formula in cell C15 .