Monthly Archives: March 2017

The application options and customizing the interface

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.

Managing and Sharing Office Files

The Microsoft Office 2016 applications provide you with all the tools you need to create documents, presentations, workbooks, and publications. After you create your various files using the Office applications, it is up to you to manage your files and share them with colleagues and co-workers.

In this chapter, we take a look at the Office file formats used in each of the Office applications. We also look at your options for managing and sharing files.

 

Understanding Office File Formats

The default file formats for each of the Office applications (all except for OneNote) take advantage of the open XML (eXtensible Markup Language) file standards. The file formats provide benefits in terms of file compaction, improved damage recovery, better detection of files containing macros, and better compatibility with other vendor software.

Although some backward-compatibility issues may be involved when you attempt to share a file using one of these file formats with a user who still works with an earlier version of a particular Office application (think pre-Office 2007 versions), most problems have been ironed out. Users still working with earlier versions of the Office applications can take advantage of various conversion utilities and software updates that enable them to convert or directly open a file using one of the new file formats.

You can also save your files in file formats that offer backward compatibility for co-workers still using older versions of the Office applications. And the Office applications (such as Word and Excel) provide you with compatibility-checking tools that help negate any issues with files shared with users of legacy Office applications.

As already mentioned, Word, Excel, and PowerPoint use the open XML file formats by default when you save a file in these applications. And you have a number of other file format options in these applications, if needed.

Publisher 2016, on the other hand, saves publications by default in the .pub file type. The .pub file type is “directly” compatible with Publisher 2013, through Publisher 2003. Although Publisher does not enable you to save a publication in the open XML file format (like Word and Excel), you can save Publisher files in the XPS file type, which is an XML file format for “electronic paper.” Publisher also has file types available that you can use to make your publications backward compatible with collaborators who are using previous versions of Microsoft Publisher.

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.

The charted data series includes data labels showing which quarter each data point belongs to. The chart echoes the message of the averages in G2:G5: Quarters 1 and 4 repeatedly get the most hits. There’s clear seasonality in this data set.

 

Calculating Seasonal Indexes

After you’ve decided that a time series has a seasonal component, you’d like to quantify the size of the effect. The averages shown in Figure 5.2 represent how the simple-averages method goes about that task.

Shows you different ways of referring to ranges

A range can be a cell, a row, a column, or a grouping of any of these. The RANGE object is probably the most frequently used object in Excel VBA; after all, you are manipulating data on a sheet. Although a range can refer to any grouping of cells on a sheet, it can refer to only one sheet at a time. If you want to refer to ranges on multiple sheets, you must refer to each sheet separately.

This chapter shows you different ways of referring to ranges, such as specifying a row or column. You’ll also find out how to manipulate cells based on the active cell and how to create a new range from overlapping ranges.

The Range object is a property of the Worksheet object. This means it requires that a sheet be active or else it must reference a worksheet. Both of the following lines mean the same thing if Worksheets(1) is the active sheet:

There are several ways to refer to a Range object. Range("A1") is the most identifiable because that is how the macro recorder refers to it. However, all the following are equivalent when referring to a range:

Range("D5")
[D5]
Range("B3").Range("C3")
Cells(5,4)
Range("A1").Offset(4,3)
Range("MyRange") 'assuming that D5 has a 'Name of MyRange