O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 1. Creating and Navigating Worksheets

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.

Creating a Basic Worksheet

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.

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.

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.

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.

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.

Starting a New 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.

Note

Creating new workbooks doesn't disturb what you've already done. Whatever workbook you were currently using remains open in another window (check the taskbar to move from one to the other).

Adding the Column Titles

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.

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.

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.

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.

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.

Adding Data

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.

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.

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required