English Subtitles for Basic Excel Business Analytics #43: Visualizing Data: Table & Chart Guidelines



Subtitles / Closed Captions - English

Welcome to Highline BI 348 class video number 43. Hey, if you want to download this workbook-- BI 348, chapter three-- or the PowerPoint slides, click on the link below the video. Hey, we're in chapter three, data visualization for tables and charts.

Our topics-- data visualization, then we'll talk about data visualization's golden rule. We're going to look at tables. We're going to look at a bunch of charts. We're going to, briefly, look at conditional formatting, graphical information system.

And we'll remind ourselves about data dashboards. Now data visualization-- we basically have tables and charts. Now tables are great for summarizing the data and seeing the exact numbers. Charts, on the other hand, give us

that quick visual impression-- great for seeing the trends and patterns. Now both charts and tables will help us interpret, analyze, and learn from the data. One big difference between tables and charts-- tables-- that's when we want to make precise comparisons.

But charts, we very rarely can make precise comparisons. What we want to do is make relative comparisons, right? The height of the columns, here, it's looking at the exact numbers. Another important use for tables is when we have numbers that are in different units or magnitudes.

You could imagine you have number of sales reps at a store and the revenues per day. This would be in a different unit and magnitude. So you might want to show that in a table instead of a chart. Data visualization-- we actually will look at conditional formatting.

Just one example, heat maps-- back in chapter two we did a bunch with conditional formatting. We'll look at one example for graphical information systems. And actually, that's a quite amazing data analysis tool where we put statistics or data on maps. And then dashboards-- we did that back in chapter two.

Dashboards are important metrics presented visually and connected to updatable data sources. Remember, we did our power query and then clicked Refresh and updated all of our source data, pulled it in, and all of our charts updated. Now effective data visualization-- it always

allows you to take the raw data and see patterns and trends, analyze the data, learn from the data, and find errors. But really, the most important thing that effective data visualization does, is it makes our analysis easier for others to see and understand.

Effective, right-- if we have a chart with a bunch of chartjunk, then someone reading our chart is not going to understand the analysis we did. So we want to make sure that we learn how to effectively visualize our data. Now visualization, in terms of research-- research shows

that humans can process visual images, like charts, faster than they could process rows of numbers. Now we all know this from being alive and looking at numbers and charts. Charts-- you get that quick, visual impression. But research backs that up.

Another thing that research backs up, and actually, in recent years, data analysts have preferred columns and bar charts over pie charts. And the research shows that column and bars convey differences more easily than pie charts. Now one of the great things about our textbook

is that they have a whole section that says, don't use pie charts. And don't use 3D columns and all of the chartjunk that Microsoft Excel offers up to you. Now we've got to talk about an amazing person when it comes to charting and graphing, Edward Tufte.

He actually wrote this amazing book here and a bunch of other books all about taking numbers and visually portraying them to articulate your message. And he has two rules-- data-ink ratio rule and the no chartjunk rule. Now data-ink ratio just means, for any given amount of data,

you don't want a lot of ink. And the no chartjunk rule-- we've been using that for years in classes at Highline and videos at YouTube-- no chartjunk. All elements in the chart should help deliver the message. Now I'm going to boil all of this down on our next slide

to data visualization's golden rule. And it is, no extraneous elements in your table or chart. We don't need a bunch of extra lines and formatting in our table. We don't need a bunch of extra chartjunk in our chart.

Now the interesting thing about this rule right here-- and I have it down here as a side note-- is one of the hallmarks of great art is that great art has zero, or near zero, extraneous elements. And if you think of that, you know, art is all about visual. And that's the same thing for us.

We're visualizing data, or visualizing statistics, or visualizing our analysis. So no extraneous elements. Implementing this rule means all elements in the chart or table should help deliver the message. No extraneous elements leads to more effective tables

