cover.eps

Title page image

Excel® 2016 For Dummies®

To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel 2016 For Dummies Cheat Sheet” in the Search box.

Introduction

I’m very proud to present you with Excel 2016 For Dummies, the latest version of everybody’s favorite book on Microsoft Office Excel for readers with no intention whatsoever of becoming spreadsheet gurus.

Excel 2016 For Dummies covers all the fundamental techniques you need to know in order to create, edit, format, and print your own worksheets. In addition to showing you around the worksheet, this book also exposes you to the basics of charting, creating data lists, and performing data analysis. Keep in mind, though, that this book just touches on the easiest ways to get a few things done with these features — I don’t attempt to cover charting, data lists, or data analysis in the same definitive way as spreadsheets: This book concentrates on spreadsheets because spreadsheets are what most regular folks create with Excel.

About This Book

This book isn’t meant to be read cover to cover. Although its chapters are loosely organized in a logical order (progressing as you might when studying Excel in a classroom situation), each topic covered in a chapter is really meant to stand on its own.

Each discussion of a topic briefly addresses the question of what a particular feature is good for before launching into how to use it. In Excel, as with most other sophisticated programs, you usually have more than one way to do a task. For the sake of your sanity, I have purposely limited the choices by usually giving you only the most efficient ways to do a particular task. Later, if you’re so tempted, you can experiment with alternative ways of doing a task. For now, just concentrate on performing the task as I describe.

As much as possible, I’ve tried to make it unnecessary for you to remember anything covered in another section of the book. From time to time, however, you will come across a cross-reference to another section or chapter in the book. For the most part, such cross-references are meant to help you get more complete information on a subject, should you have the time and interest. If you have neither, no problem. Just ignore the cross-references as if they never existed.

How to Use This Book

This book is similar to a reference book. You can start by looking up the topic you need information about (in either the Table of Contents or the index) and then refer directly to the section of interest. I explain most topics conversationally (as though you were sitting in the back of a classroom where you can safely nap). Sometimes, however, my regiment-commander mentality takes over, and I list the steps you need to take to accomplish a particular task in a particular section.

What You Can Safely Ignore

When you come across a section that contains the steps you take to get something done, you can safely ignore all text accompanying the steps (the text that isn’t in bold) if you have neither the time nor the inclination to wade through more material.

Whenever possible, I have also tried to separate background or footnote-type information from the essential facts by exiling this kind of junk to a sidebar (look for blocks of text on a gray background). Often, these sections are flagged with icons that let you know what type of information you will encounter there. You can easily disregard text marked this way. (I’ll scoop you on the icons I use in this book a little later.)

Foolish Assumptions

I’m only going to make one foolish assumption about you, and that is that you have some need to use Microsoft Excel 2016 in your work or studies. If pushed, I further guess that you aren’t particularly interested in knowing Excel at an expert level but are terribly motivated to find out how to do the stuff you need to get done. If that’s the case, this is definitely the book for you. Fortunately, even if you happen to be one of those newcomers who’s highly motivated to become the company’s resident spreadsheet guru, you’ve still come to the right place.

As far as your hardware and software goes, I’m assuming that you already have Excel 2016 (usually as part of Microsoft Office 2016) installed on your computing device, using a standard home or business installation running under Windows 7, 8, or 10 (my personal favorite). I’m not assuming, however, that when you’re using Excel 2016, you are sitting in front of a large screen monitor and making cell entries and command selections with a physical keyboard or connected mouse. With the introduction of Microsoft’s Surface 3 tablets and the support for a whole slew of different Windows tablets, you may well be entering data and selecting commands with your finger or stylus using the Windows Touch keyboard.

tip To deal with the differences between using Excel 2016 on a standard desktop or laptop computer with access only to a physical keyboard and mouse and a touchscreen tablet or smartphone environment with access only to the virtual Touch keyboard, I’ve outlined the touchscreen equivalents to common commands you find throughout the text such as “click,” “double-click,” “drag,” and so forth in Chapter 1.

Keep in mind that although most of the figures in this book show Excel 2016 happily running on Windows 10, you will see the occasional figure showing Excel running on Windows 8 in the rare cases (as when opening and saving files) where what operating system you're using does make a difference.

