Excel 2016 All-in-One For Dummies

Greg Harvey

• Publisher: For Dummies
• Year: 2015
• ISBN:
  9781119077275
• Edition: 1
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

Your one-stop guide to all things Excel 2016

Excel 2016 All-in-One For Dummies, the most comprehensive Excel reference on the market, is completely updated to reflect Microsoft's changes in the popular spreadsheet tool. It offers you everything you need to grasp basic Excel functions, such as creating and editing worksheets, setting up formulas, importing data, performing statistical functions, editing macros with Visual Basic—and beyond. In no time, your Excel skills will go from 'meh' to excellent.

Written by expert Greg Harvey, who has sold more than 4.5 million copies of his previous books combined and has taught and trained extensively in Microsoft Excel, this all-encompassing guide offers everything you need to get started with Excel. From generating pivot tables and performing financial functions to performing error trapping and building and running macros—and everything in between—this hands-on, friendly guide makes working with Excel easier than ever before.

• Serves as the ideal reference for solving common questions and Excel pain points quickly and easily
• Helps to increase productivity and efficiency when working in Excel
• Fully updated for the new version of Excel
• Covers basic and more advanced Excel topics

If working in Excel occasionally makes you want to scream, this will be the dog-eared, dust-free reference you'll turn to again and again.

Book II

Worksheet Design

Check out the article “Selecting Cells with the Keyboard in Excel 2016” online at www.dummies.com/extras/excel2016aio.

Contents at a Glance

1. Chapter 1: Building Worksheets
   1. Designer Spreadsheets
   2. It Takes All Kinds (Of Cell Entries)
   3. Data Entry 101
   4. Saving the Data
   5. Document Recovery to the Rescue
2. Chapter 2: Formatting Worksheets
   1. Making Cell Selections
   2. Adjusting Columns and Rows
   3. Formatting Tables from the Ribbon
   4. Formatting Tables with the Quick Analysis Tool
   5. Formatting Cells from the Ribbon
   6. Formatting Cell Ranges with the Mini-Toolbar
   7. Using the Format Cells Dialog Box
   8. Hiring Out the Format Painter
   9. Using Cell Styles
   10. Conditional Formatting
3. Chapter 3: Editing and Proofing Worksheets
   1. Opening a Workbook
   2. Cell Editing 101
   3. A Spreadsheet with a View
   4. Copying and Moving Stuff Around
   5. Find and Replace This Disgrace!
   6. Spell Checking Heaven
   7. Looking Up and Translating Stuff
   8. Marking Invalid Data
   9. Eliminating Errors with Text to Speech
4. Chapter 4: Managing Worksheets
   1. Reorganizing the Worksheet
   2. Reorganizing the Workbook
   3. Working with Multiple Workbooks
   4. Consolidating Worksheets
5. Chapter 5: Printing Worksheets
   1. Printing from the Excel 2016 Backstage View
   2. Quick Printing the Worksheet
   3. Working with the Page Setup Options
   4. Headers and Footers
   5. Solving Page Break Problems
   6. Printing the Formulas in a Report

Chapter 1

Building Worksheets

In This Chapter

Creating a spreadsheet from a template

Designing a spreadsheet from scratch

Understanding the different types of cell entries

Knowing the different ways of entering data in the worksheet

Using Data Validation to restrict the data entries in cells

Saving worksheets

Before you can begin building a new spreadsheet in Excel, you must have the design in mind. As it turns out, the design aspect of the creative process is often the easiest part because you can borrow the design from other workbooks that you’ve already created or from special workbook files, called templates, which provide you with the new spreadsheet’s form along with some of the standard, or boilerplate, data entries.

After you’ve settled upon the design of your new spreadsheet, you’re ready to begin entering its data. In doing the data entry in a new worksheet, you have several choices regarding the method to use. For this reason, this chapter not only covers all the methods for entering data — from the most basic to the most sophisticated — but also includes hints on when each is the most appropriate. Note, however, that this chapter doesn’t include information on building formulas, which comprises a major part of the data entry task in creating a new spreadsheet. Because this task is so specialized and so extensive, you find the information on formula building covered in Book III, Chapter 1.

Designer Spreadsheets

Anytime you launch Excel (without also opening an existing workbook file), the Excel screen in the Backstage view presents you with a choice between

• Opening a new workbook (with the generic filename, Book1), consisting of a single totally blank worksheet (with the generic worksheet name, Sheet1) by selecting the Blank Workbook template
• Opening a new workbook based on the design in one of the other templates displayed in the Start screen or available for download by conducting an online search

If you select the Blank Workbook template, you can start laying out and building your new spreadsheet in the blank worksheet. If you select one of the other designed templates, you can start by customizing the workbook file’s design as well as entering the data for your new spreadsheet.

Take it from a template

Spreadsheet templates are the way to go if you can find one that uses the design of the spreadsheet that you want to build. There are many templates to choose from when you initially launch Excel. (See Figure 1-1.) The templates displayed on the Excel screen in the Backstage view run the gamut from budgets and schedules to profit and loss statements, sales reports, and calendars.

Figure 1-1: Selecting a template from which to generate a new workbook in the Excel Start screen.

If none of the templates displayed on the Excel screen fit the bill, you can then search for templates. This screen contains links to common suggested searches: Business, Personal, Industry, Small Business, Calculator, Finance-Accounting, and Lists.

When you click one of these links, a New screen appears in the Backstage view showing your choices in that particular category. Figure 1-2 shows you the New screen that appears when you click the Small Business link in the Suggested Searches area of the Start screen. As you can see in this figure, the first choice in this category is an Invoice template that automatically calculates totals that you can download and open in Excel 2016.

Figure 1-2: Searching for an Invoice template using the Small Business link in Suggested Searches on the Start screen.

If the type of template you’re looking for doesn’t fit any of the categories listed in the Suggested Searches area of the Excel screen, you conduct your own template search. Simply select the Search for Online Templates text box, type in keywords describing the type of template (such as expense report), and then select the Start Searching button (the one with the magnifying glass icon).

Keep in mind that instead of using ready-made templates, you can create your own templates from your favorite Excel workbooks. After you save a copy of a workbook as a template file, Excel automatically generates a copy of the workbook whenever you open the template file. This way, you can safely customize the contents of the new workbook without any danger of inadvertently modifying the original template.

Downloading the template to use

When you locate a template whose design can be adapted to your spreadsheet needs, you can download it. Simply click its thumbnail in the Excel or New screen. Excel then opens a dialog box similar to the one shown in Figure 1-3, containing a more extensive description of the template and its download file size. To download the template and create a new Excel workbook from it, you simply select the Create button.

Figure 1-3: Downloading a Weekly Expense Report template from which to generate a new workbook.

Figure 1-4 shows the Weekly Expense Report1 workbook in the Excel worksheet area created from the Expense Report template after you click the Create button. As you can see on the Excel window title bar in this figure, when Excel generated this first workbook from the original template file, the program also gave it the temporary filename, Weekly Expense Report1. If you were to then create a second copy of this report by once again opening the Expense Report template, the program would name that copy Weekly Expense Report2. This way, you don’t have to worry about one copy overwriting another, and you never risk mistakenly saving changes to the original template file itself (which actually uses a completely different filename extension — .xltx for an Excel template as opposed to .xlsx for an Excel worksheet).

Figure 1-4: The new Weekly Expense Report1 workbook in the Excel worksheet area generated from the template by the same name.

To customize a spreadsheet generated from one of the installed templates, you replace the placeholder entries in the new sales worksheet with your own data. You might begin by replacing the COMPANY NAME placeholder in cell A1 with the actual name of your company, after which you could replace the em...