Hello everyone. This is Oppadu Excel. There are several skills that are essential to your work life. One of them is the ability to effectively communicate my thoughts to others. In this lesson, we will learn how to create an Excel chart in five steps. If you’re making the same chart with the same data, but you’re afraid there’s not enough data I’ve prepared, look at the end of this lesson. Are you ready? Let’s go together! In this lesson, we will create a chart in five steps. First analyze and summarize the given data. Select the chart type to chart the data you analyzed. Determines the color of the chart, that is, the theme of the chart. Next, simplify the chart. Simplification is divided into two parts: data item simplification and chart item simplification. Finally, we will go through the steps to decorate the chart. First is data analysis. In this lesson, we chart the sales of Oppadu’s vegetable shop by month. The data used in this lesson is fairly simple. In practice, much more complex data is used, so you need to extract the data through a pivot table or function. If the data is used regularly, it is convenient to normalize the data. For a list of lessons on data normalization, I’ve included a link on the right. How to use a PivotTable or Slicer has been explained many times in previous lessons. Please refer to the related link at the top right. There is a practice sheet. Here is the monthly sales status of Oppa’s vegetable shop. Make a chart. Select a range, then click Insert-Featured Chart. After clicking “All Charts”, in this lesson we will create a “cumulative column” chart It appears that sales for the ‘cabbage’ item in green are increasing. Try changing the chart format. Right click on the chart and select ‘Change Chart Type’. Change the chart type to ‘Bar chart with 100% cumulative verticality’. Clearly the difference is visible. Sales of green bars grew more and more in December, up to 50% of total sales. In this lesson, we will create a chart that can convey to the user the key to the increase in Chinese cabbage sales. Please go to the next page. Chinese cabbage sales increased gradually, and in December, we sold about 1.3 million won, creating a chart that tells us that sales went up to 50% of the total sales. In practice, data is presented in a much more complex form. Please note that it takes a lot of time and effort to analyze the data in different ways. Choose which chart you want to represent the data we summarize. The most important thing when creating a chart is that the audience is comfortable and communicates what I want to convey most effectively. Sometimes you need to create a 3D chart. For example, when there are two or more variables, and so on. In most cases, however, this can be solved with 2D charts. So you only need to remember the five charts below. You only need to remember five columns, one stacked bar, one line graph, one bar graph, and one pie chart. Solve most business line charts. When creating a chart, make it as simple as possible using 2D charts, and then highlight one or two key points on the chart to make the chart look much better. Here is an example of the five charts just mentioned. More details on when and how to create each chart will be covered in later lessons. Subscribe, like, and even set alarms In this lesson, we will make a stacked bar graph like this. Once you have decided on the chart type, the next step is to determine the style and theme of the chart. This step determines the colors that go into the chart. The important point here is that anyone can make a good looking chart if they decide the theme color without special design. How to determine the color is not difficult. Each company you go to has a chart form or PPT format in use. Please determine the color according to the format. If there are no representative colors in use at the company, just remember two things. The first is background color and the second is accent color. The background color is selected based on the cold color. Commonly used gray and indigo blue. Second, the accent color is selected based on the warm color. Usually red and yellow are used. As a precaution. Do not use more than one accent color in a chart. If you find it difficult to determine the color, I’ve listed the link on the right. When you come to the link, there is a table listing the colors. Use this table to decide what color to put on the chart. There is a theme provided by Office. Applying a theme is one way. Let’s move on to the next page. Click ‘Page Layout’-‘Themes.’ You can select and apply the theme you want among several themes. In this lesson, you create a chart with gray for the background color and yellow for the accent color. Change colors one by one. Right-click on the bar and change the fill color to yellow. Change the fill color from gray to gray one by one. I just changed the color of the chart, but you can see that the chart looks nice. Simplify the chart There are two major parts to simplifying charts. The first is simplifying data items and the second is simplifying chart items. The chart contains a total of six items. When I create a chart, I need to highlight what I want to convey. Create up to five data items in a chart. It’s not too inconvenient to see, but what you want to convey is to combine the above five items into ‘Other’ to emphasize the content of ‘cabbage’. The numbers going into the chart went up to a million units. If the data displayed on the chart is a high digit number, cutting through the thousands and hundred thousand units will help simplify the chart. If you change the unit, be sure to comment on the changed unit. It is important to avoid misunderstandings with the audience. Cutting thousands and hundreds of thousands is easy with custom formats. We will learn how to do this in the exercise sheet. Recreate a chart that combines five items into other items. Copy the above item and paste it below. Create an “Other” column, and then sum the five columns with the SUM function. Get the total by December with automatic filling. Copy and paste cabbage items. After selecting the range, use CTRL + 1 to enter the cell format and display the border inside and inside. Select the range again and add “Insert”-“Featured Chart.” Add a “cumulative column” chart as before. Change the color of the chart to gray and yellow. Right-click on the chart and add “Data Labels.” It’s not good because the number is displayed in hundred thousand units Change the number format in the data range. Select a range and move it to the cell format. In Display Format, enter ‘#, ## 0’ in the ‘Custom’ format. A number is displayed with thousands separators. Add a comma (,) after it. View format changed from “million units” to “thousands.” Adding another comma will cut the thousands again. Press OK to finish. The data format displayed in the chart has changed. Since you changed the units, add a comment to the chart. ‘Insert’-add a ‘textbox’ Please add (Unit: 1,000 won). Shows both ‘Fill Color’ and ‘Contour’ as none in the shape. The chart is organized more clearly. Simplify chart items. Simplify chart items are different for each company, and different styles are preferred for viewers. Basically, charts created in Excel contain all the commonly used items, so use only the items you want. In my case, I’ve simplified the chart’s title, vertical axis, gridlines, and legend. There are two general rules here. The first time you maintain the vertical axis, keep the value on the vertical axis a multiple of 5. Rather than displaying the vertical axis values as 2,700-2,500-2,300, it is important to display the numbers 2,000-2,500-3,000 as easy-to-see numbers. If you want to display gridlines, divide them into at least 3 to up to 5 instead of dividing them into several ones to display them at a glance. Duplicate items will be grouped together. For example, the product 읰 ㅎ ㅡ ㅁ For example, groups of products, or dates, often show duplicate years. Grouping them together can simplify the chart. One tip is that if you have a long entry on the horizontal axis, the text may be smaller or appear diagonally. In that case, using line breaks or the CHAR function displays two lines of silence, making the chart much simpler. Grid lines and vertical axis are erased. The year is a duplicate. I will tie the years together. Use the YEAR function. Displays the year of the date. Add & “year” after it. After selecting the range, change the display format. After entering the cell format with CTRL + 1, delete ‘yyyy year’ from the display format. Only month is displayed. Right click the chart again and go to “Select Data.” Under ‘Horizontal (Item) Axis Labels’, go to ‘Edit’ and select a range including year. The year is displayed simplified. If the year is on the left instead of in the middle, If so, right click on the horizontal axis and go to Format Axis. When it comes to ‘Label’, check the ‘Multilevel Item Label’ button. Duplicate items are displayed in a bundle. I will show you the data labels. I’ll click on the bar of the chart and then right click to add a ‘data label’ to the bar. In stacked bar graphs, the ‘data label’ must appear in the bar. When you come to the label format, the label position is only ‘middle’, ‘inner end’, close to the axis’, so if you want to display the label outside the bar, you have to use different tricks. Let’s learn how to display labels outside of bars in a stacked bar graph. I have a total row. Get the sum of guitar and cabbage. Right-click on the chart and select “Data Selection.” In the Legend line item, click Add to add the total you just created. The series name is cell A7. Series values are added to the range you just summed. Item added Right-click on the chart and select “Change Chart Type.” After coming to “Mixed”, add the “Total” column to “Secondary.” Change the chart type to “Line graph.” Existing items will be selected as ‘cumulative bar’ charts as before. Line charts are created along the stacked bars. Delete the existing label, right click on the line chart you just created, and make the ‘Contour’ None. And add a “data label.” Right click on ‘Label’ and go to ‘Label Format’. Move the position of the label upwards. The label is marked and you only need to change the value of the label to the value of the cabbage. Right click on the label and go to ‘Label Format’. In Label Options, you can choose the value you want to appear on the label. Select “Cell Value.” Select the data label range as the ‘cabbage value’ range. Uncheck existing ‘values’. The values for the cabbage were shown. Please remove the secondary shaft. Change the font to bold. Added ‘Total’ to legend below. Click on the total twice and delete only the total. Move the position of the legend to the top, top corner. The final step is to decorate the chart. Decorating a chart is very simple. In step 1, we decided what we wanted to highlight by summarizing the data. You can change the color of the highlight and add a border. Each person watching the video has a different style, so let’s emphasize it according to your preference. Just highlight the chart. One tip here is that changing the chart’s width and aspect ratio can change the overall impression of the chart. Let’s change the chart’s proportions for a long time. It gives the impression that the amount of change in the chart has changed more significantly You can change this ratio as needed to better communicate what you want to convey. One thing to note is that you don’t deceive your audience by changing the values directly on the chart. I’ll decorate the chart. Right-click on the bar and change the fill color to pale yellow. I will change the last three colors to dark yellow. And for the border, I’ll put the outline as a thick, thick dotted line. Please reduce the font size of your overall label. Only the last three fonts will be larger. Pressing the keyboard F4 will repeat the previous action. Finally, add ‘arrow’ from ‘insert’ to ‘shape’. The fill is red and the outline is none. Please fill in the transparency of the fill color in ‘Shape Format’. Rotate the shape to make it grow. The chart is clean. In this lesson, you learned how to create a chart in five steps. Making a good chart is one of the skills you need for work. Watch this lesson over and over again, and build it to your own strength. If you have any questions during this lesson, please leave a comment below. If you enjoyed this lesson please subscribe and subscribe.