After performing these steps, a new icon appears on your QAT.
To use a data entry form, you must arrange your data so that Excel can recognize it as a table. Start by enter- ing headings for the columns in the first row of your data entry range. Select any cell in the table and click the Form button on your QAT. Excel then displays a dialog box customized to your data. You can use Tab to move between the text boxes and supply information. If a cell contains a formula, the formula result appears as text (not as an edit box). In other words, you can’t modify formulas using the data entry form.
When you complete the data form, click the New button. Excel enters the data into a row in the worksheet and clears the dialog box for the next row of data.
Entering the current date or time into a cell
If you need to date-stamp or time-stamp your worksheet, Excel provides two shortcut keys that do this task for you:
n Current date:Ctrl+; (semicolon) n Current time:Ctrl+Shift+; (semicolon)
When you use either of these shortcuts to enter a date or time into your worksheet, Excel enters a static value into the worksheet. In other words, the date or time entered doesn’t change when the worksheet is recalculated. In most cases, this setup is probably what you want, but you should be aware of this limitation. If you want the date or time display to update, use one of these formulas:
=TODAY()
=NOW()
NOTE NOTE
Applying Number Formatting
Number formatting refers to the process of changing the appearance of values contained in cells. Excel pro- vides a wide variety of number formatting options. In the following sections, you see how to use many of Excel’s formatting options to quickly improve the appearance of your worksheets.
Remember that the formatting you apply works with the selected cell or cells. Therefore, you need to select the cell (or range of cells) before applying the formatting. Also remember that changing the number format does not affect the underlying value. Number formatting affects only the appearance.
Improving readability by formatting numbers
Values that you enter into cells normally are unformatted. In other words, they simply consist of a string of numerals. Typically, you want to format the numbers so that they’re easier to read or are more consistent in terms of the number of decimal places shown.
Figure 3.8 shows a worksheet that has two columns of values. The first column consists of unformatted val- ues. The cells in the second column are formatted to make the values easier to read. The third column describes the type of formatting applied.
This workbook is available on the companion CD-ROM. The file is named number format- ting.xlsx.
FIGURE 3.8
Use numeric formatting to make it easier to understand what the values in the worksheet represent.
If you move the cell pointer to a cell that has a formatted value, the Formula bar displays the value in its unformatted state because the formatting affects only how the value appears in the cell — not the actual value contained in the cell.
Using automatic number formatting
Excel is smart enough to perform some formatting for you automatically. For example, if you enter 12.2%
into a cell, Excel knows that you want to use a percentage format and applies it for you automatically. If you use commas to separate thousands (such as 123,456), Excel applies comma formatting for you. And if you precede your value with a dollar sign, the cell is formatted for currency (assuming that the dollar sign is your system currency symbol).
TIP TIP
ON the CD-ROM ON the CD-ROM
TIP TIP
A handy default feature in Excel makes entering percentage values into cells easier. If a cell is formatted to display as a percent, you can simply enter a normal value (for example 12.5 for 12.5%). If this feature isn’t working (or if you prefer to enter the actual value for percents), access the Excel Options dialog box and click the Advanced tab. In the Editing Options section, locate the check box labeled Enable Automatic Percent Entry, and remove the check mark.
Formatting numbers by using the Ribbon
The Home➪Number group in the Ribbon contains controls that let you quickly apply common number formats (see Figure 3.9).
FIGURE 3.9
You can find number formatting commands in the Number group of the Home tab.
The Number Format drop-down list contains 11 common number formats. Additional options include an Accounting Number Format drop-down list (to select a currency format), plus a Percent Style and a Comma Style button. In addition, the group contains a button to increase the number of decimal places, and another to decrease the number of decimal places.
When you select one of these controls, the active cell takes on the specified number format. You also can select a range of cells (or even an entire row or column) before clicking these buttons. If you select more than one cell, Excel applies the number format to all the selected cells.
Using shortcut keys to format numbers
Another way to apply number formatting is to use shortcut keys. Table 3.1 summarizes the shortcut-key combinations that you can use to apply common number formatting to the selected cells or range. Notice that these Ctrl+Shift characters are all located together, in the upper left part of your keyboard.
TABLE 3.1
Number-Formatting Keyboard Shortcuts
Key Combination Formatting Applied
Ctrl+Shift+~ General number format (that is, unformatted values)
Ctrl+Shift+$ Currency format with two decimal places (negative numbers appear in parentheses) Ctrl+Shift+% Percentage format, with no decimal places
Ctrl+Shift+^ Scientific notation number format, with two decimal places Ctrl+Shift+# Date format with the day, month, and year
Ctrl+Shift+@ Time format with the hour, minute, and AM or PM
Ctrl+Shift+! Two decimal places, thousands separator, and a hyphen for negative values
TIP TIP
Formatting numbers using the Format Cells dialog box
In most cases, the number formats that are accessible from the Number group on the Home tab are just fine. Sometimes, however, you want more control over how your values appear. Excel offers a great deal of control over number formats through the use of the Format Cells dialog box, shown in Figure 3.10. For for- matting numbers, you need to use the Number tab.
FIGURE 3.10
When you need more control over number formats, use the Number tab of the Format Cells dialog box.
You can bring up the Format Cells dialog box in several ways. Start by selecting the cell or cells that you want to format and then do the following:
n Choose Home➪Number and click the small dialog launcher icon.
n Choose Home➪Number, click the Number Format drop-down list, and select More Number Formats from the drop-down list.
n Right-click and choose Format Cells from the shortcut menu.
n Press the Ctrl+1 shortcut key.
The Number tab of the Format Cells dialog box displays 12 categories of number formats from which to choose. When you select a category from the list box, the right side of the tab changes to display the appro- priate options.
The Number category has three options that you can control: the number of decimal places displayed, whether to use a thousand separator, and how you want negative numbers displayed. Notice that the Negative Numbers list box has four choices (two of which display negative values in red), and the choices change depending on the number of decimal places and whether you choose to separate thousands.
The top of the tab displays a sample of how the active cell will appear with the selected number format (vis- ible only if a cell with a value is selected). After you make your choices, click OK to apply the number for- mat to all the selected cells.
Selecting the Precision As Displayed option changes the numbers in your worksheets to perma- nently match their appearance onscreen. This setting applies to all sheets in the active work- book. Most of the time, this option is notwhat you want. Make sure that you understand the consequences of using the Set Precision As Displayed option.
Chapter 11 discusses ROUNDand other built-in functions.
The following are the number-format categories, along with some general comments:
n General:The default format; it displays numbers as integers, as decimals, or in scientific notation if the value is too wide to fit in the cell.
n Number:Enables you to specify the number of decimal places, whether to use a comma to sepa- rate thousands, and how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses).
n Currency:Enables you to specify the number of decimal places, whether to use a currency sym- bol, and how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses). This format always uses a comma to separate thousands.
n Accounting:Differs from the Currency format in that the currency symbols always line up vertically.
n Date:Enables you to choose from several different date formats.
n Time:Enables you to choose from several different time formats.
n Percentage:Enables you to choose the number of decimal places and always displays a percent sign.
CROSS-REF CROSS-REF CAUTION CAUTION
When Numbers Appear to Add Up Incorrectly
Applying a number format to a cell doesn’t change the value — only how the value appears in the work- sheet. For example, if a cell contains 0.874543, you may format it to appear as 87%. If that cell is used in a formula, the formula uses the full value (0.874543), not the displayed value (87%).
In some situations, formatting may cause Excel to display calculation results that appear incorrect, such as when totaling numbers with decimal places. For example, if values are formatted to display two decimal places, you may not see the actual numbers used in the calculations. But because Excel uses the full precision of the values in its formula, the sum of the two values may appear to be incorrect.
Several solutions to this problem are available. You can format the cells to display more decimal places. You can use the ROUNDfunction on individual numbers and specify the number of decimal places Excel should round to. Or you can instruct Excel to change the worksheet values to match their displayed format. To do so, access the Excel Options dialog box and click the Advanced tab. Check the Set Precision As Displayed check box (which is located in the section named When Calculating This Workbook).
n Fraction:Enables you to choose from among nine fraction formats.
n Scientific:Displays numbers in exponential notation (with an E): 2.00E+05 = 200,000; 2.05E+05
= 205,000. You can choose the number of decimal places to display to the left of E.
n Text:When applied to a value, causes Excel to treat the value as text (even if it looks like a num- ber). This feature is useful for such items as part numbers.
n Special:Contains four additional number formats (Zip Code, Zip Code +4, Phone Number, and Social Security Number).
n Custom:Enables you to define custom number formats that aren’t included in any other category.
If a cell displays a series of hash marks (such as #########), it usually means that the col- umn isn’t wide enough to display the value in the number format that you selected. Either make the column wider or change the number format.
Adding your own custom number formats
Sometimes you may want to display numerical values in a format that isn’t included in any of the other cat- egories. If so, the answer is to create your own custom format.
Excel provides you with a great deal of flexibility in creating number formats — so much so that I’ve devoted an entire chapter (Chapter 24) to this topic.
CROSS-REF CROSS-REF
TIP TIP
This chapter covers some basic information regarding workbooks, work- sheets, and windows. You discover tips and techniques to help you take control of your worksheets. The result? You’ll be a more efficient Excel user.
Learning the Fundamentals of Excel Worksheets
In Excel, each file is called a workbook,and each workbook can contain one or more worksheets.You may find it helpful to think of an Excel workbook as a notebook and worksheets as pages in the notebook. As with a notebook, you can view a particular sheet, add new sheets, remove sheets, and copy sheets.
The following sections describe the operations that you can perform with worksheets.
Working with Excel’s windows
An Excel workbook file can hold any number of sheets, and these sheets can be either worksheets (sheets consisting of rows and columns) or chart sheets(sheets that hold a single chart). A worksheet is what people usually think of when they think of a spreadsheet. You can open as many Excel workbooks as necessary at the same time.
Figure 4.1 shows Excel with four workbooks open, each in a separate window.
One of the windows is minimized and appears near the lower-left corner of the screen. (When a workbook is minimized, only its title bar is visible.) Worksheet windows can overlap, and the title bar of one window is a different color. That’s the window that contains the active workbook.
IN THIS CHAPTER
Understanding Excel worksheet essentials
Controlling your views Manipulating the rows and columns
Essential Worksheet Operations
FIGURE 4.1
You can open several Excel workbooks at the same time.
The workbook windows that Excel uses work much like the windows in any other Windows program. Each window has three buttons at the right side of its title bar. From left to right, they are Minimize, Maximize (or Restore), and Close. When a workbook window is maximized, the three buttons appear directly below Excel’s title bar.
Excel’s windows can be in one of the following states:
n Maximized:Fills Excel’s entire workspace. A maximized window doesn’t have a title bar, and the workbook’s name appears in Excel’s title bar. To maximize a window, click its Maximize button.
n Minimized:Appears as a small window with only a title bar. To minimize a window, click its Minimize button.
n Restored:A nonmaximized size. To restore a maximized or minimized window, click its Restore button.
If you work with more than one workbook simultaneously (which is quite common), you have to know how to move, resize, and switch among the workbook windows.
Moving and resizing windows
To move a window, make sure that it’s not maximized. Then click and drag its title bar with your mouse.
To resize a window, click and drag any of its borders until it’s the size that you want it to be. When you position the mouse pointer on a window’s border, the mouse pointer changes to a double-sided arrow, which lets you know that you can now click and drag to resize the window. To resize a window horizontally and vertically at the same time, click and drag any of its corners.
You can’t move or resize a workbook window if it’s maximized. You can move a minimized window, but doing so has no effect on its position when it’s subsequently restored.
If you want all your workbook windows to be visible (that is, not obscured by another window), you can move and resize the windows manually, or you can let Excel do it for you. Choosing View➪Window➪ Arrange All displays the Arrange Windows dialog box, shown in Figure 4.2. This dialog box has four window- arrangement options. Just select the one that you want and click OK. Windows that are minimized aren’t affected by this comment.
FIGURE 4.2
Use the Arrange Windows dialog box to quickly arrange all open workbook windows.
Switching among windows
At any given time, one (and only one) workbook window is the active window. The active window accepts your input and is the window on which your commands work. The active window’s title bar is a different color, and the window appears at the top of the stack of windows. To work in a different window, you need to make that window active. You can make a different window the active workbook in several ways:
n Click another window, if it’s visible. The window you click moves to the top and becomes the active window. This method isn’t possible if the current window is maximized.
n Press Ctrl+Tab (or Ctrl+F6) to cycle through all open windows until the window that you want to work with appears on top as the active window. Shift+Ctrl+Tab (or Shift+Ctrl+F6) cycles through the windows in the opposite direction.
n Choose View➪Window➪Switch Windows and select the window that you want from the drop-down list (the active window has a check mark next to it). This menu can display up to nine windows. If you have more than nine workbook windows open, choose More Windows (which appears below the nine window names).
n Click the icon for the window in the Windows taskbar. This technique is available only if the Show All Windows In The Taskbar option is turned on. You can control this setting in the Advanced tab of the Excel Options dialog box (in the Display section).
NOTE NOTE
Most people prefer to do most of their work with maximized workbook windows, which enables you to see more cells and eliminates the distraction of other workbook windows get- ting in the way. At times, however, viewing multiple windows is preferred. For example, displaying two win- dows is more efficient if you need to compare information in two workbooks or if you need to copy data from one workbook to another.
When you maximize one window, all the other windows are maximized, too (even though you don’t see them). Therefore, if the active window is maximized and you activate a different window, the new active window is also maximized.
You also can display a single workbook in more than one window. For example, if you have a workbook with two worksheets, you may want to display each worksheet in a separate win- dow in order to compare the two sheets. All the window-manipulation procedures described previously still apply. Choose View➪Window➪New Window to open an additional window in the active workbook.
Closing windows
If you have multiple windows open, you may want to close those windows that you no longer need. Excel offers several ways to close the active window:
n Choose Office➪Close.
n Click the Close button (the Xicon) on the workbook window’s title bar. If the workbook window is maximized, its title bar is not visible, so its Close button appears directly below Excel’s Close button.
n Press Ctrl+W.
When you close a workbook window, Excel checks whether you have made any changes since the last time you saved the file. If not, the window closes without a prompt from Excel. If you’ve made any changes, Excel prompts you to save the file before it closes the window.
Activating a worksheet
At any given time, one workbook is the active workbook, and one sheet is the active sheet in the active workbook. To activate a different sheet, just click its sheet tab, located at the bottom of the workbook win- dow. You also can use the following shortcut keys to activate a different sheet:
n Ctrl+PgUp:Activates the previous sheet, if one exists n Ctrl+PgDn:Activates the next sheet, if one exists
If your workbook has many sheets, all its tabs may not be visible. Use the tab-scrolling controls (see Figure 4.3) to scroll the sheet tabs. The sheet tabs share space with the worksheet’s horizontal scroll bar. You also can drag the tab split control to display more or fewer tabs. Dragging the tab split control simultaneously changes the number of tabs and the size of the horizontal scroll bar.
When you right-click any of the tab-scrolling controls, Excel displays a list of all sheets in the workbook. You can quickly activate a sheet by selecting it from the list.