Monthly Archives: December 2016

How to Customizing the Office Applications

In this chapter, you learn about customizing the Office 2016 applications, including working with the application options and customizing the interface. Topics include the following:

  • Accessing the Options dialog box for the Office 2016 applications
  • Changing your Office 2016 user name and initials
  • Pinning, hiding, and customizing the Ribbon
  • Positioning and customizing the Quick Access Toolbar
  • Changing the background for the Office 2016 applications

This book is called My Office 2016, so it’s time you learned how to put the “My” in Office 2016. I speak, of course, about customizing the applications in some way. After all, the interface and settings that you see when you first use Office 2016 are the “factory defaults.” That is, how the program looks and how it works out of the box has been specified by Microsoft. However, this “official” version of the program is almost always designed with some mythical “average” user in mind. Nothing is wrong with this concept, but it almost certainly means that the program is not set up optimally for you. This chapter shows you how to get the most out of the main Office 2016 programs—Word, Excel, PowerPoint, Outlook, OneNote, and Access—by performing a few customization chores to set up the program to suit the way you work.

 

Working with Application Options

Customizing Office 2016 most often means tweaking a setting or two in the Options dialog box that comes with each program. Each program has a unique Options dialog box configuration, so it’s beyond the scope of this book to discuss these dialog boxes in detail. Instead, I introduce them by showing you how to get them onscreen and by going through some useful settings.

 

Working with the Options Dialog Box

You often need to access the Options dialog box for an Office 2016 application, so let’s begin by quickly reviewing the steps required to access and work with this dialog box in your current Office 2016 program.

  1. Select File. The Office 2016 application, Excel in this example, displays the File menu.
  2. Select Options. The Office 2016 application opens the Options dialog box.
  3. Select a tab. The Office 2016 application displays the options related to the selected tab.
  4. Use the controls to tweak the application’s settings.
  5. Select OK. The Office 2016 application puts the changed options into effect.

Working with Seasonal Time Series

Matters get incrementally more complicated when you have a time series that’s characterized in part by seasonality: the tendency of its level to rise and fall in accordance with the passing of the seasons. We use the term season in a more general sense than its everyday meaning of the year’s four seasons. In the context of predictive analytics, a season can be a day if patterns repeat weekly, or a year in terms of presidential election cycles, or just about anything in between. An eight-hour shift in a hospital can represent a season.

This chapter takes a look at how to decompose a time series so that you can see how its seasonality operates apart from its trend (if any). As you might expect from the material in Chapters 3 and 4, several approaches are available to you.

 

Simple Seasonal Averages

The use of simple seasonal averages to model a time series can sometimes provide you with a fairly crude model for the data. But the approach pays attention to the seasons in the data set, and it can easily be much more accurate as a forecasting technique than simple exponential smoothing when the seasonality is pronounced. Certainly it serves as a useful introduction to some of the procedures used with time series that are both seasonal and trended, so have a look at the example in Figure 5.1.

The data and chart shown in Figure 5.1 represent the average number of daily hits to a website that caters to fans of the National Football League. Each observation in column D represents the average number of hits per day in each of four quarters across a five-year time span.

 

Identifying a Seasonal Pattern

You can tell from the averages in the range G2:G5 that a distinct quarterly effect is taking place. The largest average number of hits occurs during fall and winter, when the main 16 games and the playoffs are scheduled. Interest, as measured by average daily hits, declines during the spring and summer months.

Fields and Forms in Word

Fields are the often-underappreciated placeholders that work behind the scenes in a document. They help perform the magic involved with many of the most powerful features in Word, such as mail merging, indexing, automatic generation of tables of contents, automatic figure numbering, cross-referencing, page numbering, and more.

There are many different types of fields, each with a specific purpose, but they break down into three main categories. You can use fields to do the following:

  • Insert text or graphics into the document, such as page numbering, dates and times, text from other documents, graphics from external files, document properties, or calculated values.
  • Mark a location for later use, such as with a bookmark, table of contents marker, or indexing code.
  • Perform an action, such as running a macro or opening a hyperlink in a web browser.

Yet another way to use fields is to create user-interactive forms. In this chapter, you see how fields work and how to insert them, and you find out how to use form fields to create forms.

 

How Word Uses Fields

Many people use fields in Word without even realizing it because so many of Word’s features automatically insert and modify fields. For example, when you insert a date or time and set it to be automatically updated, Word inserts a {Date} or {Time} code. And when you create an OLE link to an object, Word inserts a {Link} code.

Throughout this book, you’ve learned about fields in an indirect way. Whenever a feature has been discussed that used a field, you’ve learned to insert that field via a button or dialog box, but you haven’t looked too deeply yet at what’s really going on behind the curtain. Table 16.1 lists some of the Word features that employ fields and cross-references them to where those skills are covered in the book.

This chapter delves into the technical nitty-gritty details that govern fields and shows you how you can select, insert, modify, and format fields to accomplish a variety of document-creation and formatting tasks. Even if you don’t end up working manually with fields very often, this is not wasted study! The more you understand about how fields really work, the better you will be able to troubleshoot problems that may occur, or to tweak an individual field’s options to fit an unusual formatting need.

Know The Basic Formulas in Excel

A worksheet is merely a lifeless collection of numbers and text until you define some kind of relationship among the various entries. You do this by creating formulas that perform calculations and produce results. This chapter takes you through some formula basics, including constructing simple arithmetic and text formulas, understanding the all-important topic of operator precedence, copying and moving worksheet formulas, and making formulas easier to build and read by taking advantage of range names.

 

Understanding Formula Basics

Most worksheets are created to provide answers to specific questions: What is the company’s profit? Are expenses over or under budget, and by how much? What is the future value of an investment? How big will an employee’s bonus be this year? You can answer these questions, and an infinite number of others, by using Excel formulas.

All Excel formulas have the same general structure: an equal sign (=) followed by one or more operands, which can be values, cell references, ranges, range names, or function names, separated by one or more operators, which are symbols that combine the operands in some way, such as the plus sign (+) and the greater-than sign (>).

 

Formula Limits in Excel 2016

It’s a good idea to know the limits Excel sets on various aspects of formulas and worksheet models, even though it’s unlikely that you’ll ever bump up against these limits. Formula limits that were expanded in Excel 2007 remain the same in Excel 2016. So, in the unlikely event that you’re coming to Excel 2016 from Excel 2003 or earlier, Table 3.1 shows you the updated limits.

 

Entering and Editing Formulas

Entering a new formula into a worksheet appears to be a straightforward process:

  1. Select the cell in which you want to enter the formula.
  2. Type an equal sign (=) to tell Excel that you’re entering a formula.
  3. Type the formula’s operands and operators.
  4. Press Enter to confirm the formula.

However, Excel has three different input modes that determine how it interprets certain keystrokes and mouse actions:

  • When you type the equal sign to begin the formula, Excel goes into Enter mode, which is the mode you use to enter text (such as the formula’s operands and operators).
  • If you press any keyboard navigation key (such as Page Up, Page Down, or any arrow key), or if you click any other cell in the worksheet, Excel enters Point mode. This is the mode you use to select a cell or range as a formula operand. When you’re in Point mode, you can use any of the standard range-selection techniques. Note that Excel returns to Enter mode as soon as you type an operator or any character.
  • If you press F2, Excel enters Edit mode, which is the mode you use to make changes to the formula. For example, when you’re in Edit mode, you can use the left and right arrow keys to move the cursor to another part of the formula for deleting or inserting characters. You can also enter Edit mode by clicking anywhere within the formula. Press F2 to return to Enter mode.