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.
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.
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.
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.
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.
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.