15. Excel 2013 Basics

Excel is a full-featured spreadsheet application that’s part of Microsoft Office 2013. With Excel, you can perform calculations, create charts, and analyze numerical data. An Excel file is called a workbook and each workbook can contain one or more sheets.

After exploring the Excel interface and its unique terminology, this chapter shows you how to create workbooks from scratch or from templates. It also shows you how to add, delete, and move sheets within a workbook or between workbooks.

Even a well-planned sheet layout might be missing something, such as a date column. Or you might change your mind in the middle of the design, deciding that you want a table elsewhere on a sheet. Instead of starting over, you learn how to insert rows or columns and move your tables to a new location on your sheet.

The big grid taking up most of the Excel window is the spreadsheet, also known as a worksheet or, simply, a sheet. Each little box is a cell. Multiple cells selected together are commonly known as a range.

Right above the grid are letters known as column headers. Down the left side of the grid are numbers, also known as row headers. The intersection of a single column and a single row is a cell. Each cell has an address made up of the column letter and the row number. Figure 15.3 shows cell C8 selected. Note the C in the column header is highlighted as is the 8 in the row header.

Above the headers and to the left is the Name box. This box shows you what you have selected on the sheet. This can be a cell address, a named range, a table, a chart, or some other object on the sheet. When you first open Excel, the Name box likely shows A1. If you select another cell on the sheet, the address changes to show that cell’s address, such as C8. Later in this chapter, you find out how you can use the Name box to quickly move to another area of the sheet.

To the right of the Name box is the formula bar, which is a slight misnomer. Although that is probably the most common use of this field, the formula bar actually reflects anything that’s been typed into a cell, not just formulas.

At the top of the Excel window, you find the Ribbon and the Quick Access Toolbar, which is covered in Chapter 1, “Getting Started with Microsoft Office 2013.”

Below and to the right of the grid are scrollbars, which you can use to move around the sheet. You can either click and drag a bar or use the arrows at either end of the scroll area.

The status bar is along the bottom of the Excel window. Not only does it show the status of Excel, such as Ready or Calculating, on the left side, but it also includes buttons for changing the page view or zoom. In the right corner of the status bar is the Zoom slider. You can use the slider or the – and + buttons to change the zoom of the active sheet.

To the left of the Zoom slider are three buttons for changing how you view the active sheet:

• Normal—This is the default view, showing just the sheet.

• Page Break Preview—Displays where columns and rows break to print onto other pages. Dashed lines signify automatic breaks that Excel places based on settings, such as margins. Solid lines are manually set breaks. See Chapter 22, “Preparing Workbooks for Distribution and Printing,” for more information on setting and moving page breaks.

• Page Layout—This view is similar to an editable Print Preview—you can see what your page looks like when it prints out, but you can still enter data and make other changes. Columns and rows move between pages as you adjust their widths or the margins of the page. You can also enter information directly into the header and footer of a page. See Chapter 22 for information on adding headers and footers.

• Ctrl+Home jumps to cell A1, located at the upper-left corner of the sheet.

• Ctrl+End jumps to the last row and column in use.

• Ctrl+Left Arrow jumps to the first column with data to the left of the currently selected cell. If there is no data to the left then it selects a cell in the first column, A.

• Ctrl+Right Arrow jumps to the first column with data to the right of the currently selected cell. If there is no data to the right then it selects a cell in the last column, XFD.

• Ctrl+Down Arrow jumps to the first row with data below the currently selected cell. If there is no data below the selected cell then it selects a cell in the last row.

• Ctrl+Up Arrow jumps to the first row with data above the currently selected cell. If there is no data above the selected cell then it selects a cell in row 1.

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

1. Click in a cell in the corner of your selection.

2. Hold down the mouse button as you drag the mouse to cover the desired cells.

3. When you get to the last cell, let go of the mouse button. Excel selects a range similar to what is shown in Figure 15.4. As long as you don’t click elsewhere on the sheet, the range remains selected.

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

1. Click in a cell in the corner of your selection.

2. Press F8 on the keyboard.

3. Use the arrow keys to navigate to the end of the desired range.

4. Press F8 on the keyboard to stop the selection from extending. As long as you don’t select another cell on the sheet, the range remains selected.

When you select a template, a preview window opens, as shown in Figure 15.5. The preview shows a larger example of the template, a brief explanation of its purpose, and how the template has been rated by other users. If you click Create, the template is downloaded (for first-time use) and opened.

Before you can use your own template or one provided to you and not downloaded from the Office Store, you have to configure where to store your templates. This is a folder where you place all the templates you want to access through the New screen. To set up the location, click the File tab, select Options, and select the Save tab on the Excel Options dialog box. Next, enter the path in the Default Personal Templates Location field. After you have a location configured, the Personal link appears to the right of the Featured link on the New screen. Featured shows Microsoft’s online templates; Personal shows templates in the locally configured location.

• Double-click the file from its saved location.

• From its saved location, right-click the file and select New.

• Click the File tab, select New, and select the template from the list of online templates.

• Click the File tab, select New, click the Personal link, and select from the list of templates saved locally. See the previous “Saving a Template” section for details on how to configure the location. The Personal option doesn’t appear unless you set up the location in the Excel Options dialog box.

Each sheet has a tab, visible above the status bar, as shown in Figure 15.6. The sheet with data that you’re looking at is considered the active sheet. To select another sheet, click its tab.

To insert a new sheet to the left of a specific sheet, select the sheet (refer to the “Activating Another Sheet” section), click the Insert button on the Home tab, and select Insert Sheet from the menu.

