of Contents Course Information 3 Chapter 1 – All Functions 4 Chapter 2 – The If Function 17 Chapter 3 – Using Functions to Crunch & Clean Data 22 Chapter 4 – Data Commands 40 Chapter 5 - Macros 72 Chapter 6 – Miscellaneous 75 Chapter 7 – XML Data 83 Chapter 8 – Quick Tips 91 Chapter 9 – New in Excel 2007 94 Appendix - Instructor’s Biography 105
function could be used to sort rows – which effectively means that you can sort by color in Excel 2003 – but it takes a bit more effort. Sort By Custom List – Another sorting capability in is the ability to sort by Custom List. For example, assume a CPA firm has ten partners, and the Managing partner prefers to be shown at the top of the list, and the remaining Partners based on seniority. In this case, you could create a Custom List in the excel Options dialog box listing the partners in the desired order, and then sort future reports based on that order. Perhaps a better example use of this feature would be to create a non-alphabetic custom list of your chart of accounts, and then sort transactions to produce a general ledger in chart of account order – even if your preferred chart of accounts is not alphabetical. the partner seniority does not match the alphabetic names, nor any Filtering Data - AutoFilter to filter data allows you to view a subset of your data in a range of cells or table. Once you have filtered the data, you can apply additional filters to further refine your data view. When you are done, you can clear a filter to once again redisplay all of the data. To use this tool, start with any list of data and turn on the AutoFilter tool. Then position your cursor in the column you want to filter and use the drop down arrows to apply your filters as shown in the screen below. Once the filters are applied, you will see a subset your data. For example, the screen presented below shows filtered data for only Macon and Savannah properties. As filters are applied, a funnel appears in the drop down arrow button to indicate that a filter has been applied. You can apply filters for multiple columns simultaneously. Key Points Concerning The AutoFilter Command: Contiguous Data – The AutoFilter tools works best when you are working with data that is contiguous. In other words, your data should contain no blank columns, no blank rows, and the columns must all be labeled. Filter by Multiple Columns - You can filter by more than one column.
Goal Seek If you know the result that you want from a formula, but are not sure what value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal. Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in discussed at the end of this manual. Scenarios Scenario Manager allows you to create and save multiple “what if” scenarios (such as best case, most likely, and worst cases scenarios). You can also create a summary table of the scenario results in seconds. It is particularly useful for worksheets such as budgets in which users have often saved multiple copies of the same worksheet to accomplish the same objective. An example is shown below. In this example, a tire company has prepared a revenue budget for the coming year, and has created three alternative scenarios to generate the revenues that will result given a variety of mark up assumptions – in this case 100%, 110% and 120% markups. Pressing the summary button in the scenario manager dialog box will create the following Pivot Table of possible alternative results. Here we see detailed revenue projections for all tires and labor fees given all three possible scenarios of 100%, 110%, and 120% markup. With a few simple copy paste commands, the newly created data can be positioned and formatted next to the original projections as shown in the screen below. Of course the scenarios could have been created easily using simple formulas instead of using the scenario manager tool as described above. This underscores that best purpose of scenario manager which is to keep track of older and changing data through time, rather than producing what-if scenarios. For example, a complex projection containing scenarios based on original assumptions, revised assumptions, and final assumptions will allow management to go back and review the assumptions used throughout the project, and see how those assumptions changed as project planning progressed.
4. Absolute versus Relative Macros – An “Absolute” will always affect the same cells each time whereas a “Relative” macro will affect those cells relative to where your cursor is positioned when you invoke the macro. It is crucial that you understand the difference. 5. Editing Macros – Once created, you can view and/or edit your macro using the View Macros option. This will open the macro subroutine in a Visual basic programming window and provide you with a plethora of VB tools. 6. Advanced Visual Basic Programming – For the truly ambitious CPA, in the Visual Basic Programming window, you have the necessary tools you need to build very sophisticated macros with dialog boxes, drop down menu options, check boxes, radio buttons – the whole works. I invite you to knock yourself out. To see all of this power, turn on the “Developer Tab” in “Excel Options” (see below). Presented below are more detailed comments and step-by-step instructions for creating and invoking macros, followed by some example macros. 1. Creating a - To create a macro, click “Record Macro” in the “Code Group” on the Developer Tab. Assign a Name - In the “Macro Name” box, enter a name for the macro. i. The character of the macro name must be a letter. ii. Subsequent characters can be letters, numbers, or underscore characters. iii. Spaces cannot be used in a macro name (an underscore character is often used as a word separator. iv. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid. Assign a CTRL Combination (optional) – You can assign a CTRL combination to run the by typing any lowercase letter or uppercase letter that you want to use in the Shortcut key box. i. The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. Macro Location - In the “Store Macro In” list, select the workbook where you want to store the macro.