Pivotal Advance Boosts Excel’s Power

In today’s accounting world, financial and operational data typically is stored in a variety of programs and formats. When accountants need to prepare a report based on data from various systems, the first step is to export the data into Excel. Typically, it is fairly easy to export the required data into Excel. But, depending on the structure or format of the data, or if multiple data tables need to be combined, it is not always easy to summarize the data in a single report.

That’s where a new Excel tool called PowerPivot comes into play. PowerPivot is a free plug-in from Microsoft that boosts the capabilities of the already popular PivotTable function, allowing you to create previously impossible PivotTables that enhance Excel’s efficiency and effectiveness.

How does PowerPivot help? Consider this example. Let’s assume that forecast data is stored in one place—perhaps in an Excel workbook, an application or a database. Let’s also assume that actual data is stored elsewhere, most likely in an accounting system. It is your job to prepare a forecast vs. actual analysis similar to the one pictured below. To accomplish this, you need to pull the forecast data into one Excel worksheet, pull the actual data into another worksheet, and then combine them so you can compute the variance.

Common approaches for combining data from two or more worksheets have included worksheet functions such as VLOOKUP, SUMIFS, INDEX and MATCH. While these functions work, they tend not to be suited to recurring processes because they need to be monitored and filled down as new data is added each month. PowerPivot provides a better way to handle such recurring processes, making life easier for CPAs.

This article examines how CPAs can leverage PowerPivot to enhance their Excel reports, and then provides a step-by-step technical walk-through that shows how to use this powerful tool.

BACKGROUND

Because PowerPivot essentially is an extension of the PivotTable feature, let’s start by quickly discussing PivotTables. By simple definition, a PivotTable is a report that summarizes transaction details. In practical application, this feature ranks among the most powerful data analysis tools in Excel. If you’ve never played with PivotTables, they are worth the time to explore. In addition to the JofA articles referenced in the accompanying AICPA Resources box, the Excel Help system, youtube.com and microsoft.com provide a wealth of information to get you started and ready for the advanced PivotTable features discussed in this article.

Microsoft’s developers worked on several PivotTable enhancements for the latest version of Excel, but the key technical upgrade is PowerPivot. How pivotal an advancement is PowerPivot? It’s pivotal enough that Microsoft created a new website for it: powerpivot.com, which provides videos, tutorials, information, samples and the download link for the plug-in.

PowerPivot is not installed by default with Excel. Microsoft decided to deliver it as a free plug-in, and thus, it needs to be downloaded and installed. If you need assistance with the installation, please refer to the sidebar “How to Install PowerPivot.”

POWERPIVOT

PowerPivot is a utility that sits between the source data and the report. It grabs the data and feeds it into the PivotTable engine.

We’ll explore the following PowerPivot advantages:

Multiple data sources (pull data from two or more sources into a single report) Many types of sources (pull data from just about anywhere into a PivotTable) Sets (advanced filtering) Large data sources (analyze data that exceeds Excel’s row limit) Expressions (advanced functions and time intelligence).

MULTIPLE DATA SOURCES

PowerPivot makes it easy to combine data from a variety of sources into a single PivotTable report. When the data you need for your report exists in two or more places, you can use PowerPivot to combine the data and feed it into a single PivotTable.

Let’s revisit our previous example. Forecast data is stored in one place, and actual data is stored in a different place. Your report needs to compare forecast vs. actual. In this case, you can simply place the forecast data into one Excel worksheet, then place the actual data into another worksheet. Now that both are in Excel, you can pull both worksheets into PowerPivot, and PowerPivot will send the combined results to your PivotTable report.

This approach of getting the data from wherever it is into a single Excel workbook, split into numerous worksheets, often will suffice. However, in some cases, you can gain efficiency with recurring reports by connecting directly to the data source (the application or database that contains the data).

MANY TYPES OF SOURCES

