When done, close browser tab or window.
Start video.
Narrator: "One of the great features within Microsoft Excel is the visualization feature - being able to create visualizations and charts to actually display your analysis and make decisions quickly. So, charts are a way to easily visualize your data sets in order to present findings or gain further insights. Meantime, just looking at an image, we can actually make a decision a lot faster than looking at the data itself. What I'm going to go through now are a few examples of how we can create visualization charts."
The video opens to display a Microsoft Excel spreadsheet containing information on the types of charts that can be created using Microsoft Excel, the importance of creating a chart, and the steps to create a chart using an example. The Excel sheet displays the tabs for File, Home, Insert, Page Layout, Formulas, Data, Review, View, Help, and Search options on the top row of the screen. Below that, in the second row, are tabs to insert Pivot tables, Pictures, Shapes, Icons, 3-D Models, People Graph, Recommended Charts, Charts, Maps, Comments, WordArt, Equations, Symbols, et cetera. The Excel file has three spreadsheets named Charts, Example, and Example 2.
The visual displays a spreadsheet named Charts. The following textual information is displayed within the spreadsheet: Charts: Charts are a way to easily visualize your data sets in order to present findings or to gain further insights. There are a wide variety of chart types that you can utilize in Microsoft Excel. The most common types used are bar or column charts to show comparisons of values and line charts to show trends. Below is an example of how to set up a chart:
| Day 1 | Day 2 | Day 3 | |
|---|---|---|---|
| Cost A | 55 | 65 | 50 |
| Cost B | 88 | 80 | 85 |
| Cost C | 60 | 70 | 65 |
Narrator: "Here we have a very simple "Month" and "Selling Price" data set, going through the 12 months of the year. Whenever you want to create a visualization, there are a few different ways to do this. The best way to start is to highlight the data that you're looking at, and then going up here to the charts to find out what kind of chart you want to do. So, you see that there is a button that says Recommended Charts. If you click on that, it's going to jump up and have an Insert Chart window for you. Here you can see, it recommends for you a clustered-column chart, a line chart, a stacked-area chart, a pie chart, a funnel chart, and then a combo chart, or a Pareto chart, that shows both columns and a line chart. That being said, you don't have to do any of these recommended charts. You can always click on a button that says All Charts. Then, here you have all the different types of charts that you would want to possibly visualize. And then you can modify the types within there. So, for example, within the column, you can go to Clustered Column or modify the way that clustered column looks. You can change it to a stacked column, and then a lot of different other options for you. Line charts, you could smooth lines or you could do lines with the actual markers on them themselves. Or go down to a scatter plot, and then just plot out the points or add lines, as well. So, anytime you choose one of these, you can click on it, then click OK."
The narrator now clicks the second spreadsheet named Example. The following table is displayed within the spreadsheet:
| Month | Selling Price |
|---|---|
| January | $ 41,178,364 |
| February | $ 17,651,444 |
| March | $ 20,099,321 |
| April | $ 1,245,889 |
| May | $ 38,252,793 |
| June | $ 840,532 |
| October | $ 31,010,733 |
| November | $ 39,013,975 |
| December | $ 26,411,168 |
The narrator selects the columns and rows of the table containing the data and then hovers the cursor over the tab Charts present in the second row on the top of the spreadsheet. The narrator then hovers the cursor over the tab Recommended Charts. This displays a window on the spreadsheet below the table showing the options for Formatting, Charts, Totals, Tables, and Sparklines and the text Conditional Formatting uses rules to highlight interesting data. When the narrator clicks on the tab Recommended Charts, the first window disappears and a second window named Insert Chart is displayed. The window has tabs for Recommended Charts and All Charts at the top left corner, and OK and Cancel buttons at the bottom right corner. This window displays Recommended Charts showing options for the types of charts that can be used to represent the data in the given table on the left. The Recommended Charts include the clustered-column chart, the line chart, the stacked-area chart, the pie chart, the funnel chart, and the Pareto chart. The large section on the right displays a sample of the selected chart with the table data.
The narrator then clicks on the All Charts tab which displays a list of 17 chart formats including Column, Line, Pie, and Bar available to represent the table data, on the left side section of the window. The narrator selects the Column option, which displays the sample chart of a clustered column on the right-side section. The narrator browses through the different forms of column charts listed. The narrator then selects the Line option to view the different forms of line charts listed such as smooth lines and lines with markers. This is followed by selecting the X-Y Scatter plot to show sample charts with scatter points or scatter points with lines. The narrator then selects the pie chart and clicks the OK button to display the pie chart for the given data.
Narrator: "Another option is to highlight your data - and again, you're always going to the Insert ribbon - and you can go over to charts. If you don't click on Recommend Charts like we just did, you can see there are a number of charts that are already available for you over here. So, for example, if we want to create a very simple column chart, we click on the Column Chart drop-down, then choose a 2-D column. So, now I look at this and I can quickly see that January has my highest sales, with June being my lowest. And this is the power of visualization. Once you're in here, you can do a number of things. You can change the title of your visualization. You can use Add Chart Element to change things like the axis titles. So, for example, if I want to put down here, on primary horizontal, I can say these are my months. Or I can modify anything else. So, for example, down here, I could add a trend line that shows what my trends, January through December, are. Okay?"
The narrator deletes the pie chart that was inserted earlier and then selects the table data again to insert a column chart. To insert the column chart, the narrator selects the Insert tab and clicks on the 2-D column of the Charts tab. Once the column chart is inserted, the narrator hovers the cursor over the bars for the month of January and June as the narrator refers to their sales. The narrator further demonstrates the steps to change the title of the chart by clicking on the existing title, which displays a text box around the title. The cursor is then hovered over the tab Add Chart Element present in the second row over the top of the spreadsheet, to display a drop-down list containing tabs for Axes, Axis Titles, Chart Title, Data Labels, Data Table, et cetera. The narrator clicks on the Axis Titles to display another drop-down list containing Primary Horizontal, Primary Vertical, and More Axis Title Options. Selecting the Primary Horizontal option inserts a text box below the x-axis in the chart. The narrator labels the x-axis as Months by typing the text within the text box that was displayed. Similarly, a trendline is inserted over the column chart by selecting the Trendline option under the Add Chart Element tab.
Narrator: "To show you another example, here's a clustered-column chart that I've already created. Let me show you how I did that. So, again, when you have this data, you want to highlight the totality of the data. Go to Insert. Then, again, you need to click on Recommended Charts and choose from their options. Or, however, there are some charts that you might want to use. So, here, very simple, if I choose Clustered Column, it will give me a lot of ability to come in here and just quickly look at these different expenses. So, here, for example, I have actual results, flexible budget, planning budget. So, for wages, my actual results for this blue column, my flexible budget for wages were the orange column, and my planning budget for wages was the gray one. Okay? Once again, here again, you can modify some things. So, you can do some quick chart styles by clicking anything here. You can change the colors to whatever you want. Change the quick layout to show how maybe you want to show the axes a little bit different. Or then, if you didn't get the data you wanted, you can always go to the Select Data option, and come in here and change any of your data sets by adding or removing these. Okay? Also, you have the option to switch rows and columns. So, here, for example - let me change my color set back to the default - we have our wages, supplies, rent, advertising, and actual expenses down here along the bottom, and then you see our legend shows actual, flexible, and planning. If I switch rows and columns, it literally does that. So, now I have my five tested expenses here, and then my actual, flexible, and planning budgets down here. So it changes that ability. Maybe I want to go in here and modify the chart type. Here, I can just click on Change Chart Type, and it will pop up here. So, maybe I want to make this look more like a line chart. So, now, if you see my different expenses, overtime is based on the different types of expenses that are out there. Okay?"
The narrator now clicks the third spreadsheet named Example 2. The following table is displayed within the spreadsheet:
| Actual Results | Flexible Budget | Planning Budget | |
|---|---|---|---|
| Wages | $ 159,200 | $153,600 | $ 144,000 |
| Supplies | $ 39,300 | $ 34,400 | $ 43,000 |
| Rent | $ 49,600 | $ 48,000 | $ 45,000 |
| Advertising | $ 10,950 | $ 11,200 | $ 10,500 |
| Admin Expenses | $ 41,650 | $ 42,192 | $ 43,200 |
A clustered column chart titled Expenses Actual versus Budget is displayed beside the table. The horizontal axis shows Wages, Supplies, Rent, Adverting, and Admin Expenses. The vertical axis ranges from $0 to $180,000 in increments of $20,000. Actual Results are represented in blue columns, Flexible Budget in orange columns, and Planning Budget in grey columns. The narrator deletes the chart and demonstrates the steps to insert the above-mentioned chart. From the Insert tab, the chart can be inserted using the Recommended Charts tab, followed by selecting the 2-D column tab. The narrator then demonstrates using various formats within the clustered column chart, for example, providing a background color, changing the colors of the columns, displaying the values of each column, et cetera. The narrator then clicks on the Select Data tab to display a window named Select Data Source displaying the Legend Entries in one column and the Horizontal Axis Labels in another column. This window can be used to add or remove Legend Entries. Switching between rows and columns is demonstrated using the tab Switch Row slash Column, where the Actual Results, Flexible Budget, and Planning Budget are displayed on the x-axis and currency values on the y-axis. Wages, Supplies, Rent, Adverting, and Admin Expenses are now represented as columns of different colors. The narrator reverses the chart to the earlier format and then to a line chart by clicking on the Change Chart Type tab.
Narrator: "Anytime that I want to do something called a combo chart, here, again, I'm going to go into this chart title, change chart type, then go down here and go to Combo. This is the way you can do things such as a clustered column and a line chart. So, for example, maybe I want to have my planning and flexible budget be columns, but then I want my actual results to be aligned. That way, I can see them overlay. Okay? You can see here, there's an option for a secondary axis. That means, if we want to, we can add an axis on the right-hand side that gives it its own ability to look at things. So, here, for example, we see that we have the right axis here. It's possible that our secondary axis could be in dollars, while, for example, our primary axis is in units. Okay? So, here, we don't really need the secondary axis, but for illustrative purposes, you can see orange and gray are different budgets, while blue signifies the line for our actual results. So, the opportunities are relatively endless when it comes to visualizations within Excel. It's really about playing with the data and finding out what best represents the information you're trying to present. So, when going forward and using visualizations for your data, think about, "Am I trying to compare trends over time, and use a line chart; compare actual results using bar charts; or trying to show the proportions of a whole, using a pie chart?" Again, it's all depending upon what you're trying to show your audience."
The narrator now demonstrates a combo chart by clicking the Change Chart Type tab, followed by selecting the Combo option which displays a clustered column chart along with a line chart. In the table mentioned above, the Actual Results and Flexible Budget are represented as columns, while the Planning Budget is represented as a line. The chart type for Actual Results, Flexible Budget, and Planning Budget are selected in the window that opens when the Change Chart Type tab is clicked. The line and clustered column options can be interchanged for these three entries in the selection area provided in the window. Selecting the secondary axis option displays the y-axis readings on the right side of the chart as well. The final chart that is displayed has Flexible Budget represented as orange columns, Planning Budget as grey columns, and Actual Results as a line over the columns.
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.