CS 100 (Learn) — CS 100 (Web) — Module 06
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video, we are going to explore how to use charts in Excel.
Charts are useful to visualize your data in a wide variety of different formats and visualization techniques.
We are going to explore a few [charts,] and we are not only going to scratch the surface.
There is no way we can show you all [of] the different ways to use Excel charts: it would take forever. You are going to have to explore [charts] on your own.
Warning! Different versions of Excel [display] charts differently. If you are using a different version of Excel, what we see today may look a little different [in your version].
I have sample data from some fictitious company that shows sales data [for] each month [of] a year, and we have an East and [a] West division. We want to visualize this data.
To illustrate changes over time, a line graph is often the best choice.
[To generate the chart,] I highlight the data and Excel is going to do most of the work for me.
I [choose the option for] a 2D line graph. I want something that looks like [what is displayed for a 2D line graph].
[Excel] automatically [analyzes] my spreadsheet, [determines] what I want to do and [generates the chart].
We now have a visualization of the sales from each division (the East division and the West division) over each month of the year.
We can see the trends: there is a little dip over the summer and we can see sales ramping up in the East Coast when it gets close to December for Christmas time.
One [issue] I want to address is where to put your chart. The default [in] Excel is to place it in the middle of your spreadsheet. [This chart] is hovering over [the] spreadsheet, which means [it] could be hiding information on [the] spreadsheet.
This layout is particularly good if you want (for example) to [display the data and the chart] together on one page [on] a printout: you want to have your sales table and you [also] want to have your chart all [on] one page [for a printout].
That is very rarely the best way to organize your data [and your chart]. What you should [often] do is move the chart and create a new sheet for it.
I have created a new sheet for this [chart] and then, if we look down at the bottom, we can see our original Sheet1 which [has] our sales data, and now Chart1 is a whole separate sheet... which allows us to visualize the chart and not get bogged down [with]:
I recommend that you put most of your charts on separate worksheets. For some reason, that is not a widespread practice.
When working on your chart, there [are] two tabs at the top of the page that you can use.
[The] first [tab] helps design your chart (control the structure of the chart).
There [are] different things you can do: there [are] a couple different quick layouts you may want to use: Where do you want to show the legend? Do you want to show labels or not?
There [are] a few different ways of visualizing [a] chart, depending on what your needs are.
You can change the colours (there [are] a few different palettes you can choose from). You can get very specific on [your choice of] colours. For example, if I highlight this bar and I right-click it, and I [click on] "format data series", and then click on "formatting", I can specify exactly which colour [(for example, I want purple].
You can control the look of your chart [in great detail]. You can change the fonts for the different [regions]. You can change the layout.
A wide variety of options [are] available for you to format your chart and clearly we can not go through them all.
We can [also] change the chart type. Currently, this is a line [chart]. There [are] a wide variety of different charts available: a good one for this chart might be a column [chart], where we could see the sales for each month and then we have a different column for the east and west divisions.
[Column charts are] especially good if you are looking at different categories of data and then multiple criteria for each category.
This is our first example, where we looked at data [that changes] over time: which is [good as] a line chart, [and] it is [also] okay as a column chart.
There are other types of data that are [best displayed] a little differently.
We are going to [consider] a different example.
I have generated some fictitious data about students' grades on their assignments throughout the course and then what their final exam grade [was].
I want to see how those two correlate. This does not [display well] in a line or bar chart. The type of chart we are going to use ([first] I am going to highlight all of the data and then I am going to [click] insert) [is] an XY scatter plot.
I generate the plot (and then of course I am going to move the chart to a new sheet).
[When] we are working with this kind of [scatter] data, I am going to show you one [tip] that can be very useful when you are looking [at] scientific data or numerical data.
It is a little different than [when we were] working with the line or bar charts. [We] want to specify on the actual axis (click on "format axis") what range [we] want to see.
Currently, I am looking at the horizontal axis and I may only [be] interested in seeing 60 through 100. [This] changes how the data looks ([by zooming] in on it. Similarly, [on] the vertical axis I may want to look at data from 45 to 100.
[We] can see each dot represents a student's grade: the horizontal axis represents what their grade was on their assignment and then the vertical axis represents what their grade was on their final exam.
It is correlated, [and] we can add a trendline (for example) that [displays] what the trend [is]. Because this is fictitious data, it is a very clean trend, but [regardless] the trend shows here that people who do well on their assignments do better on their final exam, [and] people who do poorly on their assignments also do poorly on [their] final exam.
This [XY scatter chart] is a good way of representing data if you are working with scientific data or other types of [data with] interesting correlations.
The last type of graph we are going to [demonstrate] is known as a pie chart.
We [have] monthly expenses, and we want to visualize how we are spending our money in [the] different areas.
I insert [a] pie chart: there [are] different options, but let's [select] a fancy 3D pie (and let's move this chart once again to its own sheet).
Now we can see visually how we are spending all of our money. We spend a lot on groceries and we spend a lot on rent.
We can also do fancy things like "pull out" one section, and if we want to highlight it there [are] some fancy things we can do with Excel.
Pie charts are especially useful for analyzing [the] composition of something or looking at different parts that sum up to be a whole and you want to visualize the size of the relative components of the whole piece [entity].
That was a summary of working with charts in Excel. We looked at a bar chart, a line chart, a pie chart and a scatter plot.
There [are] other types of [charts] you can explore and we only scratched the surface: there are lots of different ways for you to play with charts, manipulate charts, and help you visualize [your] data.