Office 2016 For Seniors For Dummies (2016)

Part III

Excel

For an explanation of Excel's IF function, visit www.dummies.com/extras/office2016forseniors.

Chapter 7

Creating Basic Spreadsheets in Excel

Get ready to . . .

Understand Excel’s Unique Features

Get Familiar with Spreadsheet Structure

Move the Cell Cursor

Select a Range

Type and Edit Cell Contents

Insert and Delete Rows, Columns, and Cells

Work with Worksheets

Microsoft Word works great for typing text, but sometimes you want something a little more structured for data. When you have more complex needs for column-based organizing than what Word’s tables can provide, Excel is a great step up to the next level. Excel is much more than just a column organizer, though; it enables you to write formulas that perform calculations on your data. This makes Excel an ideal tool for storing financial information, such as checkbook register and investment portfolio data.

In this chapter, I introduce you to the Excel interface and show you some of the concepts you need to know to work with a spreadsheet. Some of the projects that Excel works great for include

· Tracking a stock portfolio

· Recording checkbook transactions

· Storing an address book list

· Charting progress toward a goal

Using the skills this chapter covers, you can build all these types of spreadsheets and more.

Understand Excel’s Unique Features

Excel shares many elements with the Microsoft Office interface. Figure 7-1 and the following list, however, show you the features that are unique to the Excel program. (If you need a refresher on the Microsoft Office interface in general, including features such as the Ribbon, Quick Access toolbar, and File tab, take a few minutes to review Chapter 1.)

Figure 7-1

As you examine Figure 7-1, you’ll see

· Row numbers: Each row has a unique number. To select an entire row, click its number.

· Column letters: Each column has a unique letter. To select an entire column, select its letter.

· Cells: At the intersection of each row and column is a cell. You enter content into a cell by clicking the cell and then typing.

· Active cell indicator (a.k.a. the cell cursor): This thick outline indicates which cell is the active cell — where your cursor is located at the moment. Whatever you type next appears in the active cell, and whatever commands you issue apply to that cell.

· Name box: The active cell’s name appears here. For example, if the selected cell is at the intersection of column A and row 1, A1 appears in the Name box.

· Formula bar: The contents of the selected cell appears here. If the content is text, that text shows here as in the cell itself. If the content is a formula, the formula appears here, and the result of the formula appears in the cell. For example, if the cell contains the formula =2+1, the formula bar shows =2+1 and the cell itself shows 3.

A formula is a math expression, beginning with an equals sign, such as =2+1. Formulas can also contain cell references. For example, =A2+B2 adds the amount found in cell A2 to the amount found in B2.

· Worksheet tabs: By default, each workbook file has three sheets. They’re like pages in a notebook. Each sheet is shown as a tab; click a tab to switch to that sheet.

Sheets are also called worksheets. A workbook is the entire data file, containing one or more worksheets.

Get Familiar with Spreadsheet Structure

Each cell has a unique cell address consisting of its column letter and row number. In Figure 7-1, for example, the active cell is at the intersection of column A and row 1. So, its cell address is A1. See the Name box in Figure 7-1. Each cell must have its own unique address so that you can refer to it when you create a formula. For example, suppose you want to sum (add) the values in cells B3 and B4. You can write a formula like this: =B3+B4.

When you copy a formula that refers to cells by their addresses, the references to those cells automatically adjust to take into account the new position. For example, suppose you have the formula =A1+1 in cell A2. If you copy that formula into cell B2, the copy of the formula reads=B1+1. That’s called a relative reference. In Chapter 8 in the section “Move and Copy Cell Content,” you will learn about absolute references, which don't change when copied.

A range is a group of one or more cells. You refer to a range by the address of the upper leftmost cell in the range, followed by a colon, and then followed by the lower rightmost cell in the range. For example, the range consisting of cells A1, A2, B1, and B2 is written as A1:B2.

Technically, a single cell can be a range, but usually it isn’t called a range.

Move the Cell Cursor

As I show you in Figure 7-1 earlier in the chapter, the cell cursor (also called the active cell indicator) is the dark outline around the active cell. To change which cell is active, you can do either of the following:

· With your mouse: Click the cell you want to be active.

· From the keyboard: Press one of the arrow keys on the keyboard to move the cell cursor one cell at a time in the direction the arrow is pointing until you reach the cell you want to be active.

