How To Filter Excel Data In A PowerPoint Presentation
PowerPoint Filtering
This was a great idea that came from a reader named Chris Morgan. He wanted to know if it would be possible to create a PowerPoint slide that could filter Excel data. I had never attempted such a task but after reading his question, I immediately envisioned how it could be a useful and impressive functionality to have during a presentation. Here is my take on how to accomplish this using a Combo Box and a little VBA coding magic.
Adding The Combo Box To Your Slide
In order to insert a combo box into your slide, you will need to access PowerPoint’s Developer Tab. While on the Developer Tab, look for the Combo Box icon within the Controls group (highlighted below). Once the icon is clicked, you can simply draw the combo box on your desired PowerPoint slide with your mouse.
Loading Values Into The Combo Box
PowerPoint does not have access to Form controls; only ActiveX controls. One major benefit of Form controls is that you can connect the forms to values on a spreadsheet. Obviously since we are working in PowerPoint, we do not have access to spreadsheet-style data storage, hence there are no available Form controls. With ActiveX controls, we must load in values to the combo box via VBA code. First we will want to capture an Event to trigger the code to run. The event I chose to use was the Got Focus event (meaning whenever the combo box is selected or has the focus on-screen, the code will execute). You will use this event trigger to run a subroutine that will populate the combo box with choices for the user to select from.
Private Sub ComboBox1_GotFocus()
'PURPOSE: Event to capture when a user selects the combo box
'SOURCE: www.TheSpreadsheetGuru.com
'Run Macro to populate combo box with values
Call AddItemsToComboBox
End SubThe AddItemsToComboBox subroutine loads in values to the combo box during the PowerPoint presentation. In order to prevent the Combo Box from reloading the same values over and over again (saving time), you can include some logic to prevent it from adding new items if there is already data stored in the combo box. Depending on your specific setup you may need to tweak the logic a little bit (ie if you have 10 items in your drop down list, you would want to use “<10”).
Sub AddItemsToComboBox()
'PURPOSE: Populate a Combo Box with Values if not Already Populated
'SOURCE: www.TheSpreadsheetGuru.com
If Slide2.ComboBox1.ListCount < 5 Then
Slide2.ComboBox1.ListRows = 5
Slide2.ComboBox1.AddItem ("All")
Slide2.ComboBox1.AddItem ("Bob")
Slide2.ComboBox1.AddItem ("John")
Slide2.ComboBox1.AddItem ("Ben")
Slide2.ComboBox1.AddItem ("Sally")
End If
End SubPulling In The Excel Data
Now for the last piece of code. The next thing to do is to retrieve the filtered data from the Excel source file. This code will be triggered by the Change event handler, as it will execute its code whenever the combo box’s value is changed by the user. Essentially what the code does is
- Open up the Excel source file
- Filter the data on the user’s selection from the combo box
- Replace the current image on the slide with a new “filtered” image
- Close the Excel file (without saving it)
- Resize image based on previous image’s dimensions
I will not go into depth explaining how this subroutine works, but I tried to leave meaningful code comments to explain what each line does in the macro. Leave a comment in the comments section if you would like an explanation for a certain part of this code and I would be more than happy to explain.
Private Sub ComboBox1_Change()
'PURPOSE: Bring Filtered Excel Data into PowerPoint Presentation
'SOURCE: www.TheSpreadsheetGuru.com
Dim wb As Object
Dim tbl As Object
Dim ExcelApp As Object
Dim sld As Slide
Dim ComboBx As Shape, NewShape As Shape, OldShape As Shape
Dim myCriteria As String, ExcelFilePath As String
Dim ComboBoxName As String, DataImageName As String
Dim ExcelTableName As String, TableSheet As String
Dim SlideNumber As Long
'Input Values
ExcelFilePath = "C:\Users\chris\Desktop\Data.xlsm"
SlideNumber = 2
ComboBoxName = "ComboBox1"
DataImageName = "SalesData"
ExcelTableName = "Table1"
TableSheet = "Sheet1"
'Store Object Variables
Set sld = ActivePresentation.Slides(SlideNumber)
Set ComboBx = sld.Shapes(ComboBoxName)
'Create an Instance of Excel
On Error Resume Next
'Is Excel already opened?
Set ExcelApp = GetObject(class:="Excel.Application")
'Clear the error between errors
Err.Clear
'If Excel is not already open then open PowerPoint
If ExcelApp Is Nothing Then Set ExcelApp = CreateObject(class:="Excel.Application")
'Handle if the Excel Application is not found
If Err.Number = 429 Then
MsgBox "Excel could not be found, aborting."
Exit Sub
End If
On Error GoTo 0
'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
'Determine User Selection
myCriteria = ComboBx.OLEFormat.Object.Value
'Filter on select item
Set tbl = wb.Worksheets(TableSheet).ListObjects(ExcelTableName)
If myCriteria = "All" Then
tbl.Range.AutoFilter Field:=1
Else
tbl.Range.AutoFilter Field:=1, Criteria1:=myCriteria
End If
'Copy/Paste Data
tbl.Range.Copy
'Store characteristics about current Data Image & remove
Set OldShape = sld.Shapes(DataImageName)
x = OldShape.Left
y = OldShape.Top
Z = OldShape.Width
OldShape.Delete
'Paste to PowerPoint and position
sld.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
'Set Variable Equal To Newly Pasted Image
'Handle Excel 2010 & prior bug where combobox stays "in front" of all images no matter what
If sld.Shapes(sld.Shapes.Count).Type = msoOLEControlObject Then
Set NewShape = sld.Shapes(sld.Shapes.Count - 1)
Else
Set NewShape = sld.Shapes(sld.Shapes.Count)
End If
'Reposition and Resize Filtered Picture
NewShape.Left = x
NewShape.Top = y
NewShape.Width = Z
NewShape.Name = DataImageName
'Close Excel File
ExcelApp.CutCopyMode = False
wb.Close SaveChanges:=False
End SubDetermining The Various Object Names
In order to help you tweak the code to your needs I have created a brief video to help show you how to setup both your PowerPoint and Excel files so they can work in unison. In the video I mostly cover how to determine specific names and values needed in the VBA macro code that will most likely be different for you depending on your individual needs.
Possible PowerPoint Bug?
I would like to note that there seems to be a bug in PowerPoint that crawls through the cracks when executing this VBA code. When you initially start your PowerPoint presentation and click on the combo box for the first time, your combo box list will look like this:
However, any time after the initial click on the combo box (as long as the PowerPoint file stays open) the drop down will act as normal and expand fully as shown below.
I’m not 100% sure if this is a bug on Microsoft’s end or if there is a better way to write the code in this article to prevent this from happening. If you really don’t want to have to re-click on the combo box the first time around during your presentation, just make sure you give the combo box a click (in Presentation Mode) before you begin presenting and everything will work fine. To all you PowerPoint Gurus out there, please leave a comment below if you know why this “bug” is happening and if there is any workaround.
How Will You Use This?
As I had mentioned at the beginning of this post, this was an idea suggested by one of my many readers. I have not personally used or needed this functionality during my day job however I could see it being pretty useful in certain situations. Let me know how you intend to use this functionality in the comments section below. I’m sure your ideas will inspire others to create awesome functioning presentations that will blow everyone away! Be sure to download the example files below to take my example for a test drive.
Download The Example
Make sure you watch the video in the post and update the PowerPoint VBA code with the file path where you end up storing the Excel file on your computer. This will tell PowerPoint where the Excel file is located so it can copy-in the filtered data.
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.