PRDV006: Spreadsheets II: Formatting and Functions

Let's review the use of logical functions in Excel through the construction of an investment portfolio. Although it may seem that managing investments is a specialized career choice, the reality is that almost everyone will become an investor at some point in their lives. Many companies offer employees retirement savings benefits through 401(k) or 403(b) plans. These plans allow you to deduct money from your paycheck every month, tax-free, and invest it. In addition to the tax benefits afforded by such plans, many employers match a percentage of your monthly savings or deposit money into your retirement account as an added form of compensation. When you sign up for these savings plans, your company will give you a list of options as to how your money can be invested, and you choose the type of investments you would like the company to make on your behalf. As a result of this process, you become an investor. Excel can be an extremely valuable tool to help you make these investment decisions and analyze the performance of the money you have invested.

Figure 1 Completed Personal Investment Portfolio Workbook shows the completed investment portfolio workbook that we will complete in this chapter. The Portfolio Summary worksheet contains a summary of the data entered or calculated in other worksheets in the workbook. This project begins by building on the Investment Detail worksheet.

Figure 1 Completed Personal Investment Portfolio Workbook

Freeze Panes

The Investment Detail worksheet shown in Figure 2 Investment Detail Worksheet contains the majority of the information used to create the Portfolio Summary worksheet shown in Figure 1 Completed Personal Investment Portfolio Workbook. When you first open the worksheet, you will notice it is not possible to view all twenty-four columns on your computer screen. As you scroll to the right to view the rest of the columns, you will lose site of the row headings in Columns A and B. The headings in these columns show the investment that pertains to the data in Columns C through X. To solve this problem of viewing the row headings while scrolling through the remaining columns in the worksheet, we will use the Freeze Panes command.

Figure 2 Investment Detail Worksheet

The Freeze Panes command allows you to scroll across the Investment Detail worksheet while keeping the row headings in Columns A and B locked in place. The following steps explain how to do this:

1. Click cell C4 on the Investment Detail worksheet. We select this cell because the Freeze Panes option locks the columns to the left of the activated cell as well as the rows above the activated cell.
2. Click the View tab on the Ribbon.
3. Click the Freeze Panes button (see Figure 3 Freeze Panes Command).
4. Click the Freeze Panes option from the drop-down list of options.
   
   

Figure 3 Freeze Panes Command

Once you click the Freeze Panes option shown in Figure 3 Freeze Panes Command, Columns A and B are locked in place as you scroll through the columns in the worksheet. Since this is a large worksheet, you may find it easier to navigate the columns by using the arrow keys on your keyboard. However, since rows 1 and 2 contain merged cells, make sure a cell location is activated below Row 2 before you begin using the arrow keys. Figure 4 Freeze Panes Command Activated on the Investment Detail Worksheet shows the appearance of the Investment Detail worksheet after the Freeze Panes command has been activated. To deactivate the Freeze Panes command, click the Freeze Panes button again and select the Unfreeze Panes option.

Figure 4 Freeze Panes Command Activated on the Investment Detail Worksheet

This text was adapted by Saylor Academy under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.