Jumbabox

This tutorial will look at building an interactive (drag and drop, filter etc.) Pivot Table into your PowerPoint presentation using Microsoft Office Web Components.

Related : Building a Pivot Chart into your PowerPoint presentation

Step 1 - How does it work?

With this method you can only build a Pivot Table or Pivot Chart in a PowerPoint presentation based on an Access database table (dataset) which is linked to an Excel sheet.

Pivot Table <–Microsoft Office Web Components– PowerPoint <–OLE Db Connection– Access <–linked– Excel Sheet

You will notice that I have linked an Excel worksheet to the Access database - this is so the whole process is driven by the Excel worksheet (you may just want to work with your data in Access if so skip Step 2).

Pivot Charts and Pivot Tables are only interactive in full screen or presentation mode

Step 2 - Setting up your Access database

First create a new Access database or open up the one you wish you want to build your Pivot Table on, as mine is new I will need to input some data. I will not be importing data as I would like to keep the whole process between Pivot Table - PowerPoint - Access dynamic so I will be linking my dataset from Excel as a table in Access.

To link an Excel worksheet as a table in Access, right click in the Tables area, then click on the Link Tables… option.

This should display a Link dialog box, in the File Name: drop down box I have selected the Microsoft Excel (*.xls) option so that I can find the Excel worksheet I want to link.

Once you have found the Excel file which contains the data you want to build your Pivot Table on, click the Link button.

After this a Link Spreadsheet Wizard will pop up, the first step will ask you which sheet (in your Excel workbook) you would like to link. I have labelled the sheet which I want to link, data - the wizard will also show a preview of the sheet (see below screenshot).

Once you have selected the correct sheet click the Next button, you will now be asked if the First Row Contains Headings, in this example it does, so I have checked the check box, this then reflects in the preview window of the wizard as well. Click the Next > button to continue.

The last step is to label the name of the linked sheet, I will be calling mine Data, once you have picked an appropriate name click the Finish.

A confirmation box will pop up letting you know if the linking was successful, click OK to continue.

Now you should be able to see your linked sheet in the Tables area.

Step 3 - Setting up your Pivot Table in PowerPoint

Open up your PowerPoint presentation make sure the Control Toolbox is visible, if it isn’t, right click in the top area of PowerPoint and click the Control Toolbox option.

You should be able to see the Control Toolbox now.

Click on the More Controls button found on the Control Toolbox toolbar.

This will open a up a list of objects which you can insert into your PowerPoint presentation, for this example scroll down till you find the Microsoft Office Pivot Table 10 or 11 (it doesn’t matter which version you you pick).

After selecting your object you mouse pointer will turn into a cross.

Click and drag the cross to a desired size for the Pivot Table. Within this area you should now see an embedded objects labelled Microsoft Office Web Components.

Right click inside the Microsoft Office Web Components then select the Microsoft Office Pivot 11.0 Object > Edit option.

Step 5 - Setting up the connection for a Pivot Table

The Command and Options dialog box will pop up, this is where the connection settings are located. Click on the Connection radio button and then click the Edit… button.

This will pop up a Select Data Source dialog box, if you haven’t already setup a .odc connection to your Access database, click on the New Source… button.

After clicking the New Source… button, a Data Connection Wizard will appear, click on the Other/advanced option and click the Next > button.

A Data Link Properties dialog box will appear. In this example we are connecting to an Access database, and that uses the Microsoft Jet as the OLE DB Provider so select that option or select one that suits you then click the Next >> button.

Within the same Data Link Properties dialog box you will be asked to browse for the location of your Access database by using the … button. You will also be asked if you database has a Username or Password, in this example I left my User name and Password blank.

After setting up the options in your Data Link Properties dialog box, click the Test Connection button and you should receive this message box alerting you of a successful or unsuccessful test.

Click OK and then click the OK button again to get back to the Data Connection Wizard. You will be asked to choose a table within your database, in my database the table is named Data. After selecting a Table within your database click the Next > button.

You will then asked to enter in the File Name of your .odc file and also a Description, I would suggest checking the Save password in file check box as well just to save any password prompts appearing down the line. Once you are done click the Finish button.

A dialog box will pop up asking for you to open your new .odc file, the file will already be inputted in the text box so click on the Open button.

You will then be brought back to the Command and Options dialog box, you will be able to see that in the Connection textbox that a connection string has been setup and also that the table Data has been selected under the Data member, table, view or cube name area.

You may want to also set up some options on your Pivot Table (I left them blank) but you can do this by clicking on the Behaviour and Protection tabs.

You have successfully setup a connection for your Pivot Table! Click the OK button to close the Command and Options dialog box.

Using your Pivot Table

After clicking the OK button from the Command and options dialog box, you might be shocked to see that your Pivot Table is blank (like the screenshot below).

Don’t worry! To work with Pivot Charts or Pivot Tables in PowerPoint you will need to be in Presentation or Fullscreen mode.

To do this press SHIFT+F5 or the Presentation Icon located in the bottom left hand corner:

Now you should be able to see your Pivot Table in all it’s glory!

The tool bar at the top is the Microsoft Office Web Components tool bar, hovering over each icon will give you some information on the features available.

After clicking on the Fields List icon on the tool bar (see above) and dragging and dropping some fields I now have a Pivot Table built into my PowerPoint presentation based on an Access database linked to an Excel sheet!

If you have any questions or problems please comment.

Enjoy!