Table 7-1 provides more shortcuts for moving the cell cursor within a worksheet.

Table 7-1 Movement Shortcuts

Select a Range

You might sometimes want to select a multi-cell range before you issue a command. For example, if you want to make all the text in the cell range A1:F1 bold, select that range and then issue the command for applying Bold (by clicking the B button on the Home tab or by pressing Ctrl+B). You can select a range by using either the keyboard or the mouse.

To select a range by using the mouse, follow these steps:

1. Click the upper leftmost cell in the range.

2. Click and hold down the left mouse button and drag to the lower rightmost cell in the range.

To select a range by using the keyboard, follow these steps:

1. Use the arrow keys to move the cell cursor to the upper leftmost cell in the range.

2. Press and hold down the Shift key while you press the → or ↓ keys to extend the selection until all the cells in the range are selected.

3. Release the Shift key.

You can also select a range in reverse order: that is, starting with the lower rightmost cell and dragging upward and to the left or pressing the ← or ↑ keys.

You can also use a hybrid method involving both keyboard and mouse:

1. Select the first cell in the range using any method: either clicking it with the mouse or using the arrow keys to move the cell selector to it.

2. Hold down the Shift key and click the last cell in the range.

3. Release the Shift key.

Table 7-2 provides some shortcut keys to help you select ranges.

Table 7-2 Range Selection Shortcuts

Although less common, you can also select a noncontiguous range (cells that don’t border each other). For example, suppose you want to apply a certain type of formatting to cells A1, A5, and A9. Select those three cells before issuing your formatting command, so that the formatting is applied to the three discrete cells at once:

1. Select the first cell in the range.

2. Hold down the Ctrl key and click more individual cells to be in the range. Or click and drag across more multi-cell blocks to be in the range. The additional selected cells appear highlighted.

3. Release the Ctrl key.

An entire row or column can also be a range. Here’s how to select a row or column (for example, to delete it, or to format it a certain way):

· To select an entire row: Click its row number to the left of the row.

· To select an entire column: Click its column letter at the top of the column.

· To select multiple contiguous rows or columns: Select the first row/column and then click and hold down the left mouse button while you drag to include more rows or columns.

· To select multiple noncontiguous rows or columns: Select the first row/column and then hold down Ctrl while you click more individual row numbers or column letters. A pale highlight is applied to each row or column you select.

Sometimes, you might want to apply a particular command to every cell on the entire worksheet. For example, you might want all the text in the entire worksheet to be a certain color or font. To select all cells at once, you can drag to create a range that encompasses the entire sheet; but that’s somewhat cumbersome. Here are a couple of easier alternatives:

· Press Ctrl+A.

· Click the Select All button, which you can find in the upper-left corner of the sheet at the intersection of the row numbers and column letters. (It’s pointed out in see Figure 7-2.)

Figure 7-2

Type and Edit Cell Contents

To this point in the chapter, I’ve introduced you to some spreadsheet basics. Now, it’s time to actually do something: enter some text and numbers into cells.

To type in a cell, you simply select the cell and begin typing. Whatever you type appears both in the cell and in the formula bar. (Refer to Figure 7-1 to see where the formula bar is located.) When you finish typing, you can leave the cell in any of these ways:

· Press Enter (moves you to the next cell down).

· Press Tab (moves you to the next cell to the right).

· Press Shift+Tab (moves you to the next cell to the left).

· Press an arrow key (moves you in the direction of the arrow).

If you need to edit what’s in the cell, you have these choices:

· Click the cell to select it, and then click the cell again to move the insertion point into it. Edit like you would in Word or any text program.

· Click the cell to select it, and then type a new entry to replace the old one.

If you make a mistake when editing, you can press Esc (top left of your keyboard) to cancel the edit before you leave the cell. If you need to undo an edit after having left the cell, press Ctrl+Z or click the Undo button on the Quick Access toolbar.

Now try typing some text into your spreadsheet. I entered the text shown in Figure 7-3 to create a simple mortgage calculator. Feel free to enter your own information or type the same text I entered.

Figure 7-3

If you decide you don’t want the text you typed in a particular cell, you can get rid of it in several different ways:

· Select the cell; then right-click the cell and choose Clear Contents from the menu that appears.

