MS Excel 2010: Tricks for Formulas and Macros

[BinDs14]

I am trying to show you concrete examples of how to use formulas and macros in Excel 2010. Many users search for how to calculate your monthly schedule, the repayment schedule of a loan, the sales analysis to the bank account and make handling workbooks. I am providing all tricks that can be useful for you too. Excel is probably the most versatile and effective program from the Microsoft Office package. It can be used as well as for sophisticated analysis and reports for lists, tables and simple calculations. So PC WORLD is based on Excel as already developed an MP3 player program and a file manager). Against complex of three right, but practical examples can be found here, the tricks that you use formulas and macros for many purposes, right: A is used to calculate your overtime, another to set up a payment schedule for a loan, and the third for analysis of cash flow on your bank accounts. The menu names used here refer to the Excel versions 2003, 2007 and 2010. In older versions from Excel 97 are similar to the designations in the rule where the 2003 version.


To calculate your monthly working

Open the file "Employment.xls, located in the sample files with Excel 2010. In it you need to program anything yet. All tasks can be solved only through the skillful use of formatting and formulas.


Cell as a date or time format

To track your daily working time for one month, you need cells for dates, times and number of hours. You can use Excel to assign each cell a specific format. In the sample file that is already done. Check here as the cells B4 to B25 (B4: B25), by clicking in the first and widen the selection by pressing the left mouse button down. Then click the right mouse button on the label and choose Format Cells. This will open a window where you can select the left under "Category", which type of data in the selected cells are mentioned later in this case "Date". To the right you can choose type, such as the date values to be displayed, 03/14/2001. In the same way you can in the cells E4: G25 see that the format of "Time" is of type "13:30" specified. If you want to calculate the total hours worked, as has happened in cell I28, then the format will help "time" would not achieve because they naturally never get beyond 23:59. When you open the window above cell formatting for the cell I28, you can see that here the custom format [h] mm was assigned. The square brackets around the "h" ensure that Excel counts the hours rather than days, but as the number of hours. The cells K4: K25 indeed look as if they had the format of "Time".

[BinDs14]

Computing with date and time

If you are in one of the cells B4: B25 enter a date, then appears in the cell next to it (in column C) of the corresponding week. If any of the cells (such as C6), click to see in the "Fx" bar of Excel, the formula = TEXT (B6; "TTTT"). Formulas always begin with an "=" followed by the function name and parameters. The TEXT function converts a numeric or date value, B6 of here, into a text. The placeholder "TTTT" is the name of the day. You can add dates or times into Excel as with decimal numbers, if all the cells involved in the calculation of the same resolution. Click around to I8, then you see the formula = F8-E8-G8. The number of hours worked is thus simply the "Up" time minus "from" time period minus the break. In calculating the In/Overtime in column K returns to the TEXT function.

The IF function is necessary to set the conditions to be displayed for the negative or positive values. For details about this feature, see Example 2 The function ABS computes the absolute value, so the positive value if the difference between working hours and target should be negative. The "-" which is linked to the text of the really negative difference (&) is the minus sign.


Cells differ in color

To distinguish between lower and faster overtime, helping the staining of the cells in the example. Mark the desired cells, here K4: K25, and select Start, manage Conditional Formatting Rules (Excel 2007 and 2010) or Format, Conditional Formatting (Excel 2003). The window for conditional formatting, you see three conditions (one for each color), the Excel for each of the desired cells from the bottom up is processing. The condition formula for green cells is (":"; SEARCH = K4). This means that Excel coloring each cell in which a colon, green.

The formula for the white color is SEARCH ("0:00", K4). This means that cells with a value of "0:00" dyed white. The green color by the previous condition is then overwritten. The formula for the red color is = SEARCH ("-"; K4). Thus, all cells are stained red, which contain a minus. In principle, conditional formatting is used with all formulas that at the end of either True or False are. Alternatively, you can define with numbers and ranges of values. Since Excel 2007 conditional formatting options have been extended. Please click on "New Rule" shows you the possibilities.

[BinDs14]

To calculate the amortization schedule of a loan

A little complicated, but essential for anyone who wants to finance a property, a car or other high-order purchase with a loan, a repayment schedule for this loan. In the folder containing the sample files can be found as the pattern file Financing.xls. With it, you also come from without programming. For loans, it's all about money to interest and principal repayments. So you need at least cells for entering monetary amounts and percentages. In cell D4 of the sample file to the total loan amount is entered. When you click the right mouse button on the cell and choose Format Cells, you will see that this cell the format currency in Euro (€) with two decimal places added. Exactly the same format all the cells on the worksheet, in which funds are available (look for the € sign at the end).

