Working Smarter With Excel
Tore Søfting: Working Smarter With Excel Working smarter with Excel 2007 Table of contents Table of contents ..................................................................................................................................... 2 Introduction............................................................................................................................................. 4 The Basics ................................................................................................................................................ 5 Navigation ............................................................................................................................................ 5 Selection ............................................................................................................................................... 5 Smart summing .................................................................................................................................... 6 Copying formulas ................................................................................................................................. 7 Adjusting the column width (and row height) for multiple columns................................................... 7 Automatic cell formatting .................................................................................................................... 8 The right mouse button ....................................................................................................................... 8 List functions ........................................................................................................................................... 9 Freeze column headings ...................................................................................................................... 9 Repeat headings on print ..................................................................................................................... 9 Sorting ................................................................................................................................................ 10 Subtotals ............................................................................................................................................ 10 Auto filter ........................................................................................................................................... 10 Advanced filter ................................................................................................................................... 11 Identifying and removing duplicates from a list ............................................................................... 12 Some simple functions .......................................................................................................................... 14 Useful functionality in Excel .................................................................................................................. 14 Validation and protection .................................................................................................................. 14 Validation ........................................................................................................................................... 15 Protection........................................................................................................................................... 16 Output validation ............................................................................................................................... 16 Conditional formatting ....................................................................................................................... 16 Using the IF-function to highlight cells .............................................................................................. 17 Pivot table .......................................................................................................................................... 17 Other useful functions........................................................................................................................... 18 The IF-function ................................................................................................................................... 18 VLOOKUP............................................................................................................................................ 19 HLOOKUP ........................................................................................................................................... 19 PMT .................................................................................................................................................... 21 D-functions ............................................................................................................................................ 21 DSUM, DAVERAGE, DMAX,DMIN , etc. .............................................................................................. 21 More tools on the menu ....................................................................................................................... 22 Goal Seek............................................................................................................................................ 22 Solver.................................................................................................................................................. 23 Scenarios ............................................................................................................................................ 24 Consolidation ..................................................................................................................................... 25 Single value and dual value table ....................................................................................................... 26 Importing data from the Internet and from other applications ........................................................ 26 Importing live data from the Internet ................................................................................................ 27 Split cells............................................................................................................................................. 28 Retrieving information form inside a text string................................................................................ 28 Text to columns .................................................................................................................................. 28 Hiding error messages........................................................................................................................ 30 Tracing relations in formulas ............................................................................................................. 31 New and useful features in Excel 2007 ................................................................................................. 33 New functions .................................................................................................................................... 33 Improved graphics ............................................................................................................................. 34 www.excelkurs.com Page 2 Working smarter with Excel 2007 Conditional formatting .................................................................................................................. 34 Graphics rendering ........................................................................................................................ 34 Finally – how to get help with Excel-problems .................................................................................... 36 The built in help system in Excel ........................................................................................................ 36 Internet resources .............................................................................................................................. 36 Websites............................................................................................................................................. 36 Practice files ....................................................................................................................................... 37 www.excelkurs.com Page 3 Working smarter with Excel 2007 Introduction Ever since the computerized worksheet was hatched as a concept in the late seventies, and the first worksheet application became commercially available in 1979 (VisiCalc), it has become the foundation for all financial calculation for companies, public institutions and private citizens all over the world. It is indeed hard to fathom calculations without the use of an electronic worksheet. And, with an estimated worldwide market share of around 90%, a worksheet is in most cases an Excel worksheet. This compendium has been developed as a reference tool for the course Excel for accountants. The content of the compendium mostly follows the progress of the course in its description of the cases. This is no complete textbook for learning Excel; rather, it is a collection of cases for those who are already literate in the basic use of Excel. For those of you who want to immerse yourselves in Excel, there are several excellent books available. One of my favorites is John Walkenbach’s The Excel Bible. The various versions of Excel that are in use do not differ too much form each other, that is except for the 2007-version. If you are running Excel 2000, 2002, or 2003, there are only marginal differences. But, if you are running 2007, you know that there are serious differences. The working of the worksheet itself is not very different, but the interface is totally changed. Gone are the menu and toolbars. In their place we have gotten The Ribbon. The result is that the first few days (or weeks) running 2007 will consist of a lot of searching for the various functions. But – having conquered the principles of the placement of functions – one feels things are starting to work very smoothly. And – after still more time, one tends to find those earlier versions very old fashioned. Norwegian vs. American/English versions. These two language versions have few problems communication with one another. A model built in a Norwegian version can be opened in an American/English version without any problems, and vice versa. There are two things we should be aware of when typing a formula manually, and that is the use of symbols in the formula, and the names of the functions. The American/ English version uses comma (,) to separate parts of the formula, while a Norwegian version uses semi-colon (;). So, a formula that looks like this SUM(A1:A10,B1:B10) in an American/English version would look like this SUMMER(A1:A10;B1:B10) in a Norwegian version. Also, the function names are different, and this has to be considered when creating formulas in the respective versions. This compendium is updated with some of the new functions in Excel 2007. You can download practice files for this compendium from my website, www.excelkurs.com . Asker, September 2007 Tore Søfting [email protected] [email protected] www.excelkurs.com www.excelkurs.com Page 4 Working smarter with Excel 2007 The Basics It is very important to use the basic functionality of Excel correctly in order to work rationally. The typical participant on this course is an experienced Excel user who may use the application to quite complicated calculations. However, my experience tells me that the same user does not always use the most rational methods on the basic functionality of Excel. The following describes a collection of basic techniques that should rationalize your use of Excel, even if you are an experienced user. Navigation Everybody knows we can click the cursor into any position in the worksheet. Instead of clicking, we may use the keyboard for much quicker navigation. Jump to A1: Wherever the cursor might be located in the worksheet – jump to A1 by pressing Ctrl Home. If we use only the Home command, the cursor moved horizontally to column A Similarly you can navigate within your tables like this: From B2 to B9 (next break in the series) using Ctrl (arrow down), and from A6 to G6 (next break in the series), Ctrl (right arrow) This is particularly useful when you have to move greater distances. Selection Selection and navigation are closely linked. Selection is done from the keyboard by pressing the Shift key while moving the cursor by means of the navigation commands. All navigation commands can be combined with Shift, thereby enabling selection, for example: www.excelkurs.com Page 5 Working smarter with Excel 2007 If you want to select the whole table, the area A1:G9 when the cursor is located in G9 can be accomplished by pressing Ctrl Shift Home, and presto the table is selected: Alternatively we may use the command Ctrl A the select the whole table (when the cursor is located inside the table). The first use of Ctrl A will select the table; the second use of Ctrl A will select the whole sheet. You may practice this by using other keyboard commands for selecting parts of a table! Smart summing We all know Excel is a racer when it comes to summing up numbers, but do you know that Excel can sum up this way: In this case we want the table summed both horizontally and vertically, in other words we are going to make 13 formulas. We can make a formula in B9 and auto-fill it horizontally, and we can make a formula in G2 and auto-fill it vertically. But, we can do it even quicker: Select the range (or table) to be summed up, including the blank column to the right of the table – G2:G8, and the blank row below the table - B2:G9 One click on the Auto Sum Button will create the 13 formulas instantly. You tell Excel whether you want to sum up the table horizontally, vertically or both ways by selecting the empty column to the right or the empty row below the table. www.excelkurs.com Page 6 Working smarter with Excel 2007 Copying formulas A popular Excel function is possibility of copying (or Auto-filling) a formula horizontally or vertically. The vertical copying (or Auto fill) is normally accomplished even quicker by double clicking the Auto Fill handle instead of grabbing a hold of it and dragging it. This is particularly useful when you want to auto fill a formula vertically over a large number of rows. If you have tried this you will know that you will end up several hundred rows down on the sheet, and then you have to reverse your motion, and so on. This won’t happen if you double click as Excel will only auto fill as far down as there are corresponding cells to the left of the Double click the formula. handle, don’t drag! Notice that the auto-fill stops if there is a breach in the series – that is an empty cell in the closest column on the left. Adjusting the column width (and row height) for multiple columns By selecting all columns (or only the columns that are to be adjusted) you can give them the same width by dragging on one column, or you can automatically adjust them according to the size of the content by double clicking (Auto Fit function). Double click here for Auto Fit. www.excelkurs.com Page 7 Working smarter with Excel 2007 Automatic cell formatting Initially a cell in Excel has no particular formatting. When we type something into a cell, Excel will interpret this and apply the formatting it deems correct. This works like a charm 99% of the time, but there are occasions it does things that do not correspond with what we want. Example: We have a reference number like this: 30-8. If we enter this into a cell, it will be transformed into 30. August. As we understand, the entry is interpreted as a date, and the cell is formatted accordingly. That gives us two challenges: How do we prevent this from happening, and How do we get rid of the date formatting of this cell? Prevention: We can either: Pre-format the cell as Text, or Type an apostrophe before we enter whatever needs entering. The apostrophe will format the content to Text. Removal: We can apply another format, or remove both content and format (Edit – Clear – All) The right mouse button Experienced users are well familiarized with the right mouse button. In any case, I take this opportunity to remind you of it. Instead of searching for solutions on a number of possible menus, we can simply point to “the problem” and get a limited number of choices by right-clicking. An example: Excel has a number of toolbars. We can go to Show – Toolbars to show new toolbars, but it is much easier to place your pointer anywhere on the toolbars and right click to show the menu. And – this principle works well in any Windows program. www.excelkurs.com Page 8 Working smarter with Excel 2007 List functions A list in Excel-is a collection of data organized in such a way that various tools and functions in Excel can be applied to it. Characteristics of a list: Each column has a heading The heading is formatted different from the content(for. example bold types) Preferably nothing above the list itself No blank rows or columns Practice file: Ordrer.xls Freeze column headings What happened to the column heading? If you want to freeze row 1, you place the cursor in cell A2 and point to Window – Freeze. That “parks” the heading when you scroll down in your list. If you need to see column A when you scroll to the right, you can place the cursor in cell B2 before you hit Window - Freeze. Repeat headings on print Here we have the same problem, bi t an entirely different solution. Since this is related to the print format, you point to File – Page Setup – Sheet, place your cursor in the field Rows to repeat at top .and click on row 1 in the worksheet. If your table is too wide for the sheet you are printing on, you can also repeat the key-column on the left by placing your cursor in the field Columns to repeat at left and clicking on the column(-s) you want to have repeated. www.excelkurs.com Page 9 Working smarter with Excel 2007 Sorting We select the Sorting Key by placing the cursor in the column we wish to sort by, and then clicking the sorting buttons to sort the list in ascending or descending order. Note! Do not select the whole column we want to sort by! If we do, we risk sorting only the selected column, thereby messing up our list totally. Excel up to version 2003 can sort by up to three simultaneous keys, while Excel 2007 has an unlimited number of keys available. Notice that the button Options (Data – sort – options) gives access to other types of sorting keys, like months , days and other. Subtotals Practice file: Pivot og delsummer We have a very quick and easy way of extracting information from a large list by using the tool Subtotals. Point your pointer to Data – Subtotals to use this tool. In this case, we have asked for a sum of the columns “Antall”, “Brutto pris” and “Netto pris” at every change of “Selger”. By clicking the numbers (1, 2, 3) in the top left corner we can change the detailing of the numbers. If we want to remove the subtotals we point to Tools – Subtotals and select Remove all. Note! Before applying subtotals to a list, we have to sort it by the key we want to subtotal by, in this case ”Selger”. Auto filter The Auto filter makes it very easy for us to filter out records we have no interest in seeing – in order to see those that are of interest to us. The procedure is: Place your cursor inside the list, point to Data – Filter – Auto filter, and filtering buttons will be placed at the top of each column. www.excelkurs.com Page 10 Working smarter with Excel 2007 Select ”or” Using the Auto filter is simple and intuitive. If you want to set up somewhat more complicated filtering conditions, you select Custom filter on the drop-down menu. The illustration shows that we want to see records with the names Johansen or Nilsen in the record. Logical statement The drop-down list for logical statement contains a number of different statements. Take a look at these to get to know them, one day they may come in useful. Advanced filter What then if you8 want to show three salespersons using the Auto filter? Let’s say we want to have a look at the figures of Johansen, Nilsen and Olsen. The answer is simple; it can’t be done, or to be more precise, it can’t be done using the Auto filter. If we have requirements like these, we have to proceed to the Advanced filter. As the name implies, this filter can handle more complex filtering conditions. Using and setting up the Advanced filter is very similar to using some of the more advanced functions in Excel. The set-up is decidedly less intuitive than it is for the Auto filter, so it takes a bit more practice to get it to work properly. The main difference is that we type in the filtering conditions somewhere in a worksheet before we activate the filter. Here are a couple of examples: What are the sales of Johansen, Nilsen and/or Olsen? What sales do these three gentlemen have of the product “undertøy”? Let’s move over to sheet2, and set up the filtering conditions like this: Criteria range Then we go back to the list in Sheet1, place the cursor inside the list, and point to Data – Filter – Advanced filter. The list is automatically selected at the same time as the dialog for the Advanced www.excelkurs.com Page 11 Working smarter with Excel 2007 filter appears. Hit the Tab key to move the cursor into the Criteria range field, and while the cursor is sitting there, browse over to Sheet2, and select A1:A4. The heading needs to be part of the selection, as it is used for identification of the columns. Before moving on to including the ”Produkt” as part of the criteria, we have to remove the existing filter by pointing to Data – Filter – Show all. The procedure is the same, the only difference is that we include column B in the selection of the criteria range; A1:B4 Advanced filter is a powerful tool, and can be used in a number of different ways. It understands symbols like >, <, *, etc. So, just go ahead and try this out! Identifying and removing duplicates from a list Practice file: Liste med dubletter.xls Many Excel users are familiar with the situation where we have a customer list that may have been exported from the CRM-system for “washing” in Excel. How can we automate the process of removing duplicates from such a list? If we have a hundred records we may just handle this manually, but if we have a few thousand records, we can’t do that. If we start with the practice file, we first need to sort the records based on Customer ID. This will place possible duplicates adjacent to each other. We then use the IF-function to compare each and every Customer ID with the next one. The formula looks like this: =IF(B2=B3;””Duplicate”;””). Use Auto fill to extend this formula to all cells in column A. As we have asked the IF-function to type the word “Duplicates” next to a record that is equal to the one above, we can quite easily see where the duplicates are located. However, if we have a large list with thousands of records, we would probably want to locate all duplicates at the top of the list. This entails a little more than just sorting the list. First of all we need to end the dependency of the comment “Duplicates” on the content of the adjacent column. This we do by converting the formula into value, or put in another way, into the result of the formula. 1. Select the are that is to be converted 2. Copy the selection (leave the selection) 3. Point to Edit – Special Paste 4. Select Values and click ok. The copied area is now pasted on top of the originally selected area, and the formulas are converted into whatever they were showing at the time. Now we have only the text “Duplicates” next to the duplicates, and now we can sort the list (descending) without any problems. This will place all duplicates on the top of the list, where they can be easily removed or edited. www.excelkurs.com Page 12 Working smarter with Excel 2007 We can also envisage that the requirement for a duplicate is that both the Customer ID and the name-columns have equal contents. The procedure is in principle the same, but the formula is a bit more complicated: =IF(B2&C2=B3&C3;"Duplicate";""). By using this system we can ask Excel to check any columns for duplicates, either individually or in any combination. The practice file shows this case in detail. Note! This procedure has been vastly simplified in Excel 2007, as Removal of duplicates is included as a separate tool: www.excelkurs.com Page 13 Working smarter with Excel 2007 Some simple functions MIN – finds the lowest value in a selection MAX – finds the highest value in a selection AVERAGE – calculates the average for a selection SUMIF – Sums up numbers from a column based on a criteria in another column. So, what is the total value for the field nettopris for the product dresser? We can easily answer that question using this function. COUNTIF – counts the number of occurrences of a text or value, for example how many occurrences are there of the product dresser in column B. Useful functionality in Excel Validation and protection Practice file Validering og beskyttelse 2.xls In any (real) database much effort is put into reducing the possibility of incorrect entry in the various fields. If a year is to be entered into a filed, there are always limitations as to which years can be entered, and you will also not be able to register something entirely different in the filed, like a name or similar. This is a common practice for entering any kind of information into a database. As long as we are working on our own stuff in Excel, we usually do not put much consideration to this. But, when we create models others are to enter data into, we should start taking this into consideration. We have two tools in Excel that help us in reducing the chances of messing up things, and those are Validation and Protection. The Validation tool limits what can be entered into a cell, and www.excelkurs.com Page 14 Working smarter with Excel 2007 the Protection tool makes sure nobody can change the parameters in validation or change anything else in the model for that matter. Validation By using the validation tool, we can decide what can be entered into a cell. In the practice file there is the case of reporting financial info. The user can enter information into the yellow cells. These are the only cells information can be entered, and we have decided what kind of information can be entered. In the cells B3 and B5 only predetermined alternatives from a drop-down list can be entered, while in cells B7 and B8 only numbers within a given range can be entered. To set the limitations on what may be entered, point to Data – Validation. Her we can see that at the outset we can enter anything into a cell. Then we can set the validation parameters: Cell A3: Here we are to register one out of five departments, and to make this job as easy as possible for the user, we select: Allow: List Source: here we can type the alternatives, separated with a semicolon, and when we tick In-cell dropdown, the alternatives will appear on a dropdown-list. We can also refer to a list that we have typed somewhere else in the worksheet Principally; this list has to be placed in the same sheet as the cell. There is a way around this, allowing the list to be located on another worksheet, and that is naming the location of the list and referring to that in the Source-field. Cell B5: Here we enter the names of the months, in the same way we entered the alternative departments for the first cell. Cell B7: This is the cell where we are to enter the sales. To keep the entrant from entering a digit too little or too much, we can set a lower limit of 30 000 000 and an upper limit of 40 000 000, based on empirical data. Cell B8: Expenses can be validated in a similar way. The other cells contain formulas that retrieve their information form the input cells, so we do not place any validation criteria on these. www.excelkurs.com Page 15 Working smarter with Excel 2007 Protection Now that we have decided what can be entered into the cells, it is time to make sure nobody tampers with the validation – or with the formulas, for that matter. Before we do that, two things must be clear: On the menu Format – Cells – Protection a cell is by default set to Locked. This setting has no effect until we use the tool Protect Sheet. Any cell with the formatting set to Locked will be unavailable after Protect Sheet is activated. Consequently, we have to turn off the Locked setting before we activate Protect Sheet. We have now achieved two things: We have decided which cells are available for data entry, and We have decided what can be entered into the same cells. Output validation First and foremost we use data validation to control and limit what is entered into a cell. But, we can also use validation on an output cell, which is a cell containing a formula. The scenario is then somewhat different. It would be unthinkable to deny the display of whatever values appear in such a cell, so we use this function to accentuate values that are outside what we deem an acceptable range. The screenshots are from the file Validering og beskyttelse.xls. You can also find examples in the file Analyseeksempel.xls. Click here To show which cells put out values outside an acceptable range, we set the validation as described earlier. To circle unacceptable values we display the toolbar Formula auditing, where we click the button shown on the left. This will make Excel circle unacceptable values w9ith a red circle. We click the next button to remove the circles. Conditional formatting The tool Conditional formatting can also be used to focus on values that are remarkable in a positive or negative way. This setting will render any cell with a value of less that 0,1 with red characters on a yellow background. No problem seeing that! www.excelkurs.com Page 16 Working smarter with Excel 2007 The result in the worksheet is like this: Using the IF-function to highlight cells We can of course use the IF-function to test values of numbers and render a comment like ”Bad”, or ”Good” based on the outcome of the test. Examples can be found in the practice files Analyseeksempel.xls, on the sheet Løsningseksempel4. Pivot table Earlier, we have looked at the use of the tools Subtotals, Auto filter, and Advanced filter to retrieve information from large lists. The Pivot Table is a tool that does many of the same things, usually even more elegantly than the other tools. Practice file: Pivot og delsum.xls Once more we can open this file and ask ourselves the inevitable question: How are our salespersons actually doing? The procedure is as follows: Place your cursor inside the list Point to Data – PivotTable and PivotChart report Proceed thru the wizard without changing any of the defaults. This will give us the outline shown on the left. Now we need to drag our own column headings into the outline, and place them correctly. We will start with a simple request; we want to know what each salesperson has sold of the various products, and the value criteria is the net price. www.excelkurs.com Page 17 Working smarter with Excel 2007 The procedure is as follows: Grab a hold of the field Selger and drag it to the area Drop Row Fields Here) Grab a hold of the field Produkt and drag it into the field Drop Column Fields Here Finally, we drag the field Netto Pris into the area Drop Data Items Here. This gives us a cross table showing net sales pr. salesperson and product: We can experiment my moving the fields around in the table, thus rendering other displays. The Pivot table is a very powerful tool, maybe even the most powerful analysis tool in Excel. Data can be analyzed in innumerable ways using this tool. The example described here is definitely the short version of the Saga of the Pivot Table. Other useful functions Excel has several hundred built-in functions. Many of these are very specialized, thus requiring extensive knowledge of a subject to be of any use, while others are more general, and may be useful to most users. The IF-function This function allows Excel to make a choice for us, in its most basic form between two alternatives, based on the outcome of a logical test. =IF(TEST;TRUE;FALSE) A test (or a claim) has two possible outcomes, either it is TRUE or it is FALSE. This example shows how we can use the IF-function to assign the correct membership fee to the members of the neighborhood association based on a test of their age: If the age is greater than (or equal to) 67, the fee is 75, is it is less than 67, the fee is 150. This is the formula: =IF(C6>=$C$3;$C$2;$C$1) www.excelkurs.com Page 18 Working smarter with Excel 2007 After entering the formula into cell B6, we just use the auto-fill handle to copy it into the other cells in the range, making sure we use the absolute reference-designation correctly. Another example of the use of the IF-function can be found is the file HVIS-lønn.xls. Here we test for the value of the sales of each person in order to decide if they should have a bonus, and what it should amount to. The IF-function can be used to select between up to 7 alternatives when we use the technique of nesting the functions, that is to say putting them inside each other. An example of such use is located in the file Nested IF-functions. , VLOOKUP HLOOKUP These are two lookup-functions that respectively search by column or by row for a value defined from our input. Example: By entering the employee number, the VLOOKUP-function will search horizontally in the designated column for the value that corresponds to the employee number. One notable feature with this function is that we designate the column it should look for the name in by entering an Index. That means we count columns from left to right (within the matrix), and the index 2 will give us the first name of the employee. This file also contains an example on the use of the function .HLOOKUP. Note! Two important considerations: You should make sure you enter the parameter FALSE into the field Range_lookup, like this: www.excelkurs.com Page 19 Working smarter with Excel 2007 If we do not do that, Excel will return the closest value to the one we asked for, and this may have unforeseeable consequences. If we enter 120 instead of 102 as we intended, Excel will return the name of the number closest to 120, which is 110. This is somebody entirely different from the person we were looking for! The other important consideration is that the lookup-column (Employee number) is sorted ascending. This is q prerequisite to get correct answers. www.excelkurs.com Page 20 Working smarter with Excel 2007 PMT This function calculates the payment per term for an annuity based loan, and as such is useful for everybody calculating financing. When the function is set up properly, the parameters can be changed, and we can see the result on the payment directly. Note! The interest rate that is referred to from cell B2 must be divided by the number of terms per year in cell B3.. Also, the total number of payments (B5) must be the result of a formula. If it is typed into the cell, it won’t update when parameters are changed, and the payment will not be correct. D-functions Practice file D-funksjoner 2.xls DSUM, DAVERAGE, DMAX,DMIN , etc. Excel has a series of functions with the D-prefix. The “D” is from Database, so these are functions designed to retrieve data from large collections of structured data. Setting up these functions is very similar to the set-up of advanced filter. We type the arguments of the function somewhere in the worksheet and refer to them, instead of typing them directly into the function. Example: In the file D-funksjoner.xls,on the sheet Uttrekk_1, we have a dump form an administrative system. The records all have three references; Avdeling, Periode, og Prosjekt. We wish to match these three criteria to be able to answer questions like: www.excelkurs.com Page 21 Working smarter with Excel 2007 What it the total result for the combination of Avdeling 2, Prosjekt 2? First we enter the criteria, see the sheet Uttrekk_2. We then set up the function in B4 in the same sheet. Then we start the function wizard, and enter parameters this way: Database is the sheet Data, select the area with contents, including the heading. Field is the data we want to sum up. Criteria is the area of the sheet where we have entered out criteria. Excel has D-functions corresponding to many of the better-known functions, DSUM, DAVERAGE, DMIN, DMAX, etc. What these functions actually do, is to handle multiple criteria simultaneously. More tools on the menu In Excel we solve problems partly by using functions in formulas, and partly by using tools located on menus. Let us take a look at some of these menu-based tools: Goal Seek This is a simple and easy-to-use tool that calculates backwards by simulating alternate input arguments in a function much quicker than we are able to do this. Example: We are buying a new house. We have calculated that we can manage 8000 per month in payments. The interest rate is given at 3,5%, and the bank says the maximum number of years for payment is 15. How much money can we borrow? We have looked at the function PMT earlier. We might use this and simply type in amounts until we were close to our 8 000 per month. This would take some time, and we would never be able to get an accurate amount. However, if we use Goal Seek, we can get the exact amount immediately: Locate Tools – Goal Seek on the menu, and enter parameters as shown: Set cell B9 to value 8000 by changing cell B3 - and we have the answer immediately. www.excelkurs.com Page 22 Working smarter with Excel 2007 Solver Practice file: Solver.xls Goal Seek is ok for its intended use, but in many cases we have problems with multiple variables. These variables have to be optimized against each other, and that is not possible with Goal Seek. We have to use a much more advanced tool, the Solver. Example: We have a budget for next year for our company. This is no common budget where we calculate values outside the budget-model and enter the values into the model. This is a “live” budget application where we want to optimize the use of advertising money. The idea is that spending money for advertising will increase our sales, up to a point. When a certain level of spending is reached, more spending will not increase sales sufficiently to leave is a profit. Thus, beyond this point, we will lose money by spending even more for advertising. The real-world challenge is of course to know the formula that relates these two parameters. Graphically we can render it like this: Sammenheng mellom reklameinnsats og overskudd 120000 The profit culminates here? 100000 80000 60000 Advertising spending Reklameinnsats: Profits Overskudd 40000 20000 0 Alt 1 Alt 2 Alt 3 Alt 4 Alt 5 Alt 6 Alt 7 Alt 8 Note!The Solver tool is located on the Tools meny when installed. If you can’t find it there you have to run the installation routine. Point to Tools – Add ins, og tick the Solver add in. There may be cases where Excel asks for the installation disk, but in most cases it will be able to install directly. We will now maximize the profit (F14) by changing the advertising spending(B10:E10). To illustrate how we enter limitations, we decide that maximum spending should be 50000 (F10): www.excelkurs.com Page 23 Working smarter with Excel 2007 With these criteria the profit is maximized to Endringsceller around 84 000. If we Legg til run the begrensninger simulation without setting any limitations, the profit will be somewhat higher. But, the final krone we spend on advertising does not lead to much improvement in the total profit. Even if we increase he advertising budget to 100 000, the profit will increase with only a few hundred kroners. Målcelle – skal maksimeres Scenarios Practice file: Budsjettscenario.xls The term scenario is familar to everybody working with accounting and finance. We often have three scenarios, one worst case, one bets case, and one middle-of-the-road. Technically scenarios can be handled in many different ways, even with adjacent columns in the budget. Excel has a built-in tool that enables storing several different values in one cell, and retrieving these values based on selecting the different scenarios. Example: Here we have a small and simple budget consisting of one account for income and one for expenses. These two cells have three alternative values connected to three scenarios, and we will now enter and store these values in their respective cells. We start by entering the numbers for the scenario “Dårlig” In order to keep tarck of the different scenarios, it is a good idea to enter the name of the scenario in a cell – B7. The next step is to select the cells in the scenario, B3:B4 and B7. Point to Tools – Scenarios and click Add. Type the name of the scenario and click ok until we have closed the dialog. Next, we deal with the other two scenarios the same way. After www.excelkurs.com Page 24 Working smarter with Excel 2007 entering the three scenarios, we can point to Tools – Scenarios, and now we can see the available scenarios. So, if we want to change scenario, we simply point to the scenarios we want to see, and click Show. Consolidation Practice files: Konsolidering Oslo.xls, konsolidering Bergen.xls, konsolidering Trondheim.xls These three files all show sales figures for our branches in these three different cities. Now, we want to total the numbers. The problem with that is the fact that the three worksheets we need to total are not equal. We have different brands of cars, and they are placed haphazardly in relation to each other. We could build formulas to sum up all this, but with a large number of cells, we would be lucky to get everything right, and we would spend quite some time to accomplish this. This is where the tool Consolidation shines. It is able to sum up sheets based on referring to column and row headings, not the usual cell references! Example: First, open all three files, then open another blank workbook. Point to Data – Consolidate. Leave the cursor in the field Reference, browse to the first pen workbook (Oslo), select A3:D10, and click Add in the dialog. Next, browse to Bergen and Trondheim do the same procedure. Make sure you select correctly, you need to include the column and row headings. The dialog should look like the illustration on the left: Note! We have ticked off all three tick boxes for labels and for links to the source data! The new workbook is now showing the totals, and we have hardly spent any time getting to this point! www.excelkurs.com Page 25 Working smarter with Excel 2007 Clicking the plus-signs in the left margin will display the specification behind every consolidated number. Single value and dual value table Practice file: Datatabell.xls The Table function allows us to analyze alternatives very quickly, and lined up for side-by-side comparison. Example: We want to analyze the cost of borrowing, first with a given interest rate in combination with various amounts, and later with alternate values for both the loan and the interest rate. A description of the procedure can be found in the practice file: Importing data from the Internet and from other applications Excel ”talks” very well to many other applications. Thus we can import numbers form other applications into Excel for further analysis and calculation. When exporting to Excel from any administrative system, we use the features in that respective system. In many cases we just click an Ecxel-button to create an Excel file from whatever query or report we have created. When looking at this process from the Excel-side, we use the Import features of Excel to get the various reports into Excel.. Example: We wish to import a table from a page at the website of Norwegian Statistical Office. The address is http://www.ssb.no/fobhusinnt/tab-2003-12-18-01.html Some of us may have tried to ”import” by just selecting and copying data from a web page and try toi paste in into a worksheet. In most cases this is highly unsuccessful. The correct way of doing this is to use the Import Wizard in ExcelStart by opening the web page referred to above. Select the URL (the web address) and copy it to the clipboard (Ctrl C) Go back to Excel and point to Data – Import External Data – New Web Query A “mini”-browser appears, and we now paste the address that we just copied into the address field at the top, replacing the start page address that is there. Hit Enter (or click Go) www.excelkurs.com Page 26 Working smarter with Excel 2007 Klikk og merk her! After a few seconds the page will open, and we get these yellow selection handles that enable us to import only the stuff we want form the page. We just select the table(-s) we want to import by clicking the yellow arrow. Next, click Import, then select destination cell, and presto, everything is nicely imported into the worksheet. The numbers are correctly formatted, and we can now proceed in our analysis of the numbers. Importing live data from the Internet In many calculations we have to take into account ever changing outside factors, for example currency rates , interest rates or stock values. In the following example we will create a live link from Dagens Næringsliv’s currency rate website to a workbook. The address is http://www.dn.no/finans/valuta/ Procedure: First we locate the website from where we want to import live data Then we select and copy the URL (web address) Browse to Excel and place you cursor in a blank worksheet Point to Data – Import External Data – New Web Query. Paste the address you copied into the address filed of the mini browser. Select the table(-s) you want to import by clicking the selection handles. You may og to Options and select HTML formatting for a nicer look. Click import In the next dialog, click Properties, and set the update frequency as you want it. Click ok, and the data is in the worksheet! Now we have a live link into a web page and our calculations will update when new information is retrieved from that web page! www.excelkurs.com Page 27 Working smarter with Excel 2007 Split cells Practice file:the result of the import form the Statistical Office When importing data we don’t always get the data separated the way we want. In this case we have both the ID number and the name of the municipality in the same cell. We have the same challenge when customer name and number both are in the same cell. There are several way of dealing with this in Excel, and here are some examples: We can use the functions LEFT and RIGHT to solve this. These functions are easy to use as long as we are to split at a given number if digits. In this case all numbers have four digits, so it is easy to say that the first four digits should be extracted and place din a separate cell. It is harder to extract the name, but it can be done. The example is elaborated on the sheet Høyre_Venstre in the workbook Hente data fra Internett. Retrieving information form inside a text string In addition to the two functions LEFT and RIGHT, we have the function MID that is used for extracting digits from inside a string The syntax is: MID(Cell reference of text string; Number char from the left; Number of char to be extracted) Text to columns Using the built-in tool Text to columns is normally the easiest way to accomplish the splitting. This tool is located on the Data menu. Example: In the worksheet Tekst til kolonner we want to split the municipality number and the name and place these in separate cells: Procedure Insert two new columns between cols A and B Select the cells you want to split(A9:A26) Point to Data – Text to columns Here we have the choice to split on a character or on fixed width, and since the municipality number has four digits, we can use either. As some of the municipalities have hyphenated names we get the best and easiest results by going for fixed width. Next, we see the dividing line between number and www.excelkurs.com Page 28 Working smarter with Excel 2007 name. If we select B9 as destination cell, we won’t overwrite any of the present information in the worksheet. That’s it, we have split number and name and placed them in separate cells. A new problem has appeared, we have lost the leading 0 in the number. We can solve this without converting the number to text. Procedure: Select the cells in question Point to Format- Cells –Custom Type four zeroes into the field Type. A few words on text vs numbers in the postal code. As long as the numbers are to continue their lives in Excel, we can keep the described formatting. However, if we want to work on these numbers in certain ways, notably merging the numbers into a www.excelkurs.com Page 29 Working smarter with Excel 2007 Word document, we will run into trouble. The merging process will once more remove the leadiung zero. The best solution to this is to make the right choices during the import procedure. The numbers will now be stored as text, and the leading zeroes will survive any abuse we might expose them to. But, they will be text, and we can’t use them for any kinds of calculations. Hiding error messages Practice file: Skjule feilmeldinger.xls There are times when we are annoyed by cryptic error messages in our worksheets. In an otherwise stellar set-up we get this error message if we try to divide by zero: #DIV/0! The reason may simply be the fact that the cell we try to divide by doesn’t contain any information at all: Error messages can be hidden (or dealt with) in two different ways Conditional formatting The function ISERROR Conditional formatting We simply use conditional formatting go give the cell contents white color when a condition is met, thus making it look “invisible”. This solution only cures the symptom, and if this is not good enough, we have to resort to solution number two. Procedure Select the cells you what to apply conditional formatting to Point to Format – Conditional Formatting From the left drop-down list you select Formula is and in the right window you type =ISERROR) Click Format and set the character color to white. The error messages will now be invisible. The function ISERROR www.excelkurs.com Page 30 Working smarter with Excel 2007 This is somewhat more cumbersome to use, but by using this, we no longer just cure the symptom, we go to the root of the problem and solve it there. To make this work we have combine the existing formula with both the ISERROR function and the IF function. In this example we have added the text No data: 1 2 3 4 5 6 7 8 A Teller 1 1 1 1 10 1 1 B Nevner 0 2 0 10 2 0 5 C Result No data 0,5 No data 0,1 5 No data 0,2 This is the original formula: =A2/B2 We now have ot out this formula into an IF-function together wiht ISERROR, like this: =IF(ISERROR(A2/B2);"No data";(A2/B2)) Obviously, you can replace the text ”No data” with any text string you find appropriate! Tracing relations in formulas Most of us are familiar with this scenario: You have inherited an Excel model from a colleague (who of course has quit), and you are trying to decipher the relations, and also the basic logic in the model. On the toolbar Formula Auditing we have some excellent tools to alleviate this problem. Practice file: Løsningsforslag Totalresultat for Bedriften AS_startslutt.xls Let’s say we are staring at the Resultat cell and you we are wondering where this number actually comes from. Place your cursor on the cell, click the Trace precedents button, and Trace precedent arrows will tell you where the input to this formula. Click once more, and you will see where the contents of those cells stem form, and so on. If you get a dotted arrow with an icon depicting a worksheet at the end, point to the arrow, and double click. You will now get a dialog www.excelkurs.com Page 31 Working smarter with Excel 2007 showing the origin, and you can follow it by selecting the reference and clicking ok. Using this technique, we can follow the “trail” all the way to the end www.excelkurs.com Page 32 Working smarter with Excel 2007 New and useful features in Excel 2007 New functions The examples inh this compendium are based on use of Excel 2002 and 2003, to versions that are nearly identical in look and functionality. The latest version of Excel has gotten a thorough overhaul, mostly in the interface and looks departments, but also when it comes to functionality. Notably: New user interface New functionality The number if new functions is limited. Altogether there are less than ten new functions; some of them are really something we have been waiting for. These are: IFERROR AVERAGEIF AVERAGEIFS SUMIFS COUNTIFS IFERROR simplifies search for errors substantially. As we have seen earlier in this publication we have had to use a combination of IF and ISERROR to make Excel type a free text in case of an error message. This can now de done directly by using this function. The same problems can be solved like this in 2003 and 2007: In 2003: =IF(ISERROR(VLOOKUP("John"; Sales; 3; FALSE)); " No value"; VLOOKUP ("John"; Sales; 3, FALSE)) I 2007: =IFERROR(VLOOKUP(“John”; Varesalg; 3, false), “No value”) As we can see, the process is vastly simplified in Excel 2007. AVERAGEIF corresponds to SUMIF and COUNTIF – And it gives us the average from a series of numbers that are in accordance with a given criteria. The following formula gives the average of values that are greater than 100 in the selected range: Practice file: Averageif.xlsx =AVERAGEIF(A2:A5;”Z100”;B2:B5) AVERAGEIFS enables more than one criteria. SUMIFS In earlier versions of Excel we have the function SUMIF (and COUNTIF). These are great for their intended use, but what if we need to sum while testing for multiple criteria? This can be done in these versions, but it is a rather daunting task. This has become very simple in Excel 2007. Practice file: Countifs.xlsx og Countifs2.xlsx www.excelkurs.com Page 33 Working smarter with Excel 2007 Improved graphics I guess we all expect things to look a little nicer in the new version of an application. This hope is fulfilled in the new Excel. Among other things, the graphics engine – that makes graphs, has made great leaps, for unrivalled graphics rendering. Conditional formatting We have discussed conditional formatting at length before. This functionality has been seriously improved for 2007. This is an example on how we can use CF to render numbers more understandable in relation to each other. Salg 127 935 202 651 238 567 127 101 192 267 150 504 225 494 162 690 221 378 Salg 127 935 202 651 238 567 127 101 192 267 150 504 225 494 162 690 221 378 Salg 127 935 202 651 238 567 127 101 192 267 150 504 225 494 162 690 221 378 Salg 127 935 202 651 238 567 127 101 192 267 150 504 225 494 162 690 221 378 Graphics rendering The improvements mostly consist of a nicer look to graphics. A graph in Excel 2007 may look like this: 300 000 Salg 250 000 Kostnader 200 000 150 000 100 000 50 000 1 2 3 4 5 6 7 8 9 www.excelkurs.com 10 Page 34 Working smarter with Excel 2007 Salg Or, we may go to war with a partly transparent data point in a graph. All in all, the possibilities for spending hours and days “improving” the looks of your graphs have taken a great leap forwards. 150 504 127 935 202 651 192 267 127 101 238 567 Practice file: Conditional formatting www.excelkurs.com Page 35 Working smarter with Excel 2007 Finally – how to get help with Excel-problems This little leaflet and the corresponding course has hopefully advanced your knowledge of Excel a few steps. None of them can give The Final Answer to Excel problems. This means that the advanced and interested Excel user will run into problems from time to time. The more you learn, the more you realize how much there is you don’t know. Therefore, it is very useful to know here you can turn to get some assistance: The built in help system in Excel The help system has been getting steadily better over time, and I find many of the examples described there very useful. It is also easily searchable, so go ahead, try it out! Internet resources What you can’t find on the internet, is hardly worth finding. You can feel certain that whatever problems you are faced with have been encountered by a few thousand, or even a few million users somewhere else in the world. The search engines are great for retrieving this information, so just go ahead and type in keywords. Example: Let’s say I don’t know how to split cells. I will therefore try a Google search like this: Excel split cells In less than a second I am presented with more than 6 000 references to this subject. Websites There are several websites dedicated to Excel. Just pay them a visit and do a search if you have a problem. You can find lot of great tips here. http://www.j-walk.com/ss/ - this is John Walkenbach’s website, the author of The Excel Bible http://www.rondebruin.nl/tips.htm http://www.cpearson.com/excel.htm http://www.spinnakeradd-ins.com/ And, my own website is also updated regularly with new Excel tips: http://www.excelkurs.com www.excelkurs.com Page 36 Working smarter with Excel 2007 Practice files Ordrer.xls Pivot og delsummer.xls Validering og beskyttelse2.xls Analyseeksempel.xls Pivot og delsum.xls HVIS-lønn.xls D-funksjoner2.xls Goal seek.xls Solver.xls Budsjettscenario.xls Konsolidering Oslo.xls Konsolidering Trondheim.xls Konsolidering Bergen.xls Datatabell.xls Skjule feilmeldinger.xls Totalresultat for Bedriften AS.xls Dele fornavn mellomnavn etternavn.xls Løsningsforslag Totalresultat for Bedriften AS_startslutt.xls Liste med dubletter.xls Conditional formatting.xlsx Countifs.xlsx Countifs2.xlsx Averageif.xlsx Løpende budsjett med diagram.xls Løpende budsjett med diagram.xlsx www.excelkurs.com Page 37 Working smarter with Excel 2007 Index functions, 14 A Advanced filter, 11 Auto filter, 10 AVERAGE, 14 P G Pivot table, 17 PMT, 21 protection, 14 Goal Seek, 22 H C cell formatting, 8 column headings, 9 column width, 7 Conditional formatting, 16 Consolidation, 25 Copying formulas, 7 COUNTIF, 14, 33 D Database, 22 DAVERAGE, 21 D-functions, 21 DMAX, 21 DMIN, 21 DSUM, 21 duplicates, 12 E error messages, 30 F formatting, 16 help, 36 hiding error messages, 30 HLOOKUP, 19 R Repeat heading, 9 Right mouse button, 8 row height, 7 I IF, 17 Importing, 26, 27 Internet, 26 ISERROR, 30 L list, 9, 12, 15 List functions, 9 live data, 27 M MAX, 14 MID, 28 MIN, 14 S Scenarios, 24 Selection, 5 show, 11 Solver, 23 Sorting, 9 Spore, 31 Subtotals, 10 SUMIF, 14 summing, 6 T Text to columns, 28 V N Navigation, 5 Validation, 14 VLOOKUP, 19 www.excelkurs.com Page 38
© Copyright 2024