You may wish to apply formatting to your worksheet "as you go", or you can apply formatting after you have entered data and formulas. Using formatting, you can highlight column and/or row headings and add symbols such as "$" signs to your output. You can specify the number of decimal places that the worksheet can display, and whether or not to include commas in the output. You can also apply a filter to your data, allowing you to focus on a specific part of the worksheet.

In Excel, you can apply pre-designed "Auto-Formatting" to all or part of the worksheet, or you can customize the formatting to suit your needs exactly.

Choosing a Format Style

AutoFormat allows you to select a layout style from a selection of pre-designed formats. The formats range from sophisticated to calendar-style layouts. The following exercise can be used to select an autoformat style to apply to your work:

Exercise: Applying an AutoFormat Style to Your Worksheet Click on the Format pull-down menu. Select AutoFormat. Click on the AutoFormat style that you want to apply. Click on Ok. Before moving on, click on the "undo" button in the toolbar. . This useful button can be used at any time to "undo" an action.

Designing Your Own Format

You may wish to apply certain formatting options to all or a portion of your data without using the "AutoFormat" option.

Adding Columns and Rows

Exercise: Inserting a Column or Row In your practice worksheet, place your cursor in the column heading B and left click. The entire column will be highlighted. Click on the Insert menu. Select Columns. A new column "B" will be inserted, and the old column "B" will now be Column "C". Now move to Row 15 and click on the Row Heading. The entire row should be highlighted. Right click and select Insert=>Rows. A new row 15 will be inserted, and the old Row 15 will now be Row 16.

Copying a Formula to An Entire Row or Column

Now that we have a blank row and column in our practice worksheet, we need to enter some additional data.

Exercise: Copying a Formula to an Entire Row or Column In your practice worksheet, enter "Monthly" in cell B2 (without the quotes). Highlight cell B3 and type in the formula "=(C3/12)" without the quotes. You should get the value 41666.67. Copy the formula from cell B3 to cells B4,B5,B6 and B7. To do this, select Cell B3. Right click and select Copy, or select Edit then Copy from the menu bar. Next, go to cell B4, and left click and hold the left mouse button down while you drag the mouse down through cell B7. Release the mouse button. Right click and select Paste (or select Edit then Paste from the menu bar). Now highlight cells B9 - B18. Right click and select Paste. Now take a look at the formula in each cell in column "B" by highlighting the cell and looking in the formula bar. Notice that the cell references change as you move down the column. Cell references that change when they are copied to new locations are called relative addresses. In most cases, this is property is desired, as it makes applying data manipulation to a large amount of data easy without having to type in many different cell addresses. Later, we will learn when and how to use an absolute cell address, one that doesn't change when it is copied to a new location. Now let's add data to the new row. In Cell A15, type in Long Distance Telephone. Move to Cell C15 and type in 5000. Since you have already copied a formula to cell B15, you should see a new value in that cell representing monthly long distance telephone expenses. Now, let's edit the income data for State Funds. Highlight cell C3. 500000 appears in the formula bar. Click on the "=". The cursor will begin flashing to the right of the number 500000 in the formula bar. Type "+ 5000" (without the quotes) and press Enter. This will add 5000 to the existing amount in the cell. The amount should now read 505000.

Deleting a Row or Column

To delete a row or column, move the cursor into the row or column heading and left click to highlight the row or column. Then right click and select Delete. You can also choose Delete from the Edit pull-down menu.

Centering a Category Title That Spans Several Columns

If you have an "umbrella" heading that includes several items on rows below it, you may want to center the heading so that it can be seen over several rows. This is called "merging cells". The exercise below shows you how this is done:

