In order to work with a cell—to enter data in it, edit or move it, or perform an action—you select the cell so it becomes the active cell. When you want to work with more than one cell at a time—to move or copy them, use them in a formula, or perform any group action—you must first select the cells as a range. A range can be contiguous (where selected cells are adjacent to each other) or non-contiguous (where the cells may be in different parts of the worksheet and are not adjacent to each other). As you select a range, you can see the range reference in the Name box. A range reference contains the cell address of the top-left cell in the range, a colon (:), and the cell address of the bottom-right cell in the range.
Click the first cell that you want to include in the range.
Drag the mouse to the last cell you want to include in the range.
Timesaver
Instead of dragging, hold down the Shift key, and then click the lower-right cell in the range.
When a range is selected, the top-left cell is surrounded by the cell pointer, while the additional cells are selected.
Click the first cell you want to include in the range.
Drag the mouse to the last contiguous cell, and then release the mouse button.
Press and hold
, and then click the next cell or drag the pointer over the next group of cells you want in the range.
To select more, repeat step 3 until all non-contiguous ranges are selected.
You can move around a worksheet using your mouse or the keyboard. You might find that using your mouse to move from cell to cell is most convenient, while using various keyboard combinations is easier for quickly covering large areas of a worksheet. Or, you might find that entering numbers on the keypad and pressing Return is a better method. Certain keys on the keyboard—Home, End, and Delete to name a few—are best used as shortcuts to navigate in the worksheet. However, there is no right way; whichever method feels the most comfortable is the one you should use.
Using the mouse, you can navigate to:
-
Another cell
-
Another part of the worksheet
-
Another worksheet
Did You Know?
Microsoft IntelliMouse users can roll from cell to cell with IntelliMouse. If you have the new Microsoft IntelliMouse—with the wheel button between the left and right buttons—you can click the wheel button and move the mouse in any direction to move quickly around the worksheet.
You can quickly zoom in or out using IntelliMouse. Instead of scrolling when you roll with the IntelliMouse, you can zoom in or out. To turn on this feature, click the Office button, click Excel Options, click Advanced, select the Zoom on roll with IntelliMouse check box, and then click OK.
Using the keyboard, you can navigate in a worksheet to:
-
Another cell
-
Another part of the worksheet
Refer to the table for keyboard shortcuts for navigating around a worksheet.
Keys For Navigating in a Worksheet
|
Press This Key |
To Move |
|---|---|
|
Left arrow |
One cell to the left |
|
Right arrow |
One cell to the right |
|
Up arrow |
One cell up |
|
Down arrow |
One cell down |
|
Enter |
One cell down |
|
Tab |
One cell to the right |
|
Shift+Tab |
One cell to the left |
|
Page Up |
One screen up |
|
Page Down |
One screen down |
|
End+arrow key |
In the direction of the arrow key to the next cell containing data or to the last empty cell in current row or column |
|
Home |
To column A in the current row |
|
Ctrl+Home |
To cell A1 |
|
Ctrl+End |
To the last cell in the worksheet containing data |
Click the Edit menu, and then click Go To.
Select a location or type a cell address to where you want to go.
To go to other locations (such as comments, blanks, last cell, objects, formulas, etc.), click Special, select an option, and then click OK.
Timesaver
To open the Special dialog box directly, click the Find & Select button, and then click Go To Special.
Click OK.
Labels turn a worksheet full of numbers into a meaningful report by identifying the different types of information it contains. You use labels to describe the data in worksheet cells, columns, and rows. You can enter a number as a label (for example, the year 2008), so that Excel does not use the number in its calculations. To help keep your labels consistent, you can use Excel’s AutoComplete feature, which automatically completes your entries (excluding numbers, dates, or times) based on previously entered labels.
Click the cell where you want to enter a number as a label.
Type ’ (an apostrophe). The apostrophe is a label prefix and does not appear on the worksheet.
Type a number value.
Press Return.
If a green triangle appears, it indicates a smart tag. Select the cell to display the Error Smart Tag button, where you can select options related to the label.
Type the first few characters of a label.
If Excel recognizes the entry, AutoComplete completes it.
To accept the suggested entry, press Return.
To reject the suggested completion, simply continue typing.
Did You Know?
Excel doesn’t recognize the entry. The AutoComplete option may not be turned on. To turn on the feature, click the Excel menu, click Preferences, click the AutoComplete icon, select Enable AutoComplete for cell values check box, and then click OK.
Long labels might appear truncated. When you enter a label that is wider than the cell it occupies, the excess text appears to spill into the next cell to the right—unless there is data in the adjacent cell. If that cell contains data, the label will appear truncated—you’ll only see the portion of the label that fits in the cell’s current width. Click the cell to see its entire contents displayed on the formula bar.
You can enter values as whole numbers, decimals, percentages, or dates using the numbers on the top row of your keyboard, or by pressing your Num Lock key, the numeric keypad on the right. When you enter a date or the time of day, Excel automatically recognizes these entries (if entered in an acceptable format) as numeric values and changes the cell’s format to a default date or time format. You can also change the way values, dates or times of day are shown.
To enter a date, type the date using a slash (/) or a hyphen (-) between the month, day, and year in a cell or on the formula bar.
To enter a time, type the hour based on a 12-hour clock, followed by a colon (:), followed by the minute, followed by a space, and ending with an “a” or a “p” to denote A.M. or P.M.
Press Return.
AutoFill is a feature that automatically fills in data based on the data in adjacent cells. Using the fill handle, you can enter data in a series, or you can copy values or formulas to adjacent cells. A single cell entry can result in a repeating value or label, or the results can be a more complex series. You can enter your value or label, and then complete entries such as days of the week, weeks of the year, months of the year, or consecutive numbering.
Select the first cell in the range you want to fill.
Enter the starting value to be repeated, or in a series.
Position the pointer on the lower-right corner of the selected cell. The pointer changes to the fill handle (a black plus sign).
Drag the fill handle over the range you want the value repeated.
To choose how to fill the selection, click the AutoFill Options button, and then click the option you want.
If you want to use an existing list, select the list of items.
Click the Excel menu, and then click Preferences.
Click the Custom Lists icon.
Click the option you want.
-
New list. Click NEW LIST, type the entries you want, press Return after each. Click Add.
-
Existing list. Verify the cell reference of the selected list appears in the Import list, and then click Import.
Click OK.
Even if you plan ahead, you can count on having to make changes on a worksheet. Sometimes it’s because you want to correct an error. Other times it’s because you want to see how your worksheet results would be affected by different conditions, such as higher sales, fewer units produced, or other variables. You can edit data just as easily as you enter it, using the formula bar or directly editing the active cell.
Double-click the cell you want to edit. The insertion point appears in the cell.
The Status bar now displays Edit instead of Ready.
If necessary, use the Home, End, and arrow keys to position the insertion point within the cell contents.
Use any combination of the Backspace and Delete keys to erase unwanted characters, and then type new characters as needed.
Press Return to accept the edit, or press Esc to cancel the edit.
The Status bar now displays Ready instead of Edit.
Did You Know?
You can change editing options. Click the Word menu, click Preferences, click the Edit icon, change the editing options you want, and then click OK.
You can edit cell contents using the formula bar. Click the cell you want to edit, click to place the insertion point on the formula bar, and then edit the cell contents.
You can clear a cell to remove its contents. Clearing a cell does not remove the cell from the worksheet; it just removes from the cell whatever elements you specify: data, comments (also called cell notes), or formatting instructions. When clearing a cell, you must specify whether to remove one, two, or all three of these elements from the selected cell or range.
Select the cell or range you want to clear.
Click the Edit menu, point to Clear, and then click any of the following options:
-
Clear All. Clears contents and formatting.
-
Clear Formats. Clears formatting and leaves contents.
-
Clear Contents. Clears contents and leaves formatting.
-
Clear Comments. Clears comments; removes purple triangle indicator.
Timesaver
To quickly clear contents, select the cell or range you want to clear, Control-click the cell or range, and then click Clear Contents, or press Delete.
You can insert new, blank cells anywhere on the worksheet in order to enter new data or data you forgot to enter earlier. Inserting cells moves the remaining cells in the column or row in the direction of your choice, and Excel adjusts any formulas so they refer to the correct cells. You can also delete cells if you find you don’t need them; deleting cells shifts the remaining cells to the left or up—just the opposite of inserting cells. When you delete a cell, Excel removes the actual cell from the worksheet.
Select the cell or cells where you want to insert the new cell(s).
Click the Insert menu, and then click Cells.
Click the option you want:
-
Shift cells right to move cells to the right one column.
-
Shift cells down to move cells down one row.
-
Entire row to move the entire row down one row.
-
Entire column to move entire column over one column.
Click OK.
Select the cell or range you want to delete.
Click the Edit menu, and then click Delete.
Click the option you want.
-
Shift cells left to move the remaining cells to the left.
-
Shift cells up to move the remaining cells up.
-
Entire row to delete the entire row.
-
Entire column to delete the entire column.
Click OK.
In addition to selecting a range of contiguous and non-contiguous cells in a single worksheet, you may need to select entire rows and columns, or even a range of cells across multiple worksheets. Cells can contain many different types of data, such as comments, constants, formulas, or conditional formats. Excel provides an easy way to locate these and many other special types of cells with the Go To Special dialog box. For example, you can select the Row Differences or Column Differences option to select cells that are different from other cells in a row or column, or select the Dependents option to select cells with formulas that refer to the active cell.
-
To select a single row or column, click in the row or column heading, or select any cell in the row or column, and press Shift+spacebar.
-
To select multiple adjacent rows or columns, drag in the row or column headings.
-
To select multiple nonadjacent rows or columns, press
while you click the borders for the rows or columns you want to include.
Select the range in one sheet.
Select the worksheets to include in the range.
To select contiguous worksheets, press Shift and click the last sheet tab you want to include. To select non-contiguous worksheets, press
and click the sheets you want.
When you make a worksheet selection, Excel enters Group mode.
To exit Group mode, click any sheet tab.
If you want to make a selection from within a range, select the range you want.
Click the Edit menu, and then click Go To.
Click Special.
Click the option in which you want to make a selection. When you click the Formulas option, select or clear the formula related check boxes.
Click OK.
If no cells are found, Excel displays a message.
Each new workbook opens with three worksheets (or sheets), in which you store and analyze values. You can work in the active, or selected, worksheet. The default worksheet names are Sheet1, Sheet2, and Sheet3, which appear on the sheet tab, like file folder labels. As you create a worksheet, give it a meaningful name to help you remember its contents. The sheet tab size adjusts to fit the name’s length, so using short names means more sheet tabs will be visible. If you work on a project that requires more than three worksheets, add additional sheets to the workbook so all related information is stored in one file.
You can add or delete sheets in a workbook. If, for example, you are working on a project that requires more than three worksheets, you can insert additional sheets in one workbook rather than open multiple workbooks. You can insert as many sheets in a workbook as you want. On the other hand, if you are using only one or two sheets in a workbook, you can delete the unused sheets to save disk space. Before you delete a sheet from a workbook, make sure you don’t need the data. You cannot undo the deletion.
When you insert a worksheet in Excel, you have several choice beyond the blank sheet. You can also insert a blank worksheet designed for creating a list or chart. If you don’t want to start a worksheet from scratch, Excel provides a gallery of different styles from which to choose. From the Sheets tab in the Elements Gallery (New!), you can select a Quick Style worksheet from a variety of categories, such as Accounts, Budgets, Invoices, Lists, Portfolios, or Reports. The Quick Style worksheets are templates with all the layout, formatting, and formulas you need to get started quickly. All you need to do is add your own data.
Click the sheet tab to the right of where you want to insert the new sheet.
Click the Sheets tab on the Elements Gallery.
Click the Blank Sheets tab.
Click the type of blank worksheet you want to use:
-
Blank Sheet. Inserts a blank worksheet.
-
List Sheet. Inserts a blank worksheet designed for creating a list.
-
Chart Sheet. Inserts a blank worksheet designed for creating a chart.
Click the sheet tab to the right of where you want to insert the new sheet.
Click the Sheets tab on the Elements Gallery.
Click one of the tabs (Accounts, Budgets, Invoices, Lists, Portfolios, or Reports) with the type of the worksheet you want to insert.
Click the style you want from the gallery.
-
Click the scroll up or down arrows to see additional styles.
Click the Formatting Palette tab on the Toolbox.
Click the panel associated with the Quick Style worksheet. For example, click Ledger Sheet panel for the Inventory worksheet.
Change the settings you want for the worksheet.
After adding several sheets to a workbook, you might want to reorganize them. You can arrange sheets in chronological order or in order of importance. You can easily move or copy a sheet within a workbook or to a different open workbook. Copying a worksheet is easier and often more convenient then re-entering similar information on a new sheet. If you are moving or copying a worksheet a short distance, you should use the mouse. For longer distances, you should use the Move or Copy command.
Click the sheet tab of the worksheet you want to move, and then hold down the mouse button.
When the mouse pointer changes to a sheet of paper, drag it to the right of the sheet tab where you want to move the worksheet.
Release the mouse button.
Did You Know?
You can give your worksheet a different background. Click the tab of the sheet on which you want to insert a background, click the Format menu, point to Sheet, and then click Background. Select the picture you want to use as a background, and then click Insert.
You can use groups to affect multiple worksheets. Click a sheet tab, press and hold Shift, and click another sheet tab to group worksheets. Control-click a grouped sheet tab, and then click Ungroup Sheet on the shortcut menu.
Click the sheet tab of the worksheet you want to copy.
Click the Edit menu, and then click Move or Copy Sheet.
If you want to copy the sheet to another open workbook, click the To book drop-down, and then select the name of that workbook. The sheets of the selected workbook appear in the Before Sheet list.
Trouble?
If the workbook you want to copy to does not show up in the To Book drop-down list, you must first open the workbook.
Click a sheet name in the Before Sheet list. Excel inserts the copy to the left of this sheet.
Select the Create a copy check box.
Click OK.
Did You Know?
You can copy or move a sheet to a different workbook. You must first open the other workbook, and then switch back to the workbook of the sheet you want to copy or move.
You can use the Create a copy check box to move a worksheet. Clear the Create a copy check box in the Move or Copy dialog box to move a worksheet rather than copy it.
Not all worksheets should be available to everyone. You can keep sensitive information private without deleting it by hiding selected worksheets. For example, if you want to share a workbook with others, but it includes confidential employee salaries, you can simply hide a worksheet. Hiding worksheets does not affect calculations in the other worksheets; all data in hidden worksheets is still referenced by formulas as necessary. Hidden worksheets do not appear in a printout either. When you need the data, you can unhide the sensitive information.
Not all the data on a worksheet should be available to everyone. You can hide sensitive information without deleting it by hiding selected columns or rows. For example, if you want to share a worksheet with others, but it includes confidential employee salaries, you can simply hide the salary column. Hiding columns and rows does not affect calculations in a worksheet; all data in hidden columns and rows is still referenced by formulas as necessary. Hidden columns and rows do not appear in a printout either. When you need the data, you can unhide the sensitive information.
You can insert blank columns and rows between existing data, without disturbing your worksheet. Excel repositions existing cells to accommodate the new columns and rows and adjusts any existing formulas so that they refer to the correct cells. Formulas containing absolute cell references will need to be adjusted to the new columns or rows. When you insert one or more columns, they insert to the left. When you add one or more rows, they are inserted above the selected row.
Click to the right of the location of the new column you want to insert.
To insert a row, click the row immediately below the location of the row you want to insert.
Click the Insert menu, and then click Columns or Rows.
To adjust formatting, click the Insert Options button, and then click a formatting option.
Drag to select the column header buttons for the number of columns you want to insert.
To insert multiple rows, drag to select the row header buttons for the number of rows you want to insert.
Click the Insert menu, and then click Columns or Rows.
To adjust formatting, click the Insert Options button, and then click a formatting option.
At some point in time, you may want to remove an entire column or row of data from a worksheet rather than deleting or editing individual cells. You can delete columns and rows just as easily as you insert them. Formulas will need to be checked in your worksheet prior to deleting a row or column, especially when referencing absolute cell addresses. Remaining columns and rows move to the left or up to join the other remaining data.
You’ve entered labels and values, constructed formulas, and even formatted the cells, but now some of your data isn’t visible; the value displays as ##### in the cell. Also, some larger-sized labels are cut off. You can narrow or widen each column width to fit its contents and adjust your row heights as needed. As you build your worksheet, you can change the default width of some columns or the default height of some rows to accommodate long strings of data or larger font sizes. You can manually adjust column or row size to fit data you have entered, or you can use AutoFit to resize a column or row to the width or height of its largest entry.
Click the column or row header button for the first column or row you want to adjust.
If you want, drag to select more columns or rows.
Do either of the following:
-
Column Width. Click the Format menu, point to Column, and then click Width.
-
Row Height. Click the Format menu, point to Row, and then click Height.
Timesaver
Control-click the selected column(s) or row(s), and then click Column Width or Row Height.
Type a new column width or row height in points.
Click OK.
Position the mouse pointer on the right edge of the column header button or the bottom edge of the row header button for the column or row you want to change.
When the mouse pointer changes to a double-headed arrow, double-click the mouse.
-
You can also click the Format menu, point to Column, and then click AutoFit Selection.
-
You can also click the Format menu, point to Row, and then click AutoFit.
If you are working on a large worksheet, it can be time consuming and tiring to scroll back and forth between two parts of the worksheet. You can split the worksheet into four panes and two scrollable windows that you can view simultaneously but edit and scroll independently using the Split bar. As you work in two parts of the same worksheet, you can resize the window panes to fit your task. Drag the split bar between the panes to resize the windows. No matter how you display worksheets, Excel’s commands and buttons work the same as usual.
Select the row, column, or cell location where you want to split a worksheet into panes.
A column or row selection creates two panes, while a cell selection creates four panes.
Point to the Split bar at the top of the vertical scroll bar or at the right end of the horizontal scroll bar.
When the cursor changes, drag the Split bar down or left to the place you want.
-
To split a worksheet into four pane, click the Window menu, and then click Split.
To remove the split, click the Window menu, and then click Remove Split.
Did You Know?
You can search for a value or data in a cell, and then replace it with different content. Click the cell or cells containing content you want to replace. Click the Edit menu, click Replace, specify the values or data you want to find and replace, and then click the appropriate Find or Replace buttons.
Large worksheets can be difficult to work with, especially on low-resolution or small screens. If you scroll down to see the bottom of the list, you can no longer see the column names at the top of the list. Instead of repeatedly scrolling up and down, you can temporarily set, or freeze, those column or row headings so that you can see them no matter where you scroll in the list. When you freeze a row or column, you are actually splitting the screen into one or more panes (window sections) and freezing one of the panes. You can split the screen into up to four panes and can freeze up to two of these panes. You can edit the data in a frozen pane just as you do any Excel data, but the cells remain stationary even when you use the scroll bars; only the unfrozen part of the screen scrolls. When you freeze a pane, it has no effect on how a worksheet looks when printed.
Click the View menu, and then click Normal.
Select the column to the right of the columns you want to freeze, or select the row below the rows you want to freeze.
To freeze both, click the cell to the right and below of the column and row you want to freeze.
Click the Window menu, and then click Freeze Panes.
When you freeze a pane horizontally, all the rows above the active cell freeze. When you freeze a pane vertically, all the columns to the left of the active cell freeze.
To unfreeze a column or row, click the Window menu, and then click Unfreeze Panes.
When you open a new or existing workbook, Excel displays a standard set of elements, such as the Formula Bar, Headings (columns and rows), Gridlines, and Ruler, which is available in Page Layout view. If you need a little more display room to see your data or you want to see how your data looks without the gridlines, you can quickly select or clear view settings on the Data menu in Excel to show or hide these elements.
-
View menu. Click the View menu, and then click the element you want to show or hide.
-
Ruler. In Page Layout view, the horizontal and vertical rulers.
-
Formula Bar. The bar at the top of the screen.
-
-
View Preferences. Click the Excel menu, click Preferences, click the View icon, and then select the check boxes with the options you want to show or hide. Some of the common options include:
-
Show gridlines. The gray outline around cells.
-
Show row and column headings. The column (letters) and row (numbers) headings.
-