Week 9, Chapter 4 Lesson 1: Advanced Chart Tricks (1) PDF

오피스 활용 Week 9 Chapter4 (1) Lesson 1. Advanced Chart Tricks(1) Prof. Heejung Yoon Learning Contents & Learning Objectives Learning Contents Dynamic Charts (1) Dynamic Charts (2) Learning Objectives We know the easiest way to ensure that your charts automati...

오피스 활용 Week 9 Chapter4 (1) Lesson 1. Advanced Chart Tricks(1) Prof. Heejung Yoon Learning Contents & Learning Objectives Learning Contents Dynamic Charts (1) Dynamic Charts (2) Learning Objectives We know the easiest way to ensure that your charts automatically update. We will learn another way to create dynamic chart ranges. We learn how you have charts hooked up to a dynamic range in Excel 365. We will learn about the ways that we can automatically sort values using formulas without Excel 365. 1. Dynamic Charts (1) 2. Dynamic Charts (2) 1. Dynamic Charts (1)- 1 ▪ Everybody loves a good chart. However, you find many users stick to the standard chart types provided by Excel and a few tweaks of the standard settings. ▪ You can create some very useful charts that present data effectively with the standard settings. ▪ But there is so much more to explore. ▪ By combining your charts with the power of tables, formulas, formatting, and Power Query, you can take your charts to the next level. 1. Dynamic Charts (1)-2 ▪ There are books dedicated to charts in Excel, but we only have this chapter. ▪ The rule is to keep it simple but be creative. Let tools such as Power Query and formulas do the heavy lifting and allow charts to focus on effective presentation. ▪ This chapter will cover a variety of advanced charting tricks. These include automatically changing the color of key metrics, dynamically sorting chart data, adding charts that shift with time, and building creative chart labels. 1. Dynamic Charts (1)-3 File dynamic-charts.xlsx ▪ We want to remove as many manual tasks as we can from our day- to-day Excel work. We want to save time and improve accuracy. ▪ There was a focus in Chapter 2 - The Ten Power Functions of Excel with keeping everything dynamic by basing them on tables or dynamic array formulas. It will be more of the same with our Excel charts. ▪ We want our charts to automatically adjust if data is added or removed from its source. This includes expanding to new data, sorting values, and rolling with time. 1. Dynamic Charts (1)-4 Dynamic Chart Range with Tables ▪ The easiest way to ensure that your charts automatically update when new data is added is to use data formatted as a table for its source. ▪ When new rows or columns are added to a table, it automatically expands. If our charts use a table for their source, they will automatically update too. ▪ In this example, we have a table named Sales_Summary with the sales of products. ▪ We will create a clustered column chart. 1. Dynamic Charts (1)-5 1. Select the table data. 2. Click the Insert tab and select the 2D Clustered Column Chart. ▪ That is it! Nothing more to do (Figure 4-1). ▪ If more data is added to the Sales_Summary table, the column chart will automatically add the product to the axis and plot the data. 1. Dynamic Charts (1)-6 1. Dynamic Charts (1)-7 ▪ If you ▪ select the chart and click Chart Design ➤ Select Data ▪ to view the data source for the chart, you may be surprised to see it references the range ‘From Table’!$F$3:$G$7 (Figure 4-2). You may have expected to see the table name Sales_Summary like you would in a PivotTable data source or a formula. 1. Dynamic Charts (1)-8 1. Dynamic Charts (1)-9 Despite the table not being mentioned, rest assured that the chart will react if the table data is changed. You can check this by resizing the table using the blue handle in its bottom-right corner (Figure 4-3). The chart will adapt to the table range. 1. Dynamic Charts (1)-10 Charts and Dynamic Array Formulas ▪ Another way to create dynamic chart ranges is to use dynamic array formulas and their resulting spill range. ▪ You cannot create a chart directly from the spill range as we did with the table. Doing this would create a nice chart, but because it does not understand the spill range, it would not update when the formulas’ spill range changes. ▪ You also cannot write dynamic array formulas or use spill range references directly in the chart’s source. 1. Dynamic Charts (1)-11 ▪ The way around this is to reference the spill range in a defined name and then use the defined name as the source for the chart elements. ▪ In this example, we have the same product sales data, but this time they are the result of formulas (Figure 4-4). ▪ There are separate spill ranges in cell F3 for the distinct list of products and cell G3 for the total sales of each of those products. 1. Dynamic Charts (1)-12 1. Dynamic Charts (1)-13 ▪ Let us begin by creating a defined name for each spill range Click cell F3 (the location of the spill range). 1. Dynamic Charts (1)-14 Click Formulas ➤ Define Name. 1. Dynamic Charts (1)-15 Enter a meaningful name. Product_Labels has been used here. 1. Dynamic Charts (1)-16 Type a hash (#) at the end of the F3 reference to refer to the spill range (Figure 4-5). Click OK. 1. Dynamic Charts (1)-17 1. Dynamic Charts (1)-18 Repeat the steps for the G3 spill range. Name it Total_Values. 1. Dynamic Charts (1)-19 ▪ We will now create the column chart from the range. Then edit the source of the data series and the category axis labels to use the defined names. Select range F3:G7 and click Insert ➤ Clustered Column Chart. 1. Dynamic Charts (1)-20 Click Chart Design ➤ Select Data to open the Select Data Source window (Figure 4-6). 1. Dynamic Charts (1)-21 ▪ There are buttons to edit the source of the data series and the category axis labels above each box. 1. Dynamic Charts (1)-22 Click Edit for the Legend Entries (Series). 1. Dynamic Charts (1)-23 Replace the range with the defined name Total_Values (Figure 4-7). Do not delete the sheet name. It is important to keep the sheet name before the defined name. 1. Dynamic Charts (1)-24 Click the Edit button for the Horizontal (Category) Axis Labels. 1. Dynamic Charts (1)-25 Replace the range with the defined name Product_Labels. The chart will now automatically update when new data is added to the Product_Sales table and the dynamic array formulas update. 1. Dynamic Charts (1) 2. Dynamic Charts (2) 2. Dynamic Charts (2)- 1 Automatically Sort Chart Values – With Excel 365 It is important that the values in your charts are sorted correctly. And if you have charts hooked up to a dynamic range, the sorting will also need to be dynamic. You may want to sort the category axis in A–Z order or the values in largest to smallest order. With the SORT and SORTBY functions in Excel 365, this task is simple. The SORT and SORTBY functions were covered in Chapter 2. And one of the examples was to sort the product sales we have been working with in this chapter in descending order by total sales value. 2. Dynamic Charts (2)-2 ▪ To recap, this is how it was done. ▪ Beginning with the total sales in cell G3, this formula sums the sales of each distinct product in the table Product_Sales2 and sorts the values in descending order: =SORT( SUMIFS(Product_Sales2[Total], Product_Sales2[Product],UNIQUE(Product_Sales2[Product])) ,,-1) 2. Dynamic Charts (2)-3 Then in cell F3, this formula uses SORTBY to sort the distinct products array using the SUMIFS function for the by array argument: =SORTBY(UNIQUE(Product_Sales2[Product]), SUMIFS(Product_Sales2[Total], Product_Sales2[Product],UNIQUE(Product_Sales2[Product])) ,-1) With the formulas written, we then repeat the steps from the previous example. Define names for the two spill ranges, insert the chart, and use the defined names for the data series and category axis (Figure 4-8). 2. Dynamic Charts (2)-4 2. Dynamic Charts (2)-5 Automatically Sort Chart Values – Without Excel 365 ▪ The SORT and SORTBY functions may only be available to Excel 365 users, but there are still ways that we can automatically sort values using formulas. ▪ We will continue with the same example we have been working with in this chapter, the total sales of products. We have a table named Chart_Prep, and it uses the SUMIFS function to total the sales of each product. ▪ We will use this table to rank the total sales values. Then order them in another table, which will feed the chart. It follows good practice when creating reports to have separate tables or ranges for the data, the calculations, and then the report 2. Dynamic Charts (2)-6 Note You can also group, sum, and sort the values for a chart using Power Query or a PivotTable. This chapter is focusing on formulas to prepare your chart source data. The benefits of using formulas include their flexibility and that they automatically update. To rank the total sales values, the following formula was added to the Chart_Prep table (Figure 4-9): =RANK.EQ([@Total],[Total],0)+COUNTIFS($G$3:G3,[@Total])-1 2. Dynamic Charts (2)-7 ▪ This formula uses the RANK.EQ function to rank each total value in the Total column. The 0 specifies descending order, so the largest value is the first and so on. 2. Dynamic Charts (2)-8 ▪ The COUNTIFS function is used to separate tied values and ensure a unique rank for each total value. It does this by counting the occurrences of each value, the minus is performed, and the result is added to the rank. ▪ For example, if a value has occurred twice, the –1 is performed, and then the 1 is added to the rank. ▪ In this example, the values are unique, but in Figure 4-10 the coffee sales are equal to hot dogs. The COUNTIFS function ensures that a unique rank number is applied. 2. Dynamic Charts (2)-9 ▪ We then need to order the product names and totals by their rank. We can do that with lookup formulas. Let us use the INDEX and MATCH combination (Figure 4-11). 2. Dynamic Charts (2)-10 ▪ This formula has been entered into cell K3 to return the sales value. The same formula has also been used for the product name in cell J3 but with the INDEX array as Chart_Prep[Products]. =INDEX(Chart_Prep[Total],MATCH(ROW(I1),Chart_Prep[Rank],0)) ▪ It uses the ROW function to return the row number from a cell in row 1. Cell I1 has been used for this example. This returns rank number 1, but when the formula is filled down, it returns 2, 3, 4, and so on. ▪ MATCH finds the position of the rank number, and INDEX returns the product name and total. 2. Dynamic Charts (2)-11 2. Dynamic Charts (2)-12 ▪ The column chart (or any other chart) can then be created from this ordered table (Figure 4-12). Learning Summary 1. Dynamic Charts (1) -We know the easiest way to ensure that your charts automatically update when new data is added is to use data formatted as a table for its source. -We learned another way to create dynamic chart ranges is to use dynamic array formulas and their resulting spill range. 2. Dynamic Charts (2) -We learned how you have charts hooked up to a dynamic range, with the SORT and SORTBY functions in Excel 365. -We know there are still ways that we can automatically sort values using formulas without Excel 365. Next time, Let’s learn about Week 9. Chapter 4 (1) Lesson2.. Advanced Chart Tricks(2) THANK YOU ! References 출처 © Alan Murray 2021 A. Murray, Advanced Excel Success, figure 4-1~12