Exercise: Centering a Heading Over Several Cells Move the cursor to the row that contains the heading and highlight the the cells above the subcategories. In your practice sheet, move to Row 1 and highlight cells A1,B1, and C1. Click on the Merge and Center button on the toolbar. OR Right click and choose Format Cells . . . , then left click on the Alignment tab and place a check in the Merge Cells box under Text Control. (Left click to place a check mark in the box.

Applying Special Type Styles to Cell Content

It's easy to apply boldfacing, italics, or underline styles to text or numbers in cells. Simply highlight the cell or cells, then click on the Bold, Italics, or Underline buttons in the tool bar. As always, these formatting options can also be accessed by right clicking after you have highlighted the cells or by selecting Edit to see the pull-down editing menus.

Exercise: Formatting Text Select Cell A1 and click on the Bold button on the toolbar. Move to Cell C2 and type in "Annual" (without the quotes). Select cells A2, B2, C2, A8 and A18 by holding down the Ctrl key and highlighting the cells. Click on the Bold button. Move to Row 2 and highlight the row by clicking on the row header. Right click and select Insert. Repeat for new Row 9 and new Row 20.

Formatting Numerical Output

You can determine how numerical data will appear in your spreadsheet by using a range of formatting options. You can choose whether or not to display a comma and/or a dollar sign, whether or not negative amounts are designated by parentheses or by a negative sign, and how many decimal places are displayed. You can also select how a date or time will appear in the spreadsheet.

The next exercise demonstrates the application of formatting to cells that contain numbers. We will add commas to the cells containing numbers and dollar signs in the cells containing totals. We could format a whole row with $ signs, but that often makes a spreadsheet too busy for someone to read.

Exercise: Applying Commas, Decimal Points and Dollar Signs Format cells B4 through C19 with commas by highlighting the cells then selecting the comma box on the tool bar. Notice that when you apply comma formatting, Excel automatically assumes you desire Accounting formatting and displays two decimal places. To adjust decimal places, highlight the cells you wish to change, then select the -0.00 or the the +0.00 box until the cells are properly formatted. Set the decimals in this worksheet to two decimal places if they are not already set. Highlight cells B8,C8, B19, C19, C21, and C21 by holding down the Ctrl key and selecting the cells. Go to the tool bar and select the box with the $ sign on it.

Not all text and/or number formatting options appear on the tool bar. For access to all of the formatting options, highlight the cell(s) you wish to format, then right click and select Format Cells . . . . A dialog box with a number of tabs (Number, Alignment, Font, Borders, Patterns, Protection) will appear. Left click on the appropriate tab to display the formatting options available.

Adjusting Column and/or Row Width

Notice that when you added some formatting to the cells with numbers in them, several of our cells returned a ##### in them. This is because our cell width is no longer wide enough to hold the new figure. Let's increase the cell width.

The easiest way to increase cell width is to move your cursor to the column heading and place it on the border between two cells. Notice that the cursor changes to a crosshair. Hold down your left mouse button and drag the column edge to increase or decrease the column width. You can also right click while the crosshair is present, and a pull-down menu with column adjustment options will appear.

Another way to adjust a column is to use the AutoWidth option. To automatically adjust a column to accomodate the width of any cell in the column, select the column that you want to adjust, then click on the Format menu. Select Column, then select AutoFit.

Applying a Filter To The Data

Exercise: Filtering Data Highlight cell A3. Click on the Data pull-down menu. Select Filter => Auto Filter. Release the mouse button. You will notice a series of drop-down menu areas appear at the head of the column that contains data you have entered. The drop down menu reflects all the data in each cell of the column. With your left mouse button, select an item on the drop down menu that you want to focus on. Once you have selected a certain item, all other data will be temporarily hidden from view until you select all items again. To deselect the Filter option, click on Data => Filter and uncheck the Auto Filter option.

One of the ways that spreadsheets are used is to create "what if" scenarios. These scenarios are created by changing one or more of the formulas or data items in the spreadsheet. The spreadsheet will then automatically update all cells that contain data resulting from a formula which depends upon the data that has been changed. Let's look at an example:

Demonstration: Effects of Data Changes in a Spreadsheet Select Cell C4 (annual amount for State Funds) and change the data item to 710000. Select Cell C11 (annual amount for Faculty Salaries) and change the data item to 500700. Note the changes in the Monies To be Returned row, as well as changes in Income and Expenses.

Don't forget to save your work!