and charts. And when your table and charts are effective, then the benefit is that effective tables and charts allow you to better communicate your analysis to others. Let's go to our next slide and talk about low data-ink ratio. Now what does data-ink mean?

If you take all the data that you have and you divide it by some number that represents ink, the less ink, the bigger this ratio will be. If there's too much ink then it's a low ratio. The number is small. In this example right here-- this

is straight from the textbook that we're using-- it's assumed that there's too many lines here. And we'll see an example in the next slide that cleans it up a bit. And this chart is just terrible. We don't need this legend.

We don't need the lines. And oh, my heavens, there's no labels here. And we have a little arrow here-- contains chartjunk. Next slide-- aah, that's much better. This has got a high data-to-ink ratio. That means, there's less ink in the denominator.

So when we do the division-- the theoretical division, right-- we're going to get a bigger number. That table's a little bit easier on the eyes. And look at this chart. It's got proper labels. We don't have that legend with that useless series.

And we don't have all those horizontal lines. And if I do my little arrow, this does not contain chartjunk. All right, next slide-- we want to talk about the difference between when we use a table and a chart.

Hey, tables are great when you want to see the exact number. We want to compare each number. Tables are perfect. Here's a chart. Here we're comparing the relative differences. Same data in both of these-- clearly, we

could see the exact values. Here we get a quick, visual impression. Now sometimes you want both. So certainly, we're allowed to combine them. Here we could see the relative comparison or differences between revenues and costs.

Down here, if we want the exact numbers, boom, there they are. Hey, even in the charts-- you know, we have that green plus in our charts in Excel 2013 and later. You can just click that plus and tell the chart to plot the data table. It's pretty cool.

Now our next slide-- just as an example of when we have different units-- usually, you want to use a table. You can see here we have headcount, cost, and revenues. The magnitude is quite different and the units are different. Now you could plot this on a chart

and use a second axis to-- and have different units and whatnot. But oftentimes, it's easier to show it in a table when the magnitudes or units are different. Now I want to go two slides ahead to slide 16 and talk about table design principles.

Now, of course, the data-ink ratio should be high. And here's some rules straight from our textbook. I tend to totally agree with all these blue ones. And I don't necessarily agree with these two, although I may be out-voted by the majority of people who do data analysis and present data.

But for tables, avoid using vertical lines-- up and down-- in the table, unless they are necessary for clarity. I tend to think that, more often than not, I need them for clarity. Horizontal lines are, generally, necessary only for separating column titles-- that means our field

names at the top-- from the data values, or when indicating that a calculation has taken place, like on a income statement when you have a line before you calculate total expenses or net income. Now the rest of these are pretty awesome table design principles.

In large tables, use some light shading to differentiate each one of the columns. And we'll see an example of that in just a second. Numbers should be right-aligned. Text should be left-aligned. Now actually, I follow this one pretty faithfully.

If we go back to slide number 11-- and I'm going to hit 11 and jump back here-- oh, wait a second. Those numbers are centered. I tend to not do this. But you see a lot of examples out there in the working world on the planet Earth where

people center everything. Now it's OK for the final report. But don't do it when you have your data in the sheet in your analysis because, as we talked about earlier in the class, when you start centering things you lose your visual cue about whether Excel

thinks it's text or a number. Back to slide 16-- now here's another great table design principle. All numbers should have the same number of digits. I can't believe how many times I've seen tables where this rule isn't followed.

And it's so simple and really adds clarity to the table. Units must be indicated, either by number formatting or labels. So at the top of the column you might have a dollar sign to indicate it's in dollars, or a percent symbol, rather than cluttering the table.

So however you do it, whether it's with labels or number formatting, you've got to show the unit. And large numbers may be rounded to the dollar, the thousands, the millions, even the billions, like in GDP numbers. All right, our next slide has a great example-- there it is.

This is straight from our textbook. A, B, C, or D-- which one do you prefer? The author states that most people prefer D. I don't know what it is, but I prefer this one here. Now there's no doubt that example we just saw back on slide number 11--

