Export excel dashboard to powerpoint in one click #exceltutorial #excelautomation #powerpoint

hello everyone today we are gonna learn some magic let's say I have this Excel report and I want to put this on a PowerPoint so I can present it to management one option is to copy this report go to PowerPoint paste it as a picture and then do some formatting sizing and all that stuff well looks okay but imagine if you need to make several copies of the slide for example one slide for each category or let's say if you want to make this presentation very often let's say every week then this approach might be quite time consuming but what if I told you that this entire thing is automatable such that with a click of one button Excel will automatically export this report to PowerPoint and not just export the report to PowerPoint but do some basic formatting provide some titles Etc you don't believe it well let me show you here I have a button to generate PowerPoint see in my bottom bar the PowerPoint application is not even open right now now let me click this button and you'll see the PowerPoint will open automatically and boom not only the PowerPoint is generated it's very well formatted and the titles are also there for example we have a separate slide for each category and title of each slide shows the growth for that category so everything is dynamic now that's some real magic and if you want to learn secret code behind this magic stick around because this is what we'll be learning in this video over the next few minutes so I'll see you there to start off I have this dashboard set up it's not necessary for you to have the exact same dashboard you can do this magic on any report that you might have but if you do like this dashboard and want to learn how to build it then you can check out the full video here where I have built this dashboard from scratch so step one we will first set up the button that will do the magic I will insert a shape and do some formatting on it go to insert tab select shape of your choice draw out the shape and give some formatting I will remove the outline and give it a bit of Shadow but you can do the formatting of your choice Step 2 we will now set up the code to generate PowerPoint from Excel I will go to the developer tab and click on Visual Basic alternatively I can also press alt f11 on my keyboard to open the code editor if you do not have the developer tab enabled in your Ribbon you can do that by going to the file clicking on options then click on customize ribbon over there you will see an option for developer it might be unchecked by default so place a check next to it then click on OK and you should be able to see the developer tab on your Ribbon I will open the code editor Again by pressing alt f11 and then let's start writing the code now I will explain the entire code line by line however I'm assuming that viewers of this video will have some basic knowledge of VBA programming if you are completely new to VBA coding and are not able to follow along in this video please let me know in the comments section and I'll try to make a crash course video on VBA programming now I will go to the insert Tab and click on module this will open the code editor inside the editor I will create my first sub procedure and name it as create PPT then I will write some code so let's go through this code in detail first I'm doing some declarations the first five declarations are related to PowerPoint objects like PowerPoint application presentation slide shape Etc the last declaration is an Excel range this will be the area that we would like to be copied into the PowerPoint in The Next Step I'm turning off the screen updates to avoid any flickering [Music] next I'm setting p app equal to new PowerPoint application in simple terms this line of code will launch the PowerPoint application next line will create a new presentation and the last line will set the layout of the slide to custom layout 1. to explain it in a better way let me show you by actually doing these steps with the PowerPoint and I think that will make more sense when I launch the PowerPoint I get this screen in this screen PowerPoint is basically asking me what do I want to do next do I want to open a new presentation or go with an existing one Etc at this stage the PowerPoint application is open but the presentation is not set up this is what the first line of our code does it just launches the PowerPoint software the second line of code goes and tells PowerPoint to start a blank application when I start a blank presentation I will get a default slide if I right click on this slide and go to the layout I will get various layout options to select from depending upon how I want to structure my slide I will select the appropriate layout option this is what the third line of our code is doing for now I want to go with the layout that has a title and a subtitle this is the first option and therefore in my code I have selected layout and in the brackets I have provided index of one now let's try to run this code make sure you do not have your PowerPoint open so that we can test the code properly I will click on the play button or press F5 on my keyboard to run the macro and it doesn't work in simple words the reason why it's not working is because Excel doesn't know what PowerPoint is or how to open it to fix this I will go to the tools tab click on references and then find Microsoft PowerPoint in the list once I have found I will check the box next to it now if I play my macro a new PowerPoint application should start [Music] and here you go it's working our next step will be to modify the code such that it picks up the Excel dashboard and puts it into the PowerPoint I will first write the code and then explain it by the way I'll put the link to this file in the description of the video you can also copy and paste the code in your files rather than writing it manually if you want to save time so I have written four additional lines of code on top of this step 2. first one is telling the Excel which area on the Excel sheet I want to be copied I will quickly go back to my Excel file here I will like the area from cell B1 to cell z42 to be pasted in the PowerPoint so that is what I'm providing in the first line of my code in the second line of code I'm simply adding a new slide in the PowerPoint file which we already created in a step 2. when adding a new slide you need to provide the slide number and the layout for number I will provide one and for layout I will put P layout which we already defined above in the third line I'm copying the range which we defined above and with the last line I'm pasting it on the PowerPoint slide now let's try to run this code [Music] and voila it's working now you can see that there are still two boxes in the slide which were there from the layout I want to get rid of the first box and for the second box I will keep it so I can use it for my title but I will need to move it up and format it so it looks better so this takes us to step number four so I will add a few more lines of code here in the first line I'm setting P shape equal to shape 2 on the slide so now what is shape two when we ran the code last time we saw a new slide was created where there were two shapes pre-existing by default this was the title and the subtitle the title was the second shape and then of course there was a third shape as well which was our very own dashboard and it was set up by our code for now I want to select shape 2 which I've done in my code and then I'm saying that with shape 2 make its top position equal to zero which means that shape 2 will be placed at the top of the slide and for shape one I'm saying delete it all together as I don't need it anymore now I will run this code to see if it gives the desired result and it looks great the subtitle has moved to the top and the title has gone all together but looking at this slide you might have noticed that some numbers aren't appearing correctly for example this one and over here this one these numbers are showing hash errors or hash signs so in the next step we will try to fix these formatting issues but before we start the formatting piece I want to do an another very important step at this stage and that is to save the file if your file has a VBA code then it needs to be saved as a macro enabled workbook so I will do that [Music] and now we're gonna start working on the formatting the formatting issue which we saw few seconds back occurs mainly when the column widths in Excel are not big enough to accommodate the entire data or if the column widths are just sufficient to show the data there should always be a little bit of a gap between the data and the border of the column now honestly I can solve this problem by simply dragging The Columns and making them bigger manually that will solve the problem for now but later on if new data comes in we might see this error again to make our macro more versatile we will put in a few lines of code that will counter this error as and when it arises to do that I will write the following code these four lines of code will ensure that column widths are adjusted to fit the data before dashboard gets passed to the PowerPoint now let's give it a try to see if it's working good so the errors that we were seeing before are now gone since we are working on the formatting piece let's do some favor for our title as well right now the title looks quite Orthodox and Bland so let's give it some color and Alignment I will put in a few lines of code [Music] now let's understand the code this portion of the code was already there I have just added one line in which I'm making the left position of the title is equal to zero in the following line I'm giving it a text of this is my title it's basically a placeholder title for now next I'm selecting Tahoma font size 24 the RGB code AS mentioned here and making the font bold and lastly I'm aligning the text left now let's rerun it and see how it looks good it's looking nice now seems like that title is almost colliding with the dashboard so I would like to push the dashboard slightly down I will put one line of code and hopefully this will do the job here is the extra line of code I have added I'm selecting shape 3 which is dashboard and making its top position 50. let's run it and see how it looks and it's looking fine if I want to export just one slide our PowerPoint and macro are almost complete but let's take this macro to a Next Level now I want to create three slides with a click of one button and each slide should show dashboard for a different category to understand this let's first go to my Excel dashboard this dashboard shows sales of a fictional Superstore and this Superstore has three categories for nature office supplies and Technology my dashboard is fully interactive that is if I click on furniture it will filter all the data for furniture category same goes with the middle and the bottom part of the dashboard as well again if you want to build the same dashboard in 5 simple steps check out the video over here in simple words I want my macro to filter all the data on this dashboard with the furniture category paste the dashboard to the PowerPoint then go again and filter everything for office supplies again paste the dashboard to PowerPoint and then repeat the same with the technology category let's develop the code which will achieve this I have copied the code here note that I'm putting these lines of code before the portion of code that copies the dashboard to PowerPoint because I want the dashboard to be filtered first and then go to the PowerPoint and this piece of code essentially I'm telling my macro to select the category slicer keep Furniture as true and rest of the categories as false the buttons that we saw on my Excel dashboard are actually slicers of a pivot table then I'm repeating same with slicer category 1 and slicer category 2. on my Excel dashboard the topmost slicer is the slicer category the middle one is slicer category 1 and the last one is the slicer category 2. I'm telling my code to select furniture for all the slicers on the entire dashboard before it paste it into PowerPoint let's see if it's working [Music] and yes we have dashboard only for the furniture category crew to repeat the same steps for the other two categories for that I will copy the same code two more times and instead of keeping Furniture as true I will keep office supplies as true and in the third instance I'll keep technology as true I will fast forward the video for a few seconds from here hello [Music] thank you to give you a brief overview of what I did I have used exact same code as before instead of keeping Furniture equal to true I have kept technology equal to true on the side note I want to highlight that we can make this code more efficient for example by using Loops but I'm not getting too technical here and trying to keep the code as simple as possible so majority of my viewers can follow along if you have VBA experience and know how the loops work you can achieve this in lesser lines of code as well now let's test our code and boom it's working we have three different slides each showing a different category now let's go back to excel here you will see that our code has filtered everything to technology because that was the last step in our code as a good practice the code should return the Excel file to the original position in which it was there before that will look more professional to do that I will put some more lines of code which will essentially set all the categories to true for all the slicers now I will try to run the code but before I run the code I'll select all the categories on my Excel dashboard [Music] when we click the Run button code will set up the PowerPoint like before but also when I go back to excel I will get the file in default position that is with all the categories selected now Although our current code is giving us slides for each category the title of all the slides is the same it will be nice to have a dynamic title as well such that title of first slide says technology the next one says office supplies Etc that's what we will do in our next step first I will put one line right before the end of the code this line ensures that screen updating is back to True after the PowerPoint is all set up and all the work is done I think I had forgotten it before now let's come back to the title to set up the title I will use a couple of helper cells in my Excel sheet one helper cell will be the placeholder for the category name the other cell will have the title I will create the title with the help of the concatenate and text function the concatenate function combines different strings and the text function converts the value to a given number format inside the text function I am giving reference to the cell that has the value for growth versus previous year so all in all my title will look something like this it will say category name grew by X percentage we have set up all the helper cells now I want my code to pick these helper cells up when putting the title on the PowerPoint for that I will go back to my code editor you will recall we had put a placeholder title in this line here I will remove the placeholder and instead give reference to the helper cell address now I will repeat this for the other categories [Music] I have repeated this step for all the categories however if I run my code in the current position all three slides will mention the same category name this is because we have hard coded the category name in the helper cell we need to make helper cell dynamic as well such that our code changes the value of helper cell at the same time when it's filtering the dashboard for a particular category I will do that via another line of code in this line I'm assigning the value of furniture to cell af5 which is our helper cell just before the piece of code that modifies the title of the slide then I will repeat this step for the other categories before we run the code I would like to make a couple of Corrections at the top we do not need these three lines so I'll get rid of them and also where we are declaring the P slide we do not need I plus 1 only 1 should work as well sorry for that I think I had missed it however these errors were not fatal for our code the code was still working but I just wanted to correct these mistakes next let's go ahead and run our code [Music] now all our slides have Dynamic titles which is great we will assign this macro to the button we had set up in step 1. I will go back to my Excel sheet I will right click on the button select assign macro and then select create PPD create PPT is the name of the macro that we had set up now when our user clicks this button it will generate the PowerPoint I will share the link to this file in the description of this video if you want to reuse the code please feel free to do so with that I hope you enjoyed the video if you did please hit the like button and also subscribe to our Channel I'll see you in the next one ha [Music]

Channel: Skillnator

Views: 47,157

Keywords: #exceltips, #exceldashboards, #microsoftexcel

Id: 4PFaMX3ccIk

Length: 21min 25sec (1285 seconds)

Published: Tue Mar 21 2023