Existence of cell values to establish conditions: The length of the repayment schedule from line 20 is dependent on the duration of the loan, here between 1 to 30 years. This also means that a maximum of 30x12 = 360 rates can be taken into account and thus 360 lines need to be prepared with formulas. If you are in columns A to I select each of the cell 379, so you can see it even more formulas, no longer a line under it. So that the cells remain after the end of the desired loan term really empty and not 0's, or even error, please use the Excel IF function. With it you specify a condition under which a certain cell value is displayed. Otherwise, the cell remains empty.

If you about the cell I20 as "outstanding balance month end" mark, you see the formula = IF (A20-C20 G20 <>""; ;""). Through it first checks whether the credit is running at all, and in cell A20 is something. If so, then Excel calculates the remaining debt to the beginning of the month (cell C20) minus the redemption amount. If not, then the cell remains empty. A slightly more complex function with two nested IF functions, such as in cell E20 6nden under "unscheduled". Since unscheduled be paid annually, for loans in general, there are for these cells three possible states: During the repayment period, eleven times a year, a value of 0.00 € and once the value in cell D9, and after credit end remains empty cell . The formula is: = IF (A20 <>""; IF (MOD (A20, 12) = 0, $ D $ 9; 0 );"").

WHEN the first query checks if the credit runs. If yes, IF another query is started that checks whether the number in column A can be divided without remainder by 12. If yes, then every 12 Paying up the amount set aside for the added special repayment.

[BinDs14]

Number the cells in ascending order

The number of installments (D13) is derived from the term in years (D7) times 12 For each installment will be counted in column A from line 20. The existence of a value in column A switch as described above until the calculations for the other cells without that line. The formula for cell A21 shows how the numbering is generated: = IF ($ D $ 13> A20, A20 +1;""). Condition that at all, a value is entered in the cell that is the case that the value of the cell is less about it than the total number of rates (D13). If so, then the value of the cell also increased by 1.

Calculating with currency and percent cells:

To learn about the monthly annuity (D12), you must calculate the amount of the loan interest rate from (D5) and eradication rate (D6). If the latter two values are in cells with percentage format, you can share all this simply calculate = D4 * (D5 + D6) / 12. It must again be divided by 12, since the two rates are always a year here but monthly rates are desired.

To analyze the sales of your bank account

At many banks with online banking access, you can download the account history of the last 90 days as a CSV file (Comma Separated Value). This file can be read automatically in an Excel workbook. Then you can display, for example, in a diagram of how the balance has developed and what were the biggest issues in this period. Do this, open the folder with the sample files, the file "SalesAnalysis.xls. In this case the normal range in Excel functions is not enough. Therefore, some programming is required. When you open the workbook the first time, reports spread that the execution of macros has been prevented for security reasons. So you need first the macro protection defuse something to ever use self-developed macros. In Excel 2003, select to Tools >> Macro >> Security >> Security Level >> Medium.

In Excel 2007 and 2010, first activate the developer tools. To do this in Excel 2007 click the Office button switch, then "Show Developer tab in the Ribbon" on Excel Options Commonly Used in the box before. In Excel 2010, choose "Custom File, Options, Menu Band" and put the check in the "Developer Tools". Then select in both cases, the Developer Tools, Macro Security, disable all macros with notification. Then close the file and open it again. Will appear in Excel 2003, a warning window by clicking "enable macros" button. In Excel 2007, select Options, and then sit at two points check "Enable this content". In Excel 2010, select "enable content". A few seconds later you see on the worksheet "Data" all account transactions, which were imported from the second sample file Reservations.csv.

[BinDs14]

Balance development graphically

Upon obtaining the data, it is in the main macro continues with Diagram. This feature to create a curve that shows the development of the credit, you will find a little further down in the macro code. The line Charts.Add a new diagram is created on a new worksheet. Then two lines are important: With ActiveChart.ChartType = xlLine the chart type "Line" set. And with ActiveChart.SetSourceDataSource: = Sheets ("Data") Range ("C: C, K: K")., PlotBy = xlColumns the source data is specified. For this case, the date column C data for the X-axis and Column C balance, the data for the y-axis.

To quickly see whether a curve tends upwards or downwards, you can add a diagram of a trend line. In the example of that is with the line ActiveChart.SeriesCollection (1) Trendlines.Add (Type: = xlPolynomial, Order: = 2, Forward: = 0, Backward: = 0, DisplayEquation: = False, DisplayRSquared: = False).. Select. It is in this example, a second-order polynomial (square-function Type: = xlPolynomial, Order: = 2). Depending on how the curve of the original chart runs, you can define different trend lines. If, for example, the largest revenue and expenditure would like to see the last 90 days, which goes beyond the Auto_lter function: Select the cell A1 and select in Excel 2003 data, filter, auto filter, or in Excel 2007 / 2010 " Start sorting and filtering, filtering. Then appear in all cells of the first line drop-down lists, you can filter on. Fold about the list in column J (the amount) and click on "Top 10" (Excel 2003) or "numbers> lter, Top 10" (Excel 2007/2010). Select "Supreme" to To see the most revenue, or "bottom" to see the greatest expenditure. After clicking "OK" on the worksheet are only see the appropriate lines. To remove a filter back in, close the list again and choose "Delete Filter" "All" (Excel 2003) or (Excel 2007/2010). To simplify the use of workbooks If you create large Excel workbooks, you should be simplified and the other dealing with these files as much as possible.