A third method is to right-click the tab to the right of where you want the new sheet to appear. From the menu, select Insert, select Worksheet, and click OK. The new sheet appears to the left of the tab you right-clicked, as shown in Figure 15.7.

If you have more sheets than can fit in the tab area, three small dots appear on the left and right side of the area, to show there are more sheets available to view, as shown in Figure 15.7. Clicking the three dots quickly scrolls through multiple sheets. If instead, you need to scroll one sheet at a time, click the left and right arrows located to the left of the sheet tab area.

If you right-click the left and right arrows instead, a list of all sheets in the workbook appears. You can then select a sheet, click OK, and go straight to that sheet.

To select multiple sheets, select the first one and then, while holding down the Ctrl key, select the tabs for the others. As you select each sheet tab, the sheet name becomes bold.

To ungroup the sheets, you can either select any sheet other than the active sheet or right-click any tab and select Ungroup Sheets.

In the Move or Copy dialog box, shown in Figure 15.8, make sure the To Book field is the current workbook. If you’re making a copy of the sheet, select the Create a Copy box. Then, in the Before Sheet dialog box, select the sheet you want the active sheet to be placed in front of (to the left of). You can also reorganize the sheets in a workbook by clicking, holding the mouse button down, and dragging the sheet’s tab to a new location. If you want to copy the sheet then hold down the Ctrl key as you move the sheet tab.

To move or copy a sheet to another workbook, the second workbook must already be open. Make sure the sheet you want to move or copy is the active sheet. Then, using the Move or Copy dialog box, shown in Figure 15.8, select from the To Book field the workbook to move or copy the sheet to. If you’re making a copy of the sheet, select the Create a Copy box. In the Before Sheet dialog box, select the sheet you want the active sheet to be placed in front of (to the left of).

To change the color of a tab, click the Format button on the Home tab, and select Tab Color from the menu. A selection of colors appears from which you can select the desired color. The tab color of the active sheet appears as a gradient but fills the entire tab for the other sheets. You can also change the tab color by right-clicking the tab and selecting Tab Color.

To select an entire row, place your cursor on the header until it turns into a single black arrow and then click the header for the row. For example, to select row 5, as shown in Figure 15.9, click the header 5. To select an entire column, click the header for the column.

If you need to select multiple, contiguous rows (or columns), click the first header and then, as you hold down the mouse button, drag the mouse down (or up) until you have selected all the rows you need. Let go of the mouse button and perform your desired action on the selection.

If you need to select multiple, noncontiguous rows (or columns), click the first header and then, as you hold down the Ctrl key, carefully click the headers of the other rows. When you’re done selecting, release the Ctrl key and perform your desired action on the selection.

To insert a new row or column within an existing data set, select a cell in the row or column where you want the inserted row or column to go. For example, if you need to insert a new row 10, select a cell in row 10. On the Home tab, click the Insert button and select Insert Sheet Rows or Insert Sheet Columns. For a shortcut, you can right-click the row or column header and select Insert.

To delete a row or column, select a cell in the row or column to delete, click the Delete button on the Home tab, and select Delete Sheet Rows or Delete Sheet Columns. For a shortcut, you can right-click the row or column header and select Delete.

1. Select the row or column you want to move.

2. The selection is surrounded by a thick line. Place your cursor over the line until it turns into a four-headed arrow, as shown in Figure 15.10.

3. Click on the line and hold the mouse button down.

4. Drag the selection to a new location and let go.

5. If there is any data in the new location, Excel asks if you want to overwrite it. If you say No, the move is canceled. The original row or column is still there, but it’s empty.

If you need to keep data in the new location intact, you could insert a row or column in the new location, copy the data to be moved, paste it in the inserted rows or columns and then go back to the original location, select it again, and delete it. Or, you could get it done more quickly by selecting and cutting the row or column, right-clicking the new location, and selecting Insert Cut Cells. Excel moves the data in the new location, inserting the cut data and also deleting the data in the old location.

To perform these actions using the Ribbon, after selecting and cutting the row or column, select a cell in the new location, click the Insert button on the Home tab, and select Insert Cut Cells from the menu.

You can also jump to a cell by typing the cell address in the Name box, located above the sheet in the left corner. This is a great way of traveling quickly to a specific location on a large sheet.

To use this method, you must enter the cell address properly—that is, the column and then the row. For example, if you want to jump to cell AA33, you must type in AA33; you can’t type in 33AA. After typing the address, press Enter and the currently selected cell moves to cell AA33.

You don’t always want to select cells right next to each other. For example, you might want to select only the cells with negative values and make them bold. You could select each cell one at a time and apply the bold format. Or, you could hold down the Ctrl key and select each cell. After you have selected all the cells, you let go of the Ctrl key and select your desired format (or other action).

To delete a range from a sheet, select the range and on the Home tab, click the Delete button and select Delete Cells from the menu. From the Delete dialog box that opens, choose whether you want to Shift Cells Left or Shift Cells Up. You can also open the dialog box by right-clicking the selected range and choosing Delete from the menu.

1. Select the range you want to move.

2. The selection is surrounded by a thick line. Place your cursor over the line until it turns into a four-headed arrow, as shown in Figure 15.13.

3. Select the line and hold the mouse button down.

4. Drag the selection to a new location and let go.

5. If there is any data in the new location, Excel asks if you want to overwrite it. If you select No, Excel cancels the move. Select Yes to complete the move and clear the contents of the original range.