Graphic effect formatting across documents and applications

Microsoft introduced themes back in Office 2007, but sad to say, most people don’t understand themes today any better than they did back then. They could tell you that a theme has something to do with styles, formatting, and colors, but if you press them for details, you get averted eyes and foot-shuffling. Is a theme built into a document, or is it a separate file? Is a theme the same thing as a template? How do you share themes between applications? In this article, I hope to clear up some of the persistent confusion around what exactly a theme is and how it does its magic.


What is a Theme?

A theme is a named group of settings that you can apply to a document to change its appearance. At a minimum, it includes three elements: colors, fonts, and effects. (In PowerPoint, your choice of theme also affects a couple of other aspects of the presentation, such as background image and variants.) Themes have names, such as Facet, Integral, and Ion; these names are the same across Word, Excel, and PowerPoint, so you can use the same theme in each application for consistency.

A theme is not the same as a template. A template is an entire document file (or workbook, or presentation), but with a different extension to designate it a template rather than a regular document. It’s a full-fledged reusable sample. A theme, on the other hand, is just a collection of settings that can be applied to a document.

Is a theme a separate file? Well, it depends on what you mean by theme. The word can refer to the current theme settings in a Word, Excel, or PowerPoint document file, and in that sense, a theme is contained within the document to which it is applied. But theme can also refer to a file with a .thmx extension that stores theme settings independently of any data file.

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("MyRange") 'assuming that D5 has a 'Name of MyRange

Sharing Content Wirelessly with Apple AirDrop

Not too long ago, my mother called me in a panic. “Tim, I lost all the contacts on my iPad! Will I have to manually re-create them all again?”

The good news for my mom is that I already knew that (a) she had a copy of her contacts database on another iDevice at her home, and (b) we could use AirDrop to copy those missing contacts back to her iPad with no muss or fuss.

In just a few minutes, you too will understand how easy and convenient AirDrop makes copying content between iDevices without the hassle of wires or complicated setups.


What Exactly Is AirDrop?

AirDrop is an Apple-only technology that uses wireless, peer-to-peer networking to support near-range file transfers. Do you remember how we used infrared (IR) to swap data in the “bad old days” of early cell phones and personal digital assistants (PDAs)? Think of AirDrop as a 21st century counterpart.

Whereas infrared required line-of-sight and very close ranges (I remember my colleagues and I literally bumping our Palm Pilots against each other to catch a signal), AirDrop has a much more reasonable 30-foot range. Because AirDrop uses Wi-Fi and Bluetooth, we don’t need line-of-sight between the devices, either.

What Can You Share with AirDrop?

To my knowledge, Apple has never released a list of exactly which data types you can transmit between Apple devices by using AirDrop. In my experimentation with the technology, though, I’ve been able to share the following:

  • Photos
  • Movies
  • Contacts
  • Web sites
  • Location
  • Maps
  • Calendar appointments
  • Tasks

As you may or may not know, the key to different iOS applications “playing well with each other” is what’s called the sharing contract. Take a look at Figure 1 and let me know if you’ve seen that icon before.

Perhaps that’s why Apple doesn’t pin itself down to saying AirDrop can be used to transfer such-and-so media types. The idea is likely that if the app and its data work with the sharing contract system, you’ll probably be able to copy it by using AirDrop.

Paper Citations Made Easy with Microsoft Word

If you’ve ever taken an academic course that required a research paper (or several), you have probably agonized over how to format the citations. Citations can be a pain in the neck, even for people who write lots of papers. There are many different different standards, each one popular in a different academic discipline, and each one has complicated rules about how to format entries.

Until the last decade or so, there wasn’t an easy way of applying citation formatting. Your best bet was to look up the formatting for a specific resource type on a reference book or on a web site, and then try to duplicate it was best you could. There were word processor add-on apps that could help, but most of them weren’t very good, were expensive, or both.

All that changed, though, with Microsoft Word 2007. Among the many great new features in that version was a Citations & Bibliography tool that changed the way millions of people handled citations. That same feature, with some improvements, has carried over to Word 2010 and 2013 too.

If you’ve been formatting citations manually for your research papers, you’ll be astounded at how easy Word makes it to get the citations right, no matter what citation format your instructor wants you to use.

  • Step 1: Select the desired citation style.
  • Step 2: Enter the sources you want to cite.
  • Step 3: Insert in-text citations.
  • Step 4: Generate the bibliography.

Let’s look at each of those steps in detail, with some examples.

If you know the citation style that your instructor expects you to use, you should select it upfront. You can change to a different citation style later, but some of the form fields in step 2 change slightly depending on the style chosen.

To choose a citation format, display the References tab. Open the Style drop-down list and select the desired style. See Figure 1.

The Buffer Pool Extension in SQL

