In word, you can create electronic image files through the ____ tab in the backstage view.
Show
Chapter 1. Creating Your First SpreadsheetEvery Excel grandmaster needs to start somewhere. In this chapter, you’ll learn how to create a basic spreadsheet. First, you’ll find out how to move around Excel’s grid of cells, typing in numbers and text as you go. Next, you’ll take a quick tour of the Excel ribbon, the tabbed toolbar of commands that sits above your spreadsheet. You’ll learn how to trigger the ribbon with a keyboard shortcut, and collapse it out of the way when you don’t need it. Finally, you’ll go to Excel’s backstage view, the file-management hub where you can save your work for posterity, open recent files, and tweak Excel options. Starting a WorkbookWhen you first fire up Excel, you’ll see a welcome page where you can choose to open an existing Excel spreadsheet or create a new one (Figure 1-1). Figure 1-1. Excel’s welcome page lets you create a new, blank worksheet or a ready-made workbook from a template. For now, click the “Blank workbook” picture to create a new spreadsheet with no formatting or data. Excel fills most of the welcome page with templates, spreadsheet files preconfigured for a specific type of data. For example, if you want to create an expense report, you might choose Excel’s “Travel expense report” template as a starting point. You’ll learn lots more about templates in Chapter 16, but for now, just click “Blank workbook” to start with a brand-spanking-new spreadsheet with no information in it. NoteWorkbook is Excel lingo for “spreadsheet.” Excel uses this term to emphasize the fact that a single workbook can contain multiple worksheets, each with its own grid of data. You’ll learn about this feature in Chapter 4, but for now, each workbook you create will have just a single worksheet of information. You don’t get to name your workbook when you first create it. That 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. Adding Information to a WorksheetWhen you click “Blank workbook,” Excel closes the welcome page and opens a new, blank worksheet, as shown in Figure 1-2. A worksheet is a grid of cells where you type in information and formulas. This grid takes up most 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-2. 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:
NoteObviously, once you go beyond 26 columns, you run out of letters. Excel handles this by doubling up (and then tripling up) letters. For example, after column Z is 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. Figure 1-3. In this spreadsheet, the active cell is C6. You can recognize an active (or current) cell by its heavy black border. You’ll also notice that Excel highlights the corresponding column letter (C) and row number (6) at the edges of the worksheet. Just above the worksheet, on the left side of the window, the formula bar gives you the active cell’s 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 with any basic worksheet. Adding Column TitlesExcel lets you arrange information in whatever way you like. There’s nothing to stop you from scattering numbers left and right, across as many cells as you want. However, one of the most common (and most useful) ways to arrange information is in a table, with headings for each column. It’s important to remember that with even 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 because you can separate first names from street addresses from ZIP codes, and so on. Figure 1-4 shows the difference. Figure 1-4. Top: If you enter both first and last names in a single column, you can sort the column only by first name. And if you clump the addresses and ZIP codes together, you have no way to count the number of people in a certain town or neighborhood. Bottom: The benefit of a six-column table is significant: It lets you break down (and therefore analyze) information granularly, For example, you can sort your list according to people’s last names or where they live. This arrangement also lets you filter out individual bits of information when you start using functions later in this book. You can, of course, always add or remove columns. 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 a worksheet is to add your headings in the row of cells at the top of the sheet (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 space. Adding information is easy—just click the cell you want and start typing. When you finish, hit Tab to complete your entry and move to the cell to the right, or click Enter to head to the cell just underneath. NoteThe 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 manage 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:
Right away, you face your first glitch: awkwardly crowded text. Figure 1-5 shows how to adjust the column width for proper breathing room. Figure 1-5. Top: The standard width of an Excel column is 8.43 characters, which hardly allows you to get a word in edgewise. Here’s how to give yourself some more room. First, 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, Excel resizes the entire column of cells to the new width. NoteA column’s character width doesn’t really reflect how many characters (or letters) fit in a cell. Excel uses 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 a useful way to compare column widths. Adding DataYou 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. (If you’re familiar with databases, you can think of each row as a separate record.) As Figure 1-6 shows, the first column is for dates, the second stores text, and the third 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-6. This rudimentary expense list has three items in it (in rows 2, 3, and 4). By default, Excel aligns the items in a column according to their data type. It aligns numbers and dates on the right, and text on the left. That’s it. You’ve now created a living, breathing worksheet. The next section explains how you can edit the data you just entered. Editing DataEvery 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 moving to the cell and pressing Delete, which clears its contents.) If you want to edit cell data instead of replacing it, you need to put the cell in edit mode, like this:
TipIf 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 now you’re in edit mode, which means that you can use the arrow keys to move within the cell (instead of going from cell to cell). Press F2 again to return to data entry mode, where you can use the arrow keys to move to other cells. 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-7 illustrates the problem. One way to fix BEDP is to manually resize the column, as shown in Figure 1-5. Another option is to turn on text wrapping so you can fit multiple lines of text in a single cell, as described on Alignment and Orientation. Figure 1-7. 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 A1’s data in 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, Excel displays the content in A3 over both columns, and you don’t have a problem. Editing Cells with the Formula BarJust above the worksheet grid but under the ribbon is an indispensable editing tool called the formula bar (Figure 1-8). It displays the address of the active cell (like A1) on the left edge, and it shows you the current cell’s contents. Figure 1-8. The formula bar (just above the grid) displays information about the active cell. In this example, you can see that the current cell is B4 and it contains the number 592. Instead of editing this value in the cell, 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 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 formula bar edits 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. 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 that it fits more information, as shown in Figure 1-9. Figure 1-9. 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 to the right of the formula bar to quickly expand it to your preferred size and collapse it back to the single-line view. Using the Ribbon
The focal point of the Excel window is the worksheet grid. It’s where you enter and edit information, whether that’s an amortization table for a business loan or a catalog of your rare Spider-Man comics. However, it won’t be long before you need to direct your attention upwards, to the super-toolbar that sits at the top of the Excel window. This is the ribbon, and it ensures that even the geekiest Excel features are only a click or two away. The Tabs of the RibbonEverything you’ll ever want to do in Excel—from picking a fancy background color to pulling information out of a database—is packed into the ribbon. To accommodate all these buttons without becoming an over-stuffed turkey, the ribbon uses tabs. You start out with seven tabs. When you click one, you see a whole new collection of buttons (Figure 1-10). Figure 1-10. When you launch Excel, you start at the Home tab. But here’s what happens when you click the Page Layout tab. Now, you have a slew of options for tasks like adjusting paper size and making a decent printout. Excel groups the buttons within a tab into smaller sections for clearer organization. The ribbon makes it easy to find features because Excel groups related features under the same tab. Even better, once you find the button you need, you can often find other, associated commands by looking at the other buttons in the tab. In other words, the ribbon isn’t just a convenient tool, it’s also a great way to explore Excel. The ribbon is full of craftsman-like detail. For example, when you hover over a button, you don’t see a paltry two- or three-word description in a yellow rectangle. Instead, you see a friendly pop-up box with a mini-description of the feature and (often) a shortcut that lets you trigger the command from the keyboard. Another nice detail is the way you can jump from one tab to another at high velocity by positioning your mouse pointer over the ribbon and rolling the scroll wheel (if your mouse has a scroll wheel). And you’re sure to notice the way the ribbon rearranges its buttons when you change the size of the Excel window (see Figure 1-11). Figure 1-11. Top: A large Excel window gives you plenty of room to play. The ribbon uses the space effectively, making the most important buttons bigger. Bottom: When you shrink the Excel window, the ribbon shrinks some buttons or hides their text to make room. Shrink small enough, and Excel starts to replace cramped sections with a single button, like the Alignment, Cells, and Editing sections shown here. Click the button and the missing commands appear in a drop-down panel. Throughout this book, you’ll dig through the ribbon’s tabs to find important features. But before you start your journey, here’s a quick overview of what each tab provides.
NoteIn some circumstances, you may see tabs that aren’t in this list. Macro programmers and other highly technical types use the Developer tab. (You’ll learn how to reveal this tab on Attaching a Macro to a Button Inside a Worksheet.) The Add-Ins tab appears when you open workbooks created in previous versions of Excel that use custom toolbars. And finally, you can create a tab of your own if you’re ambitious enough to customize the ribbon, as explained in the Appendix. Collapsing the RibbonMost people are happy to have the ribbon sit at the top of the Excel window, with all its buttons on hand. But serious number-crunchers demand maximum space for their data—they’d rather look at another row of numbers than a pumped-up toolbar. If this describes you, then you’ll be happy to find out that you can collapse the ribbon, which shrinks it down to a single row of tab titles, as shown in Figure 1-12. To collapse it, just double-click the current tab title. (Or click the tiny up-pointing icon in the top-right corner of the ribbon, right next to the help icon.) Figure 1-12. 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 lifting your fingers from the keyboard, use the shortcut Ctrl+F1. Even if you collapse the ribbon, you can still use all its features. All you need to do is click a tab. For example, if you click Home, the Home tab pops open over your worksheet. As soon as you click the button you want in the Home tab (or click a cell in your worksheet), the ribbon collapses again. The same trick works if you trigger a command in the ribbon using the keyboard, as described in the next section. If you use the ribbon only occasionally, or if you prefer to use keyboard shortcuts, it makes sense to collapse the ribbon. Even then, you can still use the ribbon commands—it just takes an extra click to open the tab. On the other hand, if you make frequent trips to the ribbon or you’re learning about Excel and like to browse the ribbon to see what features are available, don’t bother collapsing it. The two or three spreadsheet rows you’ll lose are well worth it. Using the Ribbon with the KeyboardIf you’re an unredeemed keyboard lover, you’ll be happy to hear that you can trigger ribbon commands with the keyboard. The trick is using keyboard accelerators, a series of keystrokes that starts with the Alt key (the same key you used to use to get to a menu). When you use a keyboard accelerator, you don’t hold down all the keys at the same time. (As you’ll soon see, some of these keystrokes contain so many letters that you’d be playing Finger Twister if you tried.) Instead, you hit the keys one after the other. The trick to keyboard accelerators is understanding that once you hit the Alt key, there are two things you do, in this order:
Before you can trigger a specific command, you must select the correct tab (even if it’s already selected). Every accelerator requires at least two key presses after you hit the Alt key. You need to press even more keys to dig through submenus. By now, this whole process probably seems hopelessly impractical. Are you really expected to memorize dozens of accelerator key combinations? Fortunately, Excel is ready to help you out with a feature called KeyTips. Here’s how it works: When you press Alt, letters magically appear over every tab in the ribbon. Once you hit the corresponding key to pick a tab, letters appear over every button in that tab (Figure 1-13). Once again, you press the corresponding key to trigger the command (Figure 1-14).
Figure 1-13. When you press Alt, Excel displays KeyTips next to every tab, over the File menu, and over the buttons in the Quick Access toolbar. If you follow up with M (for the Formulas tab), you’ll see letters next to every command in that tab, as shown in Figure 1-11. Figure 1-14. You can now follow up with F to trigger the Insert Function button, U to get to the AutoSum feature, and so on. Don’t bother trying to match letters with tab or button names—there are so many features packed into the ribbon that in many cases the letters don’t mean anything at all. Sometimes, a command might have two letters, in which case you need to press both keys, one after the other. (For example, the Find & Select button on the Home tab has the letters FD. To trigger it, press Alt, then H, then F, and then D.) TipYou can go back one step in KeyTips mode by pressing Esc. Or, you can stop cold without triggering a command by pressing Alt again. Excel gives you other shortcut keys that don’t use the ribbon. These are key combinations that start with the Ctrl key. For example, Ctrl+C copies highlighted text, and Ctrl+S saves your work. Usually, you find out about a shortcut key by hovering over a command with your mouse. For example, hover over the Paste button in the ribbon’s Home tab, and you see a tooltip that tells you its timesaving shortcut key, Ctrl+V. And if you worked with a previous version of Excel, you’ll find that Excel 2013 uses almost all the same shortcut keys. Figure 1-15. When you press Alt+E in Excel 2013, you trigger the “imaginary” Edit menu originally in Excel 2003 and earlier. You can’t actually see the menu, because it doesn’t exist in Excel 2013, but 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 Quick Access ToolbarKeen eyes will have noticed the tiny bit of screen real estate just above the ribbon. It holds a series of tiny icons, like the toolbars in older versions of Excel (Figure 1-16). This is the Quick Access toolbar (or QAT, to Excel nerds). Figure 1-16. The Quick Access toolbar puts the Save, Undo, and Redo commands right at your fingertips. Excel provides easy access to these commands because most people use them more frequently than any others. But as you’ll learn in the Appendix, you can add any commands you want here. If the Quick Access toolbar were nothing but a specialized shortcut for three commands, it wouldn’t be worth the bother. But it has one other notable attribute: You can customize it. In other words, you can remove commands you don’t use and add your own favorites. The Appendix of this book (Creating Custom Functions) shows you how. Microsoft has deliberately kept the Quick Access toolbar very small. It’s designed to provide a carefully controlled outlet for those customization urges. Even if you go wild stocking the Quick Access toolbar with your own commands, the rest of the ribbon remains unchanged. (And that means a co-worker or spouse can still use Excel, no matter how dramatically you change the QAT.) Using the Status BarThough people often overlook it, Excel’s status bar (Figure 1-17) is a good way to monitor the program’s current state. For example, if you save or print a document, the status bar shows the progress of the save operation or print job. If your task is simple, the progress indicator may disappear before you even have a chance to notice it. But if you’re performing a time-consuming operation—say, printing an 87-page table of the hotel silverware you happen to own—you can look to the status bar to see how things are coming along. Figure 1-17. In the status bar, you can see the basic status text (which just says “Ready” in this example), the view buttons (useful as you prepare a spreadsheet for printing), and the zoom slider (which lets you enlarge or shrink the current worksheet). The status bar combines several types of information. The leftmost area shows Cell Mode, which displays one of three indicators:
Farther to the right of the status bar are the view buttons, which let you switch to Page Layout view or Page Break Preview. These help you see what your worksheet will look like when you print it. They’re covered in Chapter 7. 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) 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. If you press the Scroll Lock key, for example, a Scroll Lock indicator appears in the status bar (next to the “Ready” text). This indicator tells you that you’re in scroll mode, where the arrow keys don’t move you from one cell to another, but 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 the list of possibilities, right-click the status bar (Figure 1-8). Table 1-2 describes the options. Table 1-1. Status bar indicators
Figure 1-18. 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. The text that appears on the right side of the list tells you the current value of the indicator. In this example, Caps Lock mode is currently off and the Cell Mode text says “Ready.” Going BackstageYour data is the star of the show. That’s why the creators of Excel refer to your worksheet as being “on stage.” The auditorium is the Excel main window, which—as you’ve just seen—includes the handy ribbon, formula bar, and status bar. Sure, it’s a strange metaphor. But once you understand it, you’ll realize the rationale for Excel’s backstage view, which temporarily takes you away from your worksheet and lets you concentrate on other tasks that don’t involve entering or editing data. These tasks include saving your spreadsheet, opening more spreadsheets, printing your work, and changing Excel’s settings. To switch to backstage view, click the File button to the left of the Home ribbon tab. Excel temporarily tucks your worksheet out of sight (although it’s still open and waiting for you). This gives Excel the space it needs to display information related to the task at hand, as shown in Figure 1-19. For example, if you plan to print your spreadsheet, Excel’s backstage view previews the printout. Or if you want to open an existing spreadsheet, Excel can display a detailed list of files you recently worked on. Figure 1-19. When you first switch to backstage view, Excel shows the Info page, which provides basic information about your workbook file, its size, when it was last edited, who edited it, and so on (see the column on the far right). The Info page also provides the gateway to three important features: document protection (Chapter 21), compatibility checking (page 31), and AutoRecover backups (page 38). To go to another section, click a different command in the column on the far left. To get out of backstage view and return to your worksheet, press Esc or click the arrow-in-a-circle icon in the top-right corner of backstage view. The key to using backstage view is the menu of commands that runs in a strip along the left side of the window. You click a command to get to the page for the task you want to perform. For example, to create a new spreadsheet (in addition to the one you’re currently working on), you begin by clicking the New command, as shown in Figure 1-20. TipYou don’t need to go to backstage view to create a new, blank spreadsheet. Instead, hit the shortcut key Ctrl+N while you’re in the worksheet grid. Excel will launch a new window, with a new, blank worksheet at the ready. Figure 1-20. When you click New, you see a page resembling the welcome page that greets you when you start Excel. To create a new, empty workbook, click “Blank workbook.” Excel opens the workbook in a new window, so that it’s separate from your current workbook, which Excel leaves untouched. Here are some of the things you’ll do in Excel’s backstage view:
Saving FilesAs everyone who’s been alive for at least three days knows, you should save your work early and often. Excel is no exception. To save a file for the first time, choose File→Save or File→Save As. Either way, you end up at the Save As page in backstage view (Figure 1-21). Figure 1-21. The first time you save your spreadsheet, you need to choose where to put it. Usually, you’ll pick a location on your hard drive (click Computer in the Places list), but you can upload it to a corporate SharePoint service or to Microsoft’s SkyDrive for online sharing almost as easily. The Save As window includes a list of places—locations where you can store your work. The exact list depends on how you configured Excel, but here are some of the options you’re likely to see:
After you save a spreadsheet once, you can quickly save it again by choosing File→Save, or by pressing 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. To save your spreadsheet with a new name or in a new place, select File→Save As, or press F12. TipSaving a spreadsheet is an almost instantaneous operation, and you should get used to doing it regularly. After you make any significant change to a sheet, hit Ctrl+S to store the latest version of your data. Ordinarily, you’ll save your spreadsheets in the modern .xlsx format, which is described in the next section. However, sometimes you’ll need to convert your spreadsheet to a different type of file—for example, if you want to pass them along to someone using a very old version of Excel, or a different type of spreadsheet program. There are two ways you can do this:
Excel lets you save your spreadsheet in a variety of formats, including the classic Excel 95 format from more than a decade ago. If you want to look at your spreadsheet using a mystery program, use the CSV file type, which produces a comma-delimited text file that almost all spreadsheet programs can read (comma-delimited means that commas separate the information in each cell). And in the following sections, you’ll learn more about sharing your work with old versions of Excel (Sharing Your Spreadsheet with Older Versions of Excel) or putting it in PDF form so anyone can view and print it (Saving Your Spreadsheet As a PDF). But first, you need to take a closer look at Excel’s standard file format. The Excel File FormatModern versions of Excel, including Excel 2013, use the .xlsx file format (which means your saved spreadsheet will have a name like HotelSilverware.xlsx). Microsoft introduced this format in Excel 2007, and it comes with significant advantages:
For all these reasons, .xlsx is the format of choice for Excel 2013. 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. In fact, the .xlsx file format actually comes in two additional flavors. First, there’s the closely related .xlsm, which lets you store macro code with your spreadsheet data. If you add macros to a spreadsheet, Excel prompts you to use this file type when you save your work. (You’ll learn about macros in Chapter 29.) Second, there’s the optimized .xlsb format, which is a specialized option that might be a bit faster when opening and saving gargantuan spreadsheets. The .xlsb format has the same automatic compression and error-resistance as .xlsx, but it doesn’t use XML. Instead, it stores information in raw binary form (good ol’ ones and zeros), which is speedier in some situations. To use the .xlsb format, choose File→Export, click Change File Type, and then choose “Binary Workbook (.xlsb)” from the drop-down list. 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 share your work with other, less fortunate people who have older versions of Excel, such as Excel 2003. You’ll learn how to deal with this challenge in the following sections. TipDon’t use the .xlsb format unless you try it out and find that it really does give you better performance. Usually, .xlsx and .xlsb are just as fast. And remember, the only time you’ll see any improvement is when you load or save a file. Once you open your spreadsheet in Excel, everything else (like scrolling around and performing calculations) happens at the same speed. Figure 1-23. Inside every .xlsx file lurks a number of compressed files, each with different information. For example, separate files store printer settings, text styles, the name of the person who created the document, the composition of your workbook, and the individual worksheets themselves. Sharing Your Spreadsheet with Older Versions of ExcelAs you just learned, Excel 2013 uses the same .xlsx file format as Excel 2010 and Excel 2007. That means that an Excel 2013 fan can exchange files with an Excel 2010 devotee, and there won’t be any technical problems. However, a few issues can still trip you up when you share spreadsheets between different versions of Excel. For example, Excel 2013 introduces a few new formula functions, such as BASE (BASE() and DECIMAL(): Converting Numbers to Different Bases). If you write a calculation in Excel 2013 that uses BASE(), the calculation won’t work in Excel 2010. Instead of seeing the numeric result you want, your recipient will see an error code mixed in with the rest of the spreadsheet data. To avoid this sort of problem, you need the help of an Excel tool called the Compatibility Checker. It scans your spreadsheet for features and formulas that will cause problems in Excel 2010 or Excel 2007. To use the Compatibility Checker, follow these steps:
Once your work passes through the Compatibility Checker, you’re ready to save it. Because Excel 2013, Excel 2010, and Excel 2007 all share the same file format, you don’t need to perform any sort of conversion—just save your file normally. But if you want to share your spreadsheet with Excel 2003, follow the instructions in the next section. Saving Your Spreadsheet for Excel 2003Sharing your workbook with someone using Excel 2003 presents an additional consideration: Excel 2003 uses the older .xls format instead of the current-day .xlsx format. There are two ways to resolve this problem:
TipIf you save your Excel spreadsheet in the Excel 2003 format, make sure to keep a copy in the standard .xlsx format. Why? Because the old format isn’t guaranteed to retain all your information, particularly if you use newer chart features or data visualization. As you already know, each version of Excel introduces a small set of new features. Older versions don’t support these features. The differences between Excel 2010 and Excel 2013 are small, but the differences between Excel 2003 and Excel 2013 are more significant. Excel tries to help you out in two ways. First, whenever you save a file in .xls format, Excel automatically runs the Compatibility Checker to check for problems. Second, whenever you open a spreadsheet in the old .xls file format, Excel switches into compatibility mode. While the Compatibility Checker points out potential problems after the fact, compatibility mode is designed to prevent you from using unsupported features in the first place. For example, in compatibility mode you’ll face these restrictions:
In compatibility mode, these missing features aren’t anywhere to be found. In fact, compatibility mode is so seamless that you might not even notice its limitations. The only clear indication that you’re in Compatibility Mode appears at the title bar at the top of the Excel window. Instead of seeing something like CateringList.xlsx, you’ll see “CateringList.xls [Compatibility Mode].” NoteWhen you save an Excel workbook in .xls format, Excel won’t switch into compatibility mode right away. Instead, you need to close the workbook and reopen it. If you decide at some point that you’re ready to move into the modern world and convert your file to the .xlsx format favored by Excel 2013, you can use the trusty File→Save As command. However, there’s an even quicker shortcut. Just choose File→Info and click the Convert button. This saves an Excel 2013 version of your file with the same name but with the extension .xlsx, and reloads the file so you get out of compatibility mode. It’s up to you to delete your old .xls original if you don’t need it anymore. Saving Your Spreadsheet As a PDFSometimes 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). One way to solve this problem is to save your spreadsheet as a PDF file. This gives you the best of both worlds—you keep all the rich formatting (for when you print your workbook), and you let people who don’t have Excel (and possibly don’t even have Windows) see your work. The disadvantage is that PDFs are for viewing only—there’s no way for you to open a PDF in Excel and start editing it. To save your spreadsheet as a PDF, select File→Export, click Create PDF/XPS Document (in the “File Types” section), and then click the Create PDF/XPS button. Excel opens a modified version of the Save As window that has a few additional options (Figure 1-25). Figure 1-25. You can save PDF files at different resolutions and quality settings (which mostly affect graphics in your workbook, like pictures and charts). Normally, you use higher-quality settings if you want to print your PDF file, because printers use higher resolutions than computers. The “Publish as PDF” window gives you some control over the quality of your printout using the “Optimize for” options. If you’re just saving a PDF copy so other people can view your workbook, choose “Minimum size (publishing online)” to cut down on the storage space required. On the other hand, if 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. 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 it saves the file. That way, you can check the result. Finally, if you want to publish only a portion of your spreadsheet as a PDF file, click the Options button to open a window with even more settings. You can publish just a fixed number of pages, just selected cells, and so on. These options mirror the choices you see when you print a spreadsheet (Printing). 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. (Excel document properties are discussed in more detail on Document Properties.) Password-Protecting Your SpreadsheetOccasionally, you might want to add confidential information to a spreadsheet—a list of the hotels from which you’ve stolen spoons, for example. 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 you might email the spreadsheet to the wrong people (say, executives at Four Seasons), 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 lets you password-protect your spreadsheets, which means that anyone who wants to open them has to know the password you set. Excel actually has two layers of password protection you can apply to a spreadsheet:
To apply one or both of these restrictions to your spreadsheet, follow these steps:
If you use a password to restrict people from modifying the spreadsheet, the next time you open this file, Excel gives you the choice, shown in Figure 1-27 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-27. 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. Disaster RecoveryThe corollary to the edict “Save your data early and often” is the truism “Sometimes things fall apart quickly…before you 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 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’ worth of work. 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 adds a link named Show Recovered Files to Excel’s welcome page (Figure 1-28). Click that link, and Excel adds a panel named Document Recovery to the left side of the Excel window (Figure 1-29). Figure 1-28. Excel’s got your back—click Show Recovered Files to see what files it’s rescued. Figure 1-29. 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 deal with all the backup files, close the Document Recovery window by clicking the Close button. If you haven’t saved the backup, Excel asks you whether you want to save it permanently or delete it. If your computer crashes mid-edit, the next time you open Excel you may see the same file listed twice in the Document Recovery window, as shown in Figure 1-29. The difference is in the status: “[Autosaved]” indicates the most recent backup Excel created, while “[Original]” means the last version of the file you saved (which is safely stored on your hard drive, right where you expect it). To open a file in the Document Recovery window, just click it. You can also use a drop-down menu with additional options (Figure 1-29). If you find a file you want to keep permanently, make sure to save it. If you don’t, the next time you close Excel it asks if it should throw the backups away. If you attempt to open a backup file that’s somehow been scrambled (technically known as corrupted), Excel attempts to repair it. You can choose Show Repairs to display a list of any changes Excel made to recover the file. AutoRecover SettingsAutoRecover comes switched on when you install Excel, but you can tweak its settings. Choose File→Options, and then choose the Save section. Under the “Save workbooks” section, make sure you have “Save AutoRecover information” turned on. You can make a few other changes to AutoRecover:
Opening FilesTo open files in Excel, you begin by choosing File→Open (or using the keyboard shortcut Ctrl+O). This takes you to the Open page in Excel’s backstage view. The left side of the page includes the Places list, which matches the list in the Save As page with one addition: Recent Workbooks. Click this, and you’ll see up to 25 of the most recent spreadsheet files you worked on. If you find the file you want, click it to open it. NoteWhen you open a file, Excel loads it into a new window. If you already have a workbook on the go, that workbook remains open in a separate Excel window. The best part about the Recent Documents list is the way you can pin a document so it stays there forever, as shown in Figure 1-31. Figure 1-31. To keep a spreadsheet on the Recent Documents list, click the thumbtack on the right. Excel moves your workbook to the top of the list and pins it 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 the file later on, just click the thumbtack again to release it. Pinning is a great way to keep your most important files at your fingertips. TipDo you want to hide your recent editing work? You can remove any file from the recent document list by right-clicking it and choosing “Remove from list.” And if the clutter is keeping you from finding the workbooks you want, pin the important files, then right-click any file and choose “Clear unpinned workbooks.” This action removes every file that isn’t pinned down. If you don’t see the file you want in the list of recent workbooks, you can choose one of the other locations in the Places list. Choose Computer to see a list of locations on your hard drive. As with recently opened workbooks, you can pin your favorite locations so they remain on this list permanently. To open a file in one of these locations, click the folder (or click the Browse button underneath to look somewhere else). Either way, Excel opens the familiar Open window, where you can pick the file you want. TipThe Open window also lets you open several spreadsheets in one step, as long as they’re all in the same folder. To use this trick, hold down the Ctrl key and click to select each file. When you click Open, Excel puts each one in a separate window, just as if you’d opened them one after the other. Opening Files in Other FormatsExcel can open many file types other than its native .xlsx format. To open files in another format, begin by choosing File→Open, and then pick a location. When the Open window appears, pick the type of format you want from the “Files of type” list at the bottom. If you want to open a file but don’t know what format it’s in, try using the first option in the list, “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 (based on the type of file Excel thinks it is). NoteDepending 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 type of file it is by looking at the icon. If you see a small Excel icon next to the file name, that means Windows recognizes the file as an Excel spreadsheet. If you see something else (like a tiny paint palette, for example), you need to make a logical guess as to what type of file it is. Protected ViewEven something that seems as innocent as an Excel file can’t always be trusted. Protected view is an Excel security feature that aims to keep you safe. It opens potentially risky Excel files in a specially limited Excel window. You’ll know you’re in protected view because Excel doesn’t let you edit any of the data in the workbook, and it displays a message bar at the top of the window (Figure 1-32). Excel automatically uses protected view when you download a spreadsheet from the Web or open it from your email inbox. This is actually a huge convenience, because Excel doesn’t need to hassle you with questions when you try to view the file (such as “Are you sure you want to open this file?”). Because Excel’s protected view has bullet-proof security, it’s a safe way to view even the most suspicious spreadsheet. Figure 1-32. Currently, this file is in protected view. If you decide that it’s safe and you need to edit its content, click the Enable Editing button to open the file in the normal Excel window with no security safeguards. At this point, you’re probably wondering about the risks of rogue spreadsheets. Truthfully, they’re quite small. The most obvious danger is macro code: miniature programs stored in a spreadsheet file that perform Excel tasks. Poorly written or malicious macro code can tamper with your Excel settings, lock up the program, and even scramble your data. But before you panic, consider this: Excel macro viruses are very rare, and the .xlsx file format doesn’t even allow macro code. Instead, macro-containing files must be saved as .xlsm or .xlsb files. The more subtle danger here is that crafty hackers could create corrupted Excel files that might exploit tiny security holes in the program. One of these files could scramble Excel’s brains in a dangerous way, possibly causing it to execute a scrap of malicious computer code that could do almost anything. Once again, this sort of attack is extremely rare. It might not even be possible with the up-to-date .xlsx file format. But protected view completely removes any chance of an attack, which helps corporate bigwigs sleep at night. Opening Files—With a TwistThe Open window harbors a few tricks. To see these hidden secrets, first select the file 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 options appears, as shown in Figure 1-33. Figure 1-33. Why settle for the plain-vanilla Open command when you have all these choices? Here’s what these different choices do:
Working with Multiple Open SpreadsheetsAs you open multiple spreadsheets, Excel creates a new window for each one. Although this helps keep your work separated, it can cause a bit of clutter and make it harder to track down the window you really want. Fortunately, Excel provides a few shortcuts that are indispensable when dealing with several spreadsheets at a time:
NoteOne 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 on 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. Get Excel 2013: The Missing Manual now with the O’Reilly learning platform. O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers. What is the Backstage view in Word?The Backstage view was introduced in Word 2010. This acts as the central place for managing your documents. The backstage view helps in creating new documents, saving and opening documents, printing and sharing documents, and so on.
When you use the keyboard to scroll to a different position in the document the ____ automatically moves when you press the desired keys *?Word Module 1. What is the name of the default view applied to a document upon opening it?Detailed Solution. The correct answer is Print Layout. By default, an MS Word 2007 document opens in the print layout view.
What happens when you click print in the File tab?On the Print tab, the properties for your default printer automatically appear in the first section, and the preview of your document automatically appears in the second section. Click the File tab, and then click Print. To go back to your document and make changes before you print it, click the File tab again.
|