· Select the cell; then choose Home ⇒ Editing ⇒ Clear ⇒ Clear Contents.

· Select the cell, press the spacebar, and then press Enter. This technically doesn’t clear the cell’s content, but it replaces it with a single, invisible character — a space.

Don’t confuse Clear with Delete. They are two separate things. There is a Delete command on the Home tab (Home ⇒ Cells ⇒ Delete), but using it doesn’t clear the cell content; instead, it removes the entire cell. You find out more about deleting cells in the upcoming section, “Insert and Delete Rows, Columns, and Cells.”

And while I’m on the subject, don’t confuse Clear with Cut, either. The Cut command works in conjunction with the Clipboard. It moves the content to the Clipboard, from which you can then paste it somewhere else. In Excel, though (unlike in other applications), using the Cut command doesn’t immediately remove the content. Instead, it puts a flashing dotted box around the content and waits for you to reposition the cell cursor and issue the Paste command. If you do something else in the interim, the cut-and-paste operation is cancelled, and the content that you cut remains in its original location.

You can also clear only the formatting from a cell by clicking the Clear button on the Home tab and then choosing Clear Formats from its menu. Or, to clear both the content and the formatting, choose Clear All.

Try clearing some content from the text you entered in your spreadsheet. Select a cell and use any of the three methods from the preceding bullet list to remove the contents of the cell. From the text I typed earlier, I chose to clear the text from cell A14.

After you type some text in a worksheet, save your work! Don’t wait until you type a lot of text. Always save early in the process in case something bad happens — like a power outage — that causes your computer to shut down unexpectedly. Refer back to “Save Your Work” inChapter 3 for details.

Insert and Delete Rows, Columns, and Cells

Even if you’re a careful planner, you’ll likely decide that you want to change your spreadsheet’s layout. Maybe you want data in a different column, or certain rows turn out to be unnecessary. Excel makes it easy to insert and delete rows and columns to deal with these kinds of changes.

Instead of inserting rows and columns, you can move the content between rows or columns. In Chapter 8, read how to move content in a worksheet.

To insert a row or column, follow these steps:

1. Select the row above or the column to the right of where the insertion should occur. Or click in any cell in that row or column.

2. On the Home tab, and click the arrow to the right of the Insert button in the Cells group to open the drop-down list for the Insert button. See Figure 7-4.

3. From the menu, choose Insert Sheet Rows or Insert Sheet Columns.

Figure 7-4

Here’s another way to insert: from the right-click (contextual) menu:

1. Select a row or column adjacent to where the insertion should occur.

2. Right-click the selection and choose Insert from the menu that appears. Excel inserts either a row or a column, whichever you select in Step 1.

To insert multiple rows or columns at once, select a corresponding number of contiguous rows or columns in Step 1 in the preceding step list. For example, if you select three columns, you get three new blank columns when you choose Insert.

Deleting a row or column works in a similar way:

1. Select the row(s) or column(s) you want to delete.

2. Choose Home ⇒ Cells ⇒ Delete.

The Delete button has a drop-down list, just like the Insert button does. From it, you can choose what you wanted to delete: cells, rows, or columns. However, in Step 1, you select what you want to delete, so that isn’t necessary in this case; you could simply click the button to delete what was selected.

Deleting a cell is different from clearing a cell’s content! When you clear only the content, the cell itself remains. When you delete the cell itself, the cells to the right or below shift to the left or up to fill in the gap.

Here’s a way to envision what deletion does. Imagine that each cell is a cardboard box, stacked one on top of another in a grid. Each box opens to the front, so you can put things in it and take things out of it. When you delete a cell, you pull the box out of the stack. Gravity dictates that the boxes on top of the space fall down to fill it in. However, in Excel, gravity is backward: The cells fall up (or to the right if you specify that).

Excel is smart enough that it tries to guess which direction you want existing content to move when you delete cells. If you have content immediately to the right of a deleted cell, for example, it shifts it left. If you have content immediately below the deleted cell, it shifts it up. You can still override that, though, when needed.

To delete individual cells, follow these steps:

1. Select the cell(s) to delete.

2. Choose Home ⇒ Cells ⇒ Delete. Excel attempts to guess how you want the adjacent cells shifted and then performs that operation.

If Excel didn’t shift the other cells in the direction you expected, try again using this slightly longer, but more controllable, alternative procedure:

