Most people don’t need much convincing to use Excel, perhaps the world’s premier spreadsheet software. Its overwhelming popularity, especially in the business world, makes it the obvious choice for millions of number crunchers. But despite its wide use, few people know where to find Excel’s most impressive features or why they’d want to use them in the first place. Excel 2013: The Missing Manual fills that void, explaining everything from basic Excel concepts to the fancy tricks of the trade.
This book teaches you how Excel works, and shows you how to use Excel’s tools to answer real-world questions like “How many workdays are there between today and my vacation?”, “How much money do I need in the bank right now to retire a millionaire?”, and “Statistically speaking, who’s smarter—Democrats or Republicans?” Best of all, you’ll steer clear of obscure options that aren’t worth the trouble to learn, while homing in on the hidden gems that will win you the undying adoration of your coworkers, your family, and your friends—or at least your accountant.
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 1 shows an example.
Figure 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. And with a little more effort, you can calculate averages and medians, and determine each student’s rank in the class. Chapter 8 looks at how to perform these calculations.
Note
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 Blu-rays in your personal movie collection.
Some common types of spreadsheet 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 Ben Stiller movie they’ve ever seen.
Of course, Excel really shines in its ability to help you analyze a spreadsheet’s data. For example, once you enter a list of household expenses, you can start crunching numbers with Excel’s slick formula tools. Before long you’ll have totals, subtotals, monthly averages, a complete breakdown of cost by category, and maybe even some predictions for the future. Excel can help track your investments and tell you how long until you’ll have saved enough to buy that weekend house in Vegas.
The bottom line is that once you enter raw information, Excel’s built-in smarts can help compute all kinds of useful figures. Figure 2 shows a sophisticated spreadsheet that’s designed to help identify hot-selling product categories.
Figure 2. This spreadsheet summarizes a company’s total sales. It groups the information based on where the company’s customers live, and it further divides items according to product category. Summaries like these can help you spot profitable product categories and identify items popular in specific cities. This advanced example uses pivot tables, which are described in Chapter 22.
Note
Keen eyes will notice that neither Figure 1 nor Figure 2 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 Collapsing the Ribbon.
Excel is 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 create macros (automated sequences of steps) to help speed up repetitive formatting or editing chores. 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. (See Chapter 17 to learn about all of Excel’s chart types.) Excel can be as simple or as sophisticated as you want it to be.
Finally, it’s important to understand that you can use Excel to analyze other people’s data—for example, the sales records in a massive company database. That’s because Excel has built-in data connection features that can pull information out of different sources, from a data feed on a website to a database server in a big company. Once you bring that information into Excel, you can examine it with formulas and charts, just as you would analyze the information in an ordinary workbook. You’ll see this side of Excel in Chapters Chapter 27 and Chapter 28.
For Excel 2007 and Excel 2010, Microsoft spent most of its time rebuilding the spreadsheet program’s user interface, replacing the clutter of old-fashioned toolbars with a unified ribbon, and creating a new backstage view where you can open, save, and print files. The visual changes in Excel 2013 are much less dramatic. Excel 2013 tweaks the program’s looks, but just a little, changing the capitalizing of toolbar tabs and toning down the color scheme. But the modern Excel window (which you’ll tour in Chapter 1) stays essentially the same.
That’s not to say that the creators of Excel haven’t been busy over the past few years. In fact, they’ve introduced a range of refinements and new features, most of which fall into two categories. First, Excel 2013 aims to be the easiest, most intuitive version of Excel yet, with several new features that offer help or make suggestions as you work with batches of data. Second, Excel 2013 has grown more powerful, so it can act as a data analysis tool for big businesses with boatloads of data.
You’ll learn about all of Excel’s changes in this book. Here’s a preview of the most significant new features:
Flash Fill. Tired of making repetitive changes to a whole column of information? With Flash Fill, Excel watches you make minor changes, learns the pattern, and then offers to apply your edit to the rest of your data—automatically. You’ll put it to work on Flash Fill.
Quick Analysis. Excel always had plenty of great features, but you need to click your way through layers of buttons and menus to find them. But Excel’s new Quick Analysis feature gives you easy access to the most useful charting, summarizing, and data visualization options. Just select your data, click a simple smart tag, and pick one of the convenient choices Excel offers. Quick Analysis is particularly handy for basic charts (Embedded and Standalone Charts), but you’ll see it crop up throughout this book.
Slicers and timelines. Excel pros know all about Excel’s list and pivot table features, which let you filter masses of data to find the information you need. Now Excel sweetens the pie with slicers, which let you switch filtering options on or off with the click of a fancy floating button; and timelines, which let you select a range of dates in a handy slider widget. By using both tools, you can turn an ordinary Excel worksheet into a slick data dashboard (Filtering a Pivot Table).
The new data model. Excel has always been a brilliant tool for pulling in data from a database and crunching the numbers. Now, Excel integrates the Power-Pivot add-in, giving it the ability to handle millions of rows of data. You’ll learn more in Chapter 27.
Worksheet reporting. The Inquire add-in is a bonus that ships with the Office Professional Plus version of Excel. It lets you compare different versions of the same workbook, and discover how the formulas in sheets and workbook files link together, among other tricks. You’ll try it out on Reviewing Workbooks with Inquire.
Of course, this list is by no means complete. Excel 2013 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.
Along with the changes covered above, Microsoft has been busy tweaking the way it sells Office. Excel 2013 is available in the usual array of desktop packages, as well as through a subscription service called Office 365, which is aimed at businesses, educational institutions, and government workers. When a company signs up, they give each of their employees a separate Office 365 account that they can use to run Office (either online or on the desktop, if the subscription plan includes desktop use). Depending on the plan, the Office 365 subscription may also include other online services, such as email, messaging, document sharing, project tracking, and more.
The drawback to Office 365 is that each person who uses it needs a separate subscription plan, and each subscription plan entails a monthly payment to Microsoft (ranging from $4 to over $20 per month). For big businesses, the cost of giving their employees Office 365 subscriptions is often less than buying multiple copies of the shrink-wrapped Office software, and it saves them many administrative tasks, because Microsoft manages most of the administration, from spam filtering to setting up SharePoint. However, Office 365 probably won’t interest families, hobbyists, or self-employed people.
To learn more about Office 365 and compare the different subscription plans, visit http://office.microsoft.com.
Excel doesn’t just live on ordinary Windows PCs. Now, Microsoft gives Excel lovers a way to run their favorite program on a Windows 8 tablet (see below), or in a web browser (see the next section).
To run Office applications on a Windows 8 tablet, you use a slightly different version of the productivity suite called Office 2013 RT. (Oddly enough, no one knows exactly what the “RT” stands for. The name appears to be inspired by WinRT, the new runtime in Windows 8 that powers tile-based apps. However, Office RT doesn’t use WinRT, so go figure.)
Office RT looks almost identical to the desktop version of Office, but it has a number of changes under the hood. For example, it’s optimized to conserve battery life and save disk space. It also turns on touch mode, which makes it easier to scroll around and use the ribbon with your fingers instead of the traditional mouse pointer.
Although this book is written with the full desktop version of Excel in mind, you can also use it to feel your way around the Office RT version of Excel. However, you’ll find that the instructions in this book are unashamedly mouse-centric (we talk about “right-clicking” but not “double-tapping,” for example). You should also know that there are a small set of significant Excel features that aren’t available in Office RT. These include macros, Visual Basic programming, plug-ins, and the new data model that lets you work with related tables and huge amounts of data.
The Office Web Apps are an interesting new direction in the Office world. They provide a way to run sophisticated Office applications, like Excel, in an ordinary browser and on virtually any computer. However, the Office Web Apps have only a sliver of the features of their desktop cousins, and you can’t use them at all unless you have a SharePoint server or you’re willing to upload your documents to SkyDrive (Microsoft’s free document-hosting service). The online version of Excel is called the Excel Web App.
Note
Overall, the Excel Web App is designed for collecting data and viewing Excel spreadsheets, not creating them. For example, you can view workbooks that use common Excel ingredients like sparklines and pivot tables, but you can’t add them yourself.
Microsoft introduced the Excel Web App at the same time as Excel 2010. When the company released Excel 2013, they also updated the Excel Web App, giving it the new Excel 2013 color scheme and tweaking its chart drawing to be just a bit crisper. However, the only completely new feature you’ll find in the Excel 2013 Web App is the ability to create surveys (Creating a Survey).
Interestingly, the desktop version of Excel 2013 now has slightly better integration with the Excel Web App. It’s easier than ever to upload your work to SharePoint or SkyDrive, and you can even send out a link to your work through social media sites like Facebook, Twitter, and LinkedIn, all without leaving the Excel window. You’ll consider these minor frills, and the Excel Web App, in Chapter 23.
Despite the many improvements in software over the years, one feature hasn’t improved a bit: Microsoft’s documentation. In fact, with Office 2013, you get no printed user guide at all. To learn about the thousands of features included in this software collection, Microsoft expects you to read its online help.
Occasionally, the online help is actually helpful, like when you’re looking for a quick description explaining a mysterious new function. On the other hand, if you’re trying to learn how to, say, create an attractive chart, you’re stuck with terse and occasionally cryptic instructions.
The purpose of this book, then, is to serve as the manual that should have accompanied Excel 2013. In these pages, you’ll find step-by-step instructions and tips for using almost every Excel feature, including those you may not even know exist.
This book is divided into eight parts, each containing several chapters.
Part 1. In this part, you’ll get acquainted with Excel’s interface and learn the basic techniques for creating spreadsheets and entering and organizing data. You’ll also learn how to format your work to make it more presentable, and how to create sharp printouts.
Part 2. This part introduces you to Excel’s most important feature—formulas. You’ll learn how to perform calculations ranging from the simple to the complex, and you’ll tackle specialized functions for dealing with all kinds of information, including scientific, statistical, business, and financial data.
Part 3. The third part covers how to organize and find what’s in your spreadsheet. First, you’ll learn to search, sort, and filter large amounts of information by using tables. Next, you’ll see how to boil down complex tables using grouping and outlining. Finally, you’ll turn your perfected spreadsheets into reusable templates.
Part 4. The fourth part introduces you to charting and graphics, two of Excel’s most popular features. You’ll learn about the wide range of different chart types available and when it makes sense to use each one. You’ll also find out how you can use pictures to add a little pizazz to your spreadsheets.
Part 5. The sixth part explores ways you can share your spreadsheets with other people. You’ll learn how to collaborate with colleagues to revise a spreadsheet, without letting mistakes creep in or losing track of who did what. You’ll also learn how to copy Excel tables and charts into other programs (like Word) and how to use the Excel Web App to share and edit spreadsheets on the Web.
Part 6. In this brief part, you’ll tackle some of Excel’s most advanced features. You’ll see how to study different possibilities with scenarios, use goal-seeking and the Solver add-in to calculate “backward” and fill in missing numbers, and create multi-layered summary reports with pivot tables. You’ll also learn how to use Excel’s data connection features to pull information out of databases, websites, and XML files.
Part 7. This part presents a gentle introduction to the world of Excel programming, first by recording macros and then by using the full-featured VBA (Visual Basic for Applications) language, which lets you automate complex tasks.
Part 8. The end of this book wraps up with an appendix that shows you how to customize the ribbon to get easy access to your favorite commands.
Throughout this book, you’ll find sentences like this one: “Choose Insert→Illustrations→Picture.” This is a shorthand way of telling you how to find a feature in the Excel ribbon. It translates to the following instructions: “Click the Insert tab of the toolbar. On that tab, look for the Illustrations section. In the Illustrations box, click the Picture button.” Figure 3 shows the button you want.
Figure 3. In this book, arrow notations help simplify ribbon commands. For example, “Choose Insert→Illustrations→Picture” leads to the highlighted button shown here.
Note
The ribbon adapts itself to different screen sizes. Depending on the size of your Excel window, it’s possible that the button you need to click will include a tiny picture but no text. In this situation, you can hover over the mystery button to see its name before deciding whether to click it.
There are some tabs that appear in the ribbon only when you work on specific tasks. For example, when you create a chart, a Chart Tools section appears with two new tabs (see Figure 4).
Figure 4. Excel doesn’t bother to show these two tabs unless you’re working on a chart, because it’s frustrating to look at a bunch of buttons you can’t use. This sort of tab, which appears only when needed, is called a contextual tab.
When dealing with contextual tabs, the instructions in this book always include the title of the tab section (it’s Chart Tools in Figure 4, for example). Here’s an example: “Choose Chart Tools | Design→Type→Change Chart Type.” Notice that the first part of this instruction includes the tab section title (Chart Tools) and the tab name (Design), separated by the | character. That way, you can’t mistake the Chart Tools | Design tab for a Design tab in some other group of contextual tabs.
From time to time, you’ll encounter buttons in the ribbon that have short menus attached to them. Depending on the button, this menu might appear as soon as you click the button, or it might appear only if you click the button’s drop-down arrow, as shown in Figure 5.
Figure 5. There are several options for pasting text from the Clipboard. Click the top part of the Paste button to perform a plain-vanilla paste (with all the standard settings), or click the bottom part to see the menu of choices shown here.
When dealing with this sort of button, the last step of the instructions in this book tells you what to choose from the drop-down menu. For example, say you’re directed to “Home→Clipboard→Paste→Paste Special.” That tells you to select the Home tab, look for the Clipboard section, click the drop-down part of the Paste button (to reveal the menu with extra options), and then choose Paste Special from the menu.
Tip
Be on the lookout for drop-down arrows in the ribbon—they’re tricky at first. You need to click the arrow part of the button to see the full list of options. When you click any other part of the button, you don’t see the list. Instead, Excel fires off the standard command (the one Excel thinks is the most common choice) or the command you used most recently.
As powerful as the ribbon is, you can’t do everything using the buttons it provides. Sometimes you need to use a good ol’-fashioned dialog box. (A dialog box is a term used in the Windows world to describe a small window with a limited number of options. Usually, dialog boxes are designed for one task and aren’t resizable, although software companies like Microsoft break these rules all the time.)
There are two ways to get to a dialog box in Excel. First, some ribbon buttons take you there straightaway. For example, if you choose Home→Clipboard→Paste→Paste Special, you always get a dialog box. There’s no way around it.
The second way to get to a dialog box is through something called a dialog box launcher, which is just a nerdified name for the tiny square-with-arrow icon that sometimes appears in the bottom-right corner of a section of the ribbon. The easiest way to learn how to spot a dialog box launcher is to look at Figure 6.
Figure 6. As you can see here, the Clipboard, Font, Alignment, and Number sections all have dialog box launchers. The Styles, Cells, and Editing sections don’t.
When you click a dialog box launcher, the related dialog box appears. For example, click the dialog box launcher for the Font section and you get a full Font dialog box that lets you scroll through all the typefaces on your computer, choose a size and color, and so on.
In this book, there’s no special code word that tells you to use a dialog box launcher. Instead, you’ll see an instruction like this: “To see more font options, look at the Home→Font section and click the dialog box launcher (the small icon in the bottom-right corner).” Now that you know what a dialog box launcher is, that makes perfect sense.
If you see an instruction that includes arrows but starts with the word File, it’s telling you to go to Excel’s backstage view. For example, the sentence “Choose File→New” means click the File button (which appears just to the left of ribbon’s Home tab) to switch to backstage view, then click the New command (which appears in the narrow list on the left side of the window). You’ll take your first look around backstage view on Going Backstage.
There are a couple of other cases where you’ll use the familiar Windows menu. One is when you use the Visual Basic editor (in Chapter 29). In this case, the arrows refer to menu levels. For example, the instruction “Choose File→Save” means “Click the File menu heading. Then, on the File menu, click the Save command.”
Every time you take your hand off the keyboard to move the mouse, you lose a few microseconds. That’s why many experienced computer fans use keystroke combinations instead of toolbars and menus wherever possible. Ctrl+S, for example, is a keyboard shortcut that saves your current work in Excel (and most other programs).
When you see a shortcut like Ctrl+S in this book, it’s telling you to hold down the Ctrl key and, while it’s down, press the letter S, and then release both keys. Similarly, the finger-tangling shortcut Ctrl+Alt+S means hold down Ctrl, and then press and hold Alt, and then press S (so that all three keys are held down at once).
As the owner of a Missing Manual, you’ve got more than just a book to read. As you read this book, you’ll see a number of examples that demonstrate Excel features and techniques for building good spreadsheets. Most of these examples are available as downloadable Excel workbook files. Just surf to http://missingmanuals.com/cds/excel2013mm/ to visit a page where you can download a ZIP file that includes the examples, organized by chapter.
If you register this book at www.oreilly.com, you’ll be eligible for special offers—like discounts on future editions of this book. If you buy the ebook from oreilly.com and register your purchase, you get free lifetime updates for this edition of the ebook; we’ll notify you by email when updates become available. Registering takes only a few clicks. Type www.oreilly.com/register into your browser to hop directly to the Registration page.
Got questions? Need more information? Fancy yourself a book reviewer? On our Feedback page, you can get expert answers to questions that come to you while reading, share your thoughts on this Missing Manual, and find groups for folks who share your interest in Dreamweaver. To have your say, go to www.missingmanuals.com/feedback.
To keep this book as up to date and accurate as possible, each time we print more copies, we’ll make any confirmed corrections you suggest. We also note such changes on the book’s website, so you can mark important corrections into your own copy of the book, if you like. And if you bought the ebook from us and registered your purchase, you’ll get an email notifying you when you can download a free updated version of this edition of the ebook. Go to http://tinyurl.com/excel2013errata to report an error and view existing corrections.
As you read this book, you’ll see a number of examples that demonstrate Excel features and techniques for building good spreadsheets. Most of these examples are available as Excel workbook files in a separate download. Just surf to www.missingmanuals.com/cds and click the link for this book to visit a page where you can download a ZIP file that includes the examples, organized by chapter.
Safari© Books Online is an on-demand digital library that lets you easily search over 7,500 technology and creative reference books and videos to find the answers you need quickly.
With a subscription, you can read any page and watch any video from our library online. Read books on your cellphone and mobile devices. Access new titles before they’re available for print, and get exclusive access to manuscripts in development and post feedback for the authors. Copy and paste code samples, organize your favorites, download chapters, bookmark key sections, create notes, print out pages, and benefit from tons of other time-saving features.
O’Reilly Media has uploaded this book to the Safari Books Online service. To have full digital access to this book and others on similar topics from O’Reilly and other publishers, sign up for free at http://my.safaribooksonline.com.
Get Excel 2013: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
