Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS)

Part One

Using Excel Efficiently

Part One describes how Excel, the widely used spreadsheet software, can be used efficiently to help build your spreadsheet for a variety of purposes. As an MBA student, an analyst, or an executive, you will develop enough expertise to perform the same tasks you were performing before—using other means—much faster and in a more efficient way. This part of the book demonstrates tools, shortcuts, and techniques for carrying out some common tasks quickly and efficiently.

This part will not turn you into an Excel expert in a short time, but by the end you should improve the tasks you can do—the types of tasks that make Excel into such an incredibly powerful and flexible tool for modeling, finance, statistics, and data manipulation.

In Part One: Using Excel Efficiently, we cover the AutoFill feature, efficient selecting, and highlighting in Excel. You will also learn how to use keyboard selection shortcuts. The next topic covered is how to insert formulas, activate functions, and use absolute and relative addressing. The last three parts are the naming of cells and ranges, formatting and conditional formatting, and creating simple as well as complicated charts and Sparklines.

Chapter 1

AutoFill

The AutoFill feature in Microsoft Excel can automatically fill in cells with commonly used series (numbers, months, and days of the week) or with custom lists you can create. This chapter demonstrates how to use the drag handle and other ways to fill in information. These operations work in all directions: top down, down up, left to right, and right to left. Figure 1.1 demonstrates this feature.

FIGURE 1.1 Using the Drag Handle

Select two adjacent cells and release the mouse. When you hover again over the lower right corner, your mouse pointer should change shape to a crosshair (+) called a drag handle. You can click and drag down the column and Excel will continue the initial two-cell series for you.

You can AutoFill several types of data including, but not limited to, numbers, dates, days, and annual quarters by selecting cells and dragging the handle as shown in Figure 1.1.

By default, a number of AutoFill lists are pre-installed in the program. For a list of the available AutoFill series, in the 2010 version we use the File icon to get to the Excel Advanced Options menu and the Custom Lists menu. See Figure 1.2. For 2007 go to the Windows icon, click on Excel Options, and click on the Edit Custom Lists button. See Figure 1.2.

FIGURE 1.2 Custom Lists

You may add your own lists as needed to Custom Lists. This is explained at the end of the chapter.

Try to use the following example for using the Custom Lists shown in Figure 1.3. We filled in the information in the sheet. You may want to open the AutoFill sheet in the Excel file for Chapter 1 on the web page. The example illustrates the use of the AutoFill feature in Excel.

FIGURE 1.3 Drag Handle and AutoFill

Select the first two values in column B (B2 and B3), click on the lower right drag handle of cell B3, and pull the drag handle down toward cell B10.

Dragging down the information created the desired AutoFill effect of continuing with the same series of numbers: 6, 9, 12, 15, . . ., 30. Try to drag down the information shown in columns C and D. You will create the information shown in Figure 1.4.

FIGURE 1.4 Dragging Down the Information

After you experiment with a couple of columns, try a more efficient way: select two vertical adjacent cells E2:E4. Release the mouse for a moment. Go to the drag handle. This time, do not drag—just double-click. Excel will drag it down for you. See Figure 1.5. It will complete filling for you to the end of the adjacent column on the left. You may try double-clicking with more than one column selected at a time. Double-click works only in one direction: down.

FIGURE 1.5 Double-Click the Drag Handle

You may want to try it yourself. As shown in Figure 1.6, all of the columns selected are highlighted and the crosshair handle appears at the lower right corner of the final column. In Figure 1.7, you can see the results after using the AutoFill double-click.

FIGURE 1.6 Highlighting More than One Column

FIGURE 1.7 AutoFill Results

More features of the AutoFill function are discussed in the context of regressions in Chapter 12. I also explain the concept of Time in Excel in Chapter 12.

Creating Custom Lists in Excel enables you to use these lists as demonstrated with the AutoFill function. Custom Lists let you use them when you sort in Excel. In addition to sorting in numerical or alphanumeric order, you can also sort with these Custom Lists or with the ones you create. You can use the list created here later to sort a database.

To create a Custom List in Excel, you need to type the list in a range on a sheet as shown in Figure 1.8. I used a list of the 10 largest cities in the United States where your company may be doing business as an example.

FIGURE 1.8 Custom List Example

Refer to Figure 1.2 for how to access the Custom Lists menu. In Excel 2010, go to the File icon to get to the Excel Advanced Options menu and the Custom Lists menu. For Excel 2007, go to the Office icon, click on Excel Options, and click on the Edit Custom Lists button. The result is shown in Figure 1.9. For Excel 2003 or the Mac 2011 version, see the Appendix to this chapter.

FIGURE 1.9 Create a Custom List

Now all you have to do to make the list of the 10 largest cities in the United States, sorted by population size, part of your Excel Custom List, is click on the empty cell on the menu to the left of the Import button and select the cells on the sheet. Click on the Import button and the list is now part of your Custom Lists. See Figure 1.10.