I don't know-- that one looks OK. And there's not a lot of lines. But this one on 17, I certainly don't like that there. But I guess I'm out-voted here. So you get to use your eyeballs. And certainly, when you're in your job,

you listen to what the bosses are saying. And our last slide before we go over to Excel, this is a great example. When you have a lot of columns, differentiate every other column with a different shade. Now notice here, they've centered it again also.

That's something I do not do. But it seems like lots of people like to do that. Now let's go over to Excel. And here's a table we were given. Sales reps, sales in 2015, '16, percentage change, customer accounts, and years with company.

What do we got to do? First, I've got to show you a great feature I use all the time. I want to wipe away all formatting-- number formatting, style formatting, home ribbon, editing. This is one of the more important things--

Clear Format. Now I use the top two regularly. Clear All-- if you use this, you could delete everything and clear the formatting. How many times have you hit the Delete key but the formatting is still there?

Clear All does it. I'll even hit that. Uh-oh-- Control-Z-- but if you don't want to clear it all, you want to clear just the formats-- boom. Now I know the keyboard. I use this so often.

In 2007 or later, I don't even know what it is. It's Alt-- and I'm going to follow the keys-- H, and then E, and then F-- so half, H-A-F. I remember it from earlier versions-- Alt-EAF. And now we can start over. Now I'm actually going to try and do it.

I'm going to do it two ways. I'm going to do it my way. And I always have the field names with some dark color, like this. And then, of course, I have black borders everywhere. Now this says it's in dollar signs.

So that's pretty convenient. I'm going to highlight these numbers-- Control-1 to do number formatting. And all we're going to need-- not currency-- I'm going to say, Number with a comma separator. And these values here-- for these big numbers,

we probably don't need to see any decimals. So I'm going to put it down to zero, like that. OK, that looks much better. Now these percentages-- we have a problem here. In Excel, if you go up and use Percentage number formatting, the percent symbol will show up in every cell.

And I, actually, don't know a way, with number formatting, to get this to look like an integer, right. So you, actually, have to do this little trick. And I usually teach to not do this. And the reason why is-- times 100-- well, that'll give us the right number that we can show up here.

But the problem is, if you ever use that in any calculations, then it's the incorrect number. But we're talking about final tables-- presenting them. So I'm going to copy this down. And so, there they are. Now how do I get them back up into the table?

I'm going to copy them. And then I'm going to come up here and right-click, and right there-- Paste Special Values. Another way to do it is, once they're copied, is to remember that there's this Paste dropdown up in the Home ribbon.

You can come up here and get the values. Or here's another great trick. If you already used your mouse to get it, you might as well right-click the edge. You see, that's the Move Cursor. I'm going to right-click and drag up.

And when I let go of the right-click, a secret menu pops up. And I say, Copy Here as Values. So then I can delete these down here. So now I have what I want-- well, almost. The decimals-- there's only one.

There's two. I want to have all numbers with the same number of decimals. Control-1, Number, and use a separator. Click OK. And then these numbers are fine. All right, that's way to do it.

Let's look at another way. And I, actually, am going to have to admit that this other way is going to look a little bit better. I'm going to come down here. I copied.

I'm going to click down here and-- Control-V. Now I want to keep all of the number formatting. But I am going to remove the borders. So I come up here and-- No Borders. Now I'm going to highlight the whole table with exactly one row and column next to it.

And I'm going to come up and use white fill-- boom, like that. Now I had white headers up here. So I better come up here and do Font Color-- black. I'm going to use Control-B to highlight the headers. And I'm going to do this bottom border. So right here at the top-- and that's

looking pretty clean-- we could copy and paste. We could also change these column widths up here. Now if you don't like that white, you could come up and use a nice, light blue. And look at that. That seems easier to read than this one up here.