With CPU speeds topping out and I/O rates maximized using solid state drives (SSDs), the next available strategy for increasing OLTP performance is through memory optimization. Databases, because of their size, typically reside on disk. Historically, main memory was significantly more expensive than disk, so typically the memory available for caching data was only a fraction of the size of the database. However, with the significantly reduced cost of system memory over the past 20 years, it’s become more financially feasible to install large amounts of memory in the server. It is now possible for most OLTP databases, or at least the most critical tables, to fit entirely into memory which reduces the performance impact of disk-based I/O, which in turn increases transaction speed performance.

To take maximum advantage of the performance improvements that can be achieved from having your critical OLTP tables memory resident, Microsoft developed the In-Memory Optimization feature for SQL Server. In-Memory Optimization, more commonly referred to as In-Memory OLTP, is the primary and most important new feature introduced in SQL Server 2014. This new feature (which you may sometimes hear referred to by its project code name Hekaton) is fully integrated into the SQL Server database engine.

Another feature introduced in SQL Server 2014 to take advantage of the lower costs and increased sizes of SSDs, is the Buffer Pool Extension feature. The Buffer Pool Extension feature provides the ability for SQL Server to use solid-state drives (SSD) as a non-volatile random access memory (NvRAM) to extend the size of the buffer pool. By offloading buffer cache I/Os from mechanical disk to SSDs, the Buffer Pool Extension feature can significantly improve I/O throughput because of the lower latency and better random I/O performance of SSDs.

These exciting new features for SQL Server are discussed in this chapter.


Overview of In-Memory OLTP

In-Memory OLTP allows OLTP workloads to achieve significant improvements in performance, and reduction in processing time. The In-Memory OLTP engine is completely integrated with the SQL Server database engine and can be accessed transparently via your SQL Server applications. However, the In-Memory OLTP components’ internal behavior and capabilities are different and distinct from the standard database engine components as shown in Figure 33.1

The New Pebble Time Smart Watch

When the original Pebble smart watch was first announced in 2012, it was introduced as a concept that the inventors needed to raise funds in order to build. They turned to crowd funding via, and quickly became the online service’s biggest success story, after raising more than $10 million.

Since then, Pebble has sold more than one million first generation smart watches, but has also been focusing on innovative ways to improve upon the product. In late March 2015, Pebble completed a second crowd funding campaign in preparation for the launch of its second-generation smart watch, called Pebble Time. This time, the company quickly raised in excess of $20.3 million, thanks to almost 79,000 crowd funding backers.


Pebble Time Offers Greatly Expanded Features and Functions

Within days after announcing Pebble Time, hundreds of third-party app developers began developing cutting-edge apps for this new smart watch, which boasts a full color e-paper display and a seven day battery life. It also contains a built-in microphone, accelerometer, can communicate wirelessly with a smartphone via Bluetooth 4.0, and it runs using a re-designed operating system (Pebble OS).

Plus, in addition working in conjunction with the iPhone, as does the Apple Watch, the new water resistant Pebble Time smart watch works seamlessly with Android 4.0+ compatible mobile devices, including popular smartphones from Samsung, HTC, Sony, LG, Google, and Motorola.

While Pebble smart watch enthusiast will definitely appreciate the full-color display and the extended battery life, the unit itself is also 20 percent thinner than the original Pebble, and is more ergonomically designed. As a result, it’s more comfortable to wear during a workout, while engaged in high-intensity land or water-based activities, or under the sleeve of a dress shirt.

The display offers a backlight, so it can easily be viewed in bright sunlight, or in the darkest of areas. The Pebble Time’s dimensions are 47mm (long) by 49mm (wide) by 9.5mm (thick), and with the silicone band, the entire watch weights just 42.5 grams.

Pebble Time is vastly superior to its predecessor, and is designed to complete head-on with Apple Watch, but it offers a less expensive smart watch option. It costs just $199.00, and is available in three housing and watchstrap colors (black, white and red).

AlwaysOn and Availability Groups

With SQL Server 2014, Microsoft continues to push the high-availability (HA) and performance (scale-out) bar higher and higher. Extensive HA options such as AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances, coupled with a variety of Windows Server family enhancements, provide almost everyone with a chance at achieving the mythical five-nines (that is, 99.999% uptime). We’ll dive into the AlwaysOn new features in this chapter. This capability is taking the database world by storm. It is truly the next generation of HA and scale-out for existing and new database tiers of any kind. Some of the concepts and technical approaches in AlwaysOn and availability groups might seem a bit reminiscent of SQL clustering and database mirroring because they are. Both of these earlier features paved the way for what we now know as AlwaysOn and availability groups.


What’s New in SQL Server AlwaysOn and Availability Groups

Now, with a couple of years under their belt with these features, Microsoft is starting to open up several of the previously tight limitations such as the number of secondaries allowed.

  • You can use AlwaysOn and availability groups with complex data managed through FILESTREAM, even when using Remote Blog Storage and FileTable.
  • Up to 8 secondary replicas can be defined for any one availability group. This used to be 4 secondary replicas max.
  • There can be up to 3 Synchronous Commit replicas.
  • And, client applications can achieve failover across multiple subnets (as many as 64) almost as fast as they can achieve failover within a single subnet.

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.