PowerPivot can grab data from many data sources—for example, SQL databases, Access databases, SQL Server Reporting Services reports, Azure DataMarket, Web data feeds, flat text files, Oracle, Sybase, DB2 and Excel files.

Pulling in data from external sources eliminates a common step. Accountants frequently will do an “export” from an accounting system, do an “import” into Excel, and then spend time preparing and reformatting the data. By connecting directly to the external data source, we can eliminate those steps because a simple click will refresh and pull in recent data.

SETS

A Set is a selection of data points to be used in the PivotTable. If you are familiar with PivotTable filters, you’ll recognize this feature as an advanced filter that allows you to sift through data and choose to display just about any combination.

To illustrate, let’s say you receive a data export that includes both actual and budget values combined in a single table. Specifically, it includes the following columns: AcctNum, Account, Amount, Type (which identifies Actual or Budget) and Period (see below).

The report that you need to generate from this data should have two columns of numbers, one for Actual 2011, the other for Budget 2012 (see below).

Experienced Excel users will recognize that it is not possible to prepare this report with a traditional PivotTable. That’s because it’s impossible to apply filters to show Actual values only for 2011 and Budget values only for 2012 with a PivotTable. This is because filters apply to the entire column. Thus, you can’t show 2011 for some rows (that is, Actual rows) and show 2012 for other rows (that is, Budget rows). If “2011” is selected in the filter, all transaction types are shown for 2011. The filter applies to all transactions. Thus, the built-in filters in a traditional PivotTable are insufficient to provide this level of flexibility. Experienced Excel users naturally would conclude that this report can’t be prepared using a PivotTable and instead would use a worksheet function such as SUMIFS to finish the report.

PowerPivot literally changes the equation. With PowerPivot, you can use the Sets feature to complete this report with a PivotTable, which affords all of the related automation and efficiency.

LARGE DATA

Additionally, PowerPivot supports the inclusion of millions of records and large data sets. Thus, when connecting to external data sources, you can analyze far more data than will fit into a single Excel worksheet.

EXPRESSIONS AND CALCULATIONS

With the introduction of PowerPivot comes a powerful set of tools called Data Analysis Expressions, or DAX for short. DAX can be used to compute measures (see sidebar “PowerPivot at a Glance,” below), as well as “calculated columns” inside of PowerPivot’s tables. You’ll recognize many functions from Excel, such as SUM, but there are many additional functions that are designed specifically to operate on large tables within PowerPivot. Some of the interesting functions include time intelligence functions, such as SAMEPERIODLASTYEAR, TOTALMTD, ENDOFYEAR and others.

TECHNICAL WALK-THROUGH: CREATING A POWERPIVOT-BASED PIVOTTABLE

As you can see, PowerPivot adds a great deal of features and functionality to Excel. Now that you have seen some of the advanced applications and uses of PowerPivot, let’s go step-by-step through a simple example. The goal is to show you how to create a simple PowerPivot-based PivotTable. Specifically, this exercise will walk you through sending data stored in a worksheet to PowerPivot and using the PowerPivot data to create a basic PivotTable report.

You can follow along with this walk-through using the sample Excel file titled “Excel Workbook Walk-through.”

Let’s start by envisioning ourselves in this scenario: You have exported transactions from your accounting system, and you have the data stored in an Excel worksheet. You need to generate from this detail a summary by Account.

After you have downloaded and installed PowerPivot (refer to the sidebar “How to Install PowerPivot”), your first task is to send the Excel data into PowerPivot. This is accomplished by establishing a link between your worksheet data and PowerPivot. To establish the link, first activate any cell in the data range in Excel or highlight the entire range. Next, click the following Ribbon button from the PowerPivot Ribbon: PowerPivot > Create Linked Table

The Create Table dialog box will pop up, asking to confirm the range and to confirm if your table has a header row, as illustrated.

Click OK to convert your data range into a Table and launch the main PowerPivot window, as seen below. Please note: Don’t confuse “Table” with “PivotTable” as these are different features. Find additional information on Tables in the Excel Help system.