I still tend to-- when I'm analyzing the numbers-- I like the black lines. All right, that's our first example. Let's go over to this next sheet-- table design principles two. Oh, that is terrible.

Look at that. This violates every single rule. It's got bold, too many lines, the lines-- the actual borders-- are bold. The font color and the fill color don't have a great enough value difference.

If you print this out, it will look terrible. So we're going to highlight everything. And we love our eraser for formatting-- our Alt- EAF. We start over. Now these numbers-- we're going to highlight these numbers. And we're going to start off-- Control-1-- come over

to Number. I do not need to show decimals here. I'm going to use a comma and just look at these and see what's happening. Now notice, it says millions up here. So we've got to learn a great custom formatting trick--

Control-1-- and now, whatever you select from here, when you come down to Custom, you can see the custom number formatting. Now in our prereq class, Business 218, we learned about custom number formatting. And I also gave you some notes earlier in this class

in one of the workbooks. But the pound sign means, show significant digits. The zero means, show insignificant and significant digits. But what we want to notice here is that comma-- pound sign comma, pound pound pound,

or pound pound zero-- will show the comma separator. Now watch this. I'm going to come to the end here and I'm going to remove the zero and just use the pound custom number formatting. But watch what happens.

When I type a comma at the end here, watch what happens up here. Comma-- whoa-- it divides by 1,000. Comma-- divides by 1,000. So two commas-- that just got rid of six zeroes. So this is in millions.

Now notice our labels at the top have an M, so we do not need an M here. Plus it would clutter up all of the numbers, right? But if you needed to put an M-- for example, in charts, sometimes, on your horizontal axis, you want to show in the millions,

but you need a M-- you can-- in double quotes-- and, actually, double quotes, you can type whatever you want. I'm going to type a space, a big M, and end double quotes. Anything within double quotes appears on the surface of the spreadsheet as number formatting, not actually in the cell.

Now I'm going to backspace, backspace. That's the one we're going to use. Click OK. Those numbers look much better. Now I want to highlight the first row with our field names-- Control-B to add bold.

Now I want to come up to the Font group and click my Border button for underline. And there we go. Now I want to highlight all of these numbers and add fill-- white.

And then I want to come to the second column. And we want to do our every other column a different color. So I'm going to use a light blue. Now with that second column highlighted, right-click. And on the mini toolbar, there's our Format Painter for copying format only.

I click it once. Oh, but I don't want to click it once. I'm going to unclick it to turn it off. I'm going to double-click it. That loads up this paintbrush. So I can click, click-- and be sure not

to click down here or up here. You've got to be very exact where you click. And I'm clicking every other column. Once I'm done, I click Escape. And that looks much better. Our table of exact numbers is much easier to read.

Now we want to go over and talk about charts on the sheet, chart one. Now on the chart sheet, here's our data source. There's a pivot table. But before we start making charts, let's jump over to PowerPoint.

We're going to talk about column and bar charts, stacked column and bar, clustered column or bar, line charts, xy scatters, bubble charts, and then there's a couple other topics. Now in the prereq classes, we did a lot, especially in Statistic and Business 216.

We talked about all these different charts. But I want to go over and briefly go through each one of those. Ah, but before we see an example of each one of those-- next slide-- what do charts do? Well, we already know.

They visually portray quantitative data-- number data-- give a quick impression of the numbers so we can see trends and patterns. Effective charts, we know, our rule is no chartjunk. Every single time we make a chart, we look at every element and ask, is this helping to deliver our message?

If the answer is no, we hit the Delete key. Next slide-- most charts come from summarized tables. This is a cross-tabulated table. We simply summarize and then we make a chart. Now not all the time-- sometimes, you take all the data and you plot it, like on an xy

scatter or something like that. But most of the time, we're doing some sort of summarization. You can think back to our frequency distributions for continuous quantitative data, where we are actually counting between an upper and lower limit for classes.

