Excel has a long and colorful history. Microsoft released the first version of Excel for the Macintosh in 1985 and added a version for Windows two years later. By 1993, Excel had morphed into an all-purpose number-cruncher, and it quickly eclipsed competing spreadsheet programs like Lotus 1-2-3.
Although Microsoft is reluctant to admit it, most of Excel’s core features were completed six or seven years ago. So what has Microsoft been doing ever since? The answer, at least in part, is spending millions of dollars on so-called usability tests, which are aimed at figuring out how easy—or not—a program is to use. In a typical usability test, Microsoft gathers a group of spreadsheet novices, watches them fumble around with the latest version of Excel, and then tweaks the program to make it more intuitive. As a result, Excel is packed full of timesaving features—some nifty, some just quirky—like menus that hide advanced options, lists that complete themselves, animated assistants, and every imaginable type of button, toolbar, and dockable window. Some of the features are genuinely useful, while others can be supremely annoying—especially when Excel hides the feature you’re hunting for desperately.
The best way to avoid potential headaches like these is to take a quick tour of Excel as you start creating a spreadsheet. That’s the task you’ll complete in this chapter. Along the way, you’ll learn how to enter information in the Excel window and how to open and save spreadsheet files.
When you first launch Excel, it starts you off with a new, blank worksheet. A worksheet is the grid of cells where you type your information and formulas, and it takes up most of the window, as shown in Figure 1-1. This grid is the most important part of the Excel window. It’s where you’ll perform all your work, such as entering data, writing formulas, and reviewing the results.
Figure 1-1. The Excel window has several parts, most notably the worksheet grid where you type in your information, and the Task Pane, which shows different options depending on the task you’re currently performing.
Note
Part of getting to know Excel is figuring out where to go when you need help. For the lowdown on Excel’s online help, which ranges from indispensable to downright infuriating, see Appendix A.
Here are a few basics about Excel’s grid:
The grid divides your worksheet into rows and columns. Columns are identified with letters (A, B, C ...), while rows are identified with numbers (1, 2, 3 ...).
The smallest unit in your worksheet is the cell. Cells are identified by column and row. For example, C6 is the address of a cell in column C (the third column), and row 6 (the sixth row). Figure 1-2 shows this cell, which looks like a rectangular box. Incidentally, an Excel cell can hold up to 32,767 characters.
A worksheet can span up to 256 columns and 65,536 rows (giving you a grand total of 16,777,216 cells). In the unlikely case that you want to go beyond those limits—say you’re tracking blades of grass on the White House lawn—you’ll need to create a new worksheet. Every spreadsheet file can hold multiple worksheets, as you’ll learn in Chapter 5.
When you enter information, you enter it one cell at a time. However, you don’t have to follow any set order. For example, you can start by typing information into cell A40, without worrying about filling any data in the cells that appear in the earlier rows.
Note
There is definitely, absolutely no way around the 256 column and 65,536 row limits. Of course, it’s encouraging to note that if you fill in one cell per second, you’ll need about 200 days to fill up your worksheet, assuming you don’t break for sleep, food, or coffee.
Figure 1-2. Here, the current cell is C6. You can recognize the current (or active) cell based on its heavy black border. You’ll also notice that the corresponding column letter (C) and row number (6) are highlighted at the edges of the worksheet. Just above the worksheet, on the left side of the window, the formula bar tells you the active cell address.
The best way to get a feel for Excel is to dive right in and start putting together a worksheet. The following sections cover each step that goes into assembling a simple worksheet. This one tracks household expenses, but you can use the same approach to create any basic worksheet.
When you fire up Excel, it opens a fresh workbook. If you’ve already got Excel open and you want to create another workbook, just select File → New. Contrary to what you’d expect, this step doesn’t actually create the new file. Instead, it pops up the New Workbook task in the Task Pane (if that isn’t already visible). To finish the job, you need to click the “Blank workbook” link. The New Workbook task gives you a few other options that allow you to create workbooks based on templates , which provide customized layouts for certain types of data. You’ll learn about using (and making) templates in Chapter 15.
The most straightforward way to create a worksheet is to design it as a table with headings for each column. It’s important to remember that even for the simplest worksheet, the decisions you make about what’s going to go in each column can have a big effect on how easy it is to manipulate your information.
For example, in a worksheet that stores a mailing list, you could have two columns: one for names and another for addresses. But if you create more than two columns, your life will probably be easier since you can separate first names from street addresses from Zip Codes, and so on. Figure 1-3 shows the difference.
Figure 1-3. Top: If you type the first and last names together in one column, Excel can sort only by the first letter of the first names. And if you clump the addresses and Zip Codes together, you give Excel no way to count how many people live in a certain town or neighborhood because Excel can’t extract the Zip Codes on its own. Bottom: The benefit of a six-column table is significant: it lets you sort (reorganize) your list according to people’s last names or where they live. It also allows you to filter out individual bits of information when you start using functions later in this book.
You can, of course, always add or remove columns later. But you can avoid getting gray hairs by starting a worksheet with all the columns you think you’ll need.
The first step in creating your worksheet is to add your headings in the row of cells at the top of the worksheet (row 1). Technically, you don’t need to start right in the first row, but unless you want to add more information before your table—like a title for the chart or the date you’re creating it—there’s no point in wasting the blank space.
For a simple expense worksheet designed to keep a record of your most prudent and extravagant purchases, try the following three headings:
Date Purchased stores the date when you spent the money.
Item stores the name of the product that you bought.
Price records how much it cost.
Right away, you face your first glitch: awkwardly crowded text. Figure 1-4 shows how you can adjust column width for proper breathing room.
Figure 1-4. Top: The standard width of an Excel column is 8.43 characters, which hardly allows you to get a word in edgewise. To solve this problem, position your mouse on the right border of the column header you want to expand, so that the mouse pointer changes to the resize icon (it looks like a double-headed arrow). Now drag the column border to the right as far as you want. As you drag, a tooltip appears, telling you the character size and pixel width of the column. Both of these pieces of information play the same role—they tell you how wide the column is—only the unit of measurement changes. Bottom: When you release the mouse, the entire column of cells is resized to the new size.
Note
A column’s character width doesn’t really reflect how many characters (or letters) fit in a cell. Modern versions of Excel (including Excel 2002 and 2003) use proportional fonts, in which different letters take up different amounts of room. For example the letter W is typically much wider than the letter I. All this means is that the character width Excel shows you isn’t a real indication of how many letters can fit in the column, but it’s still a useful measurement that you can use to compare different columns.
You can now begin adding your data: simply fill in the rows under the column titles. Each row in the expense worksheet represents a separate purchase that you’ve made. (If you’re familiar with databases, you can think of each row as a separate record.)
As Figure 1-5 shows, the first column is for dates, the second column is for text, and the third column holds numbers. Keep in mind that Excel doesn’t impose any rules on what you type, so you’re free to put text in the Price column. But if you don’t keep a consistent kind of data in each column, you won’t be able to easily analyze (or understand) your information later.
Figure 1-5. This rudimentary expense list has three items (in rows 2, 3, and 4). The alignment of each column reflects the data type (by default, numbers and dates are right-aligned, while text is left-aligned), confirming that Excel understands your date and price information.
That’s it. You’ve created a living, breathing worksheet. The next two sections explain how to edit data and move around the grid.
Every time you start typing in a cell, Excel erases any existing content in that cell. (You can also quickly remove the contents of a cell by just moving to it and pressing Delete.)
If you want to edit cell data instead of just replacing it, you need to put the cell in edit mode , like this:
Move to the cell you want to edit.
Use the mouse or the arrow keys to get to the correct cell.
Put the cell in edit mode by pressing F2.
Edit mode looks almost the same as ordinary text entry mode. The only difference is that you can use the arrow keys to move through the text you’re typing and make changes. (When you aren’t in edit mode, pressing these keys just moves you to another cell.)
If you don’t want to use F2, you can also place a cell in edit mode by double-clicking it.
Complete your edit.
Once you’ve modified the cell content, you can press Enter to commit your change or Esc to cancel your edit and leave the old value in the cell. Alternatively, you can turn off edit mode (press F2 again), and then move to a new cell to commit your change. As long as you stay in edit mode, Excel won’t let you move to another cell.
Tip
If you start typing new information into a cell, and you decide you want to move to an earlier position in your entry (to make an alteration, for instance), just press F2. The cell box still looks the same, but you’re now in edit mode, which means that you can use the arrow keys to move within the cell (instead of moving from cell to cell). You can press F2 again to return to the normal data entry mode.
As you enter data, you may discover the Bigtime Excel Display Problem (known to aficionados as BEDP): cells in adjacent columns can overlap one another. Figure 1-6 shows the problem. One way to fix BEDP is to manually resize the column, as shown in Figure 1-4. Chapter 2 tackles editing in more detail and shows how you can deal with these dilemmas by resizing columns (Chapter 2). Chapter 4 shows you how to wrap multiple lines of text in a single cell.
Figure 1-6. Overlapping cells can create great headaches. For example, if you type a large amount of text into A1, and then you type some text into B1, you’ll only see part of the data in A1 on your worksheet (as shown here). The rest is truncated. But if, say, A3 contains a large amount of text and B3 is empty, the content in A3 is displayed over both columns, and you don’t have a problem.
Learning how to move around the Excel grid quickly and confidently is an indispensable skill. To move from cell to cell, you have two basic choices:
As you move from cell to cell, you’ll see the black focus box move to highlight the currently active cell.
In some cases, you might want to cover ground a little more quickly. You can use any of the shortcut keys listed in Table 1-1. The most useful shortcut keys include the Home key combinations, which bring you back to the beginning of a row or the top of your worksheet.
Note
Shortcut key combinations that use the + sign must be entered together. For example, “Ctrl+Home” means hold down the Ctrl key and press the Home key at the same time. Other key combinations work in sequence. For example, the key combination “End, Home” means press End, and then press Home.
Table 1-1. Shortcut Keys for Moving Around a Worksheet
Excel also lets you cross great distances in a single bound using a Ctrl+arrow key combination. These key combinations jump to the edges of your data. Edge cells include cells that are next to other blank cells. For example, if you press Ctrl+→ while you’re inside a group of cells with information in them, you’ll skip to the right, over all filled cells, and stop just before the next blank cell. If you press Ctrl+→ again, you’ll skip over all the nearby blank cells and land in the next cell to the right that has information in it. If there aren’t any more cells with data on the right, you’ll wind up on the very edge of your worksheet.
The Ctrl+arrow key combinations are useful if you have more than one table of data in the same worksheet. For example, imagine you have two tables of data, one at the top of a worksheet and one at the bottom. If you are at the top of the first table, you can use Ctrl+↓ to jump to the bottom of the first table, skipping all the rows in between. Press Ctrl+↓ again, and you leap over all the blank rows, winding up at the beginning of the second table.
Tip
You can also scroll off into the nether regions of the spreadsheet with the help of the scrollbars at the bottom and on the right side of the worksheet.
Finding your way around a worksheet is a fundamental part of mastering Excel. Knowing your way around the larger program window is no less important. The next few sections help you get oriented, pointing out the important stuff and letting you know what you can ignore altogether.
You probably think that the classic pull-down menu is the simplest element on your screen. But in Excel 2003, the menus force you to take part in an awkward game of hide-and-seek—unless you take a few steps to tame them.
Figure 1-7 shows two different versions of the same Tools menu. The first is the “simplified” version Excel shows you automatically. The idea behind the simplified menu is that by hiding some of the more advanced choices, Excel helps you find what you want more quickly. To its credit, Excel goes one step further, and actually stores information about what menu options you use. That means that when you select a menu like Tools, you’ll see a simplified version that includes all the choices you typically use. Excel calls this a personalized menu , and the options change as you use the menu.
Figure 1-7. Left: Personalized menus attempt to make life easier by hiding the options that Excel guesses you don’t care about. This innovation is often more trouble than it’s worth, however, because it’s hard to remember how a menu is organized when it’s constantly changing. Right: This version lists all available options.
If you can’t find an item you’re all but certain was in a particular menu, you can temporarily expand the menu to show every option. Simply click the expand icon at the bottom of the personalized menu (it looks like two arrows pointing down). Alternatively, you can just hover over the expand icon with the mouse.
The problem with hide-and-seek menus is that when you switch from the personalized menu to the full menu, Excel rearranges the entire menu, inserting new items above, below, and between the existing items. If you like to find menu items by remembering their approximate location (as most people do), Excel’s personalized menus can send you right over the edge. Fortunately, it’s easy to set Excel to always show you the full, steady, one-size-fits-all menus. Just select Tools → Customize, and then in the Customize dialog box that appears, click the Options tab. Turn on “Always show full menus” to banish the personalized menus.
Note
When you turn off personalized menus in Excel, you’re actually changing a setting that affects all Office programs. Thus, you’re turning them off in other Office applications on your computer, including Word and Outlook.
GEM IN THE ROUGH: Getting Somewhere in a Hurry
If you’re fortunate enough to know exactly where you need to go, you can use the Go To feature to make the jump. Go To moves to the cell address that you specify. It comes in useful in extremely large spreadsheets, where just scrolling through the worksheet takes half a day.
To show the Go To dialog box, select Edit → Go To, or just press Ctrl+G. Enter the cell address (as in C32), and click OK.
The Go To feature becomes more useful the more you use it. That’s because the Go To window maintains a list of the most recent cell addresses that you’ve entered. In addition, every time you open the Go To window, Excel automatically adds the current cell to the list. This feature makes it easy to jump to a far off cell and quickly return to your starting location by selecting the last entry in the list.
You’ll notice that in the list, cell addresses are written a little differently than the format you use for typing them in. Namely, dollar signs are added before the row number and column letter. Thus, C32 becomes $C$32, which is simply the convention that Excel uses for fixed cell references. (You’ll learn much more about the different types of cell references in Chapter 7.)
The Task Pane is the hub of activity in Excel. It provides information and controls to let you accomplish a specific task, like searching for help, inserting clip art, or sharing a spreadsheet. The task pane is a genuine improvement over the old way of getting work done in Excel. Instead of forcing you to dig through several different menus, the Task Pane gathers everything you need into one convenient location. It also allows you to see your spreadsheet data at all times and continue working with it (Figure 1-8). Other Excel windows aren’t nearly as polite—they lock you out of your spreadsheet and obscure your data until you’re finished with them. For example, if you choose to format a group of cells, you can’t edit any information in the Excel grid until you finish using the Format Cells dialog box and click OK.
Unfortunately, the Task Pane only lets you perform a limited number of tasks, so it doesn’t replace Excel’s menus and toolbars. Still, it’s handy. To select the task that you want to perform, click the title of the Task Pane window. Figure 1-8 shows the list of possible tasks.
Tip
The Task Pane window often uses hyperlinks , which look like ordinary text, but you can click them to trigger an action, just like you click a toolbar button or pick an option from the menu. To find out if a given text item is a hyperlink, hover over it with the mouse. If the text becomes underlined, it’s a hyperlink. (Try the “Create a new workbook” link for example, which you can see at the bottom of Figure 1-8.)
Figure 1-8. When you first start Excel, the Task Pane appears on the right with the Getting Started task displayed. You can switch to any of 10 other tasks by clicking the drop-down arrow in the window title and choosing the task from the list. The current task is identified with a checkmark.
You can close the Task Pane window at any time by clicking the “x” in the top-right corner of the window. Or, you can quickly hide or show the Task Pane by choosing View → Task Pane.
All the Office applications include a Task Pane, but the list of available tasks differs. In Excel 2003, you can use the following tasks:
Getting Started. This task is the one you see when you first start Excel. It provides a list of links from Microsoft’s Office Web site (http://office.microsoft.com). For example, you can click “Get the latest news about using Excel” to open up your Internet browser with a list of bulletins about new developments on Planet Excel. You can also search the Office Web site for help files by using the “Search for” box. Finally, at the bottom of the Getting Started window are a couple of hyperlinks that let you open an existing spreadsheet or create a new one.
Help. This task lets you search the built-in Excel help files. If your computer is connected to the Internet, this search actually branches out to Microsoft’s online Excel documentation, where it retrieves the latest relevant information. For more information about getting help in Excel, check out Appendix A.
Search Results. The Search Results task window works in conjunction with the Help or Getting Started tasks. Both of these tasks allow you to search the online Office help. Once Excel has finished a search, the Search Results task window appears with a list of matching results.
Tip
You can navigate back to the Search Results task window at any time to see the results from your most recent search.
Clip Art. This extremely useful task lets you find clip art using a straightforward keyword search. For example, if you need to impress agricultural clients with a giant farm animal interposed between your columns of numbers, type “sheep” into the Clip Art search. After a short delay, a series of thumbnails appears. You can then drag these pictures directly onto your spreadsheet and resize them as needed. The Clip Art search can find matching pictures that are installed on your computer (as a part of Office 2003) and free graphics from the Office 2003 Web site. For more information about Excel and graphics, see Chapter 18.
Research. While the Clip Art task let you find the graphics you need, the Research task lets you find the information you want. This search can include looking a term up in a dictionary or thesaurus, or performing an online search for news articles, financial stock quotes, encyclopedia entries, and more. Best of all, you perform the search inside Excel, so you can drag and drop results into your worksheet. But as you’ll find out in Chapter 24, much of the research material is covered by exclusive subscription services that are only available if you’re willing to shell out some cold cash.
Clipboard. Excel lets you place multiple pieces of data on the clipboard at the same time. You can examine these pieces of data—which might be snippets of text, pictures, charts, or entire cell ranges—using the Clipboard task window. You can then remove items or drag them back onto your spreadsheet, giving you a great way to quickly rearrange your tables. For more information, see Section 3.2.3.
New Workbook. This task lets you create a new spreadsheet. You have the choice of creating a blank spreadsheet or creating a spreadsheet based on a prebuilt template. You’ll learn more about creating and saving new spreadsheets a little later in this chapter. Templates are covered in Chapter 15.
Shared Workspace and Document Updates. These tasks let you manage the way several people can collaborate on a spreadsheet through a shared workspace, although you can’t use these features unless you have SharePoint Server (an advanced collaboration tool included with Windows 2003 Server). Chapter 21 covers Excel collaboration.
XML Source. Excel 2003 introduces new features that let you import and export XML data, which is a special format used to organize information so that it’s more easily exchanged between different operating systems and different programs. With the help of these features, you can plug your spreadsheet into an automated business process. For example, you could send data from an expense report spreadsheet directly to an application that processes expenses. You’ll learn all about XML and the future of Office integration in Chapter 23.
Note
Occasionally, you’ll come across tasks you can’t easily reach from the Task Pane menu. For example, when you launch a file search from the File menu in Excel 2003 (as described at the end of this chapter), the Basic File Search task appears, but it isn’t available through the main Task menu. (In contrast, Excel 2002 does offer file searching as one of its Task Pane options.)
Excel, like any self-respecting Windows program, is filled with toolbars. You can think of toolbars as a lighter version of the Task Pane windows. Like the Task Pane, toolbars group together buttons for a common task (like manipulating pictures, creating charts, reviewing a document, and so on). Also like the Task Pane, toolbars stay out of your way while you work. But unlike the Task Pane, toolbars don’t provide much information to help you out if you aren’t already an expert. You can hover over a toolbar button to see a one- or two-word title for a button (as shown in Figure 1-9), but that’s about it.
Figure 1-9. Toolbars provide tooltip text that describes each button. Unfortunately, this text probably won’t tell you what you need to know if you’re trying to learn about a feature you’ve never used before.
To see a menu that lists all the available Excel toolbars (Figure 1-10), right click any toolbar, or choose View → Toolbars. The toolbars that have a checkmark next to their name are the ones that are currently visible. You can use this list to show new toolbars (select a toolbar that isn’t checked) or hide visible ones (click a checked toolbar).
Tip
Resist the urge to display all the toolbars at the same time, or you’ll end up with a heavily cluttered window that shmushes your worksheet into a tiny corner.
Excel provides a grand total of 19 toolbars (not including the Task Pane, which is really a completely different type of window). In addition, some third-party products and plug-ins automatically add Excel toolbars to your computer. For example, if you install the full version of Adobe Acrobat (the software for creating PDF files), you’ll find a new PDFMaker toolbar. Excel always shows third-party toolbars at the end of the toolbar list.
Note
To make life a little easier, Excel opens some toolbars automatically when you need them. For example, when you select a chart, the Chart toolbar springs into action at the top of the window, only to disappear again as soon as you select a different part of your spreadsheet. You see the same behavior when you create lists and work with pictures.
Excel starts you off with two important toolbars:
Standard. This toolbar includes the most commonly used buttons, like those for saving, opening, and printing spreadsheets. It also holds buttons for cutting and pasting data, undoing changes, and inserting charts.
Formatting. This toolbar includes buttons for changing text font, alignment, and style. You’ll learn about these options in Chapter 4.
Both of these toolbars use a standard arrangement of buttons that is closely replicated in other Office applications, like Microsoft Word.
Figure 1-10. Excel provides a wealth of toolbars. In this picture, only two toolbars are currently displayed: the all-important Standard and Formatting toolbars. To show other toolbars, just choose View → Toolbars and select them by clicking on their name in the menu. Or, just right-click anywhere on one of the currently displayed toolbars, as shown here.
You can drag toolbars anywhere on the Excel window, which is useful, for instance, if you’re looking for ways to maximize the amount of screen space you have. To move a toolbar, follow these three steps:
Position your mouse over the left edge of the toolbar.
The left edge of the toolbar has a series of dots to show you where the toolbar “grip” is. The mouse pointer changes to a four-way arrow to indicate you’re in the right place.
Start dragging the toolbar.
As you move the toolbar, other toolbars automatically rearrange themselves.
Release the mouse when you’ve got the toolbar in the desired position.
Figure 1-11 shows some of the different ways you can position toolbars, and you can always drag toolbars back to their original positions.
Figure 1-11. You can arrange toolbars in various places in the Excel window. For example, you can drag toolbars above the menu or to the left or right side of the window, in which case they change from a horizontal row of buttons to a vertical column of buttons. You can even drag a toolbar away from the window’s edge so that it becomes a standalone floating window, like this Picture toolbar. You can resize floating windows in the same way you resize any other window.
Toolbars remember their last position. Thus, if you move a toolbar and then hide it, it appears in its new position the next time you show it.
Depending on the width of your window and the arrangement of your toolbars, some buttons on a toolbar may be invisible. You can tell if some are in hiding by examining the right edge of the toolbar. If you see a symbol that includes two tiny triangles you know that additional items lurk beneath the surface. Figure 1-12 shows the tell-tale sign and the trick for revealing the hidden buttons.
When you click the arrow on the right edge of a toolbar, Excel shows you not only any hidden buttons, but a couple of toolbar options, too. Add or Remove Buttons lets you customize the toolbar. (To learn more about customization, see Appendix B.) You can also choose “Show Buttons on One Row” to put all the buttons from the Standard and Formatting toolbars on one horizontal row at the top of the window. While this arrangement gives you more space for your data, it also hides most of the buttons on your toolbars, which leads to extra clicking around when you need those babies.
The Formula bar appears above the worksheet grid but below the other Excel toolbars (Figure 1-13). It displays the address of the active cell (like A1) on the left edge, and it also shows you the content of the current cell.
You can use the Formula bar to enter and edit data, instead of editing directly in your worksheet. This approach is particularly useful when a cell contains a formula or a large amount of information, because you have more space to work with in the Formula bar than in a typical cell. Just as with in-cell edits, formula bar edits let you press Enter to confirm your changes or Esc to cancel them. You can also use the mouse: between the cell address and the box showing its contents, click the green checkmark to commit your modification or the red “X” to roll it back.
Note
You can hide (or show) the Formula bar by choosing View → Formula Bar. But it’s such a basic part of Excel that it would be unwise to get rid of it. Instead, keep it around until Chapter 7, when you’ll learn how to build formulas.
Figure 1-13. The Formula bar (just above the grid) shows information about the active cell. In this example, the Formula bar shows that the current cell is B4 and that it contains the number 592. Instead of editing this value in the worksheet, you can click in the Formula bar and make your changes there.
Though people often overlook it, the Status bar (Figure 1-14) is a good way to keep on top of Excel’s current state. For example, if you save or print a document, the Status bar, which is located at the bottom of the Excel window, shows the progress of the printing process. If you’re performing a quick action, the progress indicator may disappear before you have a chance to even notice it. But if you’re performing a time-consuming operation—say, printing out an 87-page table of the airline silverware you happen to own—you can look to the Status bar to see how things are coming along. (To hide or show the Status bar, choose View → Status Bar.)
Usually the Status bar displays one of two things:
The word “Ready." Ready means that Excel isn’t doing anything much at the moment, other than waiting for you to take some action.
The word “Edit." Edit means the cell is currently in Edit mode, and pressing the left and right arrow keys moves through the cell data, instead of moving from cell to cell. As discussed in Quick Ways to Add Data, you can place a cell in edit mode or take it out of edit mode by pressing F2.
In addition, the compartments at the rightmost side of the Status bar give you a few other pieces of information, like whether Caps Lock is turned on. These special values are described in Table 1-2.
Figure 1-14. The Status bar is an always available (but often overlooked) part of the Excel window. In it, you can see the basic status text (which just says “Ready” in this example) and several compartments on the right that display various indicators when they’re active (like “CAPS” and “SCRL” in this example).
Table 1-2. Status Bar Indicators
|
Indicator |
Meaning |
|---|---|
|
CAPS |
Every letter you type in is automatically capitalized. To turn this feature on or off, hit the Caps Lock key. |
|
Many people find it fastest to use the numeric keypad (typically at the right side of your keyboard) to type in numbers. When this sign is off, the numeric keypad controls cell navigation instead. To turn this feature on or off, press the Num Lock key. | |
|
When this sign is on and you use the arrow keys, the worksheet scrolls as normal, but the active cell doesn’t change. This feature allows you to look at all the information you have in your worksheet without losing track of the place you’re currently in. You can turn this feature on or off by pressing the Scroll Lock key. | |
|
Overwrite mode is turned on. If you type new characters in edit mode, they overwrite existing characters (rather than displacing them). You can turn this feature on or off by pressing the Insert key. | |
|
You have pressed the End key, which is the first key in a two-key combination; the next key determines what happens. For example, hit End and then Home to move to the bottom right cell in your worksheet. See Table 1-1 for a list of key combinations, some of which use End. | |
|
As you press the arrow keys, Excel automatically selects all the rows and columns you cross—in other words, your selection is extended, which is what the three-letter abbreviation refers to. Extended mode is a useful keyboard alternative to dragging your mouse to select swaths of the grid. To turn this mode on or off, press F8. You’ll learn more about selecting cells and moving them around in Chapter 3. | |
|
Indicates that Excel will automatically add a set number of decimal places to the values you enter in any cell. For example, if you set Excel to use two fixed decimal places and you type the number 5 into a cell, Excel actually enters 0.05. This seldom-used featured is handy for speed typists who need to enter reams of data in a fixed format. You can turn this feature on or off by selecting Tools → Options, choosing the Edit tab, and then turning on “Fixed decimal.” You’ll learn more about how to format numeric cells in Chapter 4. |
Note
You can also use the Status bar to display quick totals as you select groups of numbers. This trick is explained on Sidebar 3.1.
As everyone who’s been alive for at least three days knows, you should save your work early and often. Excel is no exception. You have two choices for saving a spreadsheet file:
Save As. This choice allows you to save your spreadsheet file with a new name. You can use Save As the first time you save a new spreadsheet, or you can use it to save a copy of your current spreadsheet with a new name, in a new folder, or as a different file type (alternate file formats are discussed below). To use Save As, select File → Save As, or press F12. Figure 1-15 shows you the Save As dialog box.
Figure 1-15. The Save As dialog box lets you jump to common folders, or you can browse a folder tree using the drop-down “Save in” menu. Type the file name at the bottom of the window, and pick the file type Finally, choose Tools → General Options from the top-right of the window for additional options.
Tip
Resaving a spreadsheet is an almost instantaneous operation, and you should get used to doing it all the time. After you’ve made any significant change, just hit Ctrl+S to make sure you’ve stored the latest version of your data.
Excel saves spreadsheets as .xls files (i.e., AirlineSilverware.xls). As hard-core Excel programming gurus know, this file format has a codename, BIFF8 (which, obviously, stands for Binary Interchange File Format). This is the format used by Excel 2003, Excel 2002, Excel 2000, and Excel 97. The codename is useful to know because earlier versions of Excel also store spreadsheets as .xls files, but the underlying format is actually different—and sometimes maddeningly irreconcilable.
For example, Excel 95 uses the incompatible BIFF7 file format, which means it can’t open a BIFF8 spreadsheet at all. And while Excel 97 uses BIFF8, it doesn’t support a feature called pivot charts, which were introduced in Excel 2000. Excel 97 can still open Excel 2000 workbooks that contain pivot charts, but it doesn’t let you manipulate them.
The good news is that if you need to exchange spreadsheet files with somebody who’s saddled with a Paleolithic spreadsheet application, you can save a copy of your spreadsheet in an older format. To do so, select File → Save As. Then choose the desired format from the “Save as type” drop-down list and click Save. Excel allows you to save your spreadsheet using a variety of different formats, including the classic Lotus and dBase formats from the DOS world. If you’re looking to view your spreadsheet using another program, use the CSV file type, which produces a comma-delimited text file that almost all spreadsheet applications on any operating system can read (comma-delimited means the information will have commas separating each cell). Figure 1-16 shows the Save As dialog box.
Occasionally, you might want to add confidential information to a spreadsheet—for example, a list of the airlines from which you’ve stolen spoons. If your computer is on a network, the solution may be as simple as storing your file in the correct, protected location. But if you’re afraid that you might inadvertently email the spreadsheet to the wrong people (say, executives at American Airlines), or if you’re about to expose systematic accounting irregularities in your company’s year-end statements, you’ll be happy to know that Excel provides a tighter degree of security. It allows you to password-protect your spreadsheets, which means anyone who wants to open them has to know the password you’ve set.
Figure 1-16. Excel offers a wide variety of file type options in the “Save as type” list, including options to save your Excel data as an HTML Web page or as an XML file that you can use in another application. You’ll learn more about these features in Chapter 24 and Chapter 23, respectively.
Excel actually has two layers of password protection that you can apply to a spreadsheet:
You can prevent others from opening your spreadsheet unless they know the correct password. This level of security, which scrambles your data for anyone without the password (a process known as encryption), is the strongest.
You can let others read a spreadsheet, but you can prevent them from modifying it unless they know the correct password.
You can apply one or both of these restrictions to a spreadsheet. Applying them is easy. Just follow these steps:
Select File → Save As.
The Save As dialog box appears.
In the Save As dialog box, choose Tools → General Options.
The Save Options dialog box appears.
Type a password next to the security level you want to turn on (as shown in Figure 1-17). Then click OK.
Figure 1-17. You can use any sequence of letters and numbers as a password. Passwords are case-sensitive (which means that PanAm is different from panam), and they are masked (which means that all that appears in the window as you type is a series of asterisks).
POWER USERS’ CLINIC: Is Your Encryption Strong Enough?
With Excel’s normal level of encryption, you’re not securing your data all that well. While the temp working in your office probably won’t be able to see anything you’ve protected, people who really want to crack into your spreadsheets can use third-party programs to extract your data and password from a password-protected, encrypted file. Some of these third-party tools are expensive, but they are available.
If this risk disturbs you, consider stepping up to a stronger level of encryption. Excel offers several types of cryptographic services that encrypt spreadsheet data with industrial-strength algorithms. If you use these stronger forms of encryption, you won’t be able to open your file in a version of Excel earlier than Excel XP. And if you lose your password, your data is also lost forever. The payoff, however, is near-bulletproof protection.
To use enhanced encryption, choose File → Save As, and in the dialog box that appears, click the Advanced button next to the “Password to open” text box. In the new window that appears (shown below), you can choose the type of cryptography. The best type for compatibility and security is “RC4, Microsoft Base Cryptographic Provider v1.0.” You can also choose a key length. (A key is a secret, random sequence of binary information that’s used to encode the message. The longer the key, the harder it is to break your document open using a brute force attack).
Keep in mind that encryption is a complex topic, and even this approach won’t necessarily stop a dedicated hacker with abundant computing resources. For best results, combine strong encryption with a password made up of at least 12 characters that incorporates letters and numbers and doesn’t use any dictionary words.
Click Save to store the file.
If you use a password to restrict people from opening the spreadsheet, you are prompted to supply the “password to open” the next time you open the file (Figure 1-18 top).
If you use a password to restrict people from modifying the spreadsheet, the next time you open this file you’ll be given the choice—shown in Figure 1-18 on the bottom—to open it in read-only mode (which requires no password) or to open it in full edit mode (in which case you’ll need to supply the “password to modify”).
Figure 1-18. Top: You can give a spreadsheet two layers of protection: assign a “password to open” and you’ll see this window when you open the file. Bottom: If you assign a “password to modify,” you’ll see the choices in this window. If you use both passwords, you’ll see both windows, one after the other.
The corollary to the edict “Save your data early and often” is the truism “Sometimes it’s not possible to catch everything before a sudden software, hardware, or power failure ends your Excel session early.” Fortunately, Excel includes an invaluable safety net called AutoRecover.
AutoRecover periodically saves backup copies of your spreadsheet while you work. If you suffer a system crash, you can retrieve the last AutoRecover backup even if you never managed to save the file yourself. Of course, even the AutoRecover backup won’t necessarily have all the information you entered in your spreadsheet before the problem occurred. But if AutoRecover saves a backup every 10 minutes (the standard), then at most you’ll lose 10 minutes of work.
AutoRecover comes switched on when you install Excel, but if you want to check, select Tools → Options, and then in the dialog box that appears, click the Save tab. Make sure that “Save AutoRecover info” is turned on. You can then choose a folder where you’d like Excel to save backup files (the standard folder works fine for most people, but feel free to pick some other place). Unfortunately, there’s no handy Browse button to help you find the folder, so you need to find the folder you want in advance (using a tool like Windows Explorer), write it down somewhere, and then copy the full folder path into this dialog box. You can also adjust the backup frequency in minutes (see Figure 1-19 for tips on timing).
Figure 1-19. You can configure how often AutoRecover saves backups. There’s really no danger in being too frequent. Unless you work with extremely complex or large spreadsheets—which might suck up a lot of computing power and take a long time to save—you can set Excel to save the document every five minutes with no appreciable slowdown.
If your computer does crash, when you get it running again, you can easily retrieve your last AutoRecover backup. In fact, once you restart Excel, it automatically checks the backup folder, and, if it finds a backup, it opens a special Document Recovery window on the left of the Excel window, as shown in Figure 1-20.
If you attempt to open a backup file that’s somehow been scrambled (technically known as corrupted), Excel automatically attempts to repair it. You can choose Show Repairs to display a list of any changes Excel had to make to recover the file .
Opening existing files in Excel works much the same as it does in any Windows program. The only difference is that Excel gives you two different ways to get to the standard Open dialog box. Here are your options:
Select File → Open.
Use the Task Pane (Section 1.3.2). In Excel 2003, click the Open link at the bottom of the Getting Started task. Or, in Excel 2002, look under the “Open a workbook” heading in the New Workbook task, and click the “More workbooks” link.
Note
If the Task Pane is not currently visible, you can always choose View → Task Pane from the menu.
Both of these methods bring up the Open dialog box. Using this dialog box, you can browse to find the spreadsheet file you want, and then click Open to load it into Excel.
Excel can open many file types other than its native .xls format. To learn the other formats it supports, pull up the Open dialog box, and, at the bottom, open the “Files of type” menu, which shows you the whole list. If you want to open a file but you don’t know what format it’s in, try using the first option on the menu, “All Files.” Once you choose a file, Excel scans the beginning of the file and informs you about the type of conversion it will attempt to perform (based on what type of file Excel thinks it is).
![Top: Every time Excel starts up, it looks for AutoRecover backups. If it finds a backup, that means a document was not properly saved the last time you exited from Excel. Excel then opens a bar at the side of your window with all the backup files it finds, and lists their statuses. [Recovered] means the file backup is ready and error-free, while [Original] indicates the file was saved by you, and Excel didn’t make a backup after your last save. (The only time you’ll see the original file in the Document Recovery window is when Excel (or your computer) crashes, but there isn’t any unsaved data or backup file. In this situation, you don’t need to worry since you haven’t lost any data.) Bottom: You can save or open an AutoRecover backup like an ordinary Excel file; simply click the item in the list. Once you’ve dealt with all of the backup files, close the Document Recovery window by clicking the Close button.](/api/v2/epubs/0596006640/files/httpatomoreillycomsourceoreillyimages111012.png)
Figure 1-20. Top: Every time Excel starts up, it looks for AutoRecover backups. If it finds a backup, that means a document was not properly saved the last time you exited from Excel. Excel then opens a bar at the side of your window with all the backup files it finds, and lists their statuses. [Recovered] means the file backup is ready and error-free, while [Original] indicates the file was saved by you, and Excel didn’t make a backup after your last save. (The only time you’ll see the original file in the Document Recovery window is when Excel (or your computer) crashes, but there isn’t any unsaved data or backup file. In this situation, you don’t need to worry since you haven’t lost any data.) Bottom: You can save or open an AutoRecover backup like an ordinary Excel file; simply click the item in the list. Once you’ve dealt with all of the backup files, close the Document Recovery window by clicking the Close button.
Note
Depending on your computer settings, Windows might hide file extensions. That means that instead of seeing the Excel spreadsheet file MyCoalMiningFortune.xls, you’ll just see the name MyCoalMiningFortune (without the .xls part on the end). In this case, you can still tell what the file type is by looking at the icon. If you see a small Excel icon next to the file name, that means Windows recognizes that the file is an Excel spreadsheet. If you see something else (like a tiny paint palette, for example), you need to make a logical guess about what type of file it is.
When you open a file or save a file for the first time, Excel starts you off in the My Documents folder. This is a Windows-specific folder that many programs assume you use for all your files. If you don’t use My Documents, you can tell Excel to look elsewhere when saving and opening files. To do so, select Tools → Options. In the Options dialog box, click the General tab. You can modify the “Default file location” text box so that it points to the folder where you usually store files (as in c:\John Smith\MyExcel Files). Sadly, you can’t browse and pick the path from a dialog box—instead, you need to type it in by hand.
GEM IN THE ROUGH: Opening Files—with a Twist
The Open dialog box harbors a few tricks. To see these hidden secrets, first select the file that you want to use (by clicking it once, not twice), and then click the drop-down arrow on the right-side of the Open button. A menu with several additional options appears, as shown here.
Here’s what these different choices do:
Open opens the file in the normal way.
Open Read-Only opens the file, but won’t allow you to save changes. This option is great if you want to make sure you don’t accidentally overwrite an existing file. (For example, if you’re using last month’s sales invoice as a starting point for this month’s sales invoice, you might use Open Read-Only to make sure you can’t accidentally wipe out the existing file.) If you open a document in read-only mode, you can still make changes—you just have to save the file with a new file name (choose File → Save As).
Open as Copy creates a copy of the spreadsheet file in the same folder. If your file is named Book1.xls, the copy will be named Copy of Book1.xls. This feature comes in handy if you’re about to start editing a spreadsheet and want to be able to look at the last version you saved. Excel won’t let you open the same file twice. However, you can load the previous version by selecting the same file and using Open as Copy. (Of course, this technique only works when you have changes you haven’t saved yet. Once you save the current version of a file, the older version is overwritten and lost forever.)
Open in Browser is only available when you select an HTML file. This option allows you to open a spreadsheet for viewing in your computer’s Web browser (usually Internet Explorer), which is something you’ll only want to attempt when trying to convert Excel files to Web pages (Section 24.1).
Open and Repair is useful if you need to open a file that is corrupted. If you try to open a corrupted file by just clicking Open, Excel warns you that the file has problems and refuses to open it. To get around this problem, you can open the file using the Open and Repair option, which prompts Excel to make the necessary corrections, display them for you in a list, and then open the document. Depending on the type of problem, you might not lose any information at all.
There’s one other interesting option in the General tab. You can use the “At startup, open all files in” text box to specify a folder where you put all the Excel files you’re currently working with. Then, the next time you start Excel, it automatically opens every .xls file it finds in a separate Excel window. Of course, if you decide to use this option, make sure you don’t clutter your in-progress folder with too many files, or Excel will open a dizzying number of windows when it starts.Opening Multiple Spreadsheets at Once
As you open multiple spreadsheets, Excel creates a new window for each one. You can easily jump from one spreadsheet to another by clicking the appropriate spreadsheet button in the Windows taskbar at the bottom of your screen (see Figure 1-21, top).
If you’re using Windows XP, you’ll find that your computer has an odd habit of spontaneously bunching together taskbar buttons. For example, shortly after you open three Excel files, you might find them in one task bar button (see Figure 1-21, bottom).
Figure 1-21. Top: When you have multiple spreadsheets open at the same time, you can easily move from one to the other using the taskbar. Bottom: In Windows XP, similar taskbar buttons sometimes get bunched into groups. You can tell that a button contains a group of files when a drop-down arrow appears on the right side of the button, and a number appears on the left side. The number indicates how many buttons Windows has grouped together.
Automatic taskbar bunching does save screen space, but it also makes it a little more awkward to get to the Excel spreadsheet you want. You now need two mouse clicks instead of one—the first to click the taskbar button, and the second to choose the window you want from the group.
Tip
If the taskbar bunching in Windows XP seems like more trouble than it’s worth, you can switch off this behavior. Just right-click on an empty space in the taskbar and choose Properties. In the Taskbar and Start Menu Properties dialog box that appears, clear the checkmark next to the “Group similar taskbar buttons” option.
The taskbar, though convenient, isn’t perfect. One problem is that long file names don’t fit on the taskbar buttons, which can make it hard to spot the files you need. And the struggle to find an open file becomes dire if your taskbar is also cluttered with other applications and their multiple windows.
Fortunately, Excel provides a couple of shortcuts that are indispensable when dealing with several spreadsheets at a time:
To jump from one spreadsheet to another, pick the spreadsheet from Excel’s Window menu, which lists the full file name of all the currently open spreadsheets (Figure 1-22).
To move to the next spreadsheet, use the keyboard shortcut Ctrl+Tab or Ctrl+F6.
To move to the previous spreadsheet, use the shortcut key Ctrl+Shift+Tab or Ctrl+Shift+F6.
Figure 1-22. When you have multiple spreadsheets open at the same time, you can easily move from one to the other using the Window menu in Excel. The Window menu has the advantage of always showing the full file name.
When you have multiple spreadsheets open at the same time, you need to take a little more care when closing a window so you don’t accidentally close the entire Excel application—unless you want to. Here are your choices:
You can close all the spreadsheets at once. To do so, you need to close the Excel window. Select File → Exit from the menu in any active spreadsheet.
You can close a single spreadsheet. To do so, right-click the spreadsheet on the taskbar, and click Close. Or, switch to the spreadsheet you want to close (by clicking the matching taskbar button) and then choose File → Close from the Excel menu.
Note
One of the weirdest limitations in Excel occurs if you try to open more than one file with the same name. No matter what steps you take, you can’t coax Excel to open them both. It doesn’t matter if the files have different content or if they’re in different folders or even different drives. When you try to open a file that has the same name as a file that’s already open, Excel displays an error message and doesn’t do anything. Sadly, the only solution is to open the files one at a time, or rename one of them.
Modern hard drives hold dozens of gigabytes, layers and layers of subfolders—and files that wind up strewn everywhere. Misplacing a file in a subfolder is easier than spilling coffee on your keyboard and can lead to a mad panic the next time you try to find the document.
Windows includes tools for searching your hard drive, but they don’t always work with all types of content. Excel goes one step further by including its own tool that is fine-tuned for searching Office files. Using it, you can hunt for spreadsheet files in specific locations, containing specific text.
To use Excel’s file search feature, follow these steps:
If you’re using Excel 2003, Select File → File Search. In Excel 2002, click the drop-down arrow in the Task Pane, and choose the Search task.
The Basic File Search task appears in the Task Pane.
Enter the words you want to search for in the “Search text” box.
You can enter one or more words to search for. For example, you could try airline or silverware or airline silverware. Bear in mind that the more words you enter, the more specific your search and the more likely you are to find a relevant match. In addition, you can use the ? and * characters as wildcards, which are symbols that stand in for unknown text and can really enhance a search. In Excel’s search, the asterisk (*) represents a group of one or more characters. For example, a search for s*nd matches documents that contain sand, sound, send, or even the bizarre series of characters sgrthdnd. The question mark (?) represents any single character. For example, f?nd matches documents that contain find or fund but not friend.
From the “Search in” drop-down list, choose the locations where you want to search.
When you expand the “Search in” list, Excel shows you a tree of drives and folders on your computer (Figure 1-23, top), which is similar (though not identical) to the tree in Windows Explorer. Expand the appropriate drives where you want to search (click them or the plus signs next to them), and choose the folders that might have the file you’re looking for by clicking them. In general, a good place to search is the My Documents folder (under My Computer), which is a standard place to store documents and which tends to junk up and become Land of the Lost.
Figure 1-23. Left: In all searches, you need to tell Excel where you want to search, and what type of files you want to find. In this example, the file search will examine the root directory of drive C: (note the ordinary checkmark), but it won’t look in any subdirectories. On the other hand, it will search the My Document folder (note the checkmark with multiple boxes) and all contained subdirectories. Right: The file type list is more straightforward. This search will only find Excel files.
Excel gives you two ways to select a folder. Click once to place a checkmark next to the folder. This sign indicates that the search will include the selected folder, but it won’t branch out to cover subfolders. Click twice to place a checkmark with multiple boxes underneath it. This icon indicates that the search will include the selected folder and all the subfolders it contains. If you expand the folder, you can see that all the subfolders now have a checkmark icon to indicate they are also included.
From the “Results should be” drop-down list, choose the types of files you want to search for.
As shown in Figure 1-23 (bottom), Excel distinguishes between three main categories of files: Office documents, Web pages, and Outlook items (like email messages). Usually, Excel automatically includes Web pages and common Office document formats (Word documents, spreadsheets, PowerPoint presentations, and so on) in a search. In most cases, however, you’re only interested in Excel spreadsheets. Clear all the other checkboxes to speed up your search and reduce false matches.
Click Go to start the search.
The Task Pane switches to the Search Results task, which shows the current search progress and the list of results that Excel has found so far (Figure 1-24). If Excel finds no results, the search ends by displaying the message “No Results Found.”
If Excel finds files, select one from the search results, and open it.
If your search has turned up some results, you can open them directly from the Search Results task. Simply click the file once. If your results contain non-Excel files, when you click one, the appropriate program opens automatically. For example, if you click a Word document, a new Word window opens with the file.
If your search turns up a large number of results, Excel doesn’t show them all at once. Instead, it includes a link at the bottom of the result list indicating how many results remain to be viewed (for example, “Next 17 results”). Click this link to show the next page of results.
Tip
To get more information about a file in the Search Results task window, just hover over it with the mouse. A tooltip appears, detailing exactly where the file is on your hard drive.
Instead of opening the file, you can choose to copy the file path to the clipboard (which is useful if you want to open it in another program), or open the Properties window that shows information about the file—such as its size and author. To open a menu with these options, hover over the file in the search results list, and then click the drop-down arrow that appears to the right of the file.
Get Excel 2003: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
