Streamline your pharmaceutical pipeline tracking spreadsheet for Pharmaceutical

How to create outlook signature

hello and welcome to the dashboard course but Trump Excel I'm so MIT Bansal and in this video I will show you how to create a sales pipeline management dashboard in exam let me first show you how it looks like here I have a fully functional sales pipeline management dashboard before I take you through the dashboard the most important thing to note here is that this dashboard is as of 31st March 2015 which means that when the data was compiled it was compiled on this state and there are many deals that have either been won or lost before the state and all the remaining deals are in various stages of pipeline which would either close or would be lost or would be won by this company in later dates so what I mean by sales pipeline stages here I have five stages which is identified validated proposal qualified and won now these are typical sales stages these can change based on the company and how the processes work but more or less it would look like this now identified stage is where you have identified a client as a good prospect validated stage is where you validate it as a potential deal so you think that there is real potential of closing that deal or getting business from the client proposal is the stage where you send your proposal to the client qualified stage is where you qualify the deal as a prospect deal that could be won or you are qualified as a vendor by the client so maybe there is a tender and the client calls for for proposals and yours is one of the set up three or four proposals that get accepted and then there is win where you VIN that client there is also another stage which is lost which is at the same time where either you win a deal or you lose a deal but I would show only these five because all the deals that are not won are lost so in this dashboard I have these interactive controls at the top you can select the region so I have these countries you can either select all countries so you would have the data for all countries here or you could select these regions I also have this option to either get the dashboard values for deal value so if I select this you can see that these deal value changes so it becomes 2 4 6 9 million or I can get it by deal count so for example if I click on this you can see that it is 1 de value in dollar million by country so these are all the countries and these are the values that the country has won in this data and then if I select the account then it would change you can also select a country here so for example here if I select say Australia then this chart this pie chart would reflect all the stages of the deals in Australia so I have 42 deals in identified 28 invalidated and so on and so forth and there have been 9 wins and in total there are 110 deals in Australia similarly on this chart there are 9 wins in Australia as shown here and these are the other countries I also have the win rate which means that out of all the deals 60 4.3% deals were won by this company so for example here if I select another country it would give me 8 here and the win rate is 72 point seven percent which means that in total there were 11 deals out of which 8 deals were won by France because the win rate is 72.7% now I also have these next 10 opportunities based on the closing date so here you would see that as I mentioned this is as of 31st March 2015 so after that date these are the dates where I have deals that could be closed so I have these customer names I have the country because I have selected France it is only showing me France deals but if I select all countries it my deals from all countries I have their stage I have the deal value and then it will show me the top ten opportunities you can handpick the opportunities from here for example if you want to only pick up qualified opportunities because they are very advanced stage in the pipeline then you can pick up this steel and similarly you can change the country and pick up other deals I have the top 10 sales rep I have their name in their VIN count and again if you change the country this list will change in some countries you may not have ten sales reps so it will show you the data or whatever is available for these names and will count here is an interesting chart and what it does is here it gives you the wind count by deal size so there are deals which are less than 250 thousand there are deals from 250 to 500 thousand 500 to 1 million 1 2 3 million and 3 to 5 million now this chart would update based on these values so here if I select all countries it would show me these values because these are the deal sizes and these are the wind count but if you select these checkboxes here so for example if I uncheck all these you would see that this chart shows nothing but you can select one of these checkboxes and it will show you the channel that was used to make that sale so for example if I click on this and there is only one deal which was there is one wind count by this deal size which is less than 250 K I can see that it was done by partners if I deselect this and I select this I know there are two wins in this range which is to 50,000 to 500,000 and it was done by partners and website and similarly I can see it for all I can select and deselect based on whatever is my interest area and I can analyze the wind count by sales channel as well so this is how this entire dashboard works again the most important thing to note here is that it is as of 31st March 2015 if say you progress to next quarter and then you want created for 30th June 2015 you need not change the dashboard all you need to do is change the data in the backend plug and play in this dashboard start working so now let me show you how I have created this dashboard from scratch here I have the sales pipeline data as of 31st March 2015 so I have the reporting date and if I show you all the records I have thousand records with the final data as 31st March 2015 now there I have deal owners customer name the country where the deal company is there the sales stage as of 31st March 2015 the deal size be expected deal size and then there is a probability to each deal now I'll not be using this probability factor but if you look at a lot of sales pipeline data you would have this value where they say that the field size is say twenty nine ten million dollars but the probability of closing this deal is sixty five percent so instead of taking the total deal size value they often take weighted forecast deal value now you can take this value and replace it with what I have in my dashboard and it would work just fine I have the sales channel where I have telesales face to face partners and website then I have the close date or expected closed date so these could be dates if it is a win or a loss then it would be before or on 31st March 2015 but if it is not of Anora close then it would be after 31st March 2015 so you would see that this deal the first deal is in identified stage and the expected closed date is 7 April so there are deals which go up to June and there could be deals that go up to maybe a year long if they are really large deals and then here I also have the next steps which a sales rep usually keep a tab on what needs to be done of what are the next steps when they last spoke to the client now I also have the calculation tab here I have everything ready as in the placeholders already and what I will do is I would fill in the data show you how to create those charts show you how to create those formulas and create the dashboard so here I would have all these flavourless holders so here I already have the placeholders you would see that in the previous dashboards that I have showed you I always keep the interactive controls together in this case I would keep the interactive controls here in the shaded area and then I have all these placeholders that I would use to create the dashboard so let me create a new tab here and I would call it dashboard let me go to view and remove the gridlines and you would notice that in my way of designing dashboards I usually have this bar at the top so I would have a bar and I would keep my interactive controls together even in the dashboard now I do it for the ease of using it and this is probably a well accepted design technique but there is no reason to do it if you want you can have a different design format for your dashboards and as I create more dashboards I would also show you different designs to do it so in this case let me quickly change the fill color to maybe say grey and maybe the line color to dark grey and I would change the width of this line so this could be a place where I keep my interactive controls here I would go to the Developer tab if you do not have the Developer tab simply right-click on any of these ribbon tabs go to customize the ribbon and here you would have the developer option which will be unchecked if simply check this click OK and you would have the Developer tab in your ribbon go to Developer tab in insert and here I would insert a combo box and you would remember that I had a combo box where there was a drop-down for countries so I would use this combo box and I would simply put it here now if you go to the calculation tab you would see that I have already have this list if you do not have the list no need to worry simply go here copy this entire column and what I usually do is I go to area where there is no data and I would simply paste it and then I would use the keyboard shortcut alt a M now this is to remove duplicates if you want to go through the tabs you can go to data and here you have removed duplicates and when I click on it it would show you this dialog box I simply have the column n selected my data does not have headers so I do not have this check and when I click OK it would give me the list of these countries and what I have done here is I have copied this list and I have also added another option which is all countries because I also want to show them an option where they can have the dashboard display the data for all the countries so this is what you can do let me clear this and I already have this here at this these countries are in alphabetical order they have been sorted in that order now I would go here right click go to format control and input range would be these cells and now they are 2 3 4 5 6 7 8 9 10 11 these are 11 in number so I would keep the drop-down lines 2 11 and my cell link here would be descent because this is the cell I have specified for country and now when I click OK you can see that here my drop-down is working in when I select Australia it would give me 2 because Australia is the second country in the list now there is one more interactive control here that would control the entire dashboard and that is the radio button so let me add those radio buttons as well so I would click here I have this option I would simply change the text to deal value in dollar million and I would copy this and paste it again to have the deal count and I would simply change this to discount now I have these two here I would again have to assign a cell to it I would say keep this one checked so whenever you open this dashboard this is the option that would be checked and then you can change it and the cell link here would be this cell which is chart radio button and I click OK here and that's it if I now click on the account you would see that it changes to 2 and if I click on this one then it becomes 1 so now both these controls are in place let's get started by creating our formula so that we can create those charts before I start creating the formulas there are two things I need to do with the data first thing is as I always do I would want to convert this data into an Excel table so that it becomes really easy for me to create those formulas instead of referring to the ranges I can simply refer to these names here so to convert this data set into an Excel table I would press ctrl T and when I press ctrl T it opens the create table dialog box here it would identify the range automatically but it is always a good idea to cross check whether this range is right or not and then there is this option to select if your table has headers it's already pre-selected and my table does have headers so I would keep this selected and now when I press ok you can see that it has created a table I have filters here at the top and there are there are banded rows here so it has converted this into a table now one more thing I would do here is I would name this table so here I would go to design and here I would name this - sales data and I would click enter so I would hit enter now my table has been made there is one more thing that I need to do and that thing is that I would need to add another column I would let me first do it so I would add another column and see what happens as soon as I add a column my table extends to include that column within the Excel table and I would name this column country check and the reason I am doing it is here I in this dashboard I have this option to either select all countries or to select these countries now if I select all countries here I would want that to configure in my formulas for example if I create the deal size formula I would want it to give me the deal size for all countries if this is selected but only for Australia if this is selected now I can create a huge formula but instead of doing that I have created a helper column and I would check for the country in the hairpin column what I would do is I would use an or formula and my first condition here would be whether this value and let me press f4 to lock this whether this value is equal to one and what I do here is if I select all countries then this would become one and my or condition would always be true so if you have selected all countries that should become true in any case and if this is not the case then I would use an index formula and I would say this is the array let me press f4 to lock it and this would become my row number and let me press f4 again what it does here is if I select Australia from the drop-down then this index formula would use this number from there this array and give me Australia so this value would simply return Australia if I select Canada then this value would simply return Canada and I would check whether the given country here is the same as the one I have selected and let me close the bracket and hit enter and here it gives me true everything comes out to be true the reason here being because I have selected all countries but what if I select China here then this comes out to be true because the country here is China and all these other countries would be false till here and see that is the case all these other countries are false but this one is true because this is again China so all I have done is I have put in a condition that would give me true when I select all countries and if I select a specific country then it would give me true for that country only now let's get started and let me create these formulas the first one is here deal size by stage for the given country so I would use some product formula and the first thing that I would check here is I would first use sales data first thing I need to check is sales stage and if this is equal to a six which is identified and let me press f4 thrice now this would give me an array of truths and false let me put this within curly brackets and create another condition here and my another condition would be with multiplication sales data and I would simply select country check because country check is already an area of truths and false based on the country so in this case number four is China this value would be true only for those sales deals which are in China so this would give me a number but I need deal size so I would multiply this with sales data and I will multiply this with deal size and I would close the brackets and hit enter and it gives me the value of beer size in China and I would simply drag this down to give me all these values now I need to calculate deals count as well all I need to do is simply select this formula and leave the last part out of it because I don't want the deal size I want the count so I would simply press F to get into edit mode control V and enter and I need to close the bracket this is something there are always miserable now when I hit enter this gives me the deal count and I can simply drag it and you can test this thing if I select all countries here and I go to calculation it gives me 355 bales in identified stage let's go back and check in our data so I would go here I would go to sales stage and I would select identified and this gives me 355 fair enough which means that my formula is working now what I need to do here is this is the data that gives me deal size as well as deal count but you would remember that I had a pie chart here or donut chart rather that would give me deal value if I select this and deal count if I select this so what I would do here is I would simply check whether this value is 1 or 2 so I would do if this value and f4 to lock it is equal to 1 which means then it says deal size then I would take this value else I would take deal count and now I hit enter and it gives me deal size because I have first one selected which is 4 deals a deal value now one more thing here is that I am NOT taking the lost case here I am only taking win because if you remember the donut chart or if you think of it as a funnel I would have deals and identified then some deals would fall through but some would go to validated some would then go to proposal then qualified then VIN but the last stage is not an extension of this so I'm leaving the lost out from here and I would show this as a part of wind rate so what I do is I would show you win rate instead of showing you the total number of lost deals another thing is I need to create a header for the chart so I would simply create a formula deal space and I would close these quotes ampersand I would then use an if formula again if this value F 4 is equal to 1 then it should return sighs in say donor million crow close the brackets or it should return count and now I hit enter and it gives me deal size as of now but if I make this 2 then this gives me D count now all I need to do is I need to create a chart from this so let me create the chart here and then I can copy pasted it on the dashboard sheet I would go to insert and here I would go to chart and I would go to donut chart now in this donut chart let me delete the chart title let me delete these and I would go to design select data and in this case this is selecting the wrong data I would remove this I would add a series and that series would be these values and now when I click OK and I can also change the access label because I would need to show the access level and I click OK and you can see that now I have the donut chart ready it starts from here so I have identified then I have validated proposal and these are when now just to beautify this chart a bit what I did in the original chart is I gave it different shades of blue so this one was dark blue then I selected a lighter version then I selected further lighter version and this was the lightest save this one and for when I made it green bright green color and you can also give them a border if you like I think it would look good if I give it a gray border something like this and I also used shadow here so I went to shadow and I selected this one so this became my chart I can go back and make the fill of the area background area has no fill and this becomes my chart I can add the legend from here at the bottom so this becomes my chart you can change the shape of this you can change the doughnut hole as well so let me quickly do it hole size and I can make it maybe 60% would look good and I can simply copy and paste it here so I have this chart ready maybe I need to change the border width a little so I would make it say 0.5 this looks better and I can remove the border as well no I would not spend a lot of time showing you how to format these I assume you already are pretty good in formatting what I would do is I would place these necessary elements at the right place what I also did in the original chart was I also placed a box here that would show me the portal number of either deal counts or dealing value but before that let's see first if this works or not and sure enough it works because these values are changing I can add data labels to it and maybe make these data labels white and bold if you want and you can increase the size of the donut or later so now this is your donut chart let me also go to insert here I would insert a text box and let me go back to calculation now what I need to do here is I need to show the total count now to do that what I would simply do is I would add this so this gives me the sum and this chart because I've already copied it there and I would refer this text box so this text box is selected I go to the formula bar I press equal to and I come to this cell and now when I hit enter it gives me the value here what I can also do is I can go and change the formatting a bit so that in case I'm showing the value it gives me a thousand separator so there is no decimal place no need for decimal place and a thousand separator now if I make this one you can see that it would show me deal size so I would increase this a bit and I can show this what you can also do is you can format it to show you values as say 0.29 or only 298 that is something that can be done but I leave it at this stage you can figure out the formatting later and maybe give it this color hold it and increase this value so now this looks better and when you change these countries so you I make it all countries and let me increase yeah so when you make a deal count it would show you deals count and when you make it devalue it will show you deal value and these numbers would again update so you can use these numbers the second thing is there was a chart here that would show you the deal count or the deal value by country so to create that I have the placeholder here and all I need to do is I need to first identify whether they have selected deal value or deal size and then give you those values by country so I would simply copy this formula and here I need to give you only the wind count or the wind value and not everything so I would remove the first stage first thing here and I would say first check the country or actually I don't even need to check for the country because I need to show it for this country here so what I would do is I would rather remove everything start from scratch sales data and the country should be equal to this country so this becomes my first condition let me lock it f4 f4 f4 thrice the second condition here is that it should be a win so I would say save stage whoops sales stage is equal to one and my third condition here would be whether I am looking for a deal size or a discount so now here if I use any formula I can make it happen what I would check is if this cell and I would press f4 is equal to 1 which means that I am looking for a deal for deal size then I would simply multiply this with deals size else I would return 1 and now when I hit enter it gives me this value but before I move forward let me first copy this for all these countries before I move forward let me show you what I tried doing here so what I've done here is I have selected the range so for example if I go to this formula first I check whether the country is equal to Australia and then I check whether the sales stage is 1 or not so because I am already on only showing you VIN counter when deal size this condition was required and then I checked if this is equal to 1 and if this is equal to 1 which means that in the dashboard I have selected deal size so I returned deal size value but if it is count then I don't need to return anything this part would give me what I want so I simply returned 1 so that this entire thing multiplied by 1 would be nothing but this value only and now see what happens if I make this 2 then this gives me these values now what I can do is I can simply use this data to plot the chart initially when I did it I did it in a longer way so I had this placeholder but I would not need it here because I've used a slightly bigger formula but I have done it in this space only so now what I would do is I would go to insert and go to bar chart and I would select this and you can see that it insert these numbers which are deal with count number and if I change the value from here to 1 then it returns deal size number but what I also want here is I want to check whether the country is say Australia or not or Canada or not and if it is Australia or Canada then I want to highlight that country as well so to do that what I can simply do is I would use any formula here and I would say if this F 4 is equal to 1 which means that if all countries are selected give me not available oops give me not available because I don't want to plot anything in that case all countries are selected so all countries should be in the same shade but if the selection here so I would use index f4 and this cell again f4 is equal to this country and let me press f4 thrice if this is the case which means that if I've selected Australia then give me the number for Australia and I would have to introduce another if condition so what I'm saying here is if this is equal to all countries give me not available but if it is false then I am checking whether this is equal to Australia or not if it is equal to Australia I return this value else I return N and now when I hit enter it returns na here because I have selected all countries so there is no additional series that I want to highlight but if I make this two then it gives me the number for Australia now what I would do here is I would add this series to this chart and I would put this on maybe not on a secondary axis I just need to overlap the series completely and when I do that you can see that now this is highlighted if I change the country if I make it 3 which is Canada you can see that Canada is highlighted and that is exactly what I wanted now I can simply format this chart a little bit so what I would do is I would change the color of these to light green something like this or maybe even like this and I would make this one this is the color to be dark and I can give all of these a simple border so I would go here I would say solid line gray color and the which should be 0.5 this looks good and this becomes my chart do some minor formatting copy this and paste it here now all I need to do is adjust the width lower the font for this reduce the font so that it fits perfectly and this looks good maybe I can simply say I don't want the major to be five thousand but ten thousand so that the gap is at least minimum of ten thousand I can also increase the gap decrease the gap here so for example if I do this it would look marginally better and then I can add the data labels here and that is it maybe format the data labels a bit so ever go to numbers and I would maybe see or let me simply do this let me simply introduce a thousand separator without any decimal let me show the entire deal value here and no decimal places so that check now I have these numbers here if I select account okay it doesn't show it does show here but the problem here is that I've set this to 10,000 so maybe I need to make this and reset it now it works fine so whenever you select deal value it would highlight this country which is the selected country which is Canada in this case and if you select account then again it would highlight the same country but the data would update so it shows you only the data for these counts and not taking values I can place it nicely so that it fits perfectly here now I have both these parts ready let me also remove the border for the chart so I would say no line now let me delete this chart as well and as I mentioned earlier when I created this originally I created it separately but here I've showed you to do this within one single chart and and these these columns because here what I did is I have added this additional condition earlier when I did it I had this value and the deal value and then I pulled the respective value based on the selection so you can do it any way but I thought that we have come to an advanced stage and it's easier for you to understand these formulas again this is just a three condition formula now the next thing I need to identify is next 20 or next ten opportunities here so to do that what I would do is first I would go to this area and I need to extract all the data from here so that I can identify the opportunities so to do that let me start from here and I can give the headers later now to get the next 10 opportunities I need to check for two conditions one is whether the deal has been won or lost because if the deal has been won or lost it cannot be my next opportunity and another one is for the country so if I select all countries then I have on all the deals and then the next ten opportunities but if I only selects Australia I want only the deals from Australia so to do that I would create a formula here first I would use an if condition and within if I would have these conditions which I said which is one lost and the country so the first condition that I want to check here is whether the deal is already won so I would go to sales data and here in sales stage I would say if this is equal to one now I'm hard-coding these values and I am putting the text here but if you want you can also refer to say say the stages and put it here or do it some other way I'm hard-coding it just to show you in this video now this is my first condition and it would give me true or false based on the sales stage another condition would be for lost so I would say sales data and say the stage is equal to lost so these are two conditions and the third condition is for country so to do that I would use an or formula and first I would check whether this value and I would press f4 to lock it whether this value is equal to one if this is equal to one then this is anyway true but if this is not equal to one then I need to check for the country so I would use index this array I would press f4 to lock it and this would become the row number and again f4 to lock it if this is equal to the country so I would say sales data country and this completes my or condition and now I would end this what it would do is it would check these three conditions whether the sale sales stages won or lost if that is the case okay I should not have equal to here I need to have not equal to because I don't want it to be won or lost so I have these three conditions whether the same stage is not one not lost and equal to this country if these three conditions conditions are met then this would return true and if that is the case then I want the difference between the date the reporting date and today's date when this this data has been compiled which is 31st March so what I would do is I would simply say sales data close date or expected close date - 31st March which is the date when this report this data was compiled now again I would hard code this value if you want you can simply refer to a cell which holds that value the year is 2015 month is third which is March and the day is 31st and if this is not true then I simply want a blank and when I hit enter it gives me 7 now let us go back to the data and see how it's working the first condition I checked here is whether the sales stage is not one or not lost and which is true here because the sales stage is identified and then I checked whether the country is all country or the specified country in this case I have selected all country so it gives it this becomes true anywhere and then it goes to the close date or expected closed date and subtracts 31st March 2015 from this so that it gives me the difference for this deal to close or expected to close there are seven days remaining and again if I drag this to the bottom to cover all thousand records here it gives me all these numbers and again these numbers are the expected number of days to closing starting from 31st March so you can see that these are numbers and wherever there is a blank which means that it has either been won or lost or the country does not match now I can use this and what I need is the smallest number at the top so that I can use that date as my next opportunity but there would be repetitions so for example I have a 2 here and a 2 here so I don't want these repetitions so I would make these values unique what I would do is I would add rows function to it and I would lock the first j2 so that when I go down it becomes 1 in 2 & 3 and so on and I would divide it by a big number and what it does is you can see it gives me 7 points 0 0 0 0 0 1 and when I go down whenever it there would be another 7 it would not be 0 0 1 it would be some other number based on the row number it is giving the value whether because this is empty so I would I would wrap this within if error and I would say if there is an error give me blank and now it gives me the right result now the next foot thing here is I need to sort this based on the value and I need to sort them in in ascending order so what I would do here is I would select this array this entire array press f4 and use the rows function again so that when I go down the road gets incremented by one and close the bracket and hit enter and gives me two point zero zero zero zero two because this is the smallest number in this array and then when I double-click it gives me all these numbers now you can see all these are two but since I have made them unique I can now sort them and then all I need to do is identify the position of this number in this area I would hit f4 and then I would say I need an exact match now when I hit enter it gives me two now if you go down you would see a couple of things first is there is error in this row and there is error in this row so to avoid that error first let me use if error here and if it's not an error give me blank now when I double click you can see that the error is gone but what happens is down here it is giving me 10 as the value for all these cells the reason being that here I have a blank cell in the tenth position so what I would do is I would simply check for a condition I would say if this cell l2 is not equal to mt then give me the position but if it is empty give me blank and now when I double-click here and go down you can see that when there is blank it would not give me any number now I would use these numbers these positions to extract the data for next ten opportunities so what I would do is I will use an index formula my array would be sales data close date or expected close date and my row number would simply be this cell and that's it it would give me second April as the next opportunity date I can simply drag this down and I can do this for the remaining parts as well so now I need to get the customer name I would simply use this formula and instead of sales date or close rate expected close rate I would use customer name and it gives me the customer name here similarly I would want to get the country here so to do that I would again use the same formula and instead of this I would make it country and it gives me the country here I need to get the deal stage so I would again use the same formula sale stage and here again the same thing I would say deal value which would be the size and now I have these numbers I would double click here I would double click here and I have all these numbers and these are dynamic because when I change the country I make it say Australia see what happens I have all the numbers for Australia all the B's accept wins and loss so I would use these cells in my dashboard what I would do here simply as I would refer to those cells so to do that I would simply go maybe here I would go here go to this data and that's it I would track it down till here it's showing me this number because it's not in the date format so press control one go to custom and here I can change the date or maybe I can directly go to date and change this and I don't want this format maybe this format is good or if you want you can simply go to custom and change this format to TD mmm yyy and it will give you this format I can also copy paste it for these cells and he'll give me all these numbers here again one important thing that I always do in the dashboard is if there are objects I always select their properties and I change the properties to do not move or size with cells the reason being that now if I change the column width or the row width I don't want these things to change so I would have to do it individually for each objects for example here in these format controls I would again have to do it for format controls I would have to do it for chart as well so I would right click here go to properties do not move our size again for this chart I would do the same thing the reason here being that now if I change this row everything else would remain the same see I did not do it for this one and it moved but I don't want that so I would simply go and say do not move or size with cells and I would put it back to its place no it would not move so I can simply double click these and show the value ok here the problem is that I have the numbers here but they are formatted as date so I would simply go to number and say thousand separator but no decimal so now it gives me the numbers as well and I can format these in this case what I have done is I have chosen a different kind of formatting I will show you what I have done here I have these dash and a double dash at the top because I just wanted to make it look neater so repeat that change what I would do is I would press ctrl 1 go to border and at the top I would use this and when I leave it you can see that now it has this border at the top again I would press control one and here I would select this type of water in this type of water and now when I leave it you can see it gives that same thing I can simply select each of these cell and press f4 to get it and now it looks neater you can again do a couple of formatting changes you can reduce the font size and change the way it looks but overall it looks neater and I also try to restrict my dashboard to one single screen so that it looks better and everything is in front of you in one single go so I can simply maybe delete this and then we have it here again minimize the charts and something some formatting that we can do but I not spend a lot of time in formatting I'll show you how I've created the dashboard now the third the other thing here is that I need to get the top then sales rep by country so I have this list of sales rep again and what I need to do is I need to identify their either their deal size or deal value and then based on it I need to sort them so let me show you how I've done this now here I need to check for a couple of conditions first is that the sales rep or the deal owner should be this person the second is that the country should be either all countries or the selected country then I need to check whether the deal is won or not because I only want the numbers for Vince I want top sales rep buy a VIN value or by Vince eyes and then finally I need to check whether I'm looking for a VIN count or of insights because it would return the number in million dollars effect is of incise else it would return the count so to do that let me create a sum product formula here the first thing that I would check here is sales rep name so I would say deal owner is equal to this person so this becomes my first condition the second condition is that the sales stage should be one so I would use this condition here my third condition here is about the country so I would use if formula here and I would say if this value here and f4 to lock it is equal to 1 which means it signifies all countries then give one else check the condition in the check condition is index all these countries f4 to lock it and this is my row number so this would give me a country's name the selected countries name and if this is equal to the country in my original data so this is my condition and then finally my condition here is again and if and I will check if this thing here is equal to 1 which is if I have selected deal size then give me the deal size values which would be sales data and within here I would have deal size else if it is a deal count then I don't need this formula this formula can give me the same thing so I would simply return 1 here and now when I hit enter this gives me 0 let let's check whether this working fine or not I would hit 1 here and now when I go down this gives me the right value which means that the country I had selected earlier there were no vens by this person Adrian now let me drag it down and see if my formula works and it seems to be working let me just cross check it once and yes it seems to be working I can see that for Joe there is no when in all the countries so just to cross-check let me go back to the data let me go to deal owner here I would select Joe and I would check here whether he has any wins or not and sure enough he does not have any win he has lost a couple of deals but no way so my data here is right now I need to sort these values so that I can have the best sales rep to get that whether it be deep size or deal count I need to sort it and again I would use the same thing I used here to get the next opportunity I would first make it unique so to do that I would use ROS function and I would run through quickly through the section because I have already done it once before when identifying the next opportunities so I would quickly do it here oops I would say e 63 plus ROS divided by this number so it would give me a unique number if I press control one and show you here you can see that these are unique numbers now I would have to sort these numbers so I would simply select a large function and I'm using larger because I want the best performing sales rep with the highest bin size or highest when count this becomes my array f4 and I would want to get it for from first to second third and so on so I've used rows function oops now when I double-click on this and I lock this rows part it would give me the right value and here I would simply get the position of this from this array f4 to lock it an exact match and now when I double click it would give me all these positions now all I need to do here is extract this position number here so again a simple index formula would do the trick I would select all these names press f4 and then select this here and you can see it gives me the name I already have the formula here let me reconstruct it for you so when I lead I put these numbers here I put this formula here it gives me the top 10 sales rep now I need to identify either the wind value or the wind come based on whatever value I have here I need not do anything I simply use an index formula here I would select this array I would select the row number which would again come with match I would match this value in this array press F for exact match and I would return the value from the second column so I would simply put 2 here and now it gives me the right result which gives me the VIN value if I go here and I change this to 2 which is VIN count you can see that these values have changed two counts and even this has changed I also have a formula here which simply changes it to win count if I select count and wind value if I select value simple if formula would do the trick so now I would use these numbers these figures here to get the top ten sales rep so simply I refer to these names here and I would then track it down for top 10 sales rep and if I try to to the right it would also give me the number and I drag it down as well I can apply the same formatting here as well so I would simply copy the formatting and put it here double-click and change this to number format because it got changed to date format and maybe use a thousand separator in case I am selecting values and put it in the middle so now I have these values as well I can put something like next then for methi's and top sales rep and simply give it a different color maybe a blue color bold font formatting and I can align this to the middle so I would select all these cells press ctrl one go to alignment and here I would center across selection and it would put it here and same is the case with this one I would simply put it in the center center across selection I would change this I will make it bold and maybe I can reduce the font size a bit for both of these so this is calibrate 10 and this is Cal every 10 as well now the final thing that I want to create here is those 2 charts that give me when count numbers by Dale size and then when I check on the check box then it gives me by sales channel so to do that here I have these numbers let me first create a formula to extract the numbers of Dale size or delete count based on the deal size so again I would check for a couple of conditions let me create the formula here the first thing I need to check here is sales stage and this would be only for those deals that have been won so I would say the sales stage is 1 let me put it in round brackets the second condition here is the country so to do that again I would select if this and f4 is equal to effort to lock it and then if this is equal to one then return one else I would have to again check for the condition first is the index of these countries the row number is this country f4 and if this is equal to the country here so this becomes my second condition and my third condition here to check whether the size is less than 250 K or not so again I would use sales data and I would say teal size is less than 250 and when I hit enter it gives me 1 which means that in the selected country which is all countries there is one deal size which is less than 250 K now I would have to recreate this formula for all these cells but I would have to change the conditions so everything remains the same except that here I would say this should be greater than or equal to 250 and I would simply copy paste it here and I would say less than 500 now when I hit enter it gives me two deals which is in this range now again I would go I would simply copy this formula paste it here and change the condition greater than equal to 500 and less than 1000 it gives me 6 deals here again I copy paste it and I'll quickly 1000 here and 2 3000 here it is 22 and the final number here it is five thousand 3000 and it gives me 22 again now these are my vin conch and this is something that I can plot directly in the chart there but before I do that let me also identify these numbers based on the channels so all I need to do is I need to use the same formula but I would add another condition to it and the condition would be that the sales channel here the same channel should be equal to telesales which is here and let me lock there only but not the column now when I hit enter oops I need to close the bracket and now when I hit enter it gives me 0 but when I drag it to the right I think my formula just got changed a little bit so see in stages one the country here sale steal size and everything is fine let me drag it to the right yes it gives me one here which is the sale has been done by partners if I copy this entire formula here then it would also if I copy this formula here then it would also give me the sales by each of these sales channels so let me quickly do it let me copy this paste it here and add another condition which is sales data sales channel is equal to this four twice to lock it and the same condition here sales data sales channel is equal to this f4 f4 twice and enter and the same thing for these two remaining as well this is multiplied by sales data or sales channel is equal to this cell f4 f4 twice copy this copy this paste it here multiply sales data channel equal two telesales f4 f4 twice and it's done now all I need to do all I need to do is I need to drag it here so when I drag it you can see it gives one here in one here which combines and gives us two if I drag this here it gives us the right number drag it here and drag it here now I have the fin count by deal size and I have it by partner as well now only one thing that I need to do here is I need to check whether someone has checked telesales or not so to do that I would first have to create that chart let me first create the first chart so I would insert a column chart here and I would simply put these values I would base these values and you can see that it has come here this access label is not right so I would have to change this I would I would remove this label and now I have the right numbers I have the right access level as well let me delete this let me delete the credit lines and let me change the color to blue light blue with slight border so I want to have a gray border to it this should look nice so I have these numbers here if I add the data labels I have these winds here based on what I have selected and I would use this graph as the first graph in my dashboard so if I paste it here and again I'm not worrying too much about the formatting if I pasted this chart here and I would put those check boxes below this so I go to developer insert in here I would put those check boxes so I would just replicate it for all those channels all those teal size so I have one two three four five categories here I would need five check boxes and to be honest I don't need these names here because if I only keep the checkbox there then that should work so I would delete these names I would simply keep the checkboxes there now I would lays these checkboxes here I can change the size and go on with the formatting but let me quickly do it to show you how it works now I would have to link each and every checkbox to us individual cell in this case let me go to format control let me keep it checked let me keep everything checked and here the first thing would be 250 less than 250 K so I would put the cell here and when this is checked you can see it returns true let me delete this child because this is not in use anymore similarly I would have to keep this checked and 250 K to 500 K is this cell similarly this cell it is checked and this is the cell link and let me quickly do it for the remaining two as well keep it checked and this is selling now what happens here is that when all of these are selected I have a true here and when any of these are is is not true then if I uncheck this this this becomes false now to get the win count by sales channel all I need to do is use a simple sum product formula and here I would multiply this range press f4 to lock it and multiply this with these numbers here and again f4 to lock it and now when I hit enter it gives me 1 but when I double-click I would have to change these references these ranges here so I would quickly do that and now you can see that in the category that I've selected which is three to five million there are five sales which we can see here and of those five to have come through partners and three have come through face to face meetings now I would quickly create that chart I would simply copy this chart and create it because everything is done the formatting and everything has been taken care of so I would simply use this version change the data here edit the data to these cells and the categories to these sales channels and you can see that I have this chart ready here I would copy this and paste it here not here exactly here and I can then simply align it and put a title to it so now there is a minor formatting thing that is left in this dashboard all you need to do is format it well if you change these selections you can see that it would automatically update so this is how you can create this chart where one chart and the selection of this chart is then controlling what you see in the chart below it one minor thing here is that here I have selected Australia and there are sales rep where their sales these count or deal size win is zero so you may not want to show these numbers so instead of simply using calculation a for what I would do here is I would change this and I would say if rather I would use the condition here so let me create the formula and I would say if this value here is equal to zero then don't show me anything but if it is not then give me this number and now when I hit enter it gives me Angela let me because this time it is locked but I don't want it locked so I would change the phone number here as well as here now when I double-click you can see that I only see those sales rep name where their sales value is some number it's not zero the formatting got screwed up because I copy retire double clicked on it so I would have to remove the formatting I would go to control one and here I can then assign the border I can say I don't want this I don't even want this and I would have to do it for each cell maybe and now I can simply also for this now I can simply go here press control one and assign the same border it had earlier and same can be done for this cell as well the same level of calculation so here it is b4 and here it is a 64 so I would simply copy this formula put it here and I would say if this value is zero then don't show me anything else show me this value I would copy and paste the formula here now formatting remains intact so now when you select say deal count it shows you deal count in an descending order it shows you next ten opportunities with the next opportunity the closest one at the top and then going down with the dates as it gets gets farther from the current state it shows in extend opportunities it shows you this chart here where you have the deal count ven count based on the country selection and also based on the deal size selection when you change these selections here in the deal size you can also see what are the sales channel used to do this now all you need to do is format this - border bit maybe give headers to these charts and these tables and you can also hide these extra rows and columns just click here select all these columns and say sorry hide similarly you can do it for the rows maybe from here you go down and select hide so you would only have this area available when I create had dashboard I also hide headings and formula bar so that I get more space in the dashboard so you can see that everything fits in one single screen you can also provide some helper items here so for example you can have some text here which says that if you select these then you have this chart updated now like I had in my original dashboard so this is how you can create a sales pipeline dashboard in Excel I hope you found this video useful thank you and have a nice day