That was a lot of data. And we summarized it-- counting between the upper and lower-- and then made our column chart histograms. Hey, next slide-- we've got to talk about Excel chart terminology. Here's the numbers in our column charts.

The numbers are always called series. The categories-- whether on the horizontal axis or in the legend-- those are called categories. Here's the horizontal axis. Those are categories. Here's the legend.

Those are categories. Column heights in a column chart convey the number. The tallest one is the biggest number. And then there's a chart title. When we open up our Select Data dialog box to choose any range we want, series is always going to mean,

hey, those are the numbers. And the category is going to be the items listed along the horizontal axis. Now we have a few slides coming up for column and bar, stacked column or bar, clustered column or bar. Those we'll come back to, if we need them.

But there are, in essence, the definitions of each one of those charts. Let's just go ahead and make some charts and talk about them. Now the first one is just going to be a simple column chart. Here's sales channel.

Here's the numbers. I click in a single cell. That's a pivot table. Insert-- and we go up to Charts. And I'm going to click the dropdown for column-- 3D-- that's chartjunk.

Don't use it. We're simply going to use two-dimensional. Now here's the elements in our chart. We ask our question, hey, is this chartjunk? Well, for us, we're not going to change it or filter it, so it is.

I'm going to right-click-- Hide All Field Buttons on Chart. We talked about the Delete button earlier. But that's-- this is the one thing in a pivot chart, where you have to say Hide All Field Buttons. Now we can ask the question, do we need this legend? No way-- that's chartjunk.

So I hit Delete. Do we need this title? Well, not the way it currently sits. I'm going to simply notice that it's a solid line and just start typing. I'm going to type, revenue by sales

channel-- you can see up in the formula bar-- and Enter. Do I need the horizontal lines? Hey, and that's an important thing. Notice I tried to click the horizontal line, but it's selecting the whole chart. So you've got to be sure and click inside.

And see those little translucent circles? If you don't like them, you can hit Delete. I'm going to leave them this time-- Control-Z. We actually did a lot with columns earlier in the class. We added the numbers to the top, deleted this, we did all sorts of amazing things.

But the rest of it, we need the columns. This is perfect. I don't need a label down here, because I have the word "sales channel" up here. And the numbers are fine. Hey, that's a column chart.

Now I want to make, from this chart, a bar chart, and compare and contrast. I'm going to click on the edge-- Control-C to copy-- come over to the side-- Control-V. Now I'm going to close Pivot Table Field and right-click-- and right-click this and point to Change Chart Type.

Or you can come up to Design and Change Chart Type. And I simply want to change it over on the left to bar. And all it does is it puts it on its side. This is a bar. This is a column. Now out there in the working world and lots of books,

they call these bar charts interchangeably. But in Excel, this is a column. This is a bar. Now what do both of these charts do? They simply show you differences across categories. That's what column and bar charts do.

Now what's the difference between the bar and the column? Well sometimes, the bar chart will emphasize the differences more forcefully than a column chart. Also, if you have really long labels for your categories, then the bar chart will list them horizontally without wrapping them.

So it's kind of nice to have a label all on one line, whereas, with a column, sometimes they'll wrap it and it won't look neat-- all right, so bar and column. Now we want to talk about clustered column and stacked column or bar. Now let's scroll down.

And I have a second pivot table cross-tabulated. The row headers are months. The column headers are sales channel. The intersection is a calculation made with two conditions or criteria. So it's adding the sales that are in-store sales and in June.

Now we click in a single cell-- just like we did in the chart just a moment ago-- Insert Chart. And we do our same 2D. And look at that. Right out of the box, it's virtually perfect. Now we can ask our question of all of the elements.

Do we need them? We're not going to filter this, so I'm going to come over here and right-click-- Hide All Field Buttons on Chart. I'm going to click on this and Control-1. And I'm going to put it either at the top or the bottom.