FIGURE 1.10 Custom List Results

AUTOFILL OPTIONS

When you complete dragging any of the AutoFill lists, you will see a small Options icon at the bottom right of the list. When you click on the icon, it will allow you to choose one of the options. See Figure 1.11.

FIGURE 1.11 AutoFill Options

The AutoFill option recognizes days and dates. When you click on the menu with a list of dates or days of the week, Excel provides you with the additional options of choosing days, weekdays only—without weekends—or even spacing the list out, incrementing the dates by months or years. See Figure 1.12.

FIGURE 1.12 AutoFill Menu

RIGHT-DRAG AUTOFILL

When you right-click and drag a numeric series, the menu offers you additional features as shown in Figure 1.13. The additional feature that could help us more than the others on the list is the Growth Trend feature. We can think of many other applications once we understand what it can do. The following are two examples that can illustrate the power of this element in Excel.

FIGURE 1.13 Choosing AutoFill Options

If you need to create an exponential list of the number 2, all you have to do is type the first two terms in the series (2 and 4) and the Growth Trend feature in the list will create the series as show in Figure 1.14. It will result in 2, 4, 8, 16, and so on. Using this idea, we can create a compounded interest series. If you want a growth factor of 10 percent a year, you can type 1.0 and 1.1 or 100 percent and 110 percent and the Growth Trend feature will do the rest as illustrated in Figure 1.14.

FIGURE 1.14 Exponential Trend Results

APPENDIX: AUTOFILL IN EXCEL 2003 AND EXCEL MAC 2011

Excel 2003

Custom Lists Menu: There are two examples shown in this chapter that are slightly different in Excel 2010 than in Excel 2003. Both examples deal with the procedure to access the Custom Lists menu in Excel 2010. In the 2010 version we used the File icon to get to the Excel Advanced Options menu and the Custom Lists menu.

In Excel 2003, you click on the Tools menu and then select Options. See Figure 1.15.

FIGURE 1.15 Options Menu in Excel 2003

The Options screen appears and you can select the Custom Lists tab. See Figure 1.16. All the other features of importing custom lists are the same as in Excel 2010—as described above.

FIGURE 1.16 Custom Lists Menu in Excel 2003

Mac Excel 2011

In the Mac version of Excel, you click on the Excel menu and then select Preferences. The Excel Preferences menu has the Customs Lists Option. See Figure 1.17.

FIGURE 1.17 Excel Preferences and the Customs List Option

All the other features for importing custom lists are the same as in Excel 2010—as described above. See Figure 1.18.

FIGURE 1.18 Custom Lists Menu in Excel 2011

REVIEW QUESTIONS

You will find these examples in the Excel Chapter 1 file:

1. The chapter problems sheet of Chapter 1 has the following data:

Use the AutoFill feature to extend the first column with the values 5 and 6 creating the list through 10 as shown here:

2. Use the double-click AutoFill feature to fill up the rest of the table resulting in a complete table.

3. Use your Excel Options menu to create a custom list of the 10 largest suspension bridges in the world:

ANSWERS

1. Select the two first figures, 1.5 and 1.6. Click on the grab handle and drag down until you see the value 10.

2. a. Select the rest of the table as you see in the figure.

b. Double-click on the grab handle.

3. Click on the Office icon. Select Excel Options at the bottom of the menu. Click on Edit Custom Lists in the middle of the menu. (In Excel 2003, use Tools ⇒ Options ⇒ Custom Lists.) On the resulting menu, select the range K10:K19 and click on Import.

Chapter 2

Selecting Efficiently in Excel

Dragging the mouse is probably not the most efficient way of selecting a range in Excel. If you select a small range, it may be more effective to hold the Shift key down and use the arrow keys to select the range. In many instances, you have to select large ranges of data. This chapter will describe a number of techniques to select this data in a more efficient way.

Selecting an Entire Sheet. To select an entire sheet, either click on an empty cell and use CTRL+A, or click on the small cell between column A and row 1. See Figure 2.1.

FIGURE 2.1 The Select All Button

Selecting a Section. To select any continuous data or section, click on the first cell of the section, then press Shift and click on the last cell of the section. You can also reverse the process, click on the last cell, then press Shift and click on the first cell of this section to select, keeping the Shift key pressed down. See Figure 2.2.

FIGURE 2.2 Selecting a Region without Dragging

Selection Shortcuts. To select the current region in Excel, click on any cell in the region and use CTRL+Shift+∗ (star/asterisk) ( +A in Mac). See Figure 2.3.

FIGURE 2.3 Use CTRL+Shift+∗ ( +A in Mac) for Selecting a Region

You can select an area from a currently highlighted cell or cells to the end of the data in that region’s column. Select the cell or cells and press CTRL+Shift+↓ (down arrow) ( +Shift+↓ for the Mac). See Figure 2.4.

FIGURE 2.4 CTRL+Shift+↓ ( +Shift+↓ for the Mac) to Select Down

Using the same concept, you can select an area from a starting cell (or cells) to the end of the data on that region’s row. You select the cell(s) and click CTRL+Shift+→ (right arrow). See Figure 2.5.

FIGURE 2.5 CTRL+Shift+→ ( +Shift+→ for the Mac) to Select to the Right

Any part of the region can be highlighted (selected) by first selecting a cell, a row, or a column in a region and then by using CTRL+Shift+←, CTRL+Shift+↑, or as shown above CTRL+Shift+ →, and CTRL+Shift+↓, to extend the selection to the end of that region in the direction we wish. CTRL+Shift+ any arrow key (↓↑→←) will enable selection from the starting cell(s) until the end of the data range in the direction of the arrow. Use instead of CTRL for the Mac.

With larger spreadsheets, you may want to go back to the beginning of the sheet or to the end of the data on the sheet. To go back to cell A1, press CTRL+Home; to reach the last cell of the sheet use CTRL+End.

Also, try CTRL+Shift+End to select the data from the current cell to the last active cell on the sheet. To go to the first cell of a row, press the Home key.

Extend and Add mode: After you select a starting cell, you can press F8 to enable the Extend mode (indicated on the right of the status bar by the letters EXT in Excel 2003 and Mac Excel 2011 or Extend Selection in Excel 2007 and 2010). Now you can use the arrows and select from that point on by repeatedly pressing on the arrow keys in the direction you want to select. See Figure 2.6.

FIGURE 2.6 Extend Mode

If you wish to select noncontinuous regions, using the Add mode is much more efficient than holding the CTRL key while dragging the mouse. The Add mode allows selecting desired blocks of ranges without holding down the CTRL key. If you press SHIFT+F8 simultaneously, the status bar will indicate ADD. You can then use the mouse and select additional regions. See Figure 2.7. The Add mode in Excel 2003 or previous versions of Excel is indicated on the right side of the bottom of the screen.

FIGURE 2.7 Add Mode Reads: Add to Selection

REVIEW QUESTIONS

You will find these examples in the Excel Chapter 2 file:

1. On Sheet 3 of the workbook Chapter 2, select the entire database using a shortcut:

2. On the same sheet select C2:D2. Use a shortcut to select the two columns:

3. Start in cell G16. Use a shortcut to select the region C10:G16.

ANSWERS

1. Click anywhere in the region and use CTRL+Shift+∗ or CTRL+Shift+F8.

2. Select C2:D2. Use CTRL+Shift+↓.

3. Use Shift+click on C10.

Chapter 3

Formulas, Functions, and Relative and Absolute Addressing

This chapter explains formulas, functions, and the addressing features or issues in Excel. I will show simple and advanced ways to create formulas and functions more efficiently. The focus is on speed, accuracy, and the ability to duplicate and repeat the functions and the formulas—saving valuable time and effort.

In Figure 3.1, you can see a simple payroll example that you can find in the Excel workbook for Chapter 3. The sheet is named Simple Payroll Before. To calculate the salary, Hours worked × Rate in cell E3: Type the = (equal) sign first; then click on cell C3, type ∗, and click on cell D3. When you press the Enter key, the formula will calculate the result. Note how the cursor will skip down to cell E4. Once you are in cell E4 and you want to duplicate the result for all other employees, you have to select E3 again. This is an extra step. In order to save this additional step you can do one of two things: after entering the formula, rather than pressing the Enter key, either you click on the green check sign √ (to the left of the formula bar) or use CTRL+Enter. The cursor will remain in the selected cell. See Figure 3.1.

FIGURE 3.1 Use CTRL+Enter to Save a Step

When the operation is completed in cell E2, we are ready to copy/drag down the formula to the rest of the cells in column E—all the way to E8—the last cell that has data/information in the adjacent column D. We do not copy and paste, and though you can drag down, it is not the most efficient way. (Imagine if you had 2,000 employees!) Point to the drag handle, on the lower right corner of the cell, and when the mouse pointer takes the shape of a crosshair (+), double-click and the formula will be transferred/copied to the rest on the cells in column E. Figure 3.2 shows the results.

FIGURE 3.2 Double-Click to Copy Down All the Result

RELATIVE AND ABSOLUTE ADDRESSING

You will notice that copying or dragging a formula does not copy the values down. You copied the formula only. Figure 3.3 shows all the formulas on the sheet. To reveal the formulas on a sheet choose CTRL+′ (CTRL+accent mark) or CTRL+∼ (tilde). Notice that the formula reads =C3∗D3 in row 3, =C4∗D4 in row 4, =C5∗D5 in row 5, and so on for all the other rows. This is called "Relative Addressing." The address is relative to the position of