Pivotal Advance Boosts Excel's Power: Expand Your Analytical Skills with This Step-by-Step Guide
Jeff Lenning · Journal of accountancy online/Journal of accountancy · 2011
[ILLUSTRATION OMITTED] 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. 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. [ILLUSTRATION OMITTED] 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. [ILLUSTRATION OMITTED] 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 m 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 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) TECHNOLOGY WORKSHOP MULTIPLE DATA SOURCES PowerPivot makes it easy to combine data from a variety of sources into a single PivotTable report. …