Create Invoice from Excel Database for Marketing

Create invoice from excel database for Marketing

in this video we'll create this awesome  interactive Excel dashboard in just 15 minutes   first we will structure the dashboard and make  it Dynamic then we'll create all of the charts   and visuals and finally we'll customize them to  fit our look so let's get into it so here's the   Excel file that we'll be working with which you  can download for free in the video description   let's suppose that we're working at McDonald's and  we're tasked with creating a sales dashboard for   the South America region and for this we're given  three different tabs the dashboard where we'll   have all our charts and visuals then the inputs  tab where we have all of the data to create the   visuals and finally we've got contacts tab over  here just to have the contacts of all the managers   so let's go back to the dashboard and the first  thing here is to make this Dynamic so you can   see that we've got all of these icons here  to the side we can actually hyperlink them   such that they navigate to different tabs  so we'll go to right click on this one say   and from here towards the bottom  you'll find the link and we want to   link it to a place in this document and more  specifically to the inputs tab we'll hit OK   the third one we can link as well we'll go to link  again this time for the contacts and the final one   here you can see the question mark let's say this  is the support so it actually links to an email   so let's suppose this is to an email address and  you can specifically type say info at support   .com that's just a made-up email but we'll hit OK  there and then this first one should go to the to   this dashboard tab so we'll go to link again and  Link it to a place in this document the dashboard   and we'll hit OK the idea here is that when you  click on one it's gonna take you to the different   tab we need to add the same thing across all  of the different sheets so we'll go to this   first one just select the column by clicking on  the a here Ctrl C go to control page down Ctrl V   control page down again and Ctrl V and now we can  dynamically navigate across the different sheets   now that we have the navigation in place let's  work on the general structure so we're going to   add different shapes here we're just going to go  to insert then under shapes let's go ahead and   create a rounded rectangle and we're just going  to drag it more or less from here from column B   all the way to column m and here you can decide  how how rounded you want it to be we don't want it   too rounded so I'm just gonna hover over there the  shape fill we're gonna want it in a white color   with no outline so we're gonna go to no outline  down over here one final touch here is actually   adding a shadow on the background of the shape  kind of like so once we have the shape we can go   ahead and add a title here so let's call this a  sales dashboard for now Ctrl a in case you don't   see it's probably because the text is currently  in white so we'll change that to say a dark blue   Ctrl B to bold in to make it bigger you can just  click on this bigger a here once we have this   first one you can just hit the Ctrl shift and  then just drag it that's going to allow you to   drag it down over here so the idea is to have  three different shapes here such that they're   going to be the three kpis so we'll go ahead and  reformat this let me fast forward how I do that   then right below that we're gonna have one large  chart over here and then one smaller one down   below here so let's do the shapes for that as well  so Ctrl shift and just drag down let me reformat   that as well and finally we need to add one more  shape over here which is going to represent the   map of South America nice now that we have all  of the shape structures let me fast forward how   I rename them here's what the structure with the  titles is going to be looking like so we've got   the title and what the figures are in right below  that we have the three main kpis then we're gonna   have some kind of a line chart over here alongside  the customer satisfaction scores and finally a   breakdown using a map chart to see the sales by  country now that we've finished this first step   the second step is to work on the different charts  and visuals so let's head over to the inputs tab   for this so control page down here the first thing  we're going to do is go ahead and select all of   the customer satisfaction scores here just hit on  Ctrl a for that and then we're gonna go over to   insert and we're gonna add what's known as a radar  chart so we'll go to this drop down over here   under all charts we're gonna go towards the  bottom where it says the radar one it was hit   on OK there and we don't really need this Legend  here as we kind of have that already in the labels   so we'll delete we'll also delete the title here  now you can more or less see how we're doing on   each of the factors and speed clearly isn't a  very good one and nor is service we'll just go   ahead and move this over to the side and make it  smaller and we'll work on reformatting it later   right next to that we've got the map chart over  here so the country breakdown so control a again   this time under insert we're gonna head over to  maps go under a field map and we can just remove   the title layer you can see that's looking fairly  good already so we'll just make this a bit smaller   leave it here to the side then for these sales  figures over here to compare them easily we can   use something like a line chart so we'll go Ctrl  a insert and we're going to go under recommended   hopefully it shows up there and you can see that  we've got the line chart so we'll hit OK on that   but you'll notice that this um axis over here  has way too many labels so you actually can't   see what's going on within this range so from  more or less 180 to 250 is what we care about   so first we'll delete this title here we'll change  this axis by right clicking on it going to format   access that minimum amount is the one we want  to change and we'll add something like 180 here   and hit enter and now you can see  that discrepancy a little better   and again we'll just move that  to the side for the time being now let's work on these up over here basically  here we're gonna get the different proportions   so the percentage of complete if we look at the  formula is simply going to be the actual over   the target so we just want to get these two here  so the complete and the remainder that's going to   be enough for us to create a donut chart so we'll  go under insert under the pi over here we're gonna   select the donut down below great let's remove the  title and we don't need the legend either and one   more thing that we can do here is actually add  a text box inside which is going to give us the   percentage that's completed so in this case it  would be 85. for this let's go to the insert tab   under shapes here we're gonna select  the first one which is the text box   and then we're just gonna drag it over here but  you can't simply put the 85 percent as that's not   going to be dynamic so we're actually going to  go up over here to the formula bar go to equals   and we'll select that 85 percent ourselves and  hit enter we can then reformat it so maybe put   it in the middle and let's also Center it like  this Ctrl V to bold then we can make it a bit   bigger to something like size 16. and then we'll  change the color to something like the dark blue   great from here let me just go ahead and resize it  I'm gonna fast forward how I do the same thing for   all the other ones but one final thing is making  the donut the width here a bit thicker so we can   see it a bit better so we'll go to right click  there go under format data series you can see   that we have the donut hole size so this is  what we want to change to something like a 65 and now you can see how it looks a lot thicker  great let me fast forward how I do the same thing   for these other ones you can see here as I'm  working on the second donut chart I just need   to go ahead inside the text box and change that  from a d to a g I'm going to hit enter there but   you'll notice how the formatting all of a sudden  changes if you want to bring it back to this   original formatting you just gotta select the text  box go under the format painter and then just drop   it over here so click there you can see how it's  reformatted it the same way let me fast forward   how I do the last one nice now we have all of the  key visuals that we're gonna need if you like what   you're seeing and you want to learn more you can  also check out our range of courses which include   Excel for business and finance PowerPoint Finance  evaluation as well as a range of bundle packages   and what makes our courses different is  that they're all applied to the real world   so aside from teaching the theory our lessons  also offer case studies that simulate the type   of work you might be assigned in your day-to-day  ranging from creating a financial model from   scratch on Excel to creating a professional  slide deck presentation on PowerPoint   and if you get stuck along the way you can easily  ask us questions in the course discussion forum so   if you're interested in checking it out you can  go to the link in the description below alright   back to the video awesome now that we have the  dashboard structure individuals we just need   to bring it all together so for this let's go back  to the inputs tab first we'll select the map chart   and just hit on Ctrl X from here let's go back  to the dashboard and Ctrl V we're just gonna drag   it over to the side and let me just reformat this  one you can clearly tell it doesn't look too great   because the map is so small if you want to change  that you just need to go to right click under the   legend first click on the legend right click go  under format Legend and we want to click deselect   this button here so show The Legend one now if we  look at how that looks you can see that it looks a   lot better because it's removed that spacing then  let's also remove the fill here for the chart so   we'll go no fill and we don't want a border either  so no border now it should all look a lot better   nice so that's the map chart done now let's  focus on the other visuals starting with this   trend trend line over here so we'll go to  the inputs tab select this trend line Ctrl X   let's go back to the dashboard here and Ctrl V  and for this one we're going to want to change   both the colors and we're also going to change  kind of how the markers are currently labeled   first let's select the entire chart under chart  design let's go ahead and select the third option   here which looks a bit more elegant then we click  on that specific line the orange one for now go to   right click format data series and here is where  we want to change both the color and the markers   so we're gonna select a line that's going to be in  red color and then we're gonna select the marker   right next to it select marker options and from  here we want a built-in marker that's gonna be a   circle so we'll select the circle from here we're  fine with a size five but we want that fill color   to be a solid fill that's going to be in white and  we also want a border for it that's going to be   a solid line in red let's also increase the width  of that to something like one point and now we can   kind of see what that looks like which hopefully  looks a lot better same thing here with the blue   one let me fast forward that great so now you can  see that we've got one in red and the other one in   dark blue let's also remove the fill here so we'll  go for no fill and no borderline great now that's   looking a lot more like it now we'll work on the  radar chart here for the customer satisfaction   so go back to the inputs let's control X again  let's go back to the dashboard and Ctrl V   and again we'll just drag it over let's try to get  the same format as this one over here so with this   um dots as markers so we'll go to  right click go under format chart area   we want no fill no borderline and for these  actual points we're just gonna select them   and we're gonna go for a solid line that's a dark  blue color and we're gonna go for a marker that's   gonna be uh rounded again so built in we'll go for  a rounded one and let's go for a dark blue color   solid fill white and we're gonna go for a solid  line in dark blue and increase the thickness of   that to A1 as well and there you go great now we  just have the three kpis left over here and for   these it would be nice to not just show The  Donut chart but also have the actual number   here on the side so for this we're just gonna  insert a text box so under shapes here we'll   go for text box and we're just gonna select  it over here and again we want to make this   one Dynamic so we'll go equals and we're going to  link it over to the inputs tab under the actual   amount for sales and same thing with profit  customers later so once we have that we need   to reformat it like we've done before and then do  the same thing for these let me fast forward that   now that I have this first one I just gotta go  Ctrl shift and just drag it across like so and   then Ctrl shift again and drag this one as well  now I just need to change the labels by changing   the link and if you get this issue again where  it's not formatted properly just gonna select on   the first one go under form a painter just click  on that second same thing over with this last one   like so great now we're ready to go under the  inputs and select the three donut charts here   so just hitting the control key you can select  all three Ctrl X let's go back to the dashboard   and Ctrl V now we just need to reformat all of  these so let me show you how I do it for the   first one let's select it then go to right click  under format chart area let's go for no fill and   we'll go for no line as well then we'll select  the donut just select this this blue part only   let's say we go for a dark blue color here so  like the dark blue we've currently been using   and we'll select the remainder so this area  that we've still not fulfilled in Orange right   now so something like a lighter blue like so  great now let me do these other ones as well   awesome now we have a really nicely designed  dashboard which is also interactive using the   different hyperlinks over here and you can see  that it tells us pretty much all of the key   information that we will need in just one sheet  if you want to learn to make dashboards like this   even faster using power bi make sure you hit that  subscribe Button as those videos are coming soon   for more on dashboards check out this link over  here to make them with pivot tables or this link   over here to check out our course hit the like and  that subscribe and I'll catch you in the next one