• Technology
  • CFO.com | US

Spreadsheets: Mission Impossible Completed with PowerPivot

The makers of PowerPivot should be nominated for a Nobel prize, quips Jelen. Why? For one thing, PowerPivot will wrest the power of business intelligence from the IT department and put it in the hands of accounting and finance.

After I saw Kelly’s question, I put reason #5 to the test. Within five minutes, I had solved this previously unsolvable problem.

Follow these steps to install PowerPivot:
1. Make sure you have Excel 2010. PowerPivot requires Excel 2010.
2. Go to File, Help. Look on the right side to figure out if you are running 32-bit or 64-bit Excel. You will need this information in step 4.
3. Close Excel.

Fig. 3

MrExcel 4-13Fig03

4. Browse to www.PowerPivot.com. They offer two free downloads. Download either the 32-bit or 64-bit version to match your version of Excel. (See Figure 3, above)
5. Install PowerPivot.
6. Re-launch Excel. You should now see a PowerPivot tab at the right side of the Excel 2010 ribbon. (Figure 4)

Fig. 4

MrExcel 4-13Fig04-B.gif

 

In the above list, Big Six reason #3  says that you can import data from many different systems. However, in this case, your data is already in Excel. In that case, you can simply copy and paste, or create a linked table. Since Kelly talked about a lot of data, the linked table is probably the way to go.

7. Open the Excel workbook that has the existing data set (Figure 5). Consider deleting any existing pivot tables, since you will be replacing the traditional pivot table with a PowerPivot pivot table.
8. Select one cell in your data and press Ctrl T to declare the dataset as a Table. Click OK. The data will be formatted, but more importantly, Excel understands the data set as a defined table.

Fig. 5

MrExcel 4-13Fig05-B.gif

 

9. On the PowerPivot tab of the Excel ribbon, choose Create Linked Table (Figure 6). You will now see your data in the PowerPivot window (Figure 7).

Fig. 6

MrExcel 4-13Fig06-B.gif

 

 

 

 

 

 

 

Fig. 7

MrExcel 4-13Fig07-B.gif

 

10. From the Home tab in PowerPivot, click on the PivotTable icon. Choose New Worksheet as the location and click OK. You are returned to Excel and a blank pivot table with a slightly different Pivot Table Field List (Figure 8).

Fig. 8

MrExcel 4-13Fig08-B.gif

 

11. Select the Product field and move it to the row labels just as you would do in a regular pivot table.

Discuss

Your email address will not be published. Required fields are marked *