I'm going to try the top. And I would like to add a chart title. So I click the green plus-- Chart Title. It's got a solid line, so I simply start typing-- 2014 sales-- and Enter. Now depending on the use of this--

this is pretty obvious here-- I don't think we need to put a label down here. Also, this is probably pretty obvious. Hey, look, our chart is done. Now if you don't like these items in the legend and you'd like to switch the horizontal category

and the legend, you simply come up to Design and Switch Row or Column. You've got to be kidding me. Look at that. Now this chart is called a clustered column. All the columns are clustered for in-stores,

mail order, website. Now I'd like to copy this and paste it off to the side. I'm going to close this-- Control-V-- and I want to change the chart type-- right-click, Change Chart Type. Or come up to Design, Change Chart Type.

And we want to change it to the second one. Notice column is always clustered column. But we want to come here and do stacked column. And when I click OK, slightly different emphasis-- both the same data, but with different emphasis. Now this one we're clearly able to see,

within this sales channel, the comparison for the different columns. So the emphasis is on comparing each one of the month sales for each one of the sales channels. Over here, the emphasis is on the horizontal axis, here, and the height.

We're still allowed to compare the different categories-- the different months in each sales category. But the emphasis here is on the height. I can clearly say-- see that website sales are the highest. Stacked column, clustered column-- and both of these are awesome charts-- virtually out of the box,

with one click for cross-tabulated pivot tables. Now we want to come over to the sheet, charts two, and talk about some other chart types. A line chart-- line charts are great for showing changes over a category, most of the time, a time category. So we have sales in quarters.

And I'm simply going to highlight. And notice, we always have our field names at the top-- even on a dataset we're going to put into a chart-- because these names will actually show up inside the chart. This word, "sales," will be the name of this series of numbers.

So I'm going to highlight-- Insert-- over to line. And a line-- and I'm going to select this one with markers-- and a line simply shows the changes in the number-- up and down with the line over our categories. A single number here, listed on the vertical axis, and our categories along the horizontal.

Now I'd like to, actually, start this, maybe, at like, 500,000. So I'm going to click on the axis-- Control-1-- and there's the min. Actually, I'm going to start it at 400,000 and then Enter. I think I want to also change the number formatting. I do not need those decimals at all.

So I'm going to say Decimal Places-- zero-- and Tab. Now one trick we learned in our table example earlier in the video, is this is 700,000-- 650,000. What if we wanted to hack off these zeroes and have a K? So it would say, 650k. Well, we can do that with number formatting.

In our earlier example for tables, we saw how to put in double quotes, like M for million. But watch this. It's even easier for a thousand. I'm going to highlight this Format Code. And I'm going to do a dollar sign zero, and then a comma,

and then a K. The K is a special character in number formatting that will show up without the double quotes. So when I click Add, just like that, I get my 800k, 750k, et cetera. Now maybe I want to add a space here. I'm going to add a space and then click Add.

That's looking much better. Now one number and a category, when you have a lot-- when you're using a line chart. I'm going to drag this off to the side. Here we have two numbers. We're certainly allowed to have two numbers.

It'll just plot two lines. And, again, all of these labels at the top are important. And we'll see why in just a second. I'm going to go up to Insert-- there's our line. Select the second one and click. Now I'm going to leave that there.

Or no, I'm going to click here-- Control-1-- put it at the top. And then I'm going to delete this. I think it's self-evident what these are-- revenue and expenses. We might have some title about what type of revenue, like the date period.

But we're going to leave it like that. You know, if you don't want to see the horizontal lines, you click on them and delete. I'm going to leave them. Now I would like to go look at the series of numbers. So you can right-click-- select Data.

Or, up in the Design, you can click Select Data. And there it is. Very important to name your column headers or field names, so when you come here and try to add, edit, remove, you can see exactly what it is. Now notice, here's the horizontal axis.

It says, horizontal category. Over here it says, series. Those are the numbers. Click OK. All right, so a line chart-- one number over some category. Now an xy scatter is different, because there's two numbers.

