A macro … for the salary is likely that some data should be included in a repetitive way , even if at a later date . The Microsoft Office programs allow the user to make use of the macro to execute a series of commands and operations are always the same . For example , it can be a handy macro that automatically inserts a row with the monthly salary , given that this is a repetitive task in which some items ( like the word salary and the amount thereof ) are fixed. Place the cursor in the first empty cell in column A ( cell A6 in our example) . From the Tools menu, choose Macro, Record New Macro dialog box and enter a macro name , for example salary .
You can assign a keyboard shortcut to the macro by clicking in the text box ” Shortcut key ” and pressing one or more keys simultaneously, for example SHIFT + S. You can thenrun the macro by pressing the CTRL key (which is required by the program for macros ) and the two buttons you have chosen. Click the OK button , and the macro recording will begin. Return to the worksheet, where you will see a small toolbar . Click the second button , ” Relative Reference ” . Otherwise Excel treats all absolute cell references and the result is devastating : when running the macro again all the data would be entered in row 6 , returning to overwrite each time.
In cell A6 , type the ” = TODAY ()” ] and click on the Send button next to the formula box . Click the Copy button , and then click Paste. Position the mouse pointer near the cell where A6 is the symbol of Paste , click and choose the option ” Values Only ” . Have you turned the formula into an absolute value and when you execute the macro, Excel will insert the current date first and then transformed into the corresponding text , so it will not be updated each time you open the file. Press the right arrow key and type in cell B6 Salary . Again press the right arrow key and type 1200 in cell C6 .
Click the Enter button next to the formula box and then choose the menu command Format / Cells to change the format in which the figure will be displayed . In the Category box : choose , for example , Currency, and select the options to your liking in the boxes ” Decimal Places ” and Symbol. Click the OK button to return to the worksheet . The macro is finished , then the Macros toolbar , you can click the ” Stop Recording ” .
Excel and budget house
Excel and accounting home
About taxes … unless you have unlimited confidence in your accountant, will be good to put in a reminder of the summary sheet similar to the one in the picture and do not forget no cash ! In this case the automatic display of a deadline is based on the Excel function “Today.” The convenience of this feature is related to their ability to take advantage of the automatic update that is performed according to the date of Windows and saves you the trouble of typing the new date every day. In cell L1 enter the formula “= TODAY ()” where the word ” TODAY ” is followed by the symbols of opening and closing parentheses with no space .
You may receive a number of several digits. In this case, you just format the cell by assigning one of the date formats in Excel. Take a click inside the cell K2 and then on the Insert command / function on the Menu Bar.
Select the ” IF” part of the Logic category . In the Test box type ” I2 = $ L $ 1″. The dollar sign is used to make references to absolute column and row and will be very convenient when it comes to copy the formula in the cells below . Without this symbol references would be relevant and would be lost in the copy Excel comparison with the L1 cell that contains today’s date. In the ” Value_if_true ” type ” PAY ! ”
And in the last text box, type the -quoted followed by a space and yet by quotation marks . With this formula you instructed Excel to return the value ” PAY ! ” If today’s date and the expiration date are identical, and leave the cell blank if not. The program is not , however, be able to know if by any chance you have paid a fee in advance , and then will continue to display the message ” PAY ! ” Until the date of expiration. It is therefore necessary to insert an additional condition : the column ” J” insert the word “Hold” for each maturity. Then edit the formula in cell K2 as follows: “= IF (AND ($ L $ 1 = I2 , J2 <>” paid “); ” PAY ! “,” “)”
In this way, there are two conditions that must occur because Excel displays the peremptory order to pay. In addition to the coincidence of the date and the expiration date, it is necessary that you have not typed in the Status column , the word ” Paid ” . Of course you’ll have to remember to change the status of each term, entering exactly “Paid ” , after you have taken steps to settle the tax. Copy the formula to cells from K3 to K6 with Copy , Paste Special , selecting the radio button formulas so as not to change the formatting of the destination cells . You can further emphasize the reminder of payment formatting a cell so that it is more evident with the approach of maturity. Select the range of cells from K2 to K6 and give the command Format , Conditional Formatting. In Condition 1 , select ” Cell Value is” and in the next box ” Equal to” .
In the third box , type ” PAY ! ” . Now click on the ” Format …” and in the ” Format Cells” set of formatting options that make it quite clear cells that meet the conditions laid down as a reminder for payment.
To record the movements of the bank is better to create a new worksheet that we will call “Bank” . Set it as you see in the image , ie with five columns named in the order : Date , Reason , Revenue, Expenses , Total Current (the last three will be formatted in currency euro ) . The second line will be used to indicate the current account balance as of January this year.
Take a click in cell E3 and type the formula “= C2 + C3 -D3 ” . In cell E4 , type: ” = IF ( OR (C4 < >” ” , D4 < > ” “); E3 + C4 -D4 , ” “)” ( but without the first and last quotation marks added by convention) .
This formula tells the program to perform the sum of the result of the cell above (E3) and the cell in the same row in the Revenue column (C4) by subtracting the cell in the same row in column outputs . Using the IF function and the logical operator OR is affects the calculation in the presence of data in the two lines to the left , so as to avoid displaying a series of errors at blank lines. With the IF function , you can now copy and paste this formula to the other cells in column E , so that the totals are automatically displayed when you enter the data.