Speaker 1: 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.

Speaker 2: 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 1st 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 column chart displaying the product-wise sales. Finally a pie chart which shows the category-wise 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 these charts to display only the sales happened in the month of July. I will click on July, and hold these four charts updated accordingly. Means these charts are now displaying the amounts for July. We can have multiple selections by holding the control key, March, September. Now these charts are displaying the amounts for the selected months. To remove the filter, click on Clear 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. Control-Shift-Down arrow. Say we have 1560 records here. Control-Up arrow. 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 Table or Range is activated, and you can see our sales

Speaker 3: data is automatically selected. We will go with New Worksheet so that, the pivot table will be created in a new worksheet.

Speaker 2: Ok. See we have a pivot table placeholder here. First we will work out the amount of sales happened in each city.

Speaker 3: 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.

Speaker 2: And we have the amount of sales happened in each city.

Speaker 3: To apply number format for these values, right click, Number Format, Number, use 1000 separator,

Speaker 2: decimal places are not required, ok. Later this pivot table will be used to create a line chart, which will display the amount of sales happened in each city. Next we need the product wise sales amounts. For that we will make a copy of this pivot table.

Speaker 3: 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

Speaker 2: City, mark the checkbox against Product, and we have the product wise sales amounts. Next we need the sales done by each sales rep. We will copy this pivot table, Ctrl C to copy, we will paste it here.

Speaker 3: Remove the field called product from the area for rows, mark the checkbox for sales rep,

Speaker 2: and we have the sales done by each sales rep. Next we need the sales happened in each category, that is laptop, headphone and cell phone. For that we will copy this pivot table, paste it here, unmark the checkbox for sales rep, mark the checkbox for category, and we have the category wise sales. So we have created four pivot tables. Now we need the amount of sales happened in each month of the year 2021.

Speaker 3: For that we will 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, remove date,

Speaker 2: and here we have the sales happened in 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 will type in city, enter.

Speaker 3: We will name this pivot table as 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.

Speaker 2: We will name this worksheet as dashboard. We will rename the worksheet with pivot tables as pivot tables. Next we will create the charts required for the dashboard report. To create a line chart from this pivot table, click inside the pivot table, insert, insert line or 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 will delete these vertical axis labels. We don't need grid lines. We will remove the legend. Now to add data labels to the chart, chart elements, data labels.

Speaker 3: We will modify this chart title to sales by city. Now to move this line chart to the worksheet for dashboard, click on the chart,

Speaker 2: ctrl x to cut, go to the worksheet called dashboard,

Speaker 3: ctrl v to paste. We will reduce the zoom level 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. We will hide the field buttons. We will remove the vertical axis labels.

Speaker 2: We don't need grid lines. Remove legend. To add data labels, chart elements, data labels.

Speaker 3: We will modify the chart title to product by sales. Let's move this chart to the worksheet for dashboard, ctrl 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.

Speaker 2: Now a bar chart 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.

Speaker 3: Remove the field buttons. Delete the horizontal axis labels. Remove legend. Remove grid lines. Add data labels. Modify the chart title to sales by sales rep. Ctrl x. Ctrl v to paste. We will resize this chart. Set the height to 14.5.

Speaker 2: Width will be 12. Next one is a pie chart with the category wise sales. For that, click inside the pivot table, insert, insert pie or donut chart, pie.

Speaker 3: Hide the field buttons. Remove legend. Add data labels.

Speaker 2: To add categories to the data labels, right click on a data label, format data labels,

Speaker 3: mark the checkbox for category name. We will modify the chart title to sales by category. Now move the chart into the worksheet for dashboard.

Speaker 2: Now we need a slicer which will enable us to filter the data in these charts according 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. OK. 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.

Speaker 3: Now to hide these unwanted buttons, slicer settings, hide items with no data. OK.

Speaker 2: 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 accordingly. To remove the filter, click on this clear filter button. Now to link the other three charts to this slicer, select the slicer, go to the slicer tab, report connections. All our pivot tables are listed here. Mark the checkboxes for all pivot tables except the one for months. Category, products, sales rep. OK. Now when you click on the button for March, all four charts updated accordingly. August. April. To make multiple selection, holding the control key, click on the corresponding buttons. March. August. October. These charts 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 pivot table called months. Insert.

Speaker 3: Insert Colombo bar chart. Clustered bar. Hide the fill buttons. Remove the horizontal axis labels. Remove legend. We don't need grid lines. We will modify the chart title to month wise sales. Add data labels. Let's move this chart to the worksheet for dashboard. 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%.

Speaker 2: Last step is formatting of these charts.

Speaker 3: 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.

Speaker 2: Once again colorful palette 4. To resize the data labels, click on the data label. In the home tab. Increase font size to 12. 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 chart.

Speaker 3: To turn off the format painter, press the escape key.

Speaker 2: Next is horizontal and vertical axis labels. Make it bold. Increase font size to 12. Double click on the format painter.

Speaker 3: Click on the axis labels on each chart. Next is chart title. Increase the font size. We 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.

Speaker 2: To format the slicer, select the slicer. Go to the slicer tab.

Speaker 3: Here we can select the slicer style of our choice. To hide the grid lines on this worksheet, go to the view tab.

Speaker 2: Unmark this checkbox for grid lines. Now to smoothen this line on the line chart, right click on the line, format data series,

Speaker 3: fill and line, smoothed line. One last thing. To reverse the order of the months, right click on the axis labels, format axis, categories in reverse order. And our sales dashboard report is ready.

Speaker 1: I have also done a video on creating the same dashboard report using PowerBeader store. The link for that video is shared in the video description. Make sure to check that out. Until next time, thank you for watching. Wish you a great day.