warning This book is intended only for users of Microsoft Office Excel 2016! Because of the diversity of the devices that Excel 2016 runs on and the places where its files can be saved and used, if you’re using Excel 2007 or Excel 2010 for Windows, much of the file-related information in this book may only confuse and confound you. If you’re still using a version prior to Excel 2007, which introduced the Ribbon interface, this edition will be of no use to you as your version of the program works nothing like the 2016 version this book describes.

How This Book Is Organized

This book is organized in six parts with each part containing two or more chapters (to keep the editors happy) that more or less go together (to keep you happy). Each chapter is divided further into loosely related sections that cover the basics of the topic at hand. However, don’t get hung up on following the structure of the book; ultimately, it doesn’t matter whether you find out how to edit the worksheet before you learn how to format it, or whether you figure out printing before you learn editing. The important thing is that you find the information — and understand it when you find it — when you need to perform a particular task.

In case you’re interested, a synopsis of what you find in each part follows.

Part 1: Getting Started with Excel 2016

As the name implies, in this part 1 cover such fundamentals as how to start the program, identify the parts of the screen, enter information in the worksheet, save a document, and so on. If you’re starting with absolutely no background in using spreadsheets, you definitely want to glance at the information in Chapter 1 to discover the secrets of the Ribbon interface before you move on to how to create new worksheets in Chapter 2.

Part 2: Editing without Tears

In this part, I show you how to edit spreadsheets to make them look good, including how to make major editing changes without courting disaster. Peruse Chapter 3 when you need information on formatting the data to improve the way it appears in the worksheet. See Chapter 4 for rearranging, deleting, or inserting new information in the worksheet. Read Chapter 5 for the skinny on printing your finished product.

Part 3: Getting Organized and Staying That Way

Here I give you all kinds of information on how to stay on top of the data that you’ve entered into your spreadsheets. Chapter 6 is full of good ideas on how to keep track of and organize the data in a single worksheet. Chapter 7 gives you the ins and outs of working with data in different worksheets in the same workbook and gives you information on transferring data between the sheets of different workbooks.

Part 4: Digging Data Analysis

This part consists of two chapters. Chapter 8 introduces performing various types of what-if analysis in Excel, including setting up data tables with one and two inputs, performing goal seeking, and creating different cases with Scenario Manager. Chapter 9 introduces Excel’s vastly improved pivot table and pivot chart capabilities that enable you to summarize and filter vast amounts of data in a worksheet table or data list in a compact tabular or chart format.

Part 5: Life beyond the Spreadsheet

In Part 5, I explore some of the other aspects of Excel besides the spreadsheet. In Chapter 10, you find out just how ridiculously easy it is to create a chart using the data in a worksheet. In Chapter 11, you discover just how useful Excel’s data list capabilities can be when you have to track and organize a large amount of information. In Chapter 12, you find out about using add-in programs to enhance Excel’s basic features, adding hyperlinks to jump to new places in a worksheet, to new documents, and even to web pages, as well as how to record macros to automate your work.

Part 6: The Part of Tens

As is the tradition in For Dummies books, the last part contains lists of the top ten most useful and useless facts, tips, and suggestions. In this part, you find four chapters. Chapter 13 provides you with the top ten beginner basics you need to know as you start using this program. Chapter 14 gives you the King James Version of the Ten Commandments of Excel 2016. With this chapter under your belt, how canst thou goest astray? Chapter 15 talks about the top ten features for managing and maintaining loads of data in Excel 2016, while Chapter 16 examines the top ten features for identifying trends and vital indicators in your Excel data.

Conventions Used in This Book

The following information gives you the lowdown on how things look in this book. Publishers call these items the book’s conventions (no campaigning, flag-waving, name-calling, or finger-pointing is involved, however).

Selecting Ribbon commands

Throughout the book, you’ll find Ribbon command sequences (the name on the tab on the Ribbon and the command button you select) separated by a command arrow, as in:

HOME Copy

This shorthand is the Ribbon command that copies whatever cells or graphics are currently selected to the Windows Clipboard. It means that you click the HOME tab on the Ribbon (if it isn’t displayed already) and then click the Copy button (that sports the traditional side-by-side page icon).

Some of the Ribbon command sequences involve not only selecting a command button on a tab but then also selecting an item on a drop-down menu. In this case, the drop-down menu command follows the name of the tab and command button, all separated by command arrows, as in:

FORMULAS Calculation Options Manual

This shorthand is the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the FORMULAS tab (if it isn’t displayed already) and then click the Calculation Options button followed by the Manual drop-down menu option.

The book occasionally encourages you to type something specific into a specific cell in the worksheet. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letter-number combos), and a right parenthesis. You then, of course, have to press Enter to make the entry stick.

Occasionally, I give you a hot key combination that you can press in order to choose a command from the keyboard rather than clicking buttons on the Ribbon with the mouse. Hot key combinations are written like this: Alt+FS or Ctrl+S (both of these hot key combos save workbook changes).

With the Alt key combos on a physical keyboard, you press the Alt key until the hot key letters appear in little squares all along the Ribbon. At that point, you can release the Alt key and start typing the hot key letters (by the way, you type all lowercase hot key letters — I only put them in caps to make them stand out in the text).

Hot key combos that use the Ctrl key are of an older vintage and work a little bit differently. On physical keyboards you have to hold down the Ctrl key while you type the hot key letter (though again, type only lowercase letters unless you see the Shift key in the sequence, as in Ctrl+Shift+C).

Excel 2016 uses only one pull-down menu (File) and one toolbar (the Quick Access toolbar). You open the File pull-down menu by clicking the File tab or pressing Alt+F to access the Excel Backstage. The Quick Access toolbar with its four buttons appears to the immediate right of the File tab.

Finally, if you’re really observant, you may notice a discrepancy in how the names of dialog box options (such as headings, option buttons, and check boxes) appear in the text and how they actually appear in Excel on your computer screen. I intentionally use the convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.

Icons Used in This Book

The following icons are placed in the margins to point out stuff you may or may not want to read.

technicalstuff This icon alerts you to nerdy discussions that you may well want to skip (or read when no one else is around).

tip This icon alerts you to shortcuts or other valuable hints related to the topic at hand.

remember This icon alerts you to information to keep in mind if you want to meet with a modicum of success.

warning This icon alerts you to information to keep in mind if you want to avert complete disaster.

Where to Go from Here

If you’ve never worked with a computer spreadsheet, I suggest that you first go to Chapter 1 and find out what you’re dealing with. Then, as specific needs arise (such as, “How do I copy a formula?” or “How do I print just a particular section of my worksheet?”), you can go to the Table of Contents or the index to find the appropriate section and go right to that section for answers.

Part 1

Getting Started with Excel 2016

IN THIS PART …

Explore the Excel user Ribbon interface.

Make sense of the most commonly used tabs and command buttons.

Customize the Quick Access toolbar.

Start (and stop) Excel 2016.

Get help with the Tell Me feature and the online Excel 2016 Help window.

Become familiar with the prominent buttons and boxes for entering spreadsheet data.

Save your work and recover a lost workbook if disaster strikes.

Visit www.dummies.com for more great Dummies content online.

Chapter 1

The Excel 2016 User Experience

IN THIS CHAPTER

Getting familiar with the Excel 2016 program window and Backstage view

Selecting commands from the Ribbon

Customizing the Quick Access toolbar

Starting Excel 2016

Surfing an Excel 2016 worksheet and workbook

Getting things done with “Tell me what you want to do”

Excel 2016, like Excel 2013, 2010, and 2007 before it, relies upon a single strip at the top of the worksheet called the Ribbon that puts the bulk of the Excel commands you use at your fingertips at all times.

Add to the Ribbon a File tab and a Quick Access toolbar — along with a few remaining task panes (Clipboard, Clip Art, and Research) — and you end up with the handiest way to crunch your numbers, produce and print polished financial reports, as well as organize and chart your data. In other words, to do all the wonderful things for which you rely on Excel.

Best of all, the Excel 2016 user interface includes all sorts of graphical elements that make working on spreadsheets a lot faster and a great deal easier. Foremost is Live Preview that shows you how your actual worksheet data would appear in a particular font, table formatting, and so on before you actually select it. This Live Preview extends to the new Quick Analysis and Recommended PivotTables and Recommended Charts commands to enable you to preview your data in various formats before you apply them.

Additionally, Excel 2016 supports a Page Layout View that displays rulers and margins along with headers and footers for every worksheet with a Zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. Finally, Excel 2016 is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially in tandem with Live Preview.