Now that you have successfully sent your worksheet data into PowerPivot, you can use the data in a PivotTable report. To create a new PivotTable report based on the PowerPivot data, click the PivotTable button from within the PowerPivot window (not the “normal” PivotTable button in Excel’s Insert Ribbon tab).

You’ll be presented with the Create PivotTable dialog box, shown below, where you can opt to create the report in a New Worksheet or Existing Worksheet. For this exercise, leave the default New Worksheet option selected and click OK.

Excel will insert a PivotTable into a new sheet in the workbook and provide PivotTable controls. There are some differences between the traditional PivotTable Field List window and the PowerPivot Field List window, namely, the inclusion of Slicer areas (see sidebar, “Other PivotTable Enhancements,” below). To create a PivotTable that summarizes the amount by Account, insert the Account field into the Row Labels area by checking the box for Account in the PowerPivot Field List, and then insert the Amount into the Values area by checking the box for AmountActual. The resulting PivotTable is shown below.

Congratulations, you have successfully created your first PowerPivot PivotTable! There are many advanced features of PowerPivot, so please continue to explore this powerful new tool.

ADDITIONAL RESOURCES

How to Install PowerPivot

Installing the plug-in is relatively easy. Go to powerpivot.com and click “Download now.” That will take you to a Microsoft.com page. Click the “Download” button for your computer system. Most likely it will be 1033\PowerPivot_for_Excel_x86.msi unless you have a 64-bit processor, which would require the 1033\PowerPivot_for_Excel_amd64.msi file.

After the download completes, run the install and click Next as you walk through the wizard. Once you have successfully installed PowerPivot, you’ll notice a new Ribbon tab inside of Excel called PowerPivot. The main Ribbon button is the PowerPivot window button. If you subsequently launch Excel only to find that the PowerPivot plug-in didn’t load, follow this path to reactivate it: File > Options > Add-Ins > Manage: COM Add-ins > Go.

PowerPivot at a Glance

Here are some additional technical specifications and details about PowerPivot:

If you are familiar with database terms, PowerPivot supports one-to-many and one-to-one relationships, but not many-to-many relationships. It supports primary/foreign key relationships, but not composite key relationships. PowerPivot includes rows that don’t have a matching lookup row. In PowerPivot, the foreign key table is called the “Table,” which is the source table, because it has the source transactions. The primary key table is called the “Related Lookup Table,” because it has the values being looked up. It feels the opposite of what I would expect, so I remember it by thinking of how the VLOOKUP function operates. The Measure function is quite powerful and includes the ability to reference other computed fields and perform other advanced functions. For more information on measures go to tinyurl.com/6appkay.

Other PivotTable Enhancements

PowerPivot isn’t the only PivotTable enhancement in Excel 2010. Here’s a quick look at some of the others:

Repeat Row Labels

It is now possible to repeat row labels all the way down through a PivotTable. This is accomplished either per label (Value Field Settings) or for all row labels.

First, switch to Outline view:

PivotTable Tools > Design > Report Layout > Show in Outline Form

Then, repeat item labels:

PivotTable Tools > Design > Report Layout > Repeat All Item Labels

Last, put the subtotals under the detail:

PivotTable Tools > Design > Subtotals > Show All Subtotals at Bottom of Group

Percent of Parent

You can now show as a percent of the parent. To display a value as a percent of the parent, select the column and then open the Value Field Settings dialog box. On the Show Values As tab, select Percent of Parent Row Total.

Slicers

Slicers represent an easy and visual way to filter PivotTable data. Note that Slicers can be used with any normal PivotTable, but they are easy to implement with PowerPivot PivotTables. In the Field List window, you’ll notice Slicers Horizontal and Slicers Vertical regions. Drag the field item into the desired region, and Excel will insert the slicer automatically into the worksheet. For more on Slicers, read the Technology Q&A item “Excel’s New Slicer—It Slices, It Dices,” JofA, April 2011, page 59.