Introduction

Excel 2016 All-in-One For Dummies brings together plain and simple information on using all aspects of the latest-and-greatest version of Microsoft Excel. It's designed to be of help no matter how much or how little experience you have with the program. As the preeminent spreadsheet and data analysis software for all sorts of computing devices running Windows 7, 8, or 10 (desktops, laptops, tablet PCs and even smartphones), Excel 2016 offers its users seemingly unlimited capabilities too often masked in technical jargon and obscured by explanations only a software engineer could love. On top of that, many of the publications that purport to give you the lowdown on using Excel are quite clear on how to use particular features without giving you a clue as to why you would want to go to all the trouble.

The truth is that understanding how to use the abundance of features offered by Excel 2016 is only half the battle, at best. The other half of the battle is to understand how these features can benefit you in your work; in other words, "what's in it for you." I have endeavored to cover both the "how to" and "so what" aspects in all my discussions of Excel features, being as clear as possible and using as little tech-speak as possible.

Fortunately, Excel 2016 is well worth the effort to get to know because it's definitely one of the best data-processing and analysis tools that has ever come along. Its Quick Analysis tool, Office Add-ins, Flash Fill, and Recommended Charts and PivotTables, along with the tried-and-true Live Preview feature and tons of ready-made galleries, make this version of the program the easiest to use ever. In short, Excel 2016 is a blast to use when you know what you're doing, and my great hope is that this "fun" aspect of using the program comes through on every page (or, at least, every other page).

About This Book

As the name states, Excel 2016 All-in-One For Dummies is a reference. (Whether you keep it on your desk or use it to prop up your desk is your business.) This means that although the chapters in each book are laid out in a logical order, each stands on its own ready for you to dig into the information at any point.

As much as possible, I have endeavored to make the topics within each book and chapter stand on their own. When there's just no way around relying on some information that's discussed elsewhere, I include a cross-reference that gives you the chapter and verse (actually the book and chapter) for where you can find that related information if you're of a mind to.

Use the full Table of Contents and Index to look up the topic of the hour and find out exactly where it is in this compilation of Excel information. You'll find that although most topics are introduced in a conversational manner, I don't waste much time cutting to the chase by laying down the main principles at work (usually in bulleted form) followed by the hard reality of how you do the deed (as numbered steps).

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 go, 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 either Windows 7, 8, or 10. I'm not assuming, however, that when you're using Excel 2016 that you're 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 tablet for Windows 8 and 10 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 and Touch pointer.

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 the section that explains selecting by touch in Book I, Chapter 1.

Keep in mind that all the figures in this book were created with Excel 2016 happily running on Windows 10 so that you will see the occasional Windows 10 dialog box and desktop in figures depicting Excel files being opened and saved.

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 because your version of the program works nothing like the 2016 version this book describes.

How This Book Is Organized

Excel 2016 All-in-One For Dummies is actually eight smaller books rolled into one. That way, you can go after the stuff in the particular book that really interests you at the time, putting all the rest of the material aside until you need to have a look at it. Each book in the volume consists of two or more chapters consisting of all the basic information you should need in dealing with that particular component or aspect of Excel.

In case you're the least bit curious, here's the lowdown on each of the eight books and what you can expect to find there.

Book I: Excel Basics

This book is for those of you who've never had a formal introduction to the program's basic workings. Chapter 1 covers all the orientation material including how to deal with the program's Ribbon user interface. Of special interest may be the section selecting commands by touch if you're using Excel 2016 on a Windows touchscreen device that isn't equipped with either a physical keyboard or mouse.

Chapter 2 is not to be missed, even if you do not consider yourself a beginner by any stretch of the imagination. This chapter covers the many ways to customize Excel and make the program truly your own. It includes information on customizing the Quick Access toolbar as well as great information on how to use and procure add-in programs that can greatly extend Excel's considerable features.

Book II: Worksheet Design

Book II focuses on the crucial issue of designing worksheets in Excel. Chapter 1 takes up the call on how to do basic design and covers all the many ways of doing data entry (a subject that's been made all the more exciting with the addition of voice and handwriting input).

Chapter 2 covers how to make your spreadsheet look professional and read the way you want it through formatting. Excel offers you a wide choice of formatting techniques, from the very simple formatting as a table all the way to the now very sophisticated and super-easy conditional formatting.

Chapter 3 takes up the vital subject of how to edit an existing spreadsheet without disturbing its design or contents. Editing can be intimidating to the new spreadsheet user because most spreadsheets contain not only data entries that you don't want to mess up but also formulas that can go haywire if you make the wrong move.

Chapter 4 looks at the topic of managing the worksheets that contain the spreadsheet applications that you build in Excel. It opens the possibility of going beyond the two-dimensional worksheet with its innumerable columns and rows by organizing data three-dimensionally through the use of multiple worksheets. (Each Excel file already contains three blank worksheets to which you can add more.) This chapter also shows you how to work with and organize multiple worksheets given the limited screen real estate afforded by your monitor and how to combine data from different files and sheets when needed.

Chapter 5 is all about printing your spreadsheets, a topic that ranks only second in importance to knowing how to get the data into a worksheet in the first place. As you expect, you find out not only how to get the raw data to spit out of your printer but also how to gussy it up and make it into a professional report of which anyone would be proud.

Book III: Formulas and Functions

This book is all about calculations and building the formulas that do them. Chapter 1 covers formula basics from doing the simplest addition to building array formulas and using Excel's built-in functions courtesy of the Function Wizard. It also covers how to use different types of cell references when making formula...