[BinDs14]

Show all formulas

By default, you can see in every cell of the changes it is set in the formatting or the calculation result of a formula. The formulas used are initially hidden. Get the formula of a cell to see only in the "Fx" bar when you highlight a cell. For complex spreadsheets, it is sometimes useful to temporarily see all formulas at a glance. Hold down [Ctrl] and press [#]. Repeat this key combination to switch back to the default view.

If you use Excel spreadsheets to other people to share complex, you should not that important cells are inadvertently changed. It often makes sense to release only certain cells and to block all others for input. You can try this in the sample file Financing.xls : select the cells where D4: D9, and remove from the Format, Cells, Protection, Locked (Excel 2003) the hook. In Excel 2007/2010 is on the "Start, format, cell block." Then select in Excel 2003 Tools, Protection, Protect Sheet" or in Excel 2007 / 2010, Start, format, protect sheet". In the window that you can still choose between different forms of protection. Let this best the default and click OK. It shows that while all cells are selected and viewed. But only the previously selected cell D4: D9 may be changed.

[BinDs14]

Advanced conditional formatting: From Microsoft Ofice 2007, the features of conditional formatting expanded. Check around in the sample file Employment.xls the cells I4: I25. Then click on Start, conditional formatting color scales, and then as the green-yellow-red color scale. Then the cells will be displayed in different color shades. The day on which the most hours were worked, appears in green. The day with the least hours is marked in red and in between you have more, depending on the value of green-yellow or red and yellow hues.

You can highlight the cells but also by embedded bar charts. To do this again, the cells I4: I25 and then select "Start, clear conditional formatting, rules, rules, delete in selected cells". Then click on Start, conditional formatting, data bars, and choose a color. Depending on the size of the cell value, a longer or shorter bar is displayed in the cell. Similarly, you can use the "symbol rates" also use (arrow) symbols for emphasis.

If you must sample file Financing.xls a worksheet with many investors want the cells as belonging to the same formula to calculate, you do not all enter by hand. Enter the contrary, the formula in the top cell, then in the lower right corner of the cell and drag the marker to all the cells that will be using this formula. The cell references within the formulas are adjusted automatically. Cell references, the copying should not be adjusted to provide you with dollar signs. For example, done in cell D20: The first protects the column reference, the second the row reference.

[BinDs14]

Macros with Excel 2010

Macro security: The macro protection of Excel, Word and Co. is to prevent malicious programs in Microsoft OF9C files are executed. If you set the security level to Medium, you will be warned when any workbook, if macros are embedded in it. If the session from a dubious source, such as the Internet , then you should run the macros in any case the ban. If it is from a safe source, you can prevent the execution but of course allow that malicious programs in Microsoft Office files are executed. If you set the security level to Medium, you will be warned when any workbook, if macros are embedded in it. If the session from a dubious source, such as the Internet, then you should ban the execution of macros in any case. If it is from a safe source, you can enable the use of course.

Recording Macros: Excel has a feature that allows you to record macros. This is for entry in the macro programming would be very helpful. To start a new recording, select Record Tools, Macro, in Excel 2003. In Excel 2007/2010 record macro developer tools, click. Then run all the steps that should perform the macro later, automatically, and stop the recording afterwards. In the programming window ([Alt] [F11]) then click the macro in the "modules" of your workbook.

[Alt] [Enter]: You can write to a single Excel cell, several lines of text. If you want a line break within the cell, hold to [Alt] and press [Enter].

Beware of formula data: If you enter a formula to start with, they will not one touch [Enter] to complete however, and instead of the mouse to another cell can click with, then Excel will automatically create the unfinished formula cell reference at the clicked cell.

[justinepeter]

Macabacus Macros is a free add-in for Excel 2010/2007 and Excel 2003 that adds various tools and keyboard shortcuts to speed up the construction of financial models. According to the developer, it can trim the time required to perform common tasks by up to 75%.

[void]

A good search on the google can give you the better result for that. Other than this you can see this thread. They have mentioned some formulas and functions to be used on Excel. I hope that this will help you to get some idea for that. Please reply, if you get any other shortcut in excel other than this.

[Pabinem]

Hi..
Thanks for the informations.

I want to know, is it possible to create a username and password in MS EXCEL 2010, and give specific access to the particular user to the specific file access.

Please revert.

[balajivenkateshan]

Hi Guys,

My excel skills are good ...now i want to have a hand on Macros on 2010 as thats one imp thing in my current company.....can anybody guide me of how should i start with it ...also please suggest me some good books
on this.....Thanks in Advance.