We have an x and a y. X should come first, that way the charting engine will interpret it. y comes second. Anytime you want to see a relationship between two variables like this, x and y, we can use the xy scatter. Insert Chart and there it is.

And dots are for sample data. When you have a model, like our fixed cost variable cost model we built earlier in the class, then we use xy scatter with a line. But here, we definitely want the markers. And there it is.

Oh, what is wrong with this? This is not a good chart. And the main reason is, we have no idea what these numbers are. I'm going to come right up to the green plus and say, Axis Titles. Now I'm going to scoot this out of the way a little bit,

close this, and now, with this selected, I can take the equal sign to shoot up here. Test score-- that's the y. The y is right there and then, Enter. I click on the x-axis title. Equals sign shoots me up there and I get hours studied.

And now I need to change this. And I'm just going to type, is there a relationship, and Enter. Now that's a good-looking chart. Whether or not you want the grid lines, you can decide that. Simply click and delete.

But is there a relationship? We see hours is the x. Test score is the y. So it looks like there could be a line right through here. Next chapter, we'll see how to create that line in a model based on xy scatter.

But notice, it looks like as hours studied increases, the test scores also increase-- a direct relationship. That's an xy scatter chart to represent, visually, our xy data. Now we have another chart we want to look at-- the bubble chart.

Now the bubble chart can take 1, 2, 3 variables. And this last variable will actually plot xy. And then the last variable will be a circle. The bigger the number, the bigger the bubble will be, or the circle. I'm going to highlight field names at the top, xy, and then

our size of our marker. Go up to Insert-- under xy-- and there it is-- chart junk-- there it is. We're going to select that one. I'm going to click on the chart title and type, investment projects, and Enter.

Come up to the green plus. I definitely want axis titles. Equals sign-- that's going to be our risk estimate from 1 to 10. Down here on the x-- equals sign-- and I click on expected rate of return, and Enter. And now we come to the green plus.

And I'm going to go to Data Labels and then click the little arrow and More Options. And over here, I want to say, Bubble Size and Uncheck y. And so now we have the actual amount invested for each one of these projects. Actually, let's click on the labels for our amount

invested and come over to our series icon-- Label Options. Scroll down. I'm going to try and center and then click off to the side. That's looking OK. That might not always work, depending on where the circles are.

Now we do not have a label for that. So I'm simply going to go up to Insert and, right here, on Shape, click the dropdown and get, like, a rectangle or something. And maybe right here, click and drag. Now go up to the formula bar-- equals sign and amount

invested in millions. Click and drag. Maybe I'm going to bring that down here. And so there we have a bubble chart, in essence, to represent 1, 2, and 3 different variables.

Now let's scroll over. And we want to talk about a heat map. Clicking on the sheet, heat, we have some sales reps and their sales for each one of the months. And what we'd like to do is use a heat map. And it is amazingly easy in Excel.

It's not called a heat map, but we go up to Home, Styles, Conditional Formatting, Color Scales. And it will divide into three essential colors. I'm going to click this one, right here. The green is going to be the biggest, all the way to the red is the smallest.

Now we can see the red values, the closer it is to that red, the smaller it is. The orange ones are starting to get bigger. The middle values are the yellow ones. And then the biggest ones are the green ones. 783 is the biggest number.

So it's a way of visualizing the data from biggest to smallest. Now the last topic-- graphical information system-- now we're on the sheet, GIS. And we want to look at a simple example of graphical information system. And all that means is, hey, we have some data

that we want to plot on a map. Now in the textbook, they just briefly talk about it. There's no homework and there's not going to be any requirement for you to do this, because I'm using 2016, and I know most of you don't have Excel 2016.

But we've got to check this out. Zip code population-- we'd like to plot this on a map. So Insert-- and over in 2016, under Tours, there's 3D Map. So I click and, just like that, look at that map. Here's our ribbon tab. Here's our pane where we can drag and drop.

