Generate a Bill in Excel for Quality Assurance Effortlessly

Bill in excel for Quality Assurance

let me show you how to create a sales dashboard report like this using Microsoft Excel and it won't take more than 17 minutes this video is divided into two parts in the first part I will explain the source data and the features of this dashboard report in the second part I will explain how to design this dashboard report step by step here is the sales data of a particular company that sells electronic Gadgets in different cities of India we have the records from first week of January 2021 to last week of December 2021 dates are in the First Column product name in the second column category in the third column name of sales representative in the fourth column name of city in the fifth column number of units sold in the next column unit price and amount in the last column now the dashboard report created using this data a bar chart that shows the amount of sales happened in each month of the year 2021 another bar chart which shows the amount of sales done by each sales representative a line chart with the amount of sales happened in each City a columns aren't displaying the product by sales finally a pie chart which shows the category by sales this slicer here can be used to filter the data in these charts by a particular month or months of our choice suppose I want this charts to display only the sales happened in the month of July I will click on July and all these four charts updated ingly means these charts are now displaying the amounts for July we can have multiple selections by holding the Ctrl key match September now these charts are displaying the amounts for the selected ones to remove the filter click on play a filter that means this report can be used to monitor different metrics like performance of sales Executives months in which business performs well fast moving products Etc ultimately this report will help in understanding and improving the business same data which we saw a moment earlier First Column has the date of sale product name in the second column category in the third column name of sales rep in the fourth column name of the city number of units sold unit price and the amount in the last column Ctrl shift down arrow say we have 1560 records here control app error now let's see how to create a sales dashboard report using this data for that click anywhere inside this table go to the insert tab of the Excel ribbon pivot table a dialog called pivot table from Tableau range is activated and you can see our sales data is automatically selected we'll go with new worksheet so that the pivot table will be created in a new worksheet okay see we have a pivot table placeholder here first we will work out the amount of sales happened in each City for that drag and drop this field called City into the area for rows see we have the list of cities here now drag and drop this field called amount into the area for values and we have the amount of sales happened in each City to apply number format for these values right click number format number use thousand separator decimal places are not required okay later this pivot table will be used to create a line chart which will display the amount of sales happening each City next we need the product wise sales amounts for that we will make a copy of this pivot table select the pivot table Ctrl C to copy Ctrl V to paste for product wise sales amount click inside the pivot table unmark the checkbox against City Mark the checkbox against a product and we have the product wise sales amounts next we need the sales done by each sales rep we'll copy this pivot table Ctrl C to copy we'll paste it here remove the field code product from the area for rows Mark the checkbox for sales rep and we have the sales done by itself next we need the sales happened in each category that is laptop headphone and cell phone for that we'll copy this pivot table paste it here unmark the checkbox for sales rep Mark the checkbox for category and we have the category by sales so we have created four pivot tables now we need the amount of sales happened in each month of the year 2021. for that we'll copy this pivot table paste it here unmark the checkbox for category Mark the checkbox for date right now this pivot table has months dates and day but we only need months remotate remove days and here we have the sales happening each month of 2021 next step is naming these pivot tables to name this pivot table as City click inside the pivot table go to the pivot table analyze tab use this input box to rename the selected pivot table we'll type in City enter we'll name this pivot tables product this one will be sales rep fourth one will be category and the last pivot table will be named as months let's add a new worksheet for the dashboard we'll name this worksheet as dashboard we'll rename the worksheet with pivot tables as pivot tables next we will create the charts record for the dashboard report to create a line chart from this pivot table click inside the pivot table insert insert Lino area chart under 2D line select line to remove these buttons from this line chart right click on a field button hide all field buttons on the chart we'll delete this vertical axis levels we don't need grid lines we'll remove the legend now to add data labels to the Chart chart elements data labels we'll modify this chart title to sales by City now to move this line chart to the worksheet for dashboard click on the chart control X to cut go to the worksheet called dashboard Ctrl V to paste will reduce the zoom load for a better View now to resize the chart click on the chart format let's make the height 7. set the width to 16.5 to create a column chart from this pivot table called products click on the pivot table insert insert column or bar chart clustered column will hide the field buttons we'll remove the vertical axis tables we don't need grid lines remote Legend to add data labels chart elements data labels we'll modify the chart title to product y sales let's move this chart to the worksheet for dashboard control X to cut go to the sheet called dashboard Ctrl V to paste to resize this chart format height will be 7 width will be 16.5 representing the sales done by each sales rep to create a bar chart from this pivot table called sales rep go to the insert tab insert column or bar chart clustered bar remove the fill buttons delete the horizontal axis levels remote Legend remote grid lines and data labels modify this are title to sales by sales rep control X control V to paste we'll resize this chart set the height to 14.5 which will be told next one is a pie chart with the category wise sales for that click inside the pivot table insert insert Pi or donut chart pi hide the field buttons remote Legend and data labels to add categories to the data labels right click on a data label format data labels Mark the checkbox for category name we'll modify the chart title to sales by category now move the chart into the worksheet for dashboard now we need a slicer which will enable us to filter the data in this charts ing to the selected month for that click on any of these charts pivot chart analyze insert slicer a dialog called insert slicers is activated select months okay we have a slicer here now to arrange these buttons in multiple columns go to the tab called slicer set the number of columns to 3. now to hide these unwanted buttons slicer settings hide items with no data okay now when I click on any of these buttons for example this button called May this line chart is displaying the sales amounts for the month of May July see the chart updated ingly to remove the filter click on this clear filter button now to link the other three charts to the slicer select the slicer go to the slicer tab report connections all over pivot tables are listed here Mark the checkboxes for both pivot tables except the one for months category products sales rep okay now when you click on the button for Mars all four charts updated ingly August April to make multiple selection holding the Ctrl key click on the corresponding buttons March August October these sounds are now displaying the details for the months April March August and October clear filter to remove the filter now to display the sales amount for each month in a bar chart click inside the paper table code months insert insert column or bar chart clustered bar hide the fill buttons remove the horizontal axis levels promo Legend we don't need grid lines we'll modify the chart title to month by sales and data labels let's move this chart to the worksheet for dashboard we'll resize this chart height will be set to 14.5 width will be 12. for a better view let's increase the zoom level to 65 percentage last step is formatting of these charts to change the color scheme of this line chart select the line chart design change colors let's go with colorful palette 4. next we will format the column chart blue colorful palette 3 now the colors for pie chart once again colorful palette four to resize the data labels click on the data label in the Home tab increase font size to tall make it bold to apply the same formatting to other data labels double click on the format painter click on the data labels on each art to turn off the format painter press the Escape key next is horizontal line vertical axis levels make it bold increase font size to 12. double click on the format painter click on the access labels on HR next is chart title increase the font size will make it red bold and italic again use the format painter to copy the formatting you can also format the slicer if you want to to format the slicer select the slicer go to the slicer tab here we can select the slicer style of our Jaws to hide the grid lines on this worksheet go to the view tab unmark this checkbox for grid lines notice more than this line on the line chart right click on the line format data series fill online smooth line one last thing to reverse the order of the months right click on the access labels format access categories in reverse order and our sales dashboard report is ready I've also done a video on creating the same dashboard report using power bi desktop the link for that video shown in the video description make sure to check that out until next time thank you for watching and wish you a great day [Music]