Snapshots of Excel Worksheets for PowerPoint

Written by Allen Wyatt (last updated January 19, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003

Rebecca Birch has some clients who require that all reports be presented to them in PowerPoint. This presents a huge task, since all of the source data for those reports is available only in Excel. Rebecca was looking about for ideas to make the burden of converting from one to the other just a bit easier--perhaps by taking "snapshots" of worksheet data and placing it into PowerPoint slides.

One solution, if there are not that many snapshots necessary, is to simply do the pasting manually. You can display information in Excel, and then press the PrintScreen key to place a picture of it in the Office Clipboard. Switch to PowerPoint and choose Office Clipboard from the Edit menu. You can then see the contents of the Clipboard and choose what you want pasted into the current slide.

A less repetitive approach would be to link data from the Excel workbook to the slides. You can use Edit | Paste Special (in PowerPoint) to paste linked data. In this way, anytime the data in the workbook is updated, the linked slides will also be updated. Done correctly, this solution carries the possibility of only needing to do your pasting a single time.

If you prefer to take the route of developing macro to do the pasting, check out one developed by Jon Peltier at his Web site:

It will take a snapshot of whatever cells are selected, and then paste them into the active slide in PowerPoint. (Obviously, you must have both Excel and PowerPoint open in order to use the macro.)

Further, the macro could be relatively easily modified so that it stepped through a series of named ranges in Excel and pasted the contents of those ranges into specified slides in PowerPoint.

Another macro-based solution is to create a new PowerPoint presentation (from within Excel) that will contain a snapshot of each of the worksheets in the current Excel workbook. The following macro accomplishes this task:

Sub CopyWksToPPT()
    Dim pptApp As Object
    Dim sTemplatePPt As String
    Dim wks As Worksheet
    Dim sTargetTop As Single
    Dim sTargetLeft As Single
    Dim sTargetWidth As Single
    Dim sTargetHeight As Single
    Dim sScaleHeight As Single
    Dim sScaleWidth As Single
    Dim iIndex As Integer

    'Change these as desired
    sTargetTop = 30
    sTargetLeft = 60
    sTargetWidth = 600
    sTargetHeight = 450
    sTemplatePPt = "C:\Program Files\Microsoft Office\Templates\Blank Presentation.pot"

    iIndex = 1
    Set pptApp = CreateObject("Powerpoint.Application")
    With pptApp
        .Visible = True
        .Presentations.Open _
            FileName:=sTemplatePPt, Untitled:=msoTrue
        For Each wks In Worksheets
            .ActiveWindow.View.GotoSlide _
                Index:=.ActivePresentation.Slides.Add _
                (Index:=iIndex, Layout:=12).SlideIndex
            iIndex = iIndex + 1
            With .ActiveWindow.Selection.ShapeRange
                sScaleHeight = sTargetHeight / .Height
                sScaleWidth = sTargetWidth / .Width
                If sScaleHeight < sScaleWidth Then
                    sScaleWidth = sScaleHeight
                    sScaleHeight = sScaleWidth
                End If
                .ScaleHeight sScaleHeight, 0, 2
                .ScaleWidth sScaleWidth, 0, 2
                .Top = sTargetTop + (sTargetHeight - .Height) / 2
                .Left = sTargetLeft + (sTargetWidth - .Width) / 2
            End With
        .Visible = True
    End With
End Sub

Note the area that says "Change these as desired." This contains the specifications of where the pasted snapshot will be within each PowerPoint slide, as well as its height and width. Also included, in the sTemplatePPt variable, is the full path to the template that should be used for the new PowerPoint presentation.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2378) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Controlling the Hidden Text Attribute

Want your macro to change the Hidden attribute for some text in your document? It's easier to change than you might think.

Discover More

Getting Rid of Cells Containing Only Spaces

If you have a worksheet that contains a bunch of cells that contain nothing but spaces, you may be looking for a way to ...

Discover More

Using Find and Replace to Change Text Case

Can you really use Find and Replace to change the case of text in your document? Not really, but that shouldn't stop you ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Excel Charts in PowerPoint

A common place to use Excel charts is in your PowerPoint presentations. How you paste those charts into the presentation ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 2?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.