Excel 365: Working With Charts & Sparklines
This is a guide on working with charts and sparklines.
Inserting and Working With Charts
In this course, we are going to change tracks a little bit. Our emphasis now is going to be on charting and on creating visualizations of data. We are going to start with a really simple visualizations such as line charts, bar charts and pie charts.
[Video description begins] A Microsoft Excel Workbook displays. The menu bar contains the following tabs, including: File, Home, Insert, Page Layout, Formats and Data. The ribbon beneath the menu bar display various options, including: Paste, text formatting, font style formatting, Conditional Formatting and Find & Select. The content pane displays rows and columns. The columns are labelled from A to Z and the rows are labelled with numbers, such as, 1 , 2 , 3, 4 and so on. A General Motors title displays at row1. The columns A2, B2 and C2 are labelled as: Month, Sales-2019 and Sales-2018 respectively. The rows below displays the related data for 12 months. [Video description ends]
But even here, Excel is so powerful and it allows us to customize so many aspects of our charts, that there's going to be quite a bit to take in. We are going to be working with some really simple data, which refers to sales of General Motors in the US and Canada. These are unit car sales taken from the URL on screen now, this is a GM blog.
[Video description begins] The url reads: https://gmauthority.com/blog/
Let's go ahead and get started by selecting all of the data that includes the header row. Then we click on the Insert tab up top, and there we choose from any one of the many charts and chart types on offer there.
[Video description begins] Various new options appear, namely: Pivot Table, Table, Recommended Charts, Maps and PivotChart. [Video description ends]
Let's get started with a few simple bar charts.
[Video description begins] He selects the bar chart icon. The list of various bar charts appears, underneath the following headings, namely: 2-D Column, 3-D Column, 2-D Bar and 3-D Bar. [Video description ends]
We click on the bar chart button, and we can see that there are several variants available. These include column and bar charts and 2-D and 3-D charts. As we change our mouse positioning over these different types, the visualization type, which can be seen in the screen down below, changes as well. Let's go with the simplest, that's a 2-D column chart. Notice how the two columns are represented as the two data series.
[Video description begins] The graph titled: Chart Title displays on the right hand side of the Microsoft Excel Workbook. [Video description ends]
We can edit the chart title up top, we change it to something descriptive like General Motors Sales. Clicking on the chart itself, causes the corresponding values from our workbook to be highlighted. Notice how there is highlighting of different colors for the names of the various data points on the x-axis. These correspond to the months as well as to the two sets of columns of data. Let's click on the Add Chart Element button. We can see that many different ways in which we can customize the appearance of our chart.
[Video description begins] The Chart Design tab is currently selected in the menu bar. It displays various options, including: Add Chart Element, Quick Layout, Change Colors and Select Data. He selects the Add Chart Element option. The list of options appears, including: Axes, Axis Titles, Chart Title, Delete Labels, Delete Table and Error Bars. [Video description ends]
These include the Axis, Axis Titles, and many other aspects, all of which we are going to explore. Let's start simple by customizing the axis titles.
[Video description begins] He selects the option Axis Titles. Three options appear, namely: Primary Horizontal, Primary Vertical and More Axis Title Options. In the similar manner he expands the other options listed under the option Add Chart Element . [Video description ends]
The two choices are Primary Horizontal and Primary Vertical. Let's go ahead and click on the first, that's the Primary Horizontal. Clicking on that causes a small text box to appear at the bottom of our chart near the x-axis. Notice that it contains the words Axis Title. We can then go ahead and edit those words, and change it to read Month. We perform a similar operation for the y-axis once again by clicking Add Chart Element. This time we choose Primary Vertical, a text box appears next to the y-axis and we edit that as well.
Next, let's go ahead and change the font size. We do that by clicking on the text box and a font toolbar appears. And in similar fashion, we increase the font size of the x-axis title to 16 as well. Let's go back to the Add Chart element dialog there. Let's go ahead and click on the Chart Title. The options are None, Above Chart, and Centered Overlay. Let's go with Above Chart.
But along the way do notice how hovering over the different options causes the graph to be updated down below. Next let's move to the data labels and here once again there are several options. None, Center, Inside End, Inside Base, Outside End, and Data Callout. The Data Callout has a nice elegant appearance, but actually None is probably the best.
Adding data labels often obscures the data contained within the graph. Let's return to the Add Chart Element dialog and there we click on Data Table. This allows us to add the actual data represented in the graph in the form of a table down below. This can be done either with or without legend keys. The legend keys refer of course to the small rectangles, which tell what color corresponds to what data series.
This can be a particularly handy feature when you plan to use your graph inside a presentation. There, you want your graph to be self contained. You want all of the data juxtaposed right there with the visualization. In any case, let's keep moving on. The next item on the agenda has to do with Error Bars. Now, we are not going to get into the statistics of these error bars right now, but you should be aware that there are many different options.
None is the default, but you can also have error bars corresponding to the standard errors, percentages, as well as standard deviation. In any case we need not go into a lot of depth around how these are computed. These basically are measured in the form of deviation from a trend line. The next option has to has to do with Gridlines, these are fairly easy to understand. We have primary major horizontal, and vertical, and primary minor horizontal and vertical.
Notice once again, how toggling through the different options causes the UI to be updated accordingly. This is a handy way for us to preview different options. Next is the legend. You can see that the default has the legend over on the right, that refers to the two little colored rectangles and the words Sales, 2019 and 2018 respectively. This has to do with the placement of the legend.
We could either have no legend at all, or place it at the right, the top, the left, or the bottom. The next chart element we're going to explore is an important one. This has to do with a Trendline fitted to our data.
You can see that there are several possible types of Trendlines that Excel can automatically add to our visualizations. The available options are None, Linear, Exponential, Linear Forecast, and Moving Average. Each of these can be really useful depending on your specific use case, so you should be aware that this is how trendlines are added.
[Video description begins] He selects the Trendline option. Few new options appear, including: None, Linear, Exponential and Linear Forecast. He selects the option Linear. A dialog box titled: Add Trendline appears. It contains a field titled: Add a Trendline based on Series This field contains two options, namely: Sales-2019, Sales-2018. The OK and Cancel buttons display at the bottom. He selects the option Sales-2019 and selects the OK button. He again selects the Linear option and selects the Sales-2018 option from the field. He then selects the OK button. [Video description ends]
Notice how individual trendlines have got to be added for each series in your graph. We can see that there are two thin dotted lines, one in blue and one in orange, corresponding to the two data series in our chart. This ability to add trendlines of different varieties to your visualizations is an important one. And you would do well to keep it in mind while preparing presentations or spreadsheets, especially if you're going to be talking to an executive level audience. For instance, if you'd like to make the point that sales were increasing linearly but are now increasing exponentially after maybe some change in the company's policies. These trendlines are a great way of driving home your point.
Customizing Chart Appearance
In the previous demo, we spent a lot of time familiarizing ourselves with the Add Chart Element options.
[Video description begins] The Microsoft Excel Workbook displays. The host continues with the file from the previous video. [Video description ends]
In this demo, we will focus on the button just to the right, which is the Quick Layout set of options. The Quick Layout is an easy way to toggle through different formats or layouts of our graph.
[Video description begins] He selects the Quick Layout button. Various layout options appear underneath. [Video description ends]
A couple of points worth noting here. The first is that as we hover over each of the different layouts, the visual representation of our graph changes as well. The other bit that's worth noting is the information contained in the tooltips. For instance, for Layout 2, you can see the tooltip. It tells us that this layout includes the Chart Title, the Legend, which is going to be up top.
The Data Labels, which are at the outside end, and the Horizontal Axis. We can see from this tooltip that what's not mentioned includes the Vertical Axis. And what is mentioned is the presence of the Data Labels. This is a great way for us to understand the different possible layouts. You can cycle through and you can see that there are many different variations.
Let's just pick one of these which seems visually appealing. [Video description begins] He selects Layout 5. [Video description ends] And then let's turn our attention to the next button, Change Colors. Here, each row represents one possible color scheme. Up top are a set of color schemes labeled Colorful. Down below are another set labeled Monochromatic, which consists of different shades of the same color. Next in the Chart app are a large number of Chart Styles.
You can see that there is a large variety. Let's just go ahead and pick any one of these. You can see that this particular chart style includes the data in tabular format down below. You can use these charts styles to mix and match with different aesthetics if you are using Microsoft PowerPoint for your presentations. In any case, let's keep moving on.
Or further to the right is a button titled switch rows and columns. By switching this, we can see that the x and the y-axis are interchanged. Interchanging the rows and columns is not something that makes a whole lot of sense while working with bar charts. However, it is a common operation while working with line charts, scatter plots, and regression lines. In such situations, the usual practice is to have the explanatory, or the x variable, on the x-axis and the explained variable, or the predictor variable, on the y-axis.
This is a quick and easy way to view different relationships. Does y cause x, or does x cause y? This is easier to model with the help of this switching of rows and columns. The next button further to the right is titled Select Data. And we can use this if you would like to model only a subset of the data in our chart. You can see by default that all of the data in our range is included in the chart.
[Video description begins] He clicks on the Select Data button. A dialog box titled: Select Data Source displays. It contains various fields, including: Chart data range, Legend Entries (Series) and Horizontal Category Axis Labels. A button labelled as: Switch Row/Column displays underneath the Chart data range field. A button labelled as Hidden and Empty Cells display beneath the fields. The OK and Cancel buttons display at the bottom. [Video description ends]
Let's go ahead and change that. We can click on the little button over on the extreme right.
[Video description begins] The button is present on the right of the Chart data range field. [Video description ends]
The dialog then changes appearance. This allows us to select a subset of cells. So let's go ahead and only select data for January through June. Once we are done, we click on the same button over on the extreme right. And that returns us to the original Select Data Source dialog.
You can also see in this dialog that we can switch rows and columns right here. There are also the legends for the entries as well as the Horizontal (Category) Axis Labels. In any case, let's hit OK. And when we do this, we can see that our chart now only represents a subset of the original data.
We can confirm this by clicking on the chart. We can see from the highlighting that the data that's included now extends only down to row number 8. Previously, it extended all the way down to row number 14. Let's revert the changes that we just made by clicking once again on the Select Data button and then making sure that we select all of the data. That's the range A2 through C14.
Notice once again the dollar signs. These represent absolute cell references, and we'll have a lot more to say on this topic. Also note, as usual, that when we specify a range, we've got to specify the top left and the bottom right cells, which define the bounding rectangle of that range. When we make this change, we can see that the appearance of our graph changes again.
Each of the individual bars becomes a lot slimmer. And that's because many more data points have to be fit into the same chart rectangle. Moving on, let's see how we can modify our graphs to deal with missing data. Let's take one cell at random. So this is cell B8. Let's a right-click on the cell and choose the clear cell contents option.
[Video description begins] A context menu appears. It contains various options, including: Cut, Copy, Insert, Delete, Clear Contents and Filter. [Video description ends]
In case you're wondering what the difference is between Clear Contents and Delete, which appears right above clear contents. The answer is that Clear Contents is going to get rid not only of the cell's value but also of any associated metadata. Which might include formatting, comments, hyperlinks, and so on. In any case, once we clear the cell, we can see that there is a gap that has opened up in our graph. And that, of course, is because we no longer have data for June in 2019.
How is this gap to be dealt with? To consider the options, click on the Select Data button. This is up top, towards the right. This is a familiar dialog, but now we are interested in the little button called Hidden and Empty Cells over on the bottom left. When we click on this, we can see that empty cells could be represented by Gaps, by Zero, or by connecting data points with the line.
[Video description begins] A dialog box titled: Hidden and Empty Cell Settings displays. It contains a field titled: Show empty cells as. This field contains three radio buttons, labelled as: Gaps, Zero and Connect data points with line. Two check boxes display beneath the radio buttons labelled as: Show #N/A as empty cell and Show data in hidden rows and columns. The OK and Cancel buttons display at the bottom. [Video description ends]
The third option is currently grayed out. It's not available with a bar chart. It would have been available had this been a line chart, for instance. Down below there are a couple of checkboxes. The first of these asks whether we'd like to use the special value #N/A as an empty cell. And the second asks whether we'd like to Show data in hidden rows and columns.
The first is checked by default, the second is unchecked. These options might seem a little obscure to you right now, but rest assured that they are all extremely useful. Absolutely nothing gets added to Microsoft Excel's UI unless there is a very specific and clear use case for it. Let's turn our attention back to the radio buttons at the top of this little box.
Let's change the way we represent empty cells instead of gaps. Let's represent them as zeros. If we hit OK, it might seem like nothing has changed in our graph at all. But in reality, there is a significant difference. To clarify this difference, let's change the chart type from a bar chart to a line chart. This is easy enough to do. Click on the Insert tab up top.
And that in turn brings up a new set of menu items, including all of the different chart types. So now we click on that little button which has two lines. And that brings up the different types of line charts.
[Video description begins] Various line charts listed are, namely: 2-D Line, 3-D Line, 2-D Area and 3-D Area. [Video description ends]
As soon as we make this change, we can see the significance of the change in how we handle the missing value. There's now a very weird and abrupt drop in one of our lines. This was not visible when we were working with bar charts. But switching to a continuous type, that's the line chart, made it all too obvious. Well, we now would like to revert all of the changes that we just made. So we switch back to the to the 2-D Column type. But then we'll do something a little more interesting. Let's click on the Quick Access Toolbar, or at the very top.
There, there's a little button which helps us to undo our actions. If we click on the little arrow next to that button, we get a list of all of our recent actions. Here, we would like to undo multiple steps. So let's go ahead and select the rows corresponding to all of those steps. So we select the rows Change Chart Type, Change Chart Type once again, Edit Data Source, and Clear.
And once we have all of these selected, we just go ahead and click on them. All of the changes we just made have been undone. And we are back to a full set of data points and a nice 2-D bar chart with no missing values. We're almost done with our exploration of the Chart Design tab. But let's also quickly explore the Change Chart Type button. When we click on it, we can see that there is a long list of all of the different chart types available in Excel.
[Video description begins] A dialog box titled: Change Chart Type displays. The left pane contains various options, including: Recent, Templates, Column, Line, Bar and Pie. Currently, the Column option is selected. The right pane displays related details. The OK and Cancel button displays at the bottom. [Video description ends]
Here we can also see the precise type of our current chart. It's a Clustered Column chart. Let's switch back out and change our tab so that we go from Chart Design to the Insert tab. There we will find other ways of changing the chart type. We had explored this a moment ago, but we can also quickly toggle through some different options.
[Video description begins] The bar chart contains the following options, namely: 2-D Column, 3-D Column, 2-D Bar and 3-D Bar. [Video description ends]
You can see the difference in the aesthetics between column and bar charts and 2-D and 3-D charts. For time series comparisons, such as this one, you might also want to consider different types of line charts. This is a typical use case for line charts. We have time on the x-axis, and we have a pair of continuous variables on the y-axis. Other variants which show up here include 3-D Line charts, 2-D Area charts, and 3-D Area charts.
By this point, we've got a thorough understanding of the functionality available in the Chart Design tab as well as of the different types of line and bar charts. In the demo coming up ahead, we will turn our attention to pie charts, treemaps, and sunburst charts. These are somewhat more specialized types of visualizations which we should also have in our Excel armory.
Creating Treemaps and Pie and Sunburst Charts
Let's turn our attention to another chart type. This is the pie chart type. Pie charts are most useful when used to compare compositions or parts of a whole. So for that, let's go ahead and set up a small table containing Total Sales. This is going to include the data for all of 2018 and all of 2019. And then we'll go ahead and use a pie chart to represent the combined sales across two years, and the individual sales in these two years as parts of that whole.
This will also bring our first brush with an Excel formula. In cell B19, we use a formula, note the use of the =, followed by the keyword SUM. We then open parantheses and then Excel allows us to specify all of the numbers which we wish to sum up. We do this using our mouse, we select all of the sales for calendar year 2019.
Notice how this is represented as a formula in cell B2, the formula shows as sum of B3 to B14. Notice that this formula is visible in two places in the cell itself, and then also in the formula bar just up above the data grid. As soon as we hit enter the sales for 2019 appear in cell B19. We then go ahead and repeat the process for the sales of the year 2018 using the formula, this time we sum C3 to C14. Now we have the sales for these two years in Sales, B19 and C19, respectively.
Let's select these two cells as well as the headers right above them, that's cells B18 and C18. And then let's switch over to the Insert tab that's up top and add in a pie chart. As before, there are several different types of chart variants. Let's just go with the simplest which is a 2-D Pie. Further down below you can see a couple of other variants 3-D Pie as well as a doughnut.
The simple 2-D Pie is sufficient for our purposes, you can see it there on screen now. This pie chart currently overlaps with the bar chart behind it, so let's change that. We can do this by switching over to the chart design tab up top, and then at the extreme right there's an option called Move Chart. Let's click on the Move Chart button and we will be prompted for the cell location where we want this pie chart to appear.
[Video description begins] A dialog box titled: Move Chart appears. It contains a field to Choose where you want the chart to be placed. This field contains two radio buttons, which are labelled as: New sheet and Object in. The first radio button contains a text box adjacent to it. The second radio button contains a drop down adjacent to it. The OK and Cancel buttons display at the bottom. [Video description ends]
We could choose to place this in a new sheet, or we could choose to embed it as an object in an existing sheet. Let's go with the first option, we'll have a dedicated sheet which simply contains this pie chart. We hit OK and this new sheet called PieChart is added into our Excel workbook. But there's absolutely nothing different about this chart, we can still add chart elements exactly as we did before.
So let's click on the Add Chart Element button. We can customize the Chart Title, Data Labels and Legend.
[Video description begins] Various options appear underneath, namely: Chart Title, Data Labels, Legend and Lines. [Video description ends]
Let's focus on the Data Labels for now. There are many different options available None, Center, Inside End, Outside End, Best Fit, and Data Callout.
[Video description begins] He selects the option: Data Labels. A context menu with various options appears on the right. [Video description ends]
As before, we can experiment with these and see how the chart appearance changes.
[Video description begins] He selects the option Center, a dialog titled: Format Data Label appears on the right. It contains various Label Options and Text Options. The small window having various options to change the font style appears over the left side of the chart. [Video description ends]
It's also easy enough as usual, to change the font, font title, color, and so on. Now there's one interesting and important bit, which we should pay close attention to. Notice how the contents of the formula bar include the formula series, this is visible a write about our pie chart. This tells us that this pie chart is actually the result of invoking an Excel formula.
This piece of information can come in handy later if we want to build our own macros in VBA, for instance, to construct charts and add them programmatically to our workbooks. In any case moving on, you can see that all of these chart elements have tooltips. We can hover over or select those elements to examine the tooltips. In this case, we can see that our pie chart has just the two slices corresponding to sales in 2019 and sales in 2018. We can also configure the location of the legend. This is not very different from, the customizations we perform with the line charts.
[Video description begins] He selects the Legend option. Various options appears, namely: None, Right, Top and Left. He selects the Top option. [Video description ends]
in similar fashion, we can also choose different quick layouts, and we can also edit the Chart Title. Clicking on any element of a chart brings up a properties pane over on the extreme right.
[Video description begins] He selects the Quick Layout button. Various options appear underneath. [Video description ends]
Here it reads Format Chart Title, but if we click on the data, we can see that it changes to Format Data Series.
[Video description begins] The Format Chart Title pane contains various options, including: Title Options, Text Options, Fill and Border. He selects the option Fill, various options appear underneath. [Video description ends]
Let's click on the word Fill, we can see that there are many different options there which govern how the slices of our pie are represented. These include No fill, Solid fill, Gradient Fill, and so on. Let's switch back to Automatic and turn our attention to the borders down below.
Once again, we can choose a No line, Solid line, Gradient line and Automatic. There is a further customization possible in the type of Shadow and also in various presets and transparency properties Glow, Soft Edges and 3-D Formats. Let's perform a few operations just so you get a sense of what's possible. We can specify a shadow color, we can scroll down and in the Glow option we can specify a glow color. Let's pick a bright yellow, and this causes the entire pie chart to be surrounded by a bright yellow circle.
Next, let's go to the Format Data Series options right up top and modify the orientation of our slices. You'll agree that the chart looks a lot more interesting with the angle of the first slice set to 16 degrees. Then we can use the slider for the Pie Explosion if we'd like to introduce a separation between the different slices of the pie. Next, let's switch back to our data, this is in the tab titled Excel Charts. And their let's explore a couple of other types of visualizations.
So for instance, we can click on the button next to the bar chart button, and that allows us to construct treemaps and sunburst plots. A treemap is very similar to a pie chart, it's used to represent parts of a whole. However here, those parts of the hole are represented in a rectangle rather than a circle.
[Video description begins] He selects the Treemap option. A window titled: Chart Title appears. It contains two rectangles. He selects the button Move Chart. A window titled: Move Chart appears. [Video description ends]
The area of each component in our treemap is proportional to the data that's being visualized.
[Video description begins] He enters the value: TreeMap in the text box present adjacent to the New sheet field. The drop down menu adjacent to Object in field displays the current selection as: ExcelCharts. [Video description ends]
Let's go ahead and hit OK and we can now customize our treemap just as we would a pie chart or any other type of chart. Finally let's switch back and really quickly construct a sunburst chart as well. To do this, let's switch back to ExcelCharts and let's change the chart type to sunburst. A sunburst chart is also useful in situations where we want to represent parts of a whole. Once again, we choose to place this in a new sheet called Sunburst.
[Video description begins] He enters the value: Sunburst in the text box present adjacent to the New sheet field. The drop down menu adjacent to Object in field displays the current selection as: ExcelCharts. [Video description ends]
As we've seen, Excel offers a really rich variety of visualization types. It's not really going to be possible for us to go through each one of these one by one, but it would be a good use of your time to play around with these visualization types. As you can tell, for instance, treemaps and sunburst charts are excellent additions or alternatives to pie charts. You want to consider all of these different types of visualizations in your Excel spreadsheets.
Creating Histograms
The data set that we are going to use for this demo represents attendance at the games of a major league baseball team, that's the Baltimore Orioles. The source for this data is visible in the URL on screen now.
[Video description begins] The screen displays a url, that reads: https://en.wikipedia.org/wiki/
We are going to use this data to construct histograms and box and whisker plots.
[Video description begins] Column A1 is labelled as: Year and Column B1 is labelled as: Game average. The rows underneath contains the corresponding data. [Video description ends]
These are two types of plots which were added to Excel relatively recently, only in Excel 2016. For years, in fact for decades, analysts struggled to construct histograms and box and whisker plots, but that's no longer the case. You now can create these plots right out of the box using Excel 2016 functionality.
Before we get down to the construction of these plots, let's take a moment to parse our data. You can see that the data on screen is sorted and you can see that the largest value, which occurs for the year 2000 is 40,704, that's on row 2. And the smallest value is 16,146, that's on row 21. Let's go ahead and very quickly increase the font size of this data.
We do this as usual by selecting all of the cells and changing the font size up up. So now that it's font 16, it's a little more visible. Next, let's go ahead and select all of the values in column B, and then switch over from the Home tab to the Insert tab that's up top. In the Insert tab, we have various buttons for the different types of charts available.
Here we are interested in the button which shows something which looks like a blue colored bar chart. Clicking on that button allows us to add either a histogram or a box and whisker plot. Let's go ahead and select the first histogram available and it appears in our workbook below. Now histograms are a great way of plotting frequency information.
So on the x-axis we have different ranges of values. And on the y-axis, we have the number of data points in our data which reside between those values. With this in mind, let's examine the x and the y-axes of this histogram. The first point on the x-axis is the range from 16,146 to 24,046. And how many data points are in this range? Well, the answer is 5.
We can see that because 5 is the number on the y-axis. Let's take the second point. This range corresponds to 24,046 up to 31,946. And there are 10 data points in this range. In similar fashion, we can understand the data points corresponding to the third and the fourth histogram bars as well. Now a couple of questions come to mind looking at this histogram. The first is, why are there only four bins?
Why not five or ten or three or a different number? And a second legitimate question is, why is the end interval of the last bin, that's the extreme right end of the x-axis. Why is that number 47,746? Because as we commented a moment ago, the largest value in our data is actually quite different, it's just 40,704. So where did this extreme value come from?
The answer is that all of these decisions were made by Excel. This is because by default, histograms are going to come with an automatic choice of bin widths. To see this, right-click on the chart area. As usual, the right-click menu is context sensitive, and here it includes various options. Down at the very bottom is Format Axis. Go ahead and click on that.
And a Format Axis dialog appears over on the right of your screen. There you can see that the Axis Options, which include the bins, by default are set to automatic. Let's take a moment to understand the various types of bins. The first type is By Category. That will happen or that would apply in cases where we have categorical data, that is data such as male, female, or day of the week. Where all of the data points can be divided into discrete data categories.
That does not apply here. The next default option is Automatic. The third option is a Bin width. We're going to try this in a moment. But the basic idea here is that the user specifies the width of each bin. So for instance, if we pass in a value of 4000, the shape of our histogram will change. And we can now see from the values on the x-axis that each range has a width of 4000.
For instance, the smallest range begins at 16,146 and extends up to 20,146. In this situation, the extreme value on the right hand side of the x-axis is simply determined on the basis of the smallest value and then the bandwidth. Another option is to build a histogram based on the number of bins. If we check the radio button corresponding to this, we've got to specify a number of bins.
Initially, this is set to 7. Then a histogram is built using a number of bins. The extreme values correspond to the min and the max. We can confirm this. You can see that x-axis starts with the value of 16,146, which is the smallest value, and it ends with the value of 40,704. Which in turn is the largest value visible on cell B2. So in this situation, the histogram is built by dividing the range, that is the set of values between the min and the max into seven equal intervals.
Of course, we can specify a different number of bins. So if we change that number to 3, we can instantaneously see that our plot updates. Each bin now is a lot wider, and it encompasses a far greater range, but the extreme values are still the min and the max respectively. If we'd like to change that, we could make use of the two check boxes which read Overflow bin and Underflow bin.
These two check boxes can be used if you would like all values below or above a certain threshold to appear in a specific bucket. The bin options are the most important attributes of a histogram chart, but let's also spend a little bit of time understanding how we can modify its appearance in other ways. We begin by clicking the Add Chart Element button over at the top left. All of the usual options appear here, allowing us to customize the Axes, Axis Titles, Chart Title, Data Labels, Gridlines, and Legend. For now, let's go ahead and experiment with different configurations of the data labels.
[Video description begins] He selects the option: Data Labels. Various configuration options of Data Labels appear, namely: None, Center, Inside End, Inside Base and Outside End. [Video description ends]
Let's go ahead and choose Outside End data labels. This causes the y-axis values to appear right above the histogram bars. This makes it really clear that there are 5, 11, and 4 values in our three histogram bars. Let's go back to the right-click menu option.
[Video description begins] The menu with the following options appear: Delete, Font, Change Chart Type, Select Date and Format Axis. [Video description ends]
There we will format axis and change the number of bins from 3 to 5. Let's go ahead and make this change. And now we can see that our histogram has something approaching a regular bell curve. A bell curve or a standard distribution, a standard normal or a Gaussian distribution. All of these terms are synonyms for the distribution of many natural properties such as height or weight of individuals. If you surveyed a large number of individuals and plotted a histogram of their heights or weights, you would likely find a histogram which looks like a bell curve.
[Video description begins] He selects the Add Chart Element button. The menu with various options appear. He selects the Legend option. The sub menu appears. He selects the option: Top. [Video description ends]
The values closer to the average height would be very common, extreme small, as well as extreme large values would be relatively uncommon. And that would result in a histogram in which the tallest bars are concentrated in the middle. And the bars towards either end of the x-axis are a relatively short. That is a shape that you can also see on screen right now. That's why, for instance, we have 7 data points. These correspond to years in which the attendance ranged from 25,969 to 30,881.
In contrast, there were only two years in which the average attendance was below 21,058, that's the extreme left bar. And there were only two years in which the attendance was above 35,792, that's the extreme right bar. In any case, let's turn our attention to some of the aesthetic aspects of this chart. You can currently see that the Chart Title and the name of the series are both set to generic values. You've got to fix that. In order to change the name of the series, the simplest way is to click on the Select Data button in the top right.
[Video description begins] The dialog box titled: Select Data Source appears. It contains various fields, including: Chart data range, Legend Entries (Series) and Horizontal Category Axis Labels. A button labelled as: Switch Row/Column displays underneath the Chart data range field. A button labelled as Hidden and Empty Cells display beneath the fields. The OK and Cancel buttons display at the bottom. [Video description ends]
And then extend our selection so that we also include the cell B1 that contains the words Game average, and that will be interpreted by the histogram tool as the name of the series. And we can now see that Game average is the name of our data series. What's more, the highlighting around cell B1 is red. This indicates to us that the histogram tool is treating it as the name of the series. We also would like to fix the Chart Title, and here it's simple enough.
We simply click on it, that makes the area editable. We go ahead and type out the word Histogram. That gets us to the end of our quick exploration of histograms in Microsoft Excel. Once again, please remember that histograms were only added as a chart type in Excel 2016. Next we'll turn our attention to yet another statistical type of plot, that is the box and whisker plot. That's in the demo coming up ahead.
Using Box-and-Whisker Plots
Let's start by making a copy of our data.
[Video description begins] He right clicks on the screen, a context menu appears. This menu contains the following options, including: Cut, Copy, Paste Special, Insert, Delete and Filter. He selects the Copy option. [Video description ends]
Remember that this is the data which represents attendance for games of the Baltimore Orioles. That's a Major League baseball team from the US. We go ahead and copy this data into a new tab.
And once we are done with that copy operation, we rename the sheet BoxAndWhisker. Once we paste the data in, we can then go ahead and select it and insert a new type of visualization, that's the Box and Whisker plot. So that's where we pick up the action, we click on the Insert tab up top. That in turn brings up a new set of menu options.
[Video description begins] Various options that appears are: Pivot Table, Recommended Pivot Tables, Table, Get Add-ins and Recommended Charts. [Video description ends]
And there we click on the button to insert statistical charts. We've encountered this button before, but this time we are not interested in adding histograms. Rather, it's the second option, the Box and Whisker plot, that we are interested in. Let's take a moment to read the tool tip provided by Microsoft Excel. The Box and Whisker plot is used to display variation within a set of data. And we ought to use it when there are multiple data sets that relate to each other in some way.
Let's go ahead and try out this plot and see for ourselves what we get. The box and whisker plot is now visible onscreen. You can see that on the y-axis are numbers corresponding to game attendance. On the x-axis we just have the one data point that represent the one data series for the game average. What exactly is this plot even telling us? Well, that will become a little clearer if we change the appearance of the plot to add in some of the important numbers.
So let's do that. First, we open up the Format Data Series option that's visible in the pane on the extreme right. There, let's change the fill color so that it's a little lighter, to be easier for us to read the data labels once they do appear. Once we've changed that, let's now go back to the Add Chart Element button over in the top left.
And there, let's go to the Data Labels menu and add data labels over on the right.
[Video description begins] He selects the option: Data Labels. A list appears on the right. He selects the option Right in the list. [Video description ends]
We've explored several different types of charts so far. The data labels were rarely that useful. This box and whisker plot is an exception to that. With box and whisker plots, the data labels are extremely important. Let's really quickly add in the gridlines that we want, once again, by clicking on the Add Chart Element box.
And then we go with Primary Major Vertical gridlines. Now, we have all of the numbers clearly on screen before us. Let's spend a moment trying to understand them. The way the box and whisker plot works, it's got a box that's in the middle. And that's what's colored in that light orange hue. And then it's got a pair of whiskers.
The box is intersected by a horizontal line. That horizontal line corresponds to the median of the data which here is 26,773. In addition, there is a single point marked with an X. That indicates the average, which is 27,772. The average is simple enough. It's simply the sum of all of the numbers divided by the count of the numbers.
What is the median? Well, it's that value from a data set such that half of the points in the data set are greater than the median and the other half are less than the median. In an example like this one where we have a number of discrete data points, an easy way to compute the median is to sort the data, and then find the position which appears in the middle.
If you have an odd number of data points, there is just going to be the one median. If there is an even number of data points, well then, the usual procedure is to find the two indexes which are in the middle and then compute their average. Here we can see that the median is ever so slightly smaller than the mean. The mean is 27,772. That's about 1,000 larger than the median.
This means that our data is skewed towards the right. It's got slightly more extreme values greater than the median than below the median. Okay, so that does it for the center point. What about the box boundaries? Here the upper boundary of the box is 31,910 and the lower box boundary is 23,753. These numbers correspond to something known as the interquartile range.
Basically, the 25th and 75th percentiles are going to be calculated. The difference between these two is known as the interquartile range. So the upper box boundary, that's 31,910, is the 75th percentile in the data. The lower box boundary, that's 23,753, is the 25th percentile. And the height of the box is the interquartile range, which is the difference between the 75th and the 25th percentiles. Next, let's turn to the whiskers.
Here we can see that the top whisker has the value 40,704 and the bottom whisker has the value 16,146. We know from having examined this data that these correspond to the maximum and minimum values in our data. But this does not mean that the whiskers are always going to end the max and the min values. In reality the top of the upper whisker can be selected as either the maximum value in the sample or as 1.5 times the interquartile range.
Likewise, the minimum value could be selected either as the minimum value in the sample or as 1.5 times the interquartile range below the median. Which of these is used? Well, that depends on the Excel chart options. But the default is to show outliers and to go with values which are greater than 1.5 times the interquartile range in either direction, being indicated by dots.
In our example, our data actually does not have any outliers. All of the data values fit within 1.5 times of the interquartile range on either side of the median. And that's why we don't see any dots corresponding to outliers. Let's very quickly right-click on our chart and examine some of the series options.
[Video description begins] The Format Data Series dialog appears on the right. It contains various Series Options. [Video description ends]
You can see that there are checkboxes corresponding to Show inner points, Show outlier points, Show mean markers, and Show mean line. We can also see that there are a couple of different alternatives that can be used in the quartile calculation. These could be either Inclusive median or Exclusive median. Notice that the current default settings use the Exclusive median method of quartile calculation.
They show mean markers and they also show outlier points. It just so happens that this particular data set does not have any outliers. This difference between the inclusive and exclusive medians is an important one. So let's take a moment to understand it. The easiest way to do this is to go back to the raw data. If we look at the raw data, we can see that we have data ranging for the years 2000 to 2019.
Here, the median is calculated as the average of the two values, 26,726, that's in cell B13, and 26,819, that's in cell B11. The average of those two numbers is 26,773, which is the median we saw in our box and whisker plot. Now, the manner in which the 75th percentile is calculated is by taking all of the data points greater than the median, and then finding the median of those data points.
And that turned out to be 31,910. Likewise, the 25th percentile was found by taking all points smaller than the median and calculating their median. That gave us the value 23,753. The reason we only considered values greater than the median and less than the median is because we were making use of the exclusive median method for quantile calculation. Had we gone with the inclusive method, the median would have been included while calculating the 25th and 75th percentiles.
So exclusive calculations tend to give us wider interquartile ranges than inclusive calculations. That gets us to the end of our exploration of box and whisker plots. Please remember again that histograms and box and whisker plots were only added to Microsoft Excel in the 2016 version. And these are powerful tools which are handy for every data analyst.
Analyzing Scatter Plots
The data that we are going to make use of belongs to a car add data set from Kaggle, which is a repository with a large number of great data sets. I'd highly recommend that you spend some time going through the data sets available on Kaggle and playing around with them for yourself.
[Video description begins] The url which host refers to, reads as: https://www.kaggle.com/
Here we have intentionally chosen to arrange the data such that we have two mini data sets, each consisting of two columns. The first mini data set has mileage and price, and the second mini data set has engine capacity versus price.
[Video description begins] Mileage and Price dataset is listed under the columns A and B respectively. [Video description ends]
[Video description begins] Engine and Price dataset is listed under the columns D and E respectively. [Video description ends]
As you can see, the price columns are actually the same, so column B and column E are identical. The reason that we've chosen to arrange our data in this fashion is because scatter plots are actually explicitly meant for plotting relationships between pairs of variables.
So here, for instance, we are going to look at the relationship between mileage and price. And then we'll also look at the relationship between engine capacity and price. Now just to be clear, here mileage refers to the number of kilometers driven by this car in thousands. So a car which has high mileage is one which has been used very heavily and so can be expected to have a lower price.
In other words, we would expect a negative relationship between mileage and price. Let's see whether this is the case. Let's go ahead and select our two columns, so that's the data in E1 through B96. Then we click on the Insert tab up top and then navigate down to one of the charts and this is the one right above the keyword charts.
[Video description begins] Various options appear, including: Pivot Table, Recommended Pivot Tables, Table, Get Add-ins and Recommended Charts. [Video description ends]
Here you can see that we have different types of scatter plots available to us. A scatterplot is a great tool for detecting relationships and correlations between two variables. And you would use scatter plots where you have two data series, both of which are continuous. As you can see, in this example, we have the x-axis representing mileage in thousands of kilometers and the y-axis corresponds to the price.
We can see from this data that there is indeed a negative relationship between the two variables. For instance, there's the one point which has a really high price, that's $200,000, and that car has a really low level of mileage. That particular car is something of an outlier. It probably represents an expensive brand which has been only lightly used. On the other hand, as we move further towards the right on the x-axis, we get to cars which have really large numbers of kilometers clock.
For instance, we have one car which has between 300 and 350,000 kilometers, and as you might expect, its price is almost 0. Now scatter plots are an important tool in something known as EDA. EDA is an acronym for exploratory data analysis. The whole point of exploratory data analysis is to figure out what kind of models can be used to represent our data.
And one of the most common types of models is a linear regression model. So scatter plots are very often used to answer a question like, is linear regression a good choice for modeling the relationship between these two variables? And to help answer that question, you can click on the Add Chart Element button in the top left, and then go ahead and add a trendline. You can see that Microsoft Excel supports various different types of trendlines.
First on the default is none and then there are linear, exponential, linear forecast, and moving average trendlines. Let's just go with the linear trendline. This is what you would use if you wanted to evaluate the goodness of fit of a linear regression. And now you can see that trendline has been added into our plot, there is a clear negative slope.
So it's very clear that the best fitting straight line between mileage and price slopes downwards, and this indicates a negative relationship between these two variables. Let's also change the chart title. Titles of scatter plots often contain the word versus. The usual notation is to have the name of the x-axis variable first, followed by the word versus, followed by the name of the y-axis variable. And that's why here, we've titled our plot Mileage Versus Price.
Again, the word versus appears in the titles of scatter plots because they are explicitly used to compare two continuous variables. Let's now turn our attention to the second pair of variables, engine volume and price. This time we would expect a positive relationship because more engine capacity implies a more powerful car, which in turn usually implies a higher price. Let's confirm this by selecting all of the data, inserting a scatter plot again.
[Video description begins] He selects the Insert tab. Various options appear. He selects the option for Scatter plots. The list of different scatter plots appear. He selects a scatter plot option from the list. [Video description ends]
And this time when we do so, we can see that the points, broadly speaking, are positively correlated. So a high value on the x-axis also implies a high value on the y-axis. Let's go ahead and confirm this by juxtaposing or placing the two graphs one next to the other.
We can see in the Mileage Versus Price graph that it's quite clear that the trendline slopes downwards, implying a negative relationship. Let's now go ahead and add a trendline in the other graph, that is, the engine capacity versus price graph. So we click on Add Chart Element and we add in a linear trendline.
[Video description begins] He selects the option: Trendline. Various list options appear on the right. He selects the option: Linear. [Video description ends]
And it's very clear that this trendline is upwards sloping. Let's go ahead and modify the chart title so that it's consistent with our x versus y notation. This time we have engine capacity on the x-axis and price on the y-axis, so we'll title the graph Engine Volume Versus Price. And in this way, we have successfully demonstrated the use of scatter plots and trendlines in order to detect positive and negative relationships between pairs of variables. We will have more to say on this topic when we return to the subject of correlations and covariances down the road.
Plotting Maps and Combo Charts
We begin with data which we are going to plot on a map. And the source of this data is the URL on screen now. Once again, this is a repository from Kaggle.
[Video description begins] The url reads: https://www.kaggle.com/noriuk/
This data set measures academic achievement in different states of the United States in the subject of mathematics. What's important to note here is the state column. That's column B. The values in this column represent states of the US and these can be recognized by Microsoft Excel. Excel is smart enough to figure out that these are US states. And that's why if we go ahead and try to insert a map, we will be able to get a precise map of the United States and the associated states. So let's click on the Insert button right up top. That of course changes all of the menu items.
[Video description begins] Various options appear, including: Pivot Table, Recommended Pivot Tables, Table, Get Add-ins and Recommended Charts. [Video description ends]
It introduces the buttons for the different types of charts. We go with the Map icon, that's pretty easy to identify, it's a globe. You can see that there is a filled map type. Let's go ahead and select that.
You can see right away that we have this incredibly professional map chart appear in our workbook. Note how it's a gradient chart. The shade of blue corresponds to the Average_Score. Very light shades correspond to low scores and very deep shades correspond to higher scores. What's important to note here is that the names in Column B have been automatically picked up and assigned to the correct states.
In fact, maps are an offer new feature added in Excel 2016. And they support not just the names of the states, but even zip codes if you're working with US data, As well as a variety of international country names and other locations. Maps were actually an add-in in Excel 2013 but now, they are fully fledged first class citizens, which means that you can do all of the usual operations. For instance, you can click on the Add chart element button and add data labels.
[Video description begins] He selects the Add Chart Element button. The list of options appear underneath. He selects the option Data Labels. Various list options appear on the right. He selects the option: Show. [Video description ends]
Then as you hover over individual points, you will be able to see not only the values corresponding to the scores in those states. But you can even see the other attributes such as the state name. So because our mouse is hovering over the state of Nevada, we can see the tool tip with the corresponding data.
The average score for the state of Nevada is 231.685. Next, let's turn our attention from maps to combo charts. For this, we'll go back to a data set, which we worked with in a previous demo. This had the sales for General Motors for the calendar years 2019 and 2018. We're going to make use of this data to build a combo chart.
But before that, let's actually build some of the other chart variants that we haven't explored in detail as well. You can see, for instance, that in the Insert menu, we have options for different types of 2D and 3D line and area charts. We've already worked with some variants of these. We can go with a 2-D Area chart, a 3-D Area chart. And we can very easily change the chart type so that instead of being a 3D area chart, we instead have a 3D column chart.
So that's what you see on screen now. Let's now use this 3D column chart as the starting point for a combo chart. This is a chart which combines different data sets and different graphs into the same chart, combo of course is a short form for combination. So with this same data selected, let's go ahead and click on the button for a Combo chart. You can see that next to the maps, this has various combinations.
And the default is the Clustered Column which combines a line chart and a bar chart. You can see this mentioned in the tool tip on screen now. So we go ahead and accept this option and we see right away that we have a combo chart.
[Video description begins] The combo chart is labelled as: General Motors Sales. The Y-axis contains the Sales data and the X-axis contains the Month. [Video description ends]
We now have a line which represents the sales for the year 2018. And we have bars which represent the sales for 2019. A combo chart is a great way to have different chart types such as a line chart and a bar chart in the same chart area. In this particular example, both of the data series represented in the combo chart have the same units. Both of these represent the sales.
However, combo charts are commonly used with two y-axis. For instance, let's say you wanted to plot revenue as well as revenue growth. The units of revenue would be dollars, the units of revenue growth would be percentage changes. In such an example, it would make total sense to have a combo chart where your bars represent the revenues and the line chart represents the revenue growth.
Creating Sparklines
In this demo, we are going to work with sparklines. Sparklines are simple, lightweight visualizations which usually do not have either axes or coordinates. These are typically very small and they are useful additions to a spreadsheet where we have a lot of numeric data. The data that we are going to use for working with sparklines is visible on screen now.
[Video description begins] The Microsoft Excel Workbook displays. The column A2, B2 and C2 are labelled as: Month, General Motors and Ford Motors respectively. [Video description ends]
This represents the stock prices of two companies. The source of this data is Yahoo Finance.
[Video description begins] The url displays on the screen, that reads: finance.yahoo.com. [Video description ends]
Let's go ahead and get started. To begin with, let's only select all of the stock data for General Motors, that's in cells B2 through B14. Once we select this data, let's click on the Insert button.
[Video description begins] Various options appear, including: Pivot Table, Recommended Pivot Tables, Table, Get Add-ins, Recommended Charts, Line and Column. [Video description ends]
And there we've got to scroll all the way over towards the right where there's a group of items titled sparklines. You can see that there are three types of sparklines available, line, column and win-loss. For now let's go with the first, that's the line sparkline. When we click on the line sparkline button, we are prompted by Excel for the data range that we wish to select.
[Video description begins] A dialog box labelled as: Create Sparklines appears. [Video description ends]
Note that there are two input arguments, the data range is the first input argument, the second is the location range, this tells Excel where we would like the sparkline to be placed. Here, as usual, we click on the little black arrow over on the right side of that input argument box that allows us to use our mouse to navigate to a cell location.
In this fashion, we navigate to the cell B19, and that is where the sparkline gets created. Let's resize this cell so that we can view the sparkline a little more clearly. So we increase the size of the row as well as the size of the column.
Note how the sparkline seems to start from 0. This is not quite what we had in mind when we selected the first cell, which included the text General Motors. We've seen how some other types of Excel charts are correctly able to infer that the first value is a header name, but that clearly is not the case with sparklines. So let's go ahead and label this cell, we'll call it General Motors.
And then let's go ahead and go back to the sparkline menu and edit the data. To do this, we click on the sparkline menu up top, that's in the ribbon at the top of your screen. Notice how this only appears when you actually have a sparkline, and also notice how the menu options look very different.
[Video description begins] The menu options includes: Edit Data, Line, Column, Win/Loss, various spark line representations and Sparkline Color. [Video description ends]
In any case, for now, we are interested in the first button, which is called edit data, it's over on the extreme left.
[Video description begins] He selects the Edit Data button. [Video description ends]
There are 3 options available here, edit group, location and data, edit single sparklines data and hidden an empty cells. Let's just go with the first. This allows us to modify the data range.
[Video description begins] A dialog box titled: Edit Sparklines appears. It contains two fields, labelled as: Data Range and Location Range. The OK and Cancel buttons display at the botttom. [Video description ends]
Once again, we click on that little black arrow over on the extreme right of the data range input box that allows us to use our mouse as well as our keyboard to select a data range, we select B3 to B14. In other words, we leave out the header column. Once we are done, we click the little button over on the far right of this input dialog that takes us back to the edit sparklines dialog.
Once we are there we hit okay, and that causes the original sparkline to be updated. Notice that when we do this, we no longer have the abrupt start from a price of 0. In other words, this sparkline looks exactly as we would like it to. It gives us a quick summary of the stock price variation of General Motors in the months January through December. Notice again that the sparkline has no coordinates, no axes, it has very little information.
It just has a quick visual representation of the data. Let's now move on and create a similar sparkline for Ford Motors, which is the other company that we are interested in here. We are going to use this as an opportunity to introduce the idea of a named range, this is a powerful and important idea in Excel, so please make sure that you understand it. A name range is a descriptive name, which is assigned to a specific set of cells.
For instance, now on screen you can see that we have selected the stock prices of Ford Motor, that is from C3 to C14. Once we have selected the cells, we then click on the formulas tab. You can see that up top. That in turn access to a set of menu options over on the extreme left is the FX Insert function box, which we have used in the past. There are some pretty cool advanced features in the formulas tab, which you should know about.
For instance, a little over towards the right, you can see the trace precedents, trace dependence, shoe formulas and error checking buttons. Trace precedents and trace dependence are a great way of visually finding which cells depend on which other cells. In any case, that's a little more advanced than we would like to go right now. For now, we click on the define name button.
Again, this is going to allow us to define a named range. When we click on it, we are prompted by Excel for the name, the scope, a comment and then which cells this name refers to. You can see here that by default the name has already been filled in by Excel to be Ford Motors. Excel was smart enough to pick up the value of cell C2, it informed here that this is probably a title which refers to the selected cell range C3 to C14. A few other important points worth noting here.
You can see that the scope of this named range is workbook, which means that we can use this name range as a proxy in any formula for these cells and we can do this anywhere in the workbook. The alternative would be worksheet, in which case we would only be able to refer to this name range in this worksheet. Finally, notice down below how in the Refers to input dialog, that the input range is fully qualified, so it includes the name of the current worksheet followed by an exclamation point, followed by the absolute cell references.
The word absolute is important here. These are absolute cell references because of the presence of the dollar signs before each of the row and column identifiers. Because these are absolute cell references, if we were to cut and move the cells to a different location, the named range would pick up and adjust accordingly. In fact, this is the whole point of using named ranges.
Named ranges are a great and very commonly used mechanism for getting a level of abstraction. If you have formulas or if you have logic and you need some kind of logical name or logical grouping for that set of cells, which contain that logic, a named range is what you would use. Let's go ahead and rename this range to be just FM, and then we hit okay. This causes the new named range to be created.
Now, when we create our sparkline, we are going to do so by referring to our named range. Please remember again that name ranges can be used in pretty much any Excel or VBA function, they are not specific to sparklines in any manner at all. We are done with the formulas tab. So let's click on Insert over on the top ribbon, that's the menu bar on top, then scroll over towards the right, and there in the sparkline section we click on line.
[Video description begins] The dialog box titled: Create Sparklines appears. [Video description ends]
This time the data range is simply going to be the name range FM, which we created a moment ago. The location range is C19 because that's where our cursor is currently positioned, this is known as the active cell in Excel. Once we hit okay, the sparkline for Ford Motors is going to appear in cell C19. You can see from this just how convenient it is to use named ranges. People who use Excel sometimes make the mistake of over using named ranges.
While they are very advantageous, one important disadvantage of named ranges is the absolute nature of the cell references with a lot of other cell formulas. As the formula is copy pasted, cell references are automatically updated or adjusted by Excel, that does not happen with named ranges and that sometimes leads to complications. Just keep this in mind when you work with named ranges in Excel.
Customizing Sparklines
We're going to begin by now constructing a pair of column Sparklines. For this, we have created a nice titled cell, now, let's go ahead and select the data for General Motors. Hit the Insert button up top in the menu bar.
[Video description begins] Various options appear, including: Pivot Table, Recommended Pivot Tables, Table, Get Add-ins, Recommended Charts, Line and Column. [Video description ends]
Navigate over to the Sparkline section, and there this time, we will choose a column Sparkline. You can see that this is the second type of Sparkline below the line Sparkline, which we used in the previous demo. When we click on it, once again, we are going to be prompted for the data as well as the cell location where we would like the Sparkline to appear.
[Video description begins] The dialog box titled: Create Sparklines appears. It contains two fields, labelled as: Data Range and Location Range. The OK and Cancel buttons display at the botttom. [Video description ends]
Because we had already selected the cells B3 to B14, we don't need to change the data range input selection. We do need to change the location range. We are now comfortable doing so by clicking on that little black arrow and then using the mouse to navigate to our target cell. Here, that cell is F19. Notice how when we go ahead and enter the cell, it's entered in the form of an absolute reference with the dollar symbols before F and 19, respectively.
Once we are done, we go back to the Create Sparklines dialog, click OK, and this causes the column Sparkline to be added to cell F19. As you can see, this is a really handy visual representation. Once again, there's no axis, there's no units information, but we do have bars representing each of the monthly stock prices of General Motors. Let's go ahead and create a similar set of column Sparklines for Ford motors.
As before, we are going to make use of the named range. So we click on the Insert menu option up top, navigate over to the Sparklines section, click on Sparklines column.
[Video description begins] The dialog box titled: Create Sparklines appears. [Video description ends]
Then when we are prompted for the input range, we do not enter the actual cells, we simply type the name of our named range that is FM. The location range G 19 is already correct, so we just hit OK. We've now got four Sparkline graphs, which represent the stock prices of General Motors and Ford respectively.
Two of these are lined Sparklines, two of these are column Sparklines. Now, let's get to something even more interesting. Let's select all four of these Sparklines and then navigate to the Sparkline menu up top. It's in the menu bar towards the right. And then let's start customizing the appearance of these four Sparklines. Now, one important little note here, the usual manner in which Sparklines are used is in groups like this one on screen.
And that's why it's totally usual for you to select groups of Sparklines, and modify all of the properties simultaneously. This is a little different than the way we work with individual graphs, but that's because they are a lot more heavyweight.
[Video description begins] He selects the Sparkline tab. Various options appear, including: Edit Data, Line, Column, Win/Loss, various spark line representations, Marker Color and Sparkline Color. [Video description ends]
In any case, now you can see in the Sparkline's menu that we can edit data, that's the button over on the extreme left. We can easily toggle across the different types of Sparklines. One quick word here, the Win/Loss Sparkline would make more sense when used with categorical data, specifically binary categorical data, while all values must belong to one out of two categories. Here, because our data is continuous, line and column Sparklines make the most sense. Moving on, you can see that there are check boxes for the high point, low point, negative point, first point, last point, and all markers.
Let's go ahead and check a couple of these check boxes. We're going to add markers for the high point and the low point. You can see that as soon as we make this change, we now have two of the points in each of our four Sparklines appear in red. This is most apparent with the column Sparklines, but it's also true with the line Sparklines over on the left. In the case of the column Sparklines, the tallest bar is now red, the shortest bar is also red.
It's just that the shortest bar is a little hard to see. With the line Sparklines, we can similarly see that the low and the high prices are marked with red points. Let's now also check the boxes which highlight the first and the last point. You can see that this causes two additional points to be marked in red in each of our four Sparklines. Which two additional points?
Well, the first and the last. Let's keep going with other modifications to the appearance of these Sparklines. You can see that there is a tool for the Sparkline color that's over towards the right in the Sparkline menu. We can use this to experiment with different color schemes for our Sparklines.
If we change our choice of Sparkline color, we can see that all of the lines in the Sparklines below change in color as well. If we'd like to customize the appearance of the point markers, we simply click on the marker color control right below the Sparkline color. And really it's menus like this one which tell us why Microsoft Excel is such a popular and powerful product. Look at the level of detail that we can exercise in controlling the marker colors.
We can explicitly set colors for negative points, for the high point, the low point, the first point, and the last point. Let's go ahead and experiment with these different types of marker colors.
[Video description begins] He selects the option: First Point and selects a color from the color option window. [Video description ends]
And once we do this, we can change the appearance so that we have different colors for the first and last points, as well as for the high and low points.
[Video description begins] He selects the option: Last Point and selects a color from the color option window. [Video description ends]
As you can see from this, Excel Sparklines are a really handy way to visualize data and it can become a little addictive playing around with the customizations available.
Let's move on from the marker color and turn our attention to the axis of the Sparkline. You can see in the Sparkline menu that there is a control, a button for the axis that's right next to the Sparkline color and the marker color controls. If we click on it, we can see that there are options for the horizontal axis, the vertical axis, minimum value options, and the vertical axis maximum value options.
For now, let's go ahead and change the orientation of our data by clicking on the option to plot data from right to left. We can do this and we see immediately that the Sparklines down below seem to be laterally inverted. Let's go ahead and reverse that because, of course, we have data in time order. So let's uncheck the box to plot data from right to left.
[Video description begins] The Plot Data Right-to-Left option is listed underneath the option: Horizontal Axis Options. [Video description ends]
And this restores the Sparklines to the correct order, where time increases from left to right. It's also important to note the Sparklines are dynamic. So for instance, let's say we casually edit the contents of a cell. Let's say we edit the value in cell B13. So we change it from 35.18 to 50. As soon as we hit Enter, we will see that both Sparklines for General Motors will update automatically.
Watch for it, I'm going to hit Enter and now, the Sparklines for GM changed. In similar fashion, if we change the data point for Ford to 50 as well, we can now see that there is a much more dramatic change because 50 is something of an outlier for Ford's stock prices in this period. Before we move on, let's undo these two changes that we just made using the Quick Access Toolbar. So we click on the little undo sign over at the very top left of the screen.
There's a small arrow button there next to that sign. As before, when we click on it, we get a list of all recent actions. From this list, let's find the actions that we wish to undo and select and click on them. We do this one by one, we undo the typing of 50 in cell C 13 and then we undo the typing of 50 in cell B 13. The reason for doing these one after the other, is to note that after we undo the typing in cell C 13 that no longer appears in the list of actions the second time around. In any case, that restores our Sparklines to their original form and that also concludes our exploration of Sparklines. We will now move on to other aspects of working with Excel in the demos coming up ahead.