Excel 2019 includes three new types of charts you can use to summarize your data. These charts include funnel charts, for demonstrating how many items move through a process, waterfall charts that track cash flows, and map charts to summarize geographical
- [Narrator] Excel 2019 includes three new types of charts that you can use to summarize your data. Waterfall charts for visualizing cash flows, funnel charts for showing the results of a sales process, and mapping for summarizing geographic data. In this movie, I will show you how to create all three. My sample file is the "New charts workbook", and you can find it in the chapter seven folder of the exercise files collection. This workbook has three worksheets and I am starting on the waterfall worksheet.
And you can see here that I have a cash flow, I have the starting balance, then cost for materials and labor, then sales revenue related to this project, and then overhead, and finally a total. To create a waterfall chart, I will click any cell within this table. Excel will know to use the entire data range. Click "Insert", and then in the charts section, I will click the waterfall icon, and then click the waterfall chart type.
When I do, you can see that Excel creates a waterfall chart, except that I need to make some changes to it. I'll start by editing the chart title. So I'll just select that and then just type "Cash Flow," so it doesn't say "Chart Title". And then I will drag to resize the chart so that all of the labels are on the same level down here. Didn't need to do that. I just think it looks neater. And now I can look at the chart. I see that I have an increase of $450,000 to start, and then decreases of $102,000, $75,000 and $140,000.
And you can see the increases are shown in blue and decreases are shown in orange. But I also have totals, and that is where the first value of $450,000 and the final value of $218,000 come into play. Because, in fact, $218,000 is definitely not an increase, it is the amount that I am left with for my total, as shown in cell B7. So what I need to do is to click this first column once. That highlights the entire data series.
Then I need to click it again to select it. Now you can see that only the first column is selected. Now I'll move up so I am away from the tool tip that appears at the bottom and control click this column. The next to last item is "Set as Total", so I will do that. And you see that it turned gray. If I click away, you see that I now have a total of $450,000. And I can do the same thing for the last item. So I'll click that once to select the entire series, click it again to select only it, control click, set as total.
It's repositioned and I'm done. And you can see that I start with $450,000, and end with $218,000 with the increases and the decreases in the middle showing how we got there. So this was an excellent storytelling tool. Another type of chart you can create is a funnel chart. So I'll click the "Funnel" worksheet tab, and let's say that I'm tracking sales and I have a number of leads, number of responses, and number of sales that came out of those leads and responses.
To create a funnel chart, click any cell in the data range you want to summarize. Then go to the "Insert" tab, and you might remember from last time that the funnel chart is also under the waterfall button so I'll click that, click funnel, and I get the funnel chart. We start with the leads, then the responses, and then the sales. And I won't bother changing the chart title so I already did that once. Finally, you have mapping, so I will click the "Mapping" worksheet tab, and here, I have revenue for six states: California, Washington, Oregon, Idaho, Wyoming, and Colorado.
To map this data, I use Bing maps. So I will click any cell within the table that contains my data. Then again on the "Insert" tab, which I already have displayed, I will click the maps button, and I will click the globe, the filled map icon. And over time, I will see the start of the map and then, let me see, "data needed to create your map chart will be sent to Bing." Very well, I accept, or I won't be able to create the chart, and there I have it.
Drag a little bit over to the side, and you can see that I have values for Washington, Oregon, California, Idaho, Wyoming, and Colorado. As you can see, these new chart types allow you to summarize your data in many interesting ways.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks