Effortless Invoice Tracking Spreadsheet for Quality Assurance

Invoice tracking spreadsheet for Quality Assurance

foreign in this video we will set up an eye-catching and flashy dashboard in five simple steps this dashboard is fully interactive there are so many options in this dashboard which users can click to play around with the information in building this dashboard you will learn a great set of skills you will learn how to work with shapes and make them dynamic the engine of this dashboard runs on pivot tables therefore we will go through advanced pivot tables techniques like how to associate and disassociate slicers from pivot tables and many more you will study custom formatting and conditional formatting techniques like applied in this column this video will also teach you about how to build Dynamic and beautiful charts such as these ones down here and best of all you will step up your design game by learning how to set up custom color palette to maintain consistent colors how to distribute and align shapes evenly to make your reports look visually pleasing and so much more so I hope you're excited and I look forward to seeing you all right so first thing first let's navigate through our data and get some familiarity we have 21 columns and round about 10 000 rows this data shows sales of a fictional Superstore for four years from 2015 to 2018. we will use the following strategy in setting up this dashboard we will first create a pivot table from this data set and put it on a new sheet let's call it a working sheet next we will set up a dashboard sheet this is where our final dashboard will live once the working sheet is set up we will link our dashboard to the working worksheet First Step let's do some wireframing wireframing the dashboard will give us an idea of all the data needed it also gives us a sense of planning saving you a lot of time later on so let's start off this is how we want the final dashboard to look like so based on this I will do some rough sketching of how I want my dashboard elements to be spread out so on the top left I would like to have a logo I will create a border Box by pressing Ctrl shift and 7 on my keyboard and inside this box I will write logo for now then we will have let's say six boxes here [Music] and then we will have a separator line somewhere over here so in order to create a line I'll just put in a shape and put a line here for now we will not worry about formatting a lot so over here we will have four tables so let's say my first table will come in somewhere in this range [Music] [Music] after that we will need another separator line over here and in the end we will need two charts here and next I will add some space for the filter buttons [Music] now that we have set up the wireframe as a second step let's shut down all the data points we will need for our dashboard [Music] thank you so let's review the data points I have identified here in the top left box we will have sales number for the latest here the second box will have the sales number for the last year followed by the third box which will show the growth next we have the profit number for the latest year followed by the profit for the previous year and then the profit growth all these boxes should be connected to buttons these buttons should enable user to filter these boxes by either category or segment in the middle we have four tables the first table will show sales for top 10 subcategories for year 2017 and 2016 followed by a column for growth second table will show the same data but instead of subcategory it will have States these two tables should be filterable by category table 3 and 4 are very similar to table 1 and 2. only difference is that we will need to have segment as the filter rather than category in the bottom section we need shorts the first chart will show sales and price for each quarter from year 2014 to 2017. the second chart should show the top 10 states with the highest sales arranged in descending order later we will also add a feature where users can select if they want to choose top 10 or bottom 10 states by sales both the charts should be filterable by category segment or ship mode in the third step we will start setting up the data identified in Step number two I will first rename my wireframe sheet as dashboard wireframe then I'll make a copy of the sheet and call it dashboard final plus I will also rename sheet1 as the source data so first of all I'll need to set up a pivot table that shows me the data for the top 6 boxes that is sales for the latest here sales for the last year and the profit numbers so let's do that I will go to the source data select the entire data set and add pivot table by pressing the alt n v t on my keyboard alternatively you can also go to the insert Tab and add pivot table from there I will specify the worksheet where I want my Pivot to appear that is working sheet cell A3 and there we go we have our pivot setup so first of all we need the sales for the two years so I'll simply go and select other date and put it into years I'll remove the quarters and the order date I'll just need the years and on the years I will filter just 2016 and 17 because I need the data for the latest two years next I will just drop in the sales values and I will drop in the profit values I will first format these as numbers now when it comes to sales and profit growth numbers I have two options I can either have them as calculated Fields inside the pivot tables or I can have them as manual calculations on the side for this case I'll go with the second option [Music] foreign set up for at least this part of our dashboard now notice that in the final dashboard we also want this part to be impacted by filter buttons that we will provide at the top so let's set up those filter buttons to do that I will create a slicer for category and segment field in this pivot table I will right click on the category field and add a slicer then I'll do the same for Segment field [Music] let's set up the other tables we need one table which shows sales by subcategory for 2017 and 16 and gets filtered by category then we need another similar table but something that gets filtered only by segment and then we need sales by cities so let's go back to the working I will set up a new pivot table similar to how I did for the first one I'll go to the source data select the entire data press the alt NV shortcut and get a new pivot table on the working sheet in cell A14 for this pivot table I'll need the subcategory in the rows sales in the values and then I'll drag Auto date to the columns I'll filter for only year 2016 and 2017. and then I'll also get rid of grand totals thank you [Music] next looking at the Prototype of final output I see that in the tables we only need top 10 values therefore I will go to my Pivot tables and filter for only top 10 items to do that I will go to my pivot table click on the drop down in the row labels then click on value filters and select top 10. foreign [Music] the list in the descending order of sales so this way the subcategory with the highest sales in both the years combined will come at the top and then it's going to move in the descending order Okay cool so I think this looks fine the only thing we need to do now is add a filter for category here or a slicer for category similar to how we did it for the first pivot table I will select any cell on the second pivot table go to the category field right click on it and add a slicer now we already have a slicer here for category but this slicer is only impacting this pivot table and this slicer is only impacting this pivot table if you want to then how you go to pivot table analyze just keep your cursor on the pivot table that you want to inspect and click on filter connections so you will see that this particular pivot table is only impacted by slicer of category 1. now which slicer is category one how can we know that I'll just click on OK I'll select the slicer go to slicer and click on slider search settings and this is the name of the slicer it's called category one I can name it as category table one just for Simplicity because we know that this table is for table one this is the data for table one and I'll just click on OK and now if I go to pivot table analyze and click on filter connection it will say that this pivot table is only impacted by slicer that has the name of category for table one and it's not being impacted by this slicer and if you want we'll go to this one and click on the filter connections you will see that the top table is getting impacted by only the top two slicers so now we need to set up the second table which is which shows a sales by cities for the top 10 cities in the same way so let's go back here since I want the second table to be impacted by the same exact slicer I have again two options one is to set up the pivot table from a scratch and then link it to this slicer or I can just copy this pivot table here down let's say somewhere here maybe we can even keep it there and there you go now you will see that this pivot table as well will already be linked to this this to the slicer of category table one so now all I need to do is instead of subcategory I will replace this field with City and again I will put a value filter of top 10 so it will give me top 10 items by some of sales and then I will just sort it in the descending order of sales so we have the data available for this portion we have it set up for this portion now let's do the same for this one now we need very identical tables for this particular portion so to save time I will just copy these tables and put them over here D I'll name it as data for table 1 and table two and this one is for the data for table three and table four so what is the difference between two tables at the top and two at the bottom the two tables at the top which are also the table one and table two on our wireframe are getting impacted by the category slicer whereas table three and four will get impacted by the segment field slicer so I don't need to change much now all I need to change is just disconnect these two pivot tables from the category slicer to do that I'll go to pivot table analyze filter connections and uncheck it and then I'll do the same for the fourth table I'll place my cursor anywhere in the table go to filter connections and then uncheck the category slicer now if I tweak with this or play around with this slicer these two tables would get impacted but these will not and I want these two tables to be impacted by the slicer of segment field so I'll put my cursor anywhere in this table and right click on the segment and add it as a slicer I'll just steam this segment as instead of segment one I'll see segment for table okay so let's see this pivot table is already getting impacted by a segment table I can see it over here and this pivot table is currently not impacted by any slicer but I will connect it to segment table one now if I select anything here you will see that these two tables will get impacted yep the numbers are changing in both the tables and nothing else is getting impacted good so we have the data set up for all of this portion now we just need to set up the data for this portion and then we will start our dashboarding so for this portion I need the trend of sales and price by quarter for the last four years let's set up this table first I think it's slightly different so I'll just go back to the source data again select the entire Source data press Alt nvt and then I'll select existing worksheet and then I will go to my working and put the data over here since all right now I need the Years first so I'll just drag the order date to rows and I'll get rid of quarters and order it and or maybe I'll need the quarters so I'll just keep the quarters I'll just keep the quarters in there then I will open these cells I will add sales and quantity to the values for for the purposes of the chart we need price but we do not have the price field in the data set by default so we will calculate it based on the sales and quantity by default my table is set up in a compact form and I want it to be in tabular format as that will help me later on in picking up the numbers for charting with the table selected I will go to design tab click on the report layout and select show in tabular format now my years and quarters are side by side with each other now we want to set up a chart we have two options here one is to go with the pivot chart the second one is to go with the general bar chart pivot charts are slightly inflexible when it comes to formatting therefore I'll go with the general one to set up a bar chart I'll need to bring the data outside of the pivot table I will first do that note that I'm linking the numbers via formula so they always stay connected to the pivot table next I will add a column for price as we do not have the price data by default price will be calculated as sales divided by quantity and then I will format these numbers as currency next we will set up a column chart to do that I will select the data click on insert Tab and select the column chart then I will add a second series to this chart to do that I will right click anywhere on the chart click on select data and then add a new series I will give the name of the series and in the data I will give range which contains data for pricing next I will change the chart series type to line also I will plot the pricing series on a secondary axis the second chart we need the states with the highest and lowest sales of all time for this we can create a copy of the last pivot table instead of fears and quarters we can put in the state name then I will sort it in descending order similar to the last instance I will create a copy of data outside of the pivot table so I can set up the chart manually [Music] next I will select the top 10 rows and create a column chart by going into the insert Tab and selecting column chart [Music] [Music] thank you [Music] now that our charts are arranged let's set up the filter buttons which are essentially the slicer we need three slicers here a slicer for ship mode category and segment so I will put that in the procedure is the same I'll select any cell in the first pivot table or the pivot table for the first chart and then go to the relevant Fields like segment right click on it and add a slicer so I'll fast forward the video for few seconds from here [Music] foreign to ensure we don't mix the slicers up I will give these three new slicers a unique name I will suffix their names with underscore charts [Music] now my table for chart 1 will be connected to the slicers but pivot table for the second chart will not yet be connected to these slicers the reason is I had used the first pivot table to set up the slicers so I will go to the second table click on filter connections and put a check next to the relevant slicers let's quickly check if our slicers are working it seems they are working fine now that we are done with our working sheet let's start building out our dashboard first of all I will insert my logo to do that I'll go to the insert tab click on insert picture and then select the location where my logo is saved next we will create the six boxes that will act as our top cards I will first set up one box I'll click on insert and add a shape [Music] next I want to set up a color for my dashboard now if I manually change the color one by one that will take a lot of time I know right now we have only one box but very soon we will have lots of elements on our dashboard and changing the color for each of them can be time consuming to fix this I will set up my primary color in Excel as the color on which I want to base my dashboard I want my dashboard to have the same color as my logo so I'll set that up as my primary color to do that I will go to the page layout tab click on colors customize colors and then change the color of accent 1. you will see that once I change the color of accent 1 all the colors be the pivot table slicers everything will take on that color or some shade of that color so I will put in the hex code of my color another option is to use an eyedropper or you can just select the color from the RGB code but since I remember the hex code of my logo I'll just do that [Music] now we can see that not only the box shape but the pivot tables charts all the elements on our Excel workbook have acquired the same color next I will do some basic formatting reason I want to do some formatting at this stage is because we will make copy of this shape so it's a good idea to do some formatting right now I will remove the outline and put in some shadow next I will create six copies of the shape and align them in the middle [Music] [Music] to align all of the shapes I'll select them go to format click on the Align and then I can click on align middle this is a very helpful feature in Excel and it helps you align your objects precisely next we will add a text box which will Define The Heading of each of the cards I will remove the outline and give a transparent background [Music] now inside these boxes I want to show numbers and I want to keep them Dynamic to do that I will double click on the shape go to the formula bar and type in the self-reference from where I want my shape to pick the number from for latest year sales I know that the number is in cell B5 of working sheet so I'll write that in the formula I will now do some formatting to make this number pop out a bit since the first box is shaped up quite well I will recreate six copies of it then I will update header for each of the boxes [Music] now I will update the formulas for each of the box for example link the last year's sales box to last year's sales number on the working sheet and so on note that when I update the formula the formatting will go back to default a quick way to fix this is via the format painter as I'll select the first box double click on the format painter and then click on the other boxes you will see that other boxes will acquire the same exact formatting as the first box next I will make the separator line slightly thick and give it a bit lighter shade of the same color [Music] in the middle portion we need to set up the tables I will set up the table headers and give them bold formatting foreign [Music] once the headers are set up I'll copy the first table and paste the column width on the rest of the tables this will make sure our table looks symmetric and even now I will start linking these table ranges to the pivot tables on the working sheet [Music] note that the growth versus py is not available on the working sheet so we will need to calculate it here foreign column I want the positives to appear green and negatives to appear red I can achieve that via custom formatting with the range selected I will press Ctrl 1 shortcut on my keyboard this will take me to custom formatting window first I will click on custom in custom formatting the characters before the semicolon Define the formatting when the number is positive the characters after the semicolon Define the formatting for the for when the number is negative therefore before the semicolon I will put color 10 which is a kind of a shade of green inside the square brackets and after semicolon I will put red again inside the square brackets when I click on OK you will see that the negative numbers will appear red and the positive numbers will appear green [Music] now in the same way I will set up rest of the tables foreign [Music] moving on we will bring in the charts from the working sheet with the chart selected I will press Ctrl X to cut them then go back to the dashboard sheet and press Ctrl V then I will do the same with all the slicers [Music] foreign with this we are done with almost 80 percent of our dashboard and the last step is to do some final touches and formatting first we will fix the slicers we want the slicers to look like buttons that users can click to start off I will convert the slicer from one column to three columns with slicer selected I will click on the slicer Tab and then increase the number of columns next I want to get rid of the slicer borders to do that I will select any slicer and then click on slicer Tab and duplicate the current slicer style I will give this a style a new name let's say a slicer underscore no border I will click on format and get rid of the Border but you will notice that there is no change to the slicers this is because they are still on the previous style to apply the new style I will again select any slicer then go to the slicer Tab and select the new style which we just set up next with the slices selected I'll go to the settings and uncheck the display header with that the header will not display anymore now I will repeat this step for all the slicers [Music] [Music] [Music] as the next step I will format the charts I will increase the Gap width of the bars add Shadow to the bars convert the axis to thousand units add data labels to the pricing line make all the fonts black and get rid of borders on both the charts I'll fast forward the video from here however if you want to know how exactly these formattings were done you can slow down the video and watch it in detail as well [Music] [Music] thank you [Music] next for the growth rates I want to show a triangle facing upwards for the positive numbers and a downwards triangle for the negative numbers to get those triangles I will go to insert tab click on symbol in the font I will select aerial there I will be able to find the triangles I will insert them into any cell now I will select the range which I want to format go to custom formatting by pressing Ctrl 1 on my keyboard and then put these triangles in the custom formatting logic which I've defined as the next step I will copy and paste the formatting on the other columns [Music] thank you [Music] now let's check if everything is working I see that when we select certain categories the table on the left is showing errors this is because there is not enough data when certain categories are selected to get rid of this error we can build an if then else logic [Music] this logic is basically telling Excel that if there is any data then return that data otherwise keep the cell blank I will then copy this formula to all the relevant cells although the error is gone there is a still one problem and that is we can see Grand totals in our data to solve this I will go to the pivot tables on the working sheet and remove the grand totals next I will apply the same logic to the growth versus py column after that I will copy these formulas to the rest of the tables now let's check it out once more everything seems to be working fine now let's add the last feature currently the chart at the bottom right shows the top 10 states by sales we want to give our user the option to either select the top 10 states or the bottom 10 states for that we will need to add the option button to do that let's enable the developer tab to enable the developer tab go to file options customize ribbon and then check the developer tab click on OK and you will see the developer tab will be available now from the developer tab I'll go to insert and then insert two option buttons now I will right click on the option button and provide the cell link this cell will get populated with either one or two depending upon which option does the user select I will provide the link to the cell on the working sheet right next to the data for this chart [Music] [Music] lastly I will apply a combination of switch and sort formula this formula will sort the values from the pivot table in ascending or descending order depending upon what option user has selected [Music] oh [Music] now our user can toggle between top 10 and bottom 10 options and the chart will reflect the right results how about we make the title of the chart dynamic as well such that the chart title should show highest or lowest depending upon the option selected to do this I will utilize another switch formula this switch formula will give highest or lowest depending upon what option is selected then I will concatenate the result of this switch formula to create a chart title and finally I will link the chart title to this final result now if I select bottom the title of the chart changes along with it I hope you enjoyed this video if you did please hit the like button and also don't forget to subscribe [Music]