1. Select the cell(s) to delete.

2. On the Home tab, click the arrow on the Delete button in the Cells group to open its menu.

3. Click Delete Cells. A Delete dialog box opens, as shown in Figure 7-5.

4. Select the radio button for the shift you want: Shift Cells Left or Shift Cells Up.

5. Click OK.

Figure 7-5

Inserting individual cells works in much the same way. You can choose from an easy method that guesses how you want the shift of adjacent content to occur, or a more controllable method where you get to specify how.

Here’s the easy method for cell insertion:

1. Select the cell(s) above or to the left of where you want them to appear.

2. On the Home tab, click Insert. Excel attempts to guess how you want the adjacent content shifted and performs that action.

This method gives you direct control:

1. Select the cell(s) above or to the left of where you want them to appear.

2. On the Home tab, click the arrow on the Insert button in the Cells group to open its menu.

3. Click Insert Cells. An Insert dialog box appears.

4. Select the radio button for the shift you want: Shift Cells Right or Shift Cells Down.

5. Click OK.

Here’s a shortcut for opening the Insert or the Delete dialog box. Select the cells, right-click the selection, and then choose Insert or Delete from the right-click menu.

Try deleting some individual cells from your spreadsheet. In the Mortgage file I created earlier, I deleted cells A4:A6 and A9:A13. My spreadsheet now looks like Figure 7-6.

Figure 7-6

Work with Worksheets

Each workbook contains one sheet by default: Sheet1. Sometimes, however, you may want to add more sheets to your workbook. Sheets are like pages. They can come in very handy for creating alternative versions of the same spreadsheet, or for storing different types of information in the same workbook file. For example, you could have separate sheets for your holdings at each investment firm, or separate sheets for the membership rosters of several different clubs.

To create a new sheet, click the Insert Worksheet button shown in Figure 7-7, or press Shift+F11. The resulting blank sheet uses the default settings (formatting, column width, and so on).

Figure 7-7

When there are multiple sheets present, you can switch from one sheet to another, click the sheet’s tab at the bottom of the Excel window.

With a normal Excel window size you should have no trouble seeing several tabs at once. However, if you add a lot of sheets to the workbook or rename the tabs so that some of them have very long names, and/or if you resize your Excel window so it’s fairly narrow, not all the tabs may be visible at once. When this happens, you can use the tab scroll arrows to the left of the tabs to scroll the tabs to the left and right.

To delete a sheet, right-click its tab and choose Delete from the right-click menu that appears. If that sheet has no data, it’s deleted immediately. If the sheet does contain data, you’re prompted to confirm the deletion.

Make sure you really want to delete a sheet because this is an action you cannot undo with the Undo (Ctrl+Z) command.

Give it a whirl. In your spreadsheet, create a couple of new worksheets, and then delete them.

The default names for the sheets aren’t particularly helpful in terms of describing what they contain. In a workbook that uses multiple sheets, you will probably want to rename each sheet’s tab so that it’s meaningful to you.

To rename a sheet, follow these steps:

1. Double-click the sheet’s tab. The current name is selected.

2. Type a new name.

3. Press Enter.

If you want, rename the Sheet1 tab in your spreadsheet to something that’s easy to remember. For example, I renamed Sheet1 in my Mortgage spreadsheet to Mortgage.

You can use up to 255 characters for a sheet name, including most punctuation, but think twice before you use long, fancy names. When creating references to cells on a sheet, you must include the sheet’s name in the formula, and long names can be cumbersome to type. In addition, names that include spaces and punctuation can sometimes require special handling to avoid tripping up certain Excel features. Best bet: Stick with single-word sheet names with no punctuation.

Here’s a formatting trick to keep your work organized. Each worksheet tab can have a different color, or you can color several tabs all the same to indicate that they’re somehow related. The coloring is all up to you!

To assign a color to a sheet tab, follow these steps:

1. Right-click the tab and hover your mouse over Tab Color on the contextual menu.

2. In the Theme Colors palette that appears, click the desired color. (See Figure 7-8.)

Figure 7-8

When a colored tab is active (that is, when that particular sheet is displayed), the color appears slightly washed out in a soft gradient. But when you select a different tab, the inactive tab(s) appear in its full colorful glory. To remove the color, repeat the preceding steps and click No Color in Step 2.