Excel in PowerPoint
By: Naresh Nichani and Brian Reilly
Date Created: January 18th 2008
Last Updated: February 28th 2009
Nichani is a Most Valuable Professional (MVP) for Microsoft
Access based in Chennai, India. Naresh runs a software development
firm that specializes in Visual Basic development and Office integration.
He enjoys programming with Microsoft technologies as they are fairly easy to use and developers can build fairly complex solutions for customers with visually appealing interfaces quickly.
Brian Reilly is President of Reillyand, a Milford CT based company, that develops custom solutions typically integrating several of the Office applications for output into PowerPoint.
Brian is also a Most Valuable Professional (MVP) for Microsoft PowerPoint. He often creates customized solutions for clients that involve taking PowerPoint and other Microsoft Office applications to the limit.
Although you can copy and paste an Excel sheet inside a PowerPoint slide, there's so much more you can do to make the entire Excel-in-PowerPoint thing more useful. Microsoft Excel possesses an amazingly intuitive and powerful calculation engine -- and tons of formulae. We would like to leverage these capabilities of Excel while running a PowerPoint slide show.
Let us say you need to present some financial projections and also do some “what-if” analyses on these financial projections. Ideally you would like to:
- Do the financial projections in Excel while running the presentation in PowerPoint
- Edit the Excel content within a PowerPoint slide show and run the “what-if” analysis all within the PowerPoint slide show.
Follow these steps to get started -- you might also want to download the accompanying sample presentation here...
- Create a new presentation in PowerPoint, or add a new, blank slide to an existing one. Change the layout of the slide to either Blank of Title Only.
PowerPoint 2003 and earlier users can choose Format | Slide Layout, and apply the new layout within the relevant dialog box or task pane.
PowerPoint 2007 users need to select the Home tab on the Ribbon, and then click the Layout option to bring up the Layout gallery -- and then choose the relevant layout option.
- Now you will add an Excel spreadsheet object to this blank slide.
PowerPoint 2003 and earlier users need to make the Control Toolbox toolbar visible -- make sure that there is a checkmark next to Control Toolbox on the View | Toolbars menu option (see Figure 1).
Figure 1: Make the Control Toolbox visible.
On the Control Toolbox toolbar, click the More Controls icon to bring up the list of controls that you can add to your slide (see Figure 2).
Figure 2: Choose the Microsoft Office Spreadsheet Object
Scroll down the list till you get to the newest version of Microsoft Office Spreadsheet Object in your system -- we chose Microsoft Office Spreadsheet 11.0. This causes the cursor to change into a cross-hair -- draw a rectangle roughly in the center of the slide (see Figure 3).
Figure 3: The inserted spreadsheet object
PowerPoint 2007 users will need to enable the Developer tab on the Ribbon if it is not already visible. To do that, choose Office Button | PowerPoint Options, and select the Popular tab on the left. Then check the option that says Show Developer tab in the Ribbon.
Now select the Developer tab of the Ribbon, and click the More Controls option within the Controls group to bring up the list of controls that you can add to your slide (see Figure 4 ).
Figure 4: More Controls
This causes the cursor to change into a cross-hair -- draw a rectangle roughly in the center of the slide (refer again to Figure 3).
- Right click on the Excel object in the slide, and choose the Microsoft Office Spreadsheet 11.0 Object | Edit option, as shown in Figure 5.
Figure 5: Edit Spreadsheet Object
- Enter financial values and calculations into the Excel object. The spreadsheet below (Figure 6) is an example of some very simple calculations.
Figure 6: Enter Values
- Sales are $200,000 (cell C2).
- Fixed costs are $50,000 (cell C3).
- Variable costs are 65% of sales – $130,000 (cell C4)
- Net Profit is $20,000 i.e. Sales – (Fixed Costs + Variable Costs). It is in cell C5.
- In your presentation, you may want to allow the audience to enter different profit goals, and see what sales they need to achieve to meet those profit goals.
- Cell C7 is an entry cell to enter the profit goal.
- Cell C8 is the sales you need to achieve to meet the profit goal in cell C7.
- The formulae are listed in the D column for all C column entries
Remember, you don't have to manually input all the values above if you download the sample presentation...
Note: You can customize the appearance of the spreadsheet, insert and delete sheets, and even import data from a data source by right clicking the spreadsheet object and choosing Microsoft Office Spreadsheet 11.0 Object | Command and Options (see Figure 7).
Figure 7: Commands and Options
- Run the PowerPoint presentation, and while you are on the slide that contains the Excel object, you can edit the spreadsheet while the presentation is showing!
Tip: You can enter a profit goal with presentation running – for example, I entered $75,000 and spreadsheet recalculates with the sales I need to achieve this profit goal.
Tip: This technique can be used for any Excel sheet or formulae -- the what-if analysis shown in this tutorial is only such scenario.