Contextures

Create PowerPoint Slides From Excel List with Macro

Use these free macros to quickly create PowerPoint slides from an Excel list. You can fill 1 or 2 text boxes in each slide, and select which columns to use from the Excel list. Create a slide for every item in the list, or only items with specific text in one column, such as "Y".

PowerPoint Slides From Excel List

Why would you use macros to create PowerPoint slides from an Excel list? It's a quite way to build a slide deck, without copying and pasting between those two applications.

For example, create quick slides to:

  • Show the name and department of each person attending a company meeting
  • Start a presentation from a list of topics in Excel
  • Introduce presenters at a conference
  • And many more reasons!

This page has PowerPoint macros that create slides from an Excel list. You can copy the macros into your PowerPoint file, and run them there.

Or, download the folder with sample files, at the end of this page. The folder contains:

  • Excel file with a sample list for testing the macros
  • PowerPoint file with a main slide, notes, and the macros

Excel Setup

In the sample Excel file, there is a table with 3 columns - Name (1), Dept (2), Attend (3)

  • Columns 1 and 2 are used to fill the text boxes when the macros run
  • Column 3 is used in macros that check criteria
    • Slides are created if there's a "Y" or "y"

NOTE: You can change the macros so they use information from different columns

list in named Excel table

PowerPoint Setup

In the sample PowerPoint file that you can download below, there are:

  • Main slide that is duplicated to create the individual slides
  • Notes on two slides, about using the macros
  • Macros that create slides from an Excel list

Main Slide

In the sample PowerPoint file, there is a main slide with two text boxes. The macros copy that slide, and create individual slides from it, using the text in the Excel list.

NOTE: The macros use Slide #1 when creating duplicates, so be sure your main slide in that #1 position.

main slide in position 1

Change the Main Slide

In the sample file, the main slide's formatting is based on its Slide Master.

To change the Slide Master:

  • Select the main slide (slide #1)
  • On the Ribbon, click the View tab
  • Click Slide Master, and make changes to the formatting and content.
  • When finished, click the View tab, and click Normal

slide master

Notes Slides

There are 2 slides with brief notes about the macros. You can leave those in the PowerPoint file, or delete them.

notes files in powerpoint

PowerPoint Macros

The PowerPoint file is saved in pptm (macro-enabled) format, and contains four macros. The macro code is further down this page.

Run Macros to Create Slides

The macros that create slides are stored in the PowerPoint file. There are no macros in the Excel workbook.

To create PowerPoint slides, from a list in Excel, follow these steps:

  1. Open the Excel file
  2. Activate the sheet where the data is stored in a named Excel table
  3. Open the PowerPoint presentation with the macros and main slide
  4. Be sure the main slide, that you want to duplicate, is the first slide in the presentation
  5. At the top of PowerPoint, on the View tab, click Macros
  6. Select one of the Create Slides macros, and click Run

TIP: After you run the macro, and create the duplicate slides, save the PowerPoint file with a new name. Then, delete the main slide and the two notes slides, or hide them.

Create Slides Macro Code

Below is the code for the 4 macros in the PowerPoint presentation file.

  • Criteria Test - 1 Text (CreateSlides_Text1)
    • Fills 1st text box with text from specified column
  • Criteria Test - 2 Text (CreateSlides_Text2)
    • Fills 1st and 2nd text boxes with text from specified columns
  • All Slides - 1 Text (CreateSlidesTest_Text1)
    • Checks for criterion in specified Test column
    • Fills 1st text box with text from specified column
  • All Slides - 2 Text (CreateSlidesTest_Text2)
    • Checks for criterion in specified Test column
    • Fills 1st and 2nd text boxes with text from specified columns

Macro Variables

In each macro, there are one or more variables that you can change, to match the TABLE column numbers where data is stored in your workbook.

NOTE: These will be different from worksheet column numbers, if your Excel table doesn't start in column A

A) These variables set the TABLE column numbers to use for the text boxes:

  • col01 = 1
  • col02 = 2

B) These variables set the TABLE column numbers to use for the criteria column and text:

  • colTest = 3
  • strTest = "y"

The macro compares UPPER CASE text for the criteria, so it will match "yes" with "YES" or "Yes", or other variations.

Criteria Test - 1 Text

This macro creates slides for items in the Excel list, after checking a criteria cell, and fills 1 text box.

  • In Excel, checks the test column (colTest), and creates a slide if it contains the specified text string (strTest)
  • In the PowerPoint slide, text from the specified column (col01), is entered in the 1st text box

NOTE: Change those variable settings to match your Excel columns

Sub CreateSlidesTest_Text1()
'https://www.contextures.com
'create slide for names
' that pass criteria test
'fill one text box
Dim myPT As Presentation
Dim myMain As Slide
Dim myDup As Slide

Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long
Dim colTest As Long
Dim strTest As String

'column with text for slides
col01 = 1
'test column and criterion
colTest = 3
strTest = "y"

On Error Resume Next
Set myPT = ActivePresentation
Set myMain = myPT.Slides(1)

Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    'Copy first slide, paste after last slide
    If UCase(wsA.Cells(i, colTest).Value) _
        = UCase(strTest) Then
      With myPT
        'Duplicate slide 1, move after last slide
        myMain.Duplicate
        Set myDup = .Slides(2)
        myDup.MoveTo myPT.Slides.Count
        
        'change text in 1st textbox
        myDup.Shapes(1).TextFrame.TextRange.Text _
            = myRng.Cells(i, col01).Value
      End With
    End If
  Next
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

Criteria Test - 2 Text

This macro creates slides for items in the Excel list, after checking a criteria cell, and fills 2 text boxes.

  • In Excel, checks the test column (colTest), and creates a slide if it contains the specified text string (strTest)
  • In the PowerPoint slide, text from the specified columns (col01 and col02), is entered in the 1st text box and 2nd text box
Sub CreateSlidesTest_Text2()
'https://www.contextures.com
'create slide for names
' that pass criteria test
'fill two text boxes
Dim myPT As Presentation
Dim myMain As Slide
Dim myDup As Slide

Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long
Dim col02 As Long
Dim colTest As Long
Dim strTest As String

'columns with text for slides
col01 = 1
col02 = 2
'test column and criterion
colTest = 3
strTest = "y"

On Error Resume Next
Set myPT = ActivePresentation
Set myMain = myPT.Slides(1)

Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    'Copy first slide, paste after last slide
    If UCase(wsA.Cells(i, colTest).Value) _
        = UCase(strTest) Then
      With myPT
        'Duplicate slide 1, move after last slide
        myMain.Duplicate
        Set myDup = .Slides(2)
        myDup.MoveTo myPT.Slides.Count
        
        'change text in 1st textbox
        myDup.Shapes(1).TextFrame.TextRange.Text _
            = myRng.Cells(i, col01).Value
         
         'change text in 2nd textbox
        myDup.Shapes(2).TextFrame.TextRange.Text _
            = myRng.Cells(i, col02).Value
      End With
    End If
  Next
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

All Items - 1 Text

This macro creates slides for all items in the Excel list, and fills 1 text box.

  • In the PowerPoint slide, text from the specified column (col01), is entered in the 1st text box
Sub CreateSlides_Text1()
'https://www.contextures.com
'create slide for each name in list
'fill one text box
Dim myPT As Presentation
Dim myMain As Slide
Dim myDup As Slide

Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long

'column with text for slides
col01 = 1

On Error Resume Next
Set myPT = ActivePresentation
Set myMain = myPT.Slides(1)

Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    With myPT
      'Duplicate slide 1, move after last slide
      myMain.Duplicate
      Set myDup = .Slides(2)
      myDup.MoveTo myPT.Slides.Count
      
      'change text in 1st textbox
      myDup.Shapes(1).TextFrame.TextRange.Text _
          = myRng.Cells(i, col01).Value
    End With
  Next i
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

All Items - 2 Text

This macro creates slides for all items in the Excel list, and fills 2 text boxes.

  • In the PowerPoint slide, text from the specified columns (col01 and col02), is entered in the 1st text box and 2nd text box
Sub CreateSlides_Text2()
'https://www.contextures.com
'create slide for each name in list
'fill two text boxes
Dim myPT As Presentation
Dim myMain As Slide
Dim myDup As Slide

Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long
Dim col02 As Long

'columns with text for slides
col01 = 1
col02 = 2

On Error Resume Next
Set myPT = ActivePresentation
Set myMain = myPT.Slides(1)

Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    With myPT
      'Duplicate slide 1, move after last slide
      myMain.Duplicate
      Set myDup = .Slides(2)
      myDup.MoveTo myPT.Slides.Count
      
      'change text in 1st textbox
      myDup.Shapes(1).TextFrame.TextRange.Text _
          = myRng.Cells(i, col01).Value
         
      'change text in 2nd textbox
      myDup.Shapes(2).TextFrame.TextRange.Text _
          = myRng.Cells(i, col02).Value
    End With
  Next
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

Download Sample File

  • To get the PowerPoint and Excel files from this page, download the PowerPoint Slides from Excel List file. The zipped folder contains a PowerPoint file in pptm format, which contains 4 macros, and an Excel file in xlsx format, which does not contain any macros.

More Tutorials

Named Excel Tables

Macros to Sheets as PDF Format

Macros, Getting Started

 

More Tutorials

Named Excel Tables

Get weekly Excel tips from Debra

 

Last updated: January 20, 2021 4:14 PM
Contextures RSS Feed