Here's our field list, right here. Let's check this out. Under location, it's already got zip code. You can see something already going on there. And under height, I'm going to click the plus and add Population.

And look at that-- instantly-- because it says, column, up here-- this is actually stack column-- there it is. We can zoom in on our map. Look at that. That is simply amazing. A couple of clicks and we have plotted something right

on our map. We can turn it to the side. Now we can come over and select-- this is clustered columns. There's no difference here, because we only have one series there.

There's a bubble, which is not going to work for us. There is the heat map. And if we adjust this, looking from the top, we can see the scale here. That is simply beautiful. Look-- the darker-- the smaller the area,

as it gets towards the dark orange here, we have larger population. And finally, we can actually color in each one of the zip codes. Look at that. That is amazing.

Here's our scale for color. And there's a lot more to this. You can plot, actually, a time variable, and actually build, and click Play. And it will play how the variable changes over time. All right, so that's an example of graphical information

system. I'm going to go back over to our Excel sheet. That's the last topic in this chapter. Chapter three, right, it's all review for us-- all the data visualization we have done, and all the prereq classes, and in this class, so far.

But we did go over tables, and charts, and some of the important ways to make effective tables. And for charting, we learned the different chart types and when to use them. Hey, that's our last video for chapter three. It's our only video-- video 43.

Next chapters-- chapter four-- we'll get to do linear regression. All right, we'll see you next video.



Video Description

Download file from “Highline BI 348 Class” section: https://people.highline.edu/mgirvin/excelisfun.htm
Learn the basic Guidelines for Visualizing Tables & Charts:
1) (00:13) Topics for video
2) (00:41) When to visualize with a Chart or a Table
3) (02:35): Effective Data Visualization makes our analysis easier for others to “see” and understand
4) (04:05) Edward R. Tufte: Data Ink Ration Rule and “No Chart Junk” Rule
5) (04:49) Data Visualization Golden Rule: No Extraneous Elements in Table or Chart
6) (07:25) Tables Vs. Charts: Tables show Exact Values and allow Exact Comparisons, Charts show Relative Comparisons
7) (08:16) Tables are good when the units or magnitudes are different for the numbers
8) (08:47) Table Design Principles
9) (12:30) Example 1 for implementing Table Design Principles for small table with “less than minimal formatting”
10) (12:45) Remove all formatting with “Eraser” button: Home Ribbon Tab, Edit group, Clear Formats. Keyboard: Alt, H, E, F or Alt, E, A, F
11) (13:48) Borders and Fill and Font Color
12) (14:02) Number Formatting for currency when the unit is implied in Field Name (Header)
13) (14:30) How to present Percentages without Percent Number Format: Times 100 and then Paste Special Values.
14) (16:51) Example 2 for implementing Table Design Principles for small table with “minimal formatting”
15) (17:35) Example 2 for implementing Table Design Principles for big table, where we shade every other column
16) (18:09) Custom Number Formatting for showing numbers in millions
17) (21:27) Overview of Charts
18) (23:12) Terms that Excel Charts use for numbers and categories. Numbers = Series. Categories = labels or criteria.
19) (24:12) Knowing when to use and how to create Column and Bar Charts
20) (27:06) Knowing when to use and how to create “Stacked Column or Bar Chart” and “Clustered column or Bar Chart”
21) (29:55) Line Charts
22) (31:00) Custom Number Formatting for showing numbers in thousands
23) (32:53) Look at Select Dialog Box to change range of cells that Chart points to for numbers and category labels.
24) (33:21) X-Y Scatter Charts
25) (35:15) Bubble Charts
26) (37:39) Conditional Formatting to create heat Map
27) (38:42) Using Excel 2016, see a basic Geographical Information System example that involves taking zip code and population data and plotting it on a map using the 3-D Mapping tool
28) (41:00) Summary and Conclusion
Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.