Most people don’t need much convincing to use Excel, Microsoft’s premier spreadsheet software. The program comes preinstalled on a lot of computers, making it the obvious choice for millions of number crunchers. Despite its wide use, however, few people know where to find Excel’s most impressive features or why they’d want to use them in the first place.
The Excel portion of this book fills that void, explaining everything from basic Excel concepts to time-and frustration-saving shortcuts. You’ll learn how Excel 2007 works, when to steer clear of obscure options that aren’t worth the trouble to learn, and how to home in on the hidden gems that’ll win you the undying adoration of your coworkers, family, and friends—or at least your accountant.
Of course, every Excel grandmaster needs to start somewhere. In this chapter, you’ll create a spreadsheet and learn how to move around in it, enter basic information, and save it for posterity. Along the way, you’ll take a quick tour of the Excel window, and stop to meet the different tabs in the ribbon, the status bar, and the formula bar.
Note
The Excel portion of this book is written with Microsoft’s latest and greatest release in mind: Excel 2007. This section won’t help you if you’re using an earlier version of Excel, because Microsoft has dramatically changed Excel’s user interface (the “look and feel” of the program). However, if you’re an unredeemed Excel 2003 or Excel 2002 fanatic, you can get help from another O’Reilly book, which is simply named Excel 2003: The Missing Manual. The Mac version of Excel is covered in Office 2004 for Macintosh: The Missing Manual.
Excel and Word are the two powerhouses of the Microsoft Office family. While Word lets you create and edit documents, Excel specializes in letting you create, edit, and analyze data that’s organized into lists or tables. This grid-like arrangement of information is called a spreadsheet. Figure 9-1 shows an example.
Figure 9-1. This spreadsheet lists nine students, each of whom has two test scores and an assignment grade. Using Excel formulas, it’s easy to calculate the final grade for each student.
Tip
Excel shines when it comes to numerical data, but the program doesn’t limit you to calculations. While it has the computing muscle to analyze stacks of numbers, it’s equally useful for keeping track of the DVDs in your personal movie collection.
Some common spreadsheets include:
Business documents like financial statements, invoices, expense reports, and earnings statements.
Personal documents like weekly budgets, catalogs of your Star Wars action figures, exercise logs, and shopping lists.
Scientific data like experimental observations, models, and medical charts.
These examples just scratch the surface. Resourceful spreadsheet gurus use Excel to build everything from cross-country trip itineraries to logs of every Kevin Bacon movie they’ve ever seen.
Note
Keen eyes will notice that Table 9-1 doesn’t include the omnipresent Excel ribbon, which usually sits atop the window, stacked with buttons. That’s because it’s been collapsed neatly out of the way to let you focus on the spreadsheet. You’ll learn how to use this trick yourself on The Tabs of the Ribbon.
Excel’s not just a math wizard. If you want to add a little life to your data, you can inject color, apply exotic fonts, and even check your spelling. And if you’re bleary-eyed from staring at rows and rows of spreadsheet numbers, you can use Excel’s many chart-making tools to build everything from 3-D pie charts to more exotic scatter graphs. Excel can be as simple or as sophisticated as you want it to be.
Figure 9-2. By pressing Alt+E, you’ve triggered the “imaginary” Edit menu from Excel 2003, and earlier versions. You can’t actually see it (because in Excel 2007 this menu doesn’t exist). However, the tooltip lets you know that Excel is paying attention. You can now complete your action by pressing the next key for the menu command you’re nostalgic for.
The slick new ribbon (The Ribbon) is Excel’s most dramatic change, but it’s not the only new feature in Excel 2007. Other hot additions include:
Fewer limits. Excel worksheets can now be bigger, formulas can be more complex, and cells can hold way more text. Although 99.87 percent of Excel fans never ran into any of these limits in previous versions, it’s nice to know that the Excel engine continues to get more powerful.
Faster speeds. One of the newest pieces of computing hardware is a dual core CPU. (The CPU is the brain of any computer.) A dual core CPU can perform two tasks at once, but it performs best with software that knows how to take advantage of the way it works. Excel 2007 knows all about dual core CPUs, which means intense calculations are even faster on these computers.
Better-looking charts. Excel charts have always been intelligent, but they’ve never made good eye candy. Excel 2007 shakes things up with a whole new graphics engine that lets you add fantastic looking charts to your spreadsheets.
Formula AutoComplete. The latest in a whole bunch of auto-do-something features, formula AutoComplete just might be the most helpful innovation yet. It prompts you with possible values when you type in complex formulas.
Tables. When Microsoft created Excel 2003, they added a wildly popular list feature that helped people manage lists of information. In Excel 2007, lists morph into tables and get even more powerful.
Save-as-PDF. A PDF file is Adobe’s popular electronic document format that lets you share your work with other people, without losing any of your formatting (and without letting them change any of your numbers). Due to legal head-aches, this feature didn’t quite make it into the Excel 2007 installation, but it’s available as a free download from Microsoft. Chapter 1 has the details.
Of course, this list is by no means complete. Excel 2007 is chock-full of refinements, tweaks, and tune-ups that make it easier to use than any previous version. You’ll learn all the best tricks throughout this book. And if you’ve used a previous version of Excel, look for the “Nostalgia Corner” boxes, which tell how things have changed.
Note
The material in this part of the book is based on Excel 2007: The Missing Manual (O’Reilly). That book is a truly complete reference for Excel 2007, covering every feature, including geeky stuff like XML, VBA, ERROR.TYPE( ) functions, and other things you’ll probably never encounter–or even want to. But if you get really deep into Excel and want to learn more, Excel 2007: The Missing Manual can be your trusted guide.
When you first launch Excel, it starts you off with a new, blank worksheet, as shown in Figure 9-3. A worksheet is the grid of cells where you type your information and formulas; it takes up most of the window. 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 9-3. The largest part of the Excel window is the worksheet grid where you type in your information.
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 9-4 shows this cell, which looks like a rectangular box. Incidentally, an Excel cell can hold up to 32,000 characters.
A worksheet can span an eye-popping 16,000 columns and 1 million rows. 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 work-sheet. Every spreadsheet file can hold a virtually unlimited number of work-sheets, as you’ll learn in Chapter 12.
Figure 9-4. 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.
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
Obviously, once you go beyond 26 columns, you run out of letters. Excel handles this by doubling up (and then tripling up) letters. For example, column Z is followed by column AA, then AB, then AC, all the way to AZ and then BA, BB, BC–you get the picture. And if you create a ridiculously large worksheet, you’ll find that column ZZ is followed by AAA, AAB, AAC, and so on.
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 file. If you’ve already got Excel open and you want to create another workbook, just select Office button → New. This step pops up the New Workbook window that’s shown in Figure 9-5.
Note
A workbook is a collection of one or more worksheets. That distinction isn’t terribly important now because you’re using only a single worksheet in each workbook you create. But in Chapter 12, you’ll learn how to use several worksheets in the same workbook to track related collections of data.
For now, all you need to know is that the worksheet is the grid of cells where you place your data, and the workbook is the spreadsheet file that you save on your computer.
Figure 9-5. The New Workbook window lets you create a new, blank workbook. Choose Blank Workbook (in the window’s middle section), and then click Create to get started with an empty canvas.
You don’t need to pick the file name for your workbook when you first create it. Instead, that decision happens later, when you save your workbook (Saving Files). For now, you start with a blank canvas that’s ready to receive your numerical insights.
Note
Creating new workbooks doesn’t disturb what you’ve already done. Whatever workbook you were using remains open in another window. You can use the Windows taskbar to move from one workbook to the other. Opening Multiple Spreadsheets at Once shows the taskbar close up.
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 9-6 shows the difference.
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 today’s date—there’s no point in wasting the space. Adding information is easy—just click the cell you want and start typing. When you’re finished, hit Tab to complete your entry and move to the next cell to the right (or Enter to head to the cell just underneath).
Figure 9-6. Top: If you enter the first and last names together in one column, Excel can sort only by 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. 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.
Note
The information you put in an Excel worksheet doesn’t need to be in neat, ordered columns. Nothing stops you from scattering numbers and text in random cells. However, most Excel worksheets resemble some sort of table, because that’s the easiest and most effective way to deal with large amounts of structured information.
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 9-7 shows how you can adjust column width for proper breathing room.
Figure 9-7. 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. Bottom: When you release the mouse, the entire column of cells is resized to the new size.
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 9-8 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 9-8. 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), indicating 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 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 get a cell into edit mode by double-clicking it.
Complete your edit.
Once you’ve modified the cell content, 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. 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: cells in adjacent columns can overlap one another. Figure 9-9 shows the problem. One way to fix this problem is to manually resize the column, as shown in Figure 9-7. Another option is to use wrapping to fit multiple lines of text in a single cell, as described on Alignment and Orientation.
Figure 9-9. Overlapping cells can create big headaches. For example, if you type a large amount of text into A1, and then you type some text into B1, you see only part of the data in A1 on your worksheet (as shown here). The rest is hidden from view. 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:
Use the arrow keys on the keyboard. Keystrokes move you one cell at a time in any direction.
Click the cell with the mouse. A mouse click jumps you directly to the cell you’ve clicked.
As you move from cell to cell, you see the black focus box move to highlight the currently active cell. In some cases, you might want to cover ground a little quicker. You can use any of the shortcut keys listed in Table 9-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.
Table 9-1. Shortcut Keys for Moving Around a Worksheet
|
Result | |
|---|---|
|
→ (or Tab) |
Moves one cell to the right. |
|
← (or Shift+Tab) |
Moves one cell to the left. |
|
↑ |
Moves one cell up. |
|
↓ (or Enter) |
Moves one cell down. |
|
Page Up |
Moves up one screen. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows up (unless you are already at the top of the worksheet). |
|
Page Down |
Moves down one screen. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows down. |
|
Home |
Moves to the first cell (column A) of the current row. |
|
Ctrl+Home |
Moves to the first cell in the top row, which is A1. |
|
Ctrl+End (or End, Home) |
Moves to the last column of the last occupied row. This cell is at the bottom-right edge of your data. |
Note
Shortcut key combinations that use the + sign must be entered together. For example, “Ctrl+Home” means you hold down Ctrl and press Home at the same time. Key combinations with a comma work in sequence. For example, the key combination “End, Home” means press End first, release it, and then press Home.
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 uncharted 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.
In the introduction you learned about the ribbon, the super-toolbar that offers one-stop shopping for all of Excel’s features. All the most important Office applications—including Word, Access, PowerPoint, and Excel—use the new ribbon. However, each program has a different set of tabs and buttons.
Figure 9-10. You’ll notice that in the Go To list, cell addresses are written a little differently than the format you use when you type 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 15.)
Throughout this book, you’ll dig through the different tabs of the ribbon to find important features. But before you start your journey, it’s nice to get a quick overview of what each tab provides. Here’s the lowdown:
Home includes some of the most commonly used buttons, like those for cutting and pasting information, formatting your data, and hunting down important bits of information with search tools.
Insert lets you add special ingredients like tables, graphics, charts, and hyper-links.
Page Layout is all about getting your worksheet ready for the printer. You can tweak margins, paper orientation, and other page settings.
Formulas are mathematical instructions that you use to perform calculations. This tab helps you build super-smart formulas and resolve mind-bending errors.
Data lets you get information from an outside data source (like a heavy-duty database) so you can analyze it in Excel. It also includes tools for dealing with large amounts of information, like sorting, filtering, and subgrouping.
Review includes the familiar Office proofing tools (like the spell checker). It also has buttons that let you add comments to a worksheet and manage revisions.
View lets you switch on and off a variety of viewing options. It also lets you pull off a few fancy tricks if you want to view several separate Excel spreadsheet files at the same time.
Figure 9-11. Do you want to use every square inch of screen space for your cells? You can collapse the ribbon (as shown here) by double-clicking any tab. Click a tab to pop it open temporarily, or double-click a tab to bring the ribbon back for good. And if you want to perform the same trick without raising your fingers from the keyboard, you can use the shortcut key Ctrl+F1.
The formula bar appears above the worksheet grid but below the ribbon (Figure 9-12). It displays the address of the active cell (like A1) on the left edge, and it also shows you the current cell’s contents.
Figure 9-12. 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 anywhere in the formula bar and make your changes there.
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. That’s because the formula bar gives you more work room than a typical cell. Just as with in-cell edits, you press Enter to confirm your changes or Esc to cancel them. Or you can use the mouse: When you start typing in the formula bar, a checkmark and an “X” icon appear just to the left of the box where you’re typing. Click the checkmark to confirm your entry, or “X” to roll it back.
Note
You can hide (or show) the formula bar by choosing View → Show/Hide → Formula Bar. But the formula bar’s such a basic part of Excel that you’d be unwise to get rid of it. Instead, keep it around until Chapter 15, when you’ll learn how to build formulas.
Ordinarily, the formula bar is a single line. If you have a really long entry in a cell (like a paragraph’s worth of text), you need to scroll from one side to the other. However, there’s another option—you can resize the formula bar so it fits more information, as shown in Figure 9-13.
Figure 9-13. To enlarge the formula bar, click the bottom edge and pull down. You can make it two, three, four, or many more lines large. Best of all, once you get the size you want, you can use the expand/collapse button on the right side of the formula bar to quickly expand it to your preferred size and collapse it back to the single-line view.
Though people often overlook it, the status bar (Figure 9-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 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.
Figure 9-14. In the status bar, you can see the basic status text (which just says “Ready” in this example), the view buttons (which are useful when you’re preparing a spreadsheet for printing), and the zoom slider bar (which lets you enlarge or shrink the current worksheet view).
The status bar combines several different types of information. The leftmost part of the status bar shows the Cell Mode, which displays one of three indicators.
The word “Ready” means that Excel isn’t doing anything much at the moment, other than waiting for you to take some action.
The word “Enter” appears when you start typing a new value into a cell.
The word “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 earlier, you can place a cell in edit mode or take it out of edit mode by pressing F2.
Farther to the right on the status bar are the view buttons, which let you switch to Page Layout View or Page Break Preview. These different views help you see what your worksheet will look like when you print it. They’re covered in Chapter 14.
The zoom slider is next to the view buttons, at the far right edge of the status bar. You can slide it to the left to zoom out (which fits more information into your Excel window at once) or slide it to the right to zoom in (and take a closer look at fewer cells). You can learn more about zooming on Zooming.
In addition, the status bar displays other miscellaneous indicators. For example, if you press the Scroll Lock key, a Scroll Lock indicator appears on the status bar (next to the “Ready” text). This indicator tells you that you’re in scroll mode. In scroll mode, the arrow keys don’t move you from one cell to another; instead, they scroll the entire worksheet up, down, or to the side. Scroll mode is a great way to check out another part of your spreadsheet without leaving your current position.
You can control what indicators appear in the status bar by configuring it. To see a full list of possibilities, right-click the status bar. A huge list of options appears, as shown in Figure 9-15. Table 9-2 describes the most useful status bar options.
Note
The Caps Lock indicator doesn’t determine whether or not you can use the Caps Lock key—that feature always works. The Caps Lock indicator just lets you know when Caps Lock mode is on. That way you won’t be surprised by an accidental keystroke that turns your next data entry INTO ALL CAPITALS.
Figure 9-15. Every item that has a checkmark appears in the status bar when you need it. For example, if you choose Caps Lock, the text “Caps Lock” appears in the status bar whenever you hit the Caps Lock key to switch to all-capital typing.
Table 9-2. Status Bar Indicators
|
Meaning | |
|---|---|
|
Cell Mode |
Shows Ready, Edit, or Enter depending on the state of the current cell, as described on The Status Bar. |
|
Caps Lock |
Indicates whether Caps Lock mode is on. When Caps Lock is on, every letter you type is automatically capitalized. To turn Caps Lock mode on or off, hit Caps Lock. |
|
Num Lock |
Indicates whether Num Lock mode is on. When this mode is on, you can use the numeric keypad (typically at the right side of your keyboard) to type in numbers more quickly. When this sign’s off, the numeric keypad controls cell navigation instead. To turn Num Lock on or off, press Num Lock. |
|
Indicates whether Scroll Lock mode is on. When it’s on, you can use the arrow keys to scroll the worksheet without changing the active cell. (In other words, you can control your scrollbars by just using your keyboard.) This feature lets you look at all the information you have in your worksheet without losing track of the cell you’re currently in. You can turn Scroll Lock mode on or off by pressing Scroll Lock. | |
|
Indicates when Overwrite mode is turned on. Overwrite mode changes how cell edits work. When you edit a cell and Overwrite mode is on, the new characters that you type overwrite existing characters (rather than displacing them). You can turn Overwrite mode on or off by pressing Insert. | |
|
End Mode |
Indicates that you’ve pressed End, which is the first key in many two-key combinations; 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 9-1 for a list of key combinations, some of which use End. |
|
Indicates the current Selection mode. You have two options: normal mode and extended selection. When you press the arrows keys and extended selection is on, Excel automatically selects all the rows and columns you cross. Extended selection is a useful keyboard alternative to dragging your mouse to select swaths of the grid. To turn extended selection on or off, press F8. You’ll learn more about selecting cells and moving them around in Chapter 11. | |
|
Shows the current page and the total number of pages (as in "What’s New in Office 2007 of What’s New in Office 2007“). This indicator appears only in Page Layout view (as described on Quick Printing). | |
|
Show the result of a calculation on the selected cells. For example, the Sum indicator shows the total of all the numeric cells that are currently selected. You’ll take a closer look at this handy trick on Using cell ranges with a function. | |
|
Shows the three view buttons that let you switch between Normal view, Page Layout View, and Page Break Preview (as described on Page Break Preview: A Bird’s-Eye View of Your Worksheet). | |
|
Zoom |
Shows the current zoom percentage (like 100 percent for a normal-sized spreadsheet, and 200 percent for a spreadsheet that’s blown up to twice the magnification). |
|
Shows a slider that lets you zoom in closer (by sliding it to the right) or out to see more information at once (by sliding it to the left). |
You might have already seen the Excel Options window, which provides a central hub where you can adjust how Excel looks, behaves, and calculates (see Figure 9-16). To get to this window, click the Office button, and then choose Excel Options on the bottom-right edge.
The top five sections in the Excel Options window let you tweak a wide variety of different details. Some of these details are truly handy, like the options for opening and saving files (which are described at the end of this chapter). Others are seldom-used holdovers from the past, like the option that lets Excel act like Lotus— an ancient piece of spreadsheet software—when you hit the “/” key.
Tip
Some important options have a small i-in-a-circle icon next to them, which stands for “information.” Hover over this icon and you see a tooltip that gives you a brief description about that setting.
Figure 9-16. The Excel Options window is divided into nine sections. To pick which section to look at, choose an entry from the list on the left. In this example, you’re looking at the Popular settings group. In each section, the settings are further subdivided into titled groups. You may need to scroll down to find the setting you want.
Beneath the top five sections are four more specialized sections:
Customize lets you put your favorite commands on the Quick Access toolbar, a maneuver you can learn more about in Appendix A.
Add-Ins lets you configure other utilities (mini-programs) that work with Excel and enhance its powers.
Trust Center lets you tweak Excel’s security settings that safeguard against dangerous actions (think: viruses).
Resources provides a few buttons that let you get extra diagnostic information, activate your copy of Office (which you’ve no doubt done already), and get freebies and updates on the Web.
While you’re getting to know Excel, you can comfortably ignore most of what’s in the Excel Options window. But you’ll return here many times throughout this book to adjust settings and fine-tune the way Excel works.
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 on Saving Your Spreadsheet in Older Formats.) To use Save As, select Office button → Save As, or press F12. Figure 9-17 shows you the Save As dialog box as it appears on a Windows XP computer. (The Windows Vista version of the Save As dialog box has all the same features, but way more style.)
Figure 9-17. The Save As dialog box lets you jump to common folders using the big buttons on the left, or you can browse a folder tree using the drop-down"Save in” menu. Once you’ve found the folder you want, type the file name at the bottom of the window, and then pick the file type. Finally, click Save to finish the job.
Save. This option updates the spreadsheet file with your most recent changes. If you use Save on a new file that hasn’t been saved before, it has the same effect as Save As: Excel prompts you to choose a folder and file name. To use Save, select Office button → Save, or press Ctrl+S. Or, look up at the top of the Excel window in the Quick Access toolbar for the tiny Save button, which looks like an old-style diskette.
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.
Since time immemorial, Excel fans have been saving their lovingly crafted spread-sheets in .xls files (as in AirlineSilverware.xls). Excel 2007 changes all that. In fact, it introduces a completely new file format, with the extension .xlsx (as in Airline-Silverware.xlsx).
At first glance, this seems a tad over the top. But the new file format has some real advantages:
It’s compact. The new Excel file format uses Zip file compression, so spreadsheet files are smaller—way smaller (as much as 75 percent smaller than their original sizes). And even though the average hard drive is already large enough to swallow thousands of old-fashioned Excel files, the new compact format is easier to email around.
It’s less error-prone. The new file format carefully separates ordinary content, pictures, and macro code into separate sections. (Macros are automated routines that perform a specific task in a spreadsheet.) Microsoft claims that this change makes for tougher files. Now, if a part of your Excel file is damaged (for example, due to a faulty hard drive), there’s a much better chance that you can still retrieve the rest of the information. (You’ll learn about Excel disaster recovery on Disaster Recovery.)
It’s extensible. The new file format uses XML (the eXtensible Markup Language), which is a standardized way to store information. XML storage doesn’t benefit the average person, but it’s sure to earn a lot of love from companies that plan to build custom software that uses Excel documents. As long as Excel documents are stored in XML, these companies can create automated programs that pull the information they need straight out of a spreadsheet, with-out going through Excel. These programs can also generate made-to-measure Excel documents all on their own.
For all these reasons, .xlsx is the format of choice for Excel 2007. However, Microsoft prefers to give people all the choices they could ever need (rather than make life really simple), and Excel file formats are no exception. Along with the standard .xlsx, there’s the closely related .xlsm cousin, which adds the ability to store macro code. If you’ve added any macros to your spreadsheet, Excel prompts you to use this file type when you save your spreadsheet.
Most of the time, you don’t need to think about Excel’s file format—you can just create your spreadsheets, save them, and let Excel take care of the rest. The only time you need to stop and think twice is when you need to share your work with other, less fortunate people who have older versions of Excel.
When you find yourself in this situation, you have two choices:
Save your spreadsheet in the old format. You can save a copy of your spread-sheet in the traditional .xls Excel standard that’s been supported since Excel 97. To do so, choose Office button → Save As → Excel 97-2003 Format.
Use a free add-in for older versions of Excel. People who are stuck with Excel 2000, Excel 2002, or Excel 2003 can read your Excel 2007 files—they just need a free add-in that’s provided by Microsoft. This is a good solution because it’s doesn’t require any work on your part. People with past-its-prime versions of Excel can find the add-in they need by surfing to www.microsoft.com/downloads and searching for “compatibility pack file formats” (or use the secret shortcut URL http://tinyurl.com/y5w78r).
Often, the best thing you can do is keep your spreadsheet in the newer format and save a copy in the older format (using Office button → Save As → Excel 97-2003 Format). You can then hand that copy out to your backward friends.
Some eccentric individuals have even older or stranger spreadsheet software on their computers. If you want to save a copy of your spreadsheet in a more exotic file type, you can choose Office button → Save As, and then find the desired format in the " Save as type” drop-down list (Figure 9-18). Excel lets you save your spreadsheet using a variety of different formats, including the classic Excel 95 format from a decade ago. If you’re looking to view your spreadsheet using a mystery 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 has commas separating each cell).
Figure 9-18. Excel offers a few useful file type options in the “Save as type” list. CSV format is the best choice for compatibility with truly old software (or when nothing else seems to work). If you’re a longtime Excel fan, you’ll notice that the list has been slimmed down a bit—for example, there’s no option to use the old dBase and Lotus formats from the DOS world.
Sometimes you want to save a copy of your spreadsheet so that people can read it even if they don’t have Excel (and even if they’re running a different operating system, like Linux or Apple’s OS X). In this situation, you have several choices:
Use the Excel Viewer. Even if you don’t have Excel, you can install a separate tool called the Excel Viewer, which is available from Microsoft’s Web site (search for “Excel Viewer” at www.microsoft.com/downloads). However, few people have the viewer, and even though it’s free, few want to bother installing it. And it doesn’t work on non-Windows computers.
Save your workbook as an HTML Web page. That way, all you need to view the workbook is a Web browser (and who doesn’t have one of those?). The only disadvantage is that you could lose complex formatting. Some worksheets may make the transition to HTML gracefully, while others don’t look very good when they’re squashed into a browser window. And if you’re planning to let other people print the exported worksheet, the results might be unsatisfactory.
Save your workbook as a PDF file. This gets you the best of both worlds—you keep all the rich formatting (so your workbook can be printed), and you let people who don’t have Excel (and possibly don’t even have Windows) view your workbook. The only disadvantage is that this feature isn’t included in the basic Excel package. Instead, you need to install a free add-in to get it.
To get the Save As PDF add-in, surf to www.microsoft.com/downloads and search for “PDF.” The links lead you to a page where you can download the add-in and install it with just a couple of clicks.
Once you install the Save As PDF add-in, all your Office applications have the ability to save their documents in PDF format. In Excel, you work this magic by choosing Office button → Save As → PDF, which brings up the “Publish as PDF” dialog box (Figure 9-19).
Figure 9-19. The “Publish as PDF” dialog box looks a lot like the Save As dialog box, except it has a Publish button instead of a Save button. You can switch on the “Open file after publishing” setting to tell Excel to open the PDF file in Adobe Reader assuming you have it installed) after the publishing process is complete, so you can check the result.
When you save a PDF file, you get a few extra options in the Save As dialog box. PDF files can be saved with different resolution and quality settings (which mostly affect any graphical objects that you’ve placed in your workbook, like pictures and charts). Normally, you use higher quality settings if you’re planning to print your PDF file, because printers use higher resolutions than computer monitors.
The “Publish as PDF” dialog box gives you some control over the quality settings with the “Optimize for” options. If you’re just saving a PDF copy so other people can view the information in your workbook, choose “Minimum size (publishing online)” to save some space. On the other hand, if there’s a possibility that the people reading your PDF might want to print it out, choose “Standard (publishing online and printing)” to save a slightly larger PDF that makes for a better printout.
Finally, if you want to publish only a portion of your spreadsheet as a PDF file, click the Options button to open a dialog box with even more settings. You can choose to publish just a fixed number of pages, just the selected cells, and so on. These options mirror the choices you get when sending a spreadsheet to the printer. You also see a few more cryptic options, most of which you can safely ignore. (They’re intended for PDF nerds.) One exception is the “Document properties” option—turn this off if you don’t want the PDF to keep track of certain information that identifies you, like your name.
Tip
Getting the Save As PDF add-in is a bit of a hassle, but it’s well worth the effort. In previous versions of Excel, people who wanted to create PDFs file had to get another add-in or buy the expensive full version of the Adobe Acrobat software. The Save As PDF feature was originally slated for inclusion in Excel (with no add-in required), but anti-trust concerns caused ultra-cautious Microsoft to leave it out.
The corollary to the edict “Save your data early and often” is the truism “Sometimes things fall apart quickly…before you’ve even had a chance to back up.” 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), at most you’ll lose 10 minutes of work.
AutoRecover comes switched on when you install Excel, but you can tweak its settings. Select Office → Excel Options, and then choose the Save section. Under the “Save workbooks” section, make sure that “Save AutoRecover information” is turned on. You can also make a few other changes to AutoRecover settings:
You can also adjust the backup frequency in minutes. Figure 9-20 has some tips on timing.
You can choose the 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.
Figure 9-20. 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.
Under the “AutoRecover exceptions” heading, you can tell Excel not to bother saving a backup of a specific spreadsheet. Pick the spreadsheet name from the list (which shows all the currently open spreadsheet files), and then turn on the “Disable AutoRecover for this workbook only” setting. This setting is exceedingly uncommon, but you might use it if you have a gargantuan spreadsheet full of data that doesn’t need to be backed up.
If your computer does crash, when you get it running again, you can easily retrieve your last AutoRecover backup. In fact, the next time you launch Excel, it automatically checks the backup folder, and, if it finds a backup, it opens a Document Recovery panel on the left of the Excel window.
If your computer crashes in mid-edit, the next time you open Excel you’ll probably see the same file listed twice in the Document Recovery window, as shown in Figure 9-21. The difference is the status. The status [AutoSaved] indicates the most recent backup created by Excel. The status [Original] indicates the last version of the file that you saved (which is safely stored on your hard drive, right where you expect it).
To open a file that’s in the Document Recovery window, just click it. You can also use a drop-down menu with additional options (Figure 9-21). Make sure you save the file before you leave Excel. After all, it’s just a temporary backup.
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.
Figure 9-21. You can save or open an AutoRecover backup just as you would an ordinary Excel file; simply click the item in the list. Once you’ve dealt with all the backup files, close the Document Recovery window by clicking the Close button. If you haven’t saved your backup, Excel asks you at this point whether you want to save it permanently or delete the backup.
Opening existing files in Excel works much the same as it does in any Windows program. To get to the standard Open dialog box, choose Office button → Open. Using the Open 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 .xlsx format. To learn the other formats it supports, launch 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).
Note
Depending on your computer settings, Windows might hide file extensions. That means that instead of seeing the Excel spreadsheet file MyCoalMiningFortune.xlsx, you’ll just see the name MyCoalMiningFortune (without the .xlsx 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.
Plan to take another crack at a recent spreadsheet? You can find the most recently opened documents in Excel’s Recent Documents list. To see this list, just open the Office button—it appears as a separate column on the right. The best part about the Recent Documents list is the way you can pin a document there so it stays forever, as shown in Figure 9-22.
Figure 9-22. To keep a spreadsheet around on the Recent Documents list, click the thumbtack on the right. It becomes green, and is now pinned in place. That means it won’t ever leave the list, no matter how many documents you open. If you decide to stop working with it later on, just click the thumbtack again to release it. Pinning is a great trick for keeping your most important files at your fingertips.
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.
If you have taskbar grouping switched on, you’ll find that your computer has an odd habit of spontaneously bunching together taskbar buttons. For example, shortly after you open four Excel files, you might find them in one taskbar button (see Figure 9-23). Taskbar grouping 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 grouping seems like more trouble than it’s worth, you can switch off this behavior. Just right-click 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.
Figure 9-23. 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.
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, find the window in the View → Window → Switch Windows list, which includes the file name of all the currently open spreadsheets (Figure 9-24).
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.
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 Office button → Exit Excel from any active spreadsheet, or just click the close icon (the infamous X button) in the top-righthand corner.
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 Office button → Close.
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 both of them at once. 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 refuses to go any further. Sadly, the only solution is to open the files one at a time, or rename one of them.