Excel 2016 Formulas

Michael Alexander, Richard Kusleika

• Verlag: Wiley
• Jahr: 2016
• ISBN:
  9781119067986
• Auflage: 1
• Thema: Informatique
• Thema: Applications de bureau

Leverage the full power of Excel formulas

Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas. This comprehensive book explains how to create financial formulas, release the power of array formulas, develop custom worksheet functions with VBA, debug formulas, and much more. Whether you're a beginner, a power user, or somewhere in between this is your essential go-to for the latest on Excel formulas.

When conducting simple math or building highly complicated spreadsheets that require formulas up to the task, leveraging the right formula can heighten the accuracy and efficiency of your work, and can improve the speed with which you compile and analyze data. Understanding which formulas to use and knowing how to create a formula when you need to are essential.

• Access tips, tricks, and techniques that have been fully updated to reflect the latest capabilities of Microsoft Excel
• Create and use formulas that have the power to transform your Excel experience
• Leverage supplemental material online, including sample files, templates, and worksheets from the book

PART I
Understanding Formula Basics

• Chapter 1: The Excel User Interface in a Nutshell
• Chapter 2: Basic Facts About Formulas
• Chapter 3: Working with Names

Chapter 1
The Excel User Interface in a Nutshell

In This Chapter

• The workings of Excel workbooks
• The Excel user interface
• Protection options

In this chapter, you’ll gain a foundational understanding of the various components in the Excel user interface that you’ll encounter as you move through this book. You’ll get a primer on some of the ways you can protect your formulas and data models before distributing your Excel files.

If you’re already familiar with the basic workings of Excel, you can safely skip to the next chapter. If it has been a while since you’ve worked with Excel, it may be worth your time to scan this chapter to set the stage for the subsequent chapters in the book.

The Workings of Workbooks

When you think about the different components of Excel, it helps to consider a hierarchy of objects. Excel objects include the following:

• The Excel application itself
• An Excel workbook
• A worksheet in a workbook
• A range in a worksheet
• A cell in a range

Notice the existence of an object hierarchy: the Excel application contains workbook objects, which contain worksheet objects, which contain range objects, which contain cells. Indeed, Microsoft actually has a name for this inherent hierarchy: the Excel object model.

The core object in the Excel object model is the workbook. Everything that you do in Excel takes place in a workbook.

In Excel 2003 and prior versions, Excel workbook files had the default .xls extension. Excel .xls files are binary files that can be read and manipulated with any version of Excel.

Since the release of Excel 2007, Excel workbooks have been saved as .xlsx files. These .xslsx files are actually compressed folders that can be read and manipulated with Excel 2007 and higher versions.

Inside the compressed folders are a number of files that hold all the information about your workbook, including charts, macros, formatting, and the data in its cells.

---

Tip

If you’re the curious type, make a copy of an XLSX workbook file and add a .zip extension to the filename. Then unzip the file to see what’s inside.

---

An Excel workbook can hold any number of sheets. The four types of sheets follow:

• Worksheets
• Chart sheets
• MS Excel 4.0 macro sheets (obsolete, but still supported)
• MS Excel 5.0 dialog sheets (obsolete, but still supported)

You can open or create as many workbooks as you want (each in its own window), but only one workbook is the active workbook at any given time. Similarly, only one sheet in a workbook is the active sheet. To activate a different sheet, click its corresponding tab at the bottom of the window, or press Ctrl+PgUp (for the previous sheet) or Ctrl+PgDn (for the next sheet). To change a sheet’s name, double-click its Sheet tab and type the new text for the name. Right-clicking a tab brings up a shortcut menu with some additional sheet-manipulation options.

You can also hide the window that contains a workbook by using the View ➜ Window ➜ Hide command. A hidden workbook window remains open but not visible. Use the View ➜ Window ➜ Unhide command to make the window visible again. A single workbook can display in multiple windows (choose View ➜ Window ➜ New Window). Each window can display a different sheet or a different area of the same sheet.

Worksheets

The most common type of sheet is a worksheet, which you normally think of when you think of a spreadsheet. Excel 2016 worksheets have 16,384 columns and 1,048,576 rows.

---

Note

Versions prior to Excel 2007 support only 256 columns and 65,536 rows. If you open such a file, Excel enters compatibility mode to work with the smaller worksheet grid. To work with the larger grid, you must save the file in one of the newer Excel formats (XLSX or XLSM). Then close the workbook and reopen it. XLSM files can contain macros; XLSX files cannot.

---

Having access to more cells isn’t the real value of using multiple worksheets in a workbook. Rather, multiple worksheets are valuable because they enable you to org...