Hello, this is Oppadu. In today’s lecture, we will create a dashboard that analyzes the sales status based on such sales data. When you click the button, the data is filtered in real time in this way. Let's take a step-by-step look at how to analyze product categories, days of the week, and even sales trends. For all the templates used in the dashboard, please refer to the comments at the bottom of the video. Are you ready? Let's go together First, let’s look at how to analyze seasonal factors. Select it as a table, then go to Insert in this table and click on the pivot table. Then, the place to insert will be made into the existing worksheet Please select an existing worksheet, and the location to insert is below here, and then you can analyze the seasonality factor like this. I'll create a pivot table at the bottom and click OK to create a pivot table And the data we need to look at is seasonality. Seasonality will be data that changes from month to month. Seasonality is spring, summer, autumn, winter, but it is difficult to share this spring, summer, autumn, and winter. When looking at the seasonality, you can see the seasonality of how the data changes for each month by fetching only the month from the date. I'll bring the date by row Then, since the currently added date is date data, the pivot table automatically indexes the date. Indexing is done by year, quarter, and date like this. Basically, indexing is done by year, quarter, and month. How to see this is by right-clicking on the pivot table and coming to the group here, you can see how dates are indexed in this way. If you see it now, it's indexed by year, quarter, month. I will proceed as it is And we don’t need the year and the quarter we’re going to see, we just need the month. So, from this row, subtract the year and the quarter. Then, only the data for the month was loaded. And then I'm going to bring the product category to the column and then the value to the value. Then, in this way, you can see how the product categories are sold for each month. When we say we analyze data on a monthly basis, there is always one thing to consider. The month of January, the month of March, the month of May, and the month of July and August have 31 days. And the month of February is the 28th The pivot table is basically If we take this numeric value into the field, how this value is created is summed up like this. So, when creating a pivot table, this value is calculated as a sum by default, so it might look a little bit wrong. So, if we say that we are comparing the data by month, you need to move the Ao method of this value from Sum to the Value field setting, and then change the calculation method to Average. The company analyzes the data on a monthly basis. But, what kind of trend is analyzed by month. When you say that data should be compared for each month, be sure to change it to an average, but be careful that you can see the minute difference accurately. Here, with the pivot table selected, come to insert and click Recommended Chart. And change the chart to a line. Shall I put a line graph like this? Then the chart was made And I'll simplify the chart. The chart we created now is a pivot chart created from a pivot table. Basically, a button is added to the pivot chart. If you click this button, you can filter the values like this. But we're going to hide this button because we don't need this button right now After right-clicking this button, I'll hide the button by hiding all field buttons, and then I'll erase the grid lines. Please delete this grid line Right-click on this legend and move it to the legend format. And the legend position will move upwards. And let’s move the legend to the far right like this And I'll change the font size a little like this, and then I'll decorate the chart a little Right-click on the outline of the chart and change the color of the lines one by one. I'll change it one by one in navy and red at the bottom Please decorate the color like this with the color you want. Coffee sales can be seen to increase so much in the summer. So, when you look at the seasonality, you can see that coffee sales increase a lot in summer, and in the case of tea, you can see that sales increase in November and December in winter. Seasonal analysis was completed like this. I'll cut the created chart with Ctrl + X like this Come to the sales status analysis and paste it here, then I will fit the chart in this way in the product classification area. With the chart selected, I'll change the shape outline to None after coming to format, so that the chart fits neatly inside. Then the first chart is complete Let’s look at the second time. We’ll analyze the sales by day of the week. I’m going to copy the pivot table on the left. I'll select all with CTRL + A, then copy with CTRL + C and paste it on the right. And now, since the data we need to see is sales by day of the week, subtract the product category, subtract the date, and import the day of the week as a row from here. The days of the week are organized On the days of the week, the order was Mon Tue Wed Thu Fri Sat Sun. Depending on the Excel settings, some people may see it as Sun Mon Tue Wed Thu Fri Sat. If you want to automatically change the order of this day to Monday, Tue, Wed, Thu, Fri, Sat, and Sun, you can change this sorting order by specifying this user. Let’s take a quick look at how to do it. I’ll go to the file and then go to the options I’m going to go from here to advanced I'll come down all the way down. You'll see a custom list here. If you come here, we can change the data we want to sort in the sort order we want I'm going to go here Then, after you come to the new list here, please enter one by one through enter like this on Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday. If you enter it and click the Add button, it will be added to the user list below in this way. Excel differs depending on the language you are using, but if you use it in Korean, it defaults to Sun Mon Tue Wed Thu Fri Sat. So, if you create a pivot table or sort the data, it is sorted by January, Tuesday, Thursday, Friday, and Saturday. Add the user list and sort the pivot table again in that state. It is sorted by Mon Tue Wed Thu Fri Sat Sun. Let’s make the same chart After selecting the pivot table, come to insert and go to the recommended chart. Then, I'll make a column chart like this. Please hide the button by right-clicking this button. I’ll simplify the chart by deleting the chart title, the legend, the grid line, and the vertical axis next to it. Then right-click on the bar and go to the data series format. And then I'll change this gap width to 100% Then, as the spacing of these bars narrows, the chart will look more stable. And right click on this chart and add a data label And as you can see, this data label is added and the original value in the pivot table is taken as it is, so the decimal point is displayed in this way. So, when I added a data label and the decimal point doesn't look good, you can change this data format. Right-click on the value in the pivot table and move it to the field display format. And if you change the format to #,##0 after coming to user designation from here, and click OK, it is organized neatly in this way. Please change the font in bold over there. Do you want to cut the chart? After cutting it with Ctrl + X, go to the Sales Status Analysis and paste it in the sales by day of the week. Then the sales by day of the week were completed. Next, I’ll make a chart that compares the holidays and weekdays. After selecting the entire pivot table, copy it with CTRL + C and paste it on the right. Since we are going to see the holidays and weekdays, remove the weekday from here. I will bring the weekdays and holidays as rows. In the same way, the calculation method for the value must be average. If the calculation method of this value is not an average, but a sum, if you look at the data in this way, the number on weekdays will be much more than on holidays. Weekdays are Mon, Tue, Wed, Thu, and Fri, and holidays are on Saturdays and Sundays, so the number of days is different. So, even when calculating this value, when comparing weekdays and holidays, be careful that the calculation method is changed to average rather than sum. Now, I will make a chart comparing weekdays and holidays. I'll make it like this with a column chart. Likewise, right-click the button and hide the button. Delete the chart title. Delete the vertical axis of the grid and create it by erasing the legend. In the same way, right-click the bar and change the spacing width to 100% in the data series format. Then right-click and add a data label. And decorate the label like this. If you change the chart a little longer in this way and to make the bar a little pretty There is a problem where the label is changed to two lines like this In that case, I'll widen this label after choosing a label. If you expand it one by one, the numbers will come out correctly. Like this Now, the main data we need to look at is holiday data, so we will choose a holiday. Select the chart and click the bar once more to select only the data for holidays. In that state, right-click the bar and I'll change the fill to yellow, and I'll change the fill to gray on weekdays. Then, a chart comparing weekdays and holidays is completed. And after you cut the chart in the same way, come to the dashboard and paste it here. Resize it appropriately. Likewise, there is no outline on this chart, so I will change it. Finally, we will see the sales trend After copying the pivot table, I'll paste it on the right Then right-click on the pivot table and come to the pivot table field list display. Sales trend. The trend can be seen in two ways. The first is that I will see every year. There will be something called, or secondly, you can say that you want to see the year and the quarter So I’m going to drag the value for the year into the row Then, the value for the average daily sales was calculated for each year. If I want to see not only the year but also the quarter In that case, bring this quarter down to the year again. You can see the year and the quarter together. In this lecture, I will make it a trend to see together until this quarter. After inserting, move to the recommended chart and take it. I will make a line graph. Then right-click the button and hide the button. Delete the vertical axis and grid lines. I will also erase the legend to make it neat. I’ll also delete the chart title Now, looking at this line graph, there is something like this in zigzag, but this data is a little bit unsightly So, to make the data more flexible, I'll right-click on this line and move it to Data Series Format. Then, I'll go to Fill in Data Series Options. You can see a gentle line at the bottom. I'll click on this Then, in this way, the line was changed to be more flexible and made to look good Then select the chart, and then come to Add and add a trend line And right-click on the trend line, then go to Format and display the formula on the chart. I will add it and move it to the bottom right. Then you can see a rough trend And right-click on the line, add a data label, move to the format, and then change the position to the top. Please change it and change the text to be bold, then come to the fill and change the fill to a solid white fill and add some transparency. And the flow of sales. Shall I change the outline to gray and the trend line to red? I'll increase the thickness a little. In this way, a rough graph is completed. So, when you look at it, you can basically see that this sales trend is on the rise And after I cut this chart, I'll paste it on the dashboard in the same way. And after importing the chart appropriately, change the outline to None and decorate it, and change this text to be smaller. The text on the horizontal axis will be changed a little bit like this. The analysis of the sales trend is complete. Then the dashboard was neatly completed like this Now, the last thing we're going to do is grab and add a slicer to it so that every time we click the button, the values in the chart here change in real time. After coming to the sheet that made the pivot table, you can see that the current pivot table is one, two, three, and four. Then, choose any of these pivot tables, and then come to the insert and add a slicer. Coming to the slicer, the data we need to see is the year and date, and the day of the week and the product category. And I’ll click OK Then, a slicer is added like this, but when you click the button, the pivot table changes But, for the pivot table to be changed, I made a slicer based on the pivot table that analyzes this seasonal factor, so when you click the slicer, the pivot table linked to it will change like this. But all we want to do is click this slicer and it will make the rest of the pivot table change at the same time So, I will connect the slicer. After clicking on the slicer, you will see the report link I'm going to go here Then you will see the pivot table we just made Yes, I’ll select all of these and connect them Now here, I'll explain one caveat One of the most common questions I get while giving lectures on this pivot table and slicer is that I made a pivot table, but I can't connect it through a slicer. Currently, we have created one, two, three, and four pivot tables like this, but I want to connect the slicer to the pivot table through report linking, but I get a lot of questions that the pivot table is not in the list. Why do we come to Pivot Table Analysis? You can see the data source change here. Let’s come here. Currently, this pivot table is a pivot table created based on this table 2-2 on the left. So please note that if we want to link reports through this slicer, we can only connect to the slicer if the ranges in the data source match. In the same way, right-click the rest of the slicer, and I will connect them one by one in the report connection You can see that each time you click the button, the rest of the pivot table changes at the same time. Then it's over Now I’m going to choose a slicer, choose anything You can select multiple slicers by holding down the SHIFT key on the keyboard and making the remaining selections. Then, cut it with CTRL + X keys and paste it on the dashboard. Like this Now and I’m going to decorate the slicer As you can see, the slicer looks like something deviates from the dashboard in terms of design. So, I will decorate the slicer simply If you select a slicer and then come to the slicer tab, you will see the style here. Select any style you want from the style. If you right-click here, you will see a duplicate here. I will move to the duplicate. And I'll make a name, but I'll change the name of the slicer to my slicer Make it And after selecting the whole slicer in the slicer element, I'll go to Format and change the font size to about 8 and the border to no And when you finish by clicking OK, the slicer style is added as a custom style on the top So, I'll change the slicer style one by one with my own The days of the week will be changed one by one with the one we made like this. Then the slicer was made a little prettier. Now let's move the slicers into position one by one. First, I'll bring a kite Bring the year to Yagi, and if you look in the slicer tab, you'll see the right column here. I'll change the number of columns from one to three. And if you can see, now, if you make a slice with a date, you'll see an unnecessary button like this. To hide this button, right-click the button and go to the slicer settings. Here you will see hide items with no data. Click this and then click OK to finish Then the slicer was made Likewise, bring the date to the top, and the date, change this number to six. Right-click the same and hide the no data item in the settings. Same day of the week. After you bring it, change the number of devotees to seven. Make the same size, and finally, change the number of columns to three, and then bring it like this. If you make it, the slicer went in this way. This is a little bit of a choice The selection is a little small, so I'll move this. There's an easy way to move this. When you come to the home, you'll see Find and Select here. If you look at the bottom from here, you can see the object selection If you select object selection, we can select the object we want by dragging it at once. If you keep dragging, objects are selected at once I’m going to move the object in this state Bring it in this way, and increase the shape. I will also increase this chart here. If you grow a slicer, the dashboard is neatly completed. If you click the button here, the value will change each time you click like this. Like this. You can see the prices change like this: Sunday sales, Monday sales, or among them bakery, drink, coffee, dessert, etc.
