Welcome to E-DAB video
number 5, data analysis and business intelligence made
easy with Excel power tools. And in this episode, we got
to talk about visualizing data in Excel. Tables, charts, conditional
formatting, and dashboards? You bet. We can do it all in Excel. Even though in the
working world it's common to see a table
of data like this, this is what we really want. For charts, even
though Microsoft offers lots of
chart junk options, we're always going to go simple
and effective with our charts. If we need to visualize our
table data with a heat map, conditional formatting
comes to the rescue. And of course, when we
build our dashboard, we'll use visualizations
when we want a quick impression
and tables of data when we want to compare
precise numbers. Here's the start file that
you can download in the links below the video. There's also the finished file. That's the file completed
after the video, and there are some amazing
PDF notes with everything you need to know in written
form about visualizing data. Here are the topics
for our video. And as always,
below each video-- now, this is Excel Basics
1, but below our video, if you look for that
show more button, you click and there's a time
hyperlink table of contents. If you like what you see,
click that Subscribe button and the bell icon. The definition of data analysis
and business intelligence, convert raw data into
useful information for decision makers. But guess what? Data analysis yields
numbers and visualizations. This is information
presented in a table. This is information
presented visually. Both are the end result
of data analysis. Why tables? Because we want
details of the numbers. Tables of numbers help
make precise comparisons. We can precisely compare
Majestic Beaut and Quad because we have
the exact numbers. Why visualize? A quick visual impression. A picture tells
a thousand words, and we can make relative
comparisons quickly. We may not know the exact
numbers for Quad and Majestic Beat, but we can clearly see
that Quad is much bigger. With visualizations,
we can instantly see Quad is the biggest and
Carlota is the second biggest. Here is a table
with all the detail, but it's hard to see
the pattern or trend. Here's the perfect visualization
for pattern and trends over time, the line chart. We can clearly see
up, up, and we even have a little
legend that reminds us the two high
points correspond to these two promotion efforts. Here's a table, a cross
tab showing month and sales channel. We get all the detail
here, but down here we have two perfect charts in Excel
that articulate a cross tab. Here we can clearly
compare sales channel within each month. Over here, we can
compare the months, and then within the months,
compare the sales channel. Sometimes we mix our tables,
numbers and visualizations. We definitely want a
table with all the detail, but look at this. We visualize top three using
conditional formatting. Over here, we still get
the immediate impact of our visualization, but we
also have some of the detail. Now, rules for
effective visualization. Eliminate extraneous elements
in your visualization that do not help to
deliver your message. Now, there's a guy
named Edward Tufte. He's a world-renowned
visualization expert, and he created the golden rule
for effective visualization. There's actually two parts. The first part is, data-ink
ratio should be high. Here we have lots of
data and less ink. Over here, we have the
same amount of data, but lots of ink. That lots of ink gets
in the way of delivering an effective message. too many lines. The different colors probably
don't help the message, and this is messy. We need to have the same
number of digits for all the numbers in our table. All right, so data-ink
ratio should be high. The second rule
is no chart junk. That is junk. This is simple and to the point. Yes, Microsoft
has always offered these 3D, but what does it do? It distorts the proportions. We don't need all
this crazy color. We don't need warm
colors pushing this out and cool colors pushing it back. We don't need all this clutter. And look at this,
unnecessary repetition. If we have the names here,
I don't want them also in a second location. Over here, we get a
quick visual impression with the names of the products. And over here, if we want to
estimate what the number is, we have our numbers
in thousands. Now here's a chart I
just saw this morning online at a news website. The heights of the column
represent job approval rating, and I kind of like that
since the article is about the current percentage. They put just one
data point at the top. Well, look at this down here. This is kind of inconsistent. Sometimes we have
the whole month. Sometimes we have a three letter
or four letter abbreviation. We probably want
to be consistent. Also, since they're diagonal,
it adds a certain busyness. We might convert
these to three letters and align them vertically. Now, we're going to
start on the sheet T, and we want to talk about
table design principles. We mentioned data-ink
ratio should be high. Horizontal lines are
generally necessary only for separating column
titles from data values. We can see that rule in action. Sometimes we use
lines when we indicate that a calculation
has taken place, like at the bottom
for our column total. In large tables,
light shading can be used to differentiate columns. For example, here's a
finished solution for one of your homework problems. Numbers should be
aligned right, text left. All numbers should have
the same number of digits. You can see up here
we violated that rule. Something so amazingly
common in the business world, and it always amazes me. People love to
center everything. We don't want to do that. Text to the left,
numbers to the right. And we definitely don't
want all the messy decimals. We want to keep it neat. Units Must be indicated either with
number formatting or labels. Here we didn't want
to clutter it up with all the dollar signs,
so we indicate up at the top. And then large numbers may
be rounded to the thousands, millions, or billions. We can see over
here these numbers have been rounded
to the millions, and it's indicated at
the top of the column. Now, on the sheet T,
let's scroll down. And we want to learn a few
cool tricks for formatting. The first thing is we need to
remove all the formatting-- Home, Editing Group. There's this awesome
eraser button, Clear. Clear All would remove all
the content, like delete, and the formatting. What we want is the
second one, Clear Formats. Now, this is such
a common command it's worth learning
the keyboards. Now, this keyboard is from
earlier versions of Excel before the ribbon, and
this is the one after. It is four different
clicks in succession, so you can pick the one
you want, Alt, E, A, F. So that's how quick it is
when you know the keyboard. Now, we want to add
some light fill, and I'm going to highlight
the row above and a column to the side and below. Home Ribbon tab, right
click, uncollapse. From the Paint Bucket,
I'll select a light color. Now let's try some
no formatting to get the same number of decimals. We could use dropdown. We could use the
Dialog Launcher, or we can use the keyboard
CTRL+1 to open up Format Cells. I'd like Number,
comma, zero decimals. Click OK. Already looking much neater. Now, what are we going
to do about these? Well, we can indicate the
percentage at the top, but then I need to convert
the underlying number. Instead of a decimal,
I need it 6.41. Here's a great trick. You can type the number
100 into a cell, CTRL+C to copy that cell. Highlight the numbers. Right click, and here
it is, Paste Special. This dialog box will allow
us to do an operation with that copied number
on the selected numbers. So I'm going to
say, hey, multiply. Now, if I click
OK, it'll multiply, but it will remove
the formatting. So I'm going to say
please, values only. Those two together,
when I click OK, convert the underlying
number to a different number. Now, I would never do
this trick if I was ever going to use those
in calculations, because I have fundamentally
changed the number. But as a final report,
that is a great trick. Now we need a bottom
border, so I'm going to highlight
the column headers. Up here, first
one, bottom border. Now, the column width, I'm
going to try and select all the columns from C all the
way to H. And by changing one, I will change them all. I'm making sure that I
didn't cut anything off. Maybe I want to make
it a little bit bigger. Just boop! Now we have our finished table. Now, we may want
it on this sheet. We may want to put it into a
Word document or a PowerPoint. And I gotta show
you a great trick. I'm going to
highlight CTRL+C. Now I opened up a Word
document, but the same trick will work in PowerPoint. Up to the Clipboard, Paste-- and there it is, Paste Special. Now, this is the Paste
Special dialog box. There was a button
up there for picture. Here's the picture right here. But if you want it linked-- that means if you select that,
if you change it over in Excel, it will update here. I'm not going to do that. I'm just going to
paste it as a picture. Click OK. Now I'm going to click Escape
to turn the dancing ants off. Oftentimes we want to
print reports like this. That's where Page
Setup comes in. Now we can go to Page Layout,
Page Setup, and Dialog Launcher, or you can use
the keyboard, Alt PSP. This is definitely
wider than it is tall, so I'm doing landscape. I don't know exactly how
big it's going to be. I may have to come back
and change this later. Margins. You can adjust them here. I generally just say horizontal. Header or Footer. There's some great
options for printing. I'm going to go not
to the dropdown, but that's a great one there
if you have lots of pages, page one of question mark. I want to go to Custom
Footer, and these icons here and the screen
tips that pop up offer up a bunch of
good footer options. In the center, I'm going to
say date, space, dash, space. And look at that. I want the Sheet Tab Name. Click OK. Now Sheet, we want the
print area to be forced only to this range. And now we want to click OK. Now if we Control
P, look at that. I can see page one of two. I need to reduce the size. We can go to the Page Setup
dialog box from Page Preview. I'm going to say fit to one
page wide by one page tall. Click OK. And there we go. Table formatting rules,
including pasting as a picture and page setup. Now let's go over to
our next sheet, CF. Now we already
saw last video how to create this amazing
cross-tab pivot table report with just a few clicks. But now we want to
add some visualization to show the top five
numbers in green. Now this is a pivot
table, so we can either highlight exactly
the numbers we want, or we can select a single cell
because it's a pivot table object. And conditional formatting is
home ribbon tab, Style Group, and there it is, Conditional
Formatting, the dropdown. And there are a bunch
of built-in features that are just amazing. The one we want is
Top Bottom Rules, and we want Top 10 Items. Now that's the
default formatting. We'll fix that, down, down,
down because we only want five. Click the dropdown,
and here's the one where you have all the options. Click. This is the Format
Cells dialog box. Anything we do in
this dialog box will be applied, but
only when it's true that the number in the
table is in the top five. I'm going to do fill green. That's pretty dark, so
now I'm going to go over to font, font color white. And I'm going to add Bold. Click OK. Now when I click
OK, what happened? Well, this is different than
formatting just in the cells. This is a pivot table. So this smart tag will pop up. Hover your cursor, click
the dropdown arrow, and look at this. There's our And Logical test
that we learned last video. This is summing the sales for
the column month and the sales channel column. So when I click
that, instantly I get my conditional formatting. Now we'll add some data to
this data set in just a second. I'm going to collapse this. For our second conditional
formatting example, we want to create a heat map. That means it's going to,
when I click the button, add some formatting. The top 1/3, biggest
numbers will be blue, the numbers that are in
the middle will be white, and the smallest
numbers will be red. Click in a single cell. Conditional Formatting,
Color Scales. I'm going to select this one
right here, hover my cursor, click, and I'll use
the And Logical test. That means internally
inside the pivot table, not the totals around the outside. And that is a beautiful
way to visualize. Clearly we can see website
sales and in-store sales have the two biggest ones
because they're blue. And poor email coupons, they're
stuck with most of the red. Now there is some hidden
data between G and N, so I'm going to click the
Grouping button very carefully. I'm going to select one cell. And notice there's an
empty cell because I do not want to copy the field
names, but click in one cell, and we're going to
use Control asterisk. That's either asterisk on the
number pad or Control Shift 8. That will always select
the current table or the current range. Control C. Very
carefully I'm going to click on the Date column. Control down arrow to
jump down to the last date in the last record. Click directly below. And now when I Control
V, the new records are incorporated into
the table object. Control up arrow. And now I'm going to
uncollapse, scroll over, and we have our cross tabs
showing top five in green and our cross tabs
showing our heat map. Now we can refresh. Right-click Refresh,
and look at that. Not only do we get new months,
but our conditional formatting updates showing the
top five in green. Over here are heat map updates. So conditional formatting,
we did top five in heat map. A great way to visualize
the numbers in a table. Now let's go over and
we have to talk about column, bar, and pie. Now on this sheet we already
have a pivot table with product and amounts. And we want to compare these
amounts across product. The perfect chart for that
is the column or bar chart. Insert. And over in the chart group,
we have a bunch of choices. We're going to point to the
dropdown for Insert Column. This is chart junk. We're going to either use
the column or the bar. Now we have a choice between
clustered and stacked. That only comes
into play when you have more than one variable,
like in our cross-tab, which we'll do later in the video. So we're going to select the
first one, Clustered Column. I click. We can point to the edge, and
when we see our Move cursor, we can click and drag. Now any time you
have a visualization, you want to look
at every element and ask does it help
deliver a message? Well, our goal is to
compare the differences across the products. Well, the columns
certainly help. The names certainly help. What about this right here? This is called a legend. This will help us when we
have more than one series of numbers. But for us, we don't need it. So when I select it, I
can use the Delete key. These are called field buttons. And although you can use them
to filter, most of the time we will have done our filtering
inside the pivot table. And they're interchangeable. If I use it here
or over here, it will filter both pivot
table and pivot chart. Now I want to not display
those field buttons. So up in the Pivot Chart Tools
Analyze, I see Field Buttons. I'm going to say Hide All. The next chart element
we want to look at are the horizontal grid lines. Most of the time
we don't need them. It's just extra ink that doesn't
help deliver the message. If it's important to be able
to line up exactly whatever number we have over here in the
vertical axis in the column, then we can keep them. I'm going to use the Delete key. Well, that's looking
better already. But we keep going
through all the elements. Do we need a chart title? Well, certainly not
with the word total. That doesn't help at all. Now I do want a
chart title, and I want it to say WindSport Product
Summer Sales with that dollar sign. Now watch this. This is a great trick. Whereas these labels and the
numbers are actually linked, so if something
changes over here, it will automatically update,
this chart title will not. No problem. With the chart title
selected on a solid line, I didn't click inside
with the dotted line. I want to make sure it's solid. Then I can come up to
the formula bar, type in equals sign, and that
text is sitting in cell A1, so I select A1,
and look at that. I'm linking a label in
a chart to a formula. When I hit Enter,
there's my chart title. Of course, if I change
this, it totally updates. Control Z. Do we need these
numbers in the vertical axis? Yeah, we need them. Do we need these words here? Definitely. That's the whole
point to this chart. Now both column, bar, and pie,
they all do the same thing. We use these charts or
visualizations to compare differences across categories. Quickly, I can see that
Quad is the biggest. Looks like Carlota is
the second biggest. Research shows that
the column and bar conveys differences
across categories more effectively than the pie. And in fact, throughout
history, the pie was used a lot. But in the last five to
10 years, the pie is out. Here's an example. Same exact data, but
it's just too hard to tell the differences
between the pie pieces. Much easier to tell
the differences from column heights. Remember, the meaning of this is
to compare differences quickly with a visual impression
across our categories. Now these are
diagonal, and we talked about that chart
we saw online where that makes it kind of busy. But I think we can
still do better. Now I want to copy
the edge of this. Be sure-- with charts
sometimes, it's hard if you're clicking around. If I copy and paste,
it might not work. I want to click on
the outside edge. So I see those round circles
and nothing else selected. Control C. I come over to
the side, and Control V. Now I want to change
the chart type. Pivot Chart Tools Design. There is the button to
change the chart type. Or we can come to the chart,
right-click, and there it is. I want to change the chart type. Over here we can see column. I want bar, and there we go. Click OK. Both charts do the same thing. We're comparing differences
across categories. But the bar chart can do
two things differently than the column chart. The first thing is it
accommodates longer labels, listing them horizontally
rather than diagonally. The second thing that
a bar chart will do is it emphasizes the differences
between the categories more forcefully
than a column chart. And that makes sense because
when we tilt this on its side, it's longer than it is taller. Now I'd like to change the
gap width between the columns and make it smaller. Now I select the columns, and
I'm going to use the keyboard. And guess what? If I'm in the cells and
use the keyboard Control 1, it opens up Format Cells. If I'm in a chart and I have a
certain chart element selected, when I use Control 1, it
opens up the task pane. Now if I have different
elements selected, this task pane will
show different options for formatting
that chart element. Now one thing that's kind of
tricky about the formatting task pane for charts
is that sometimes it's hard to find things. These icons at the top
you can click, and then look for what you want. You can use the triangles to
open up and see other options. Now I'm going to click on the
Series option, and what we want is gap width. I'm going to highlight and
change this to 70, 70 and Tab. Now one thing you
want to be careful is the only times you would have
zero gap width is when you have an upper and lower limit, like
you're counting between zero and 50, 50 and 100. Any time you have categorical
or qualitative variables like this, you do not
want zero gap width. But there we go. That is looking pretty good. Now I'd like to show you two
options where we actually add numbers. Let's copy this chart,
Control C, and then below it I'm going to Control V.
Same thing over here. Select Control C,
click in a cell below, Control V. Now
we'll scroll down. Now I'd like to add some numbers
to the top of each column. Now we can add some
elements and change some aspects of our chart
using the task pane. But this green plus in the
upper right of each chart allows us to add and
remove chart elements. And I want to go down to
Data Labels and simply check. And just like that,
I have the numbers at the top of each column. Now there's a problem
here because if I have numbers here and here,
that's unnecessary repetition. So if you want them at the
top, then we'll select the axis and delete. Now let's click on our bar. Let's close the task pane. There's that green plus. And this time, click that
arrow and say Inside End. Well, wait a second. The value difference between the
font color and the fill color is not big enough, so
I'm going to click once on the data labels. Up in the Home ribbon tab,
we'll use font color white. Now I click off to the side. And it looks pretty good
except for that one. Well, if I click on the data
labels, it selects them all. If I click a second time,
it selects just that one. And so now I can add a
different formatting. I'll add black font. I can also use my Move
cursor to click and drag. Click off to the side. That's looking pretty good
except for our chart junk. So I click on the
horizontal axis and delete. We can also resize the
chart if we'd like. Column and bar. The perfect chart when you
want to compare differences across categories. Not the pie. Now on the next
sheet, CT Charts, we want to talk about
a cross-tab chart. I'm going to click inside
our cross-tab pivot table. Insert, over to Charts. And guess what? We use the same either clustered
column or stacked column. We're going to start
with a clustered column. And look at that. It displays two different
columns with a unique list. Here's the month, here's
the sales channel. The clustered column
puts the emphasis on the items in the
legend because we're allowed, for each month, to
compare each one of the items from the legend. One, two, three for June,
one, two, three for July. Clearly we can see
website sales the biggest. For August, it was
in-store sales. Now let's tighten up this chart. I want to remove
the field button. So I come up, Pivot
Table Tools Analyze, Field Buttons Hide All. I don't think I want the grid
lines, so I click and delete. Now let's click on
the legend, Control 1 to open up the task pane. And because we have that
particular element selected, the pane popped up with
the options we want. Now again, that doesn't
always work, so sometimes you really have to hunt around. But there it is. I want to select Top. Now I want to go
to my green plus and I want to add a chart title. With the solid line, I
come up to the Formula bar, or I can simply hit the F2 key. That shoots my cursor
up to the formula bar. I type in equals sign, click
on the cell with my title. There it is. I hit Enter. Now let's scroll over a bit, and
we want to compare and contrast this clustered column,
Control C, click in a cell, Control V.
Right-click, Change Chart Type, and we want to compare
it to stacked column. Click, click OK. And now we have the same exact
data, the same two variables. There's our month. There's our legend. But now the emphasis isn't
on the items in the legend. It's the items in
the horizontal axis. Clearly we can see amongst
all of the months June is the biggest, August
is the second biggest. But we still have the ability,
with our second variable, to compare within each month
each of the sales channels. Stacked column, the emphasis
is on the horizontal axis variable. Clustered column, the emphasis
is on the item in the legend. Now if I copy, Control C, click
below, Control V, right-click, change chart type, we
want to come over to bar. Click OK. These two articulate
a similar message. However, the bar will
emphasize the differences between the categories
more forcefully than the column chart. Click Control C. Down below,
Control V. Right-click, Change Chart Type, bar. Click OK. So either column or bar,
clustered or stacked column. These are the perfect charts
when we have a cross-tab and we want to
visualize that report. All right. Now we want to go over to
the next sheet, line first. Now we're going to
start off on this sheet and look at a couple
simple examples. Then we'll look at a
really cool line chart. Now line charts have one
number on a vertical axis, for example, company sales. The line will go up
and down, showing us the pattern or trend. And the category will be
on the horizontal axis, in our case, quarter. So if I click in a single
cell, Insert Charts. Not the scatter. We want the line. I'm going to use
this one right here. Click on grid lines, delete. Click on the axis, Control 1. I want to start this at
500,000, so I come over to Minimum, Highlight,
type 500,000, and Tab. That's just a simple
example of a line chart showing the number
variable going up and down across a category. We can have two
numbers, but they will be two different
series being plotted across this category. So I click in a single cell,
insert, dropdown for line. Click on Chart Title, delete,
horizontal lines, delete. And we can see with this
example that we're definitely allowed from the legend
to have two series numbers plotted against the
same horizontal category. One last thing
about line charts. Wow, this is a common chart. We want Year on the horizontal
and Company Sales as the line. So we click in a single
cell, Insert Charts. There's the line. And what? The chart totally
interpreted this as a number that it should plot as a line. And that's not what we want. Now I'm to delete the chart
title and the horizontal lines. And here's the number
one trick for charts. The charts will not
always guess right or interpret what you want. Also, sometimes we may want
to add new series of numbers to our chart. So the trick is up in
Chart Tools, Design. There it is. Select Data. Or I simply right-click
Select Data. This Select Data
is the real power. We can add new
series of numbers. We can edit. We can remove. In our case, I want to
select Year and Remove. Here we want to edit and
add the correct year. So I highlight the
numbers, click OK. Select data source. This is the real power. If you have trouble, come
here add, edit, remove, or edit the horizontal axis. Click OK. All right. Now let's go over
to the sheet line. Now we want to build
that line chart we saw at the beginning of the video. Here's our data set. We went ahead and
created a pivot table, adding week down to rows,
revenue down to values. There it is. Week sum of revenue. Now we want to add a line chart. So I click in a
single cell, insert. There is our line. That's looking pretty good. I want to click on the title. And this time, instead
of linking it to a cell, I'm just going to start typing. And as soon as I start
typing, it shoots me up to the formula bar. Then when I hit Enter,
now I've typed a title in. I want to remove these
field buttons, Hide All. Now we'll delete the grid lines. That legend, we'll
deal with that later. Currently as it sits,
it's chart junk. Down here, what
are these numbers? Any time you have
a chart and there are some numbers in
the axis, it better be obvious what
this number means. Up here we have sales, so we can
imply that these are the sales. But down here? We definitely need to come up
to our green plus, Axis Titles, click Primary Horizontal. Now I see the solid line. I type in equals sign. That shoots me up
to the formula bar. I'm going to select Week
inside the pivot table. And when I hit Enter,
now I have the variable that informs the viewer of
what these numbers mean. Now that line chart
is pretty good. It shows us for these weeks
and these weeks, wow, sales were high. Now what I would
like is I would like to inform the viewer of why
we think these sales are high. I can see these numbers
in the pivot table, and actually, they
correspond perfectly to some promotions we did. And our data set has,
for a particular sale, whether it was part
of a promotion. Now I want to highlight the
D column all the way to G. You could see there's some
hidden columns, so right-click, Unhide. Now this column here
represents all the numbers. That's how the line
chart got drawn. But I only want numbers for
those particular promotions, for only a certain
number of weeks. Now the way we do
this in charts is I need a column, a separate column
that I can add to this chart. I need this column just
for Festival of Flight right here and here and
here to show these numbers. But the rest of
them, I don't want them to show up in the chart. Now the way charts
look at columns is if they see a
number, they plot it. But if we have a Not Available-- and in Excel, there's a
Not Available function-- that's the trigger to tell the
chart that that particular line or item shouldn't be included. Now actually, what
we're going to do is we're going to summarize and
add these to the pivot table. But the same thing
will happen over here. N/A's will not be
plotted by the chart. So you ready? We're going to put one of
two things into the column, either, for this record,
the actual number, or when it's not
the promotion, N/A. And the way we put one of
two things into a formula is with the If function. So I'm going to
create a logical test. If, as a relative
cell reference, that promotion item is equal
to Festival of Flight-- and I need to lock
this with the F4 key-- if that's true, then
please put the number in this column as a
relative cell reference. Otherwise, for the
value of False, we put N/A close parentheses. That formula will work. Control Enter, double-click
and send it down. And we have just what we want. The pivot table
and the line chart will interpret N/A's
and the numbers for the promotion correctly. Now we have to do this also
for the Fall Kite Event. So we look at the
promotion column. Are you equal to
Fall Kite Event? F4 comma. If that's true, I
want the number. Comma otherwise, please
show N/A. Control Enter, double-click and send it down. Now we're going to move
this over to the side because the pivot table,
when we click inside, we want to add
these two columns. Festival of Flight. I drag it down to values,
and just like that, well, wait a second. Count? Right when it saw that N/A,
it actually didn't work. But no problem. Right-click. We can change the summary
calculation to Sum, and there we go. That is beautiful. All the N/A's. Oh, we already see
a preview over here. These are all the N/A's. But just between the four weeks
when we ran that promotion, those four numbers
show up in our chart. Not only that, but look at that. We have an informative
legend that says that's part of the
promotion, Fall Kite Event down to values. Right-click, Summarize
Values by Sum. Now I'm going to hide these
two columns, click M and N, right-click, Hide. I can move the chart, extend
the edge, click on the legend, Control 1, top. Let's click on the line. I want this to be bright red. Over in Format Data Series,
there's the paint bucket. There is the line. We'll change it to red. Let's select Fall Kite Event. We'll make this blue. And I'll select the
full line for revenue, and we'll change this to green. Now I'd like to change
the labels here, so I'm going to highlight L
and O, right-click Unhide. Click in the pivot
table column header, and I want to remove
Sum Of, delete. Same over here, delete. Select, Hide. And now our chart
is looking awesome. We have the pattern or
trend for our revenue and an indicator for
our two promotions. All right. Our last visualization
is the XY scatter chart. Now on the sheet XY, we
have an X and Y variable. We want to look at
two variables and see if there is a relationship. Now there's lots of mathematical
and statistical techniques, but all we want to look at
is the visual technique. We click in a single
cell, Insert Charts, and there's the dropdown for XY. Now our example,
we've collected data, so we use the marker version. But if you have a model like
a fixed costs, variable costs model, then you use
the one with the lines. We're going to use scatter. Now the first
problem is we do not have our variable labels,
green plus, Axis Titles. This is the y equals sign. Click on Test Score and Enter. Click equals sign,
there's the X and Enter. Click on Vertical Delete. Click on Horizontal Delete. Click on the title, equals
sign, click on F1 and Enter. And now we can see very quickly
we have a visualization that asks the question, is there
a relationship between hours studied and test score? Well, it does look
like as we increase the number of hours
studied, it does look like the test score goes up. All right. So that's a bunch about
different chart types, when to use them
and how to use them. Now we want to go over
and look at our finale to this video, our
dashboard example. Let's click on CS for
Customer Service Data. Chantel Washington is a
manager, and she's tracking customer service encounters. And here's the data
set she's keeping. Each one of these
rows is a record for her customer encounter. Some of the columns
we're interested in for our dashboard, day
of the week, time, the topic of the meeting,
the meeting type, and the duration of the meeting. If we go over here,
these are the goals that Chantel has
for this dashboard. Chantel Washington
is a manager who works at a large hardware and
lumber store with many customer accounts. However, customer service is
not part of her main duty. She wants to document the
excess customer service duties she is
performing, and she wants to do this by building
a dashboard with a number of tables and charts. She wants a quick
visual impression of the frequency of customer
contact by hour and by day. And she wants the specific
counts for topics of meeting, counts of meeting
type as a percentage, and the average
meeting duration. Chantel also wants to
have the dashboard update easily when she
adds new records, and she wants to be
able to print out this dashboard
about once a week. All right. We're going to go
to the data sheet. We're going to build some
pivot tables and charts here. And then, depending on whether
we want both a pivot or chart, we'll move them or copy them
and place them over here in the dashboard. So we'll start over in our
customer service table. I'm going to click
in a single cell. And I guess we'll build the
frequency by day of week first. Insert pivot table, or
as we learned last video, the keyboard is Alt NV. We want it on an
existing sheet, Location. Build DB. Location will be cell A4. Click OK. Day of Week down to rows. Now we could drag
day of week to count, but I want it to say
Count of Customer, so I'm going to drag Customer. Remember, any field
dragged down that's text will just count how many times
that particular row label criteria occurred
in the data set. So we can see that Wednesday was
27, Sunday one, Saturday two. Now we're not going to use this
table of data in the dashboard. We're going to make a chart. But for all four of the pivot
tables we're going to create, I want to show you how
to add a custom style up in Design for our pivot tables. A couple of these pivot tables
will be used in the dashboard, and I want the pivot table to
follow our table formatting guidelines. So up in Pivot Table Tools
Design, click the More button. And down here, New
Pivot Table Style. We're definitely
going to name this. I called it Pivot Table
Following Table Rules. Now for each one of the
elements in our pivot table, you can click the Format
button and add formatting. Now there's a lot
of elements here, but we're going
to keep it simple. So Whole Table, Format. Over to Fill. I'm going to select white
because I don't ever want to see these gray lines. Click OK. So we have one element
where we've used format. Now we'll go down to the header. That's this row right here. Format. We'll use a light blue. Then I want to follow our table
rules where the headers have a border at the bottom. Click. Also, under Font,
let's add Bold. Click OK. We can scroll down. Grand Total. Format. For the totals, we want a
border, a thin line at the top. That's going to say there's
some calculation on the numbers above. Double line. That's at the bottom to indicate
that this is the final number. Over to Font, Bold. Click OK. Now you can play around
with the rest of these, but these simple
bits of formatting will follow our table rule. Click OK. It doesn't apply it immediately. We have to come back up to More. And there, if we hover,
we can see our style. Now guess what? Right-click. You can modify this any time,
and all the pivot tables in the Excel
workbook will update. And what I want to
do is I want to set this as the default
pivot table style, so any time we create
a new pivot table, this style will be used. Click OK. Now I want to go back
over to the data set, and we're going to
use our keyboard. Alt NV, and I want to put
it on that same sheet, Build Dashboard. And this one's going to
be in cell D4, click OK. Rounded Time, down to rows. We get a unique
list of our hours. Same thing. I'm going to drag
Customer because I want it to say Count of Customer. And look at that. The default style
we created is now applied to any new pivot table. Back over to our data set. Alt NV, Existing. Click Location. Build Dashboard. Scroll over. I'm going to put
this in G4, click OK. We want a count
of topic meeting, so I'm going to drag
it down to rows. There's our unique list. Topic of Meeting down to values. There's our count. Now I want to sort this,
so right-click, Sort. I want to see the biggest
on top, Z to A. New Account is the number one topic. Legal issue, number 15. Compromised Account
Issue, and so on. That will be one of
the tables we actually use in our final dashboard. Back over to CS Data, Alt
NV, Existing Build DB. Let's say J4. Click OK. Now here, this is the one where
we want some calculations based on the meeting type. So we get a unique list, either
Email, Meet in Office, Phone. I'm going to drag Meeting
Type down to Values. It defaults to Count. We could leave it like that,
but she said she wanted percent. Right-click. Show values as percent of
column total, 43% in the office, 38% by email, 18% by phone. Now we're going to drag Duration
down to values, right-click, and we want average. So the aggregate
calculation will be average. Right-click, Number Formatting. Something like number,
two decimals, click OK. Now I want to add
some Wrap Text, so I'm going to
select those cells. Home ribbon tab, Wrap Text. Select K and L. Click and drag
to make them the same width. I'm going to do the same
over here, Wrap Text. And now change the column width. Now we have one, two,
three, four pivot tables. These two have detailed numbers
that we want in our dashboard. But these two, Frequency
Distribution Counting Customer Encounters by Day and Hour,
we want to visualize these. So the perfect chart to
visualize differences across categories is
either column or bar. All right. This is a pivot table. I'm going to click
in a single cell. Insert. Over to Charts. Click the dropdown for
column, and there it is. I'm going to choose a bar. Horizontal bars tend to
emphasize the differences between the categories better. All right. I want to remove
those field buttons. Field Buttons, Hide All. We only have one series of
numbers, so this is chart junk. I'm going to Select and Delete. Select the grid lines, delete. Let's select the title. Equals sign shoots me
up to the formula bar. Select cell A1 and Enter. Let's resize it. Point to the corner,
click and drag. I'm going to move the
chart, select the columns. We use Control 1 to
open up our task pane. And I want to change
the gap width. I'm going to try 50 as
the gap width and tab. Now I'd like to not have
this axis, so I click Delete. Let's put the actual numbers
because we want some detail in our visualization. So come up to the green plus,
Data Labels, arrow, Inside End. Now we will select
the data labels, click once, home ribbon tab. I'm going to choose white font. And there we have our count
of number of customer service encounters by day. Actually, this isn't by day. I clicked on the wrong cell. No problem. I click on the chart. Up in the formula bar, highlight
the formula, equals sign. And this one is D4 and Enter. There we go. We have the right hour, not day. Now I actually want to use this
exact chart, but over here, for count by day. So very carefully in the
white area off to the side, right-click. And there it is. We can save this formatted
chart as a template. Now it's going to save
it to a folder that will allow this chart to show
up in our insert ribbon tab. I'm going to give
it a smart name. I called it Chantel W Chart. Click Save. Now let's click inside our pivot
table for count of customer by day. Insert Chart. And we're going to use
our dialog launcher. Click. Over here on the left, there's
templates, and there it is. Chantel W chart. Click. Click OK. Let's resize it. Uh oh. It looks like the
chart title is missing. But guess what? We connected this to a cell. . That's a formula. The chart title is still
there, and in fact, if you click the green plus,
you can see it's there. So watch this. It's temporarily
invisible, but it is there. Up in our context
sensitive ribbon, I want Format,
Current Selection, and there's a dropdown with
all the different elements in our chart. I want to select Chart Title. Oh, there it is. Now equals sign. And I think this one
gets cell A1 and Enter. Now it looks like this label
down here, if I select it, I can't see it. Notice when I select it
once, it highlights them all, but if I click it
a second time, I can format just this one label. Up to the Home ribbon tab. Font black. I can use my Move
cursor to move it. Now we have one, two,
three, four elements. We want to take
these four elements and move them over
to the dashboard. Now the first element we want
to move is Topic of Meeting. Highlight, Control X over
on the dashboard sheet. I've already created
a label at the top, but I'm going to try and
paste this one in E23, Control V, back over to Build. Select the second pivot table. Control X. Over
on the dashboard, let's see, H30, Control V.
Back over to the dashboard. I'm going to select both charts. Select the first
one, hold Control, click on the second one,
Control X, dashboard. Somewhere up here, Control
V. Click off to the side. I'm going to move this one,
and we will try and fit our first bar chart. Now there's a great trick when
trying to either size or move charts. If I hold the Alt key, it jumps
so that it lines up everything with columns and row. So holding Alt, I
want it to be lined up with E and the bottom of 3. Now we can do the
same thing for sizing. Now I hold Alt click,
and there we go. I've lined it up with G. I'm going to move
this holding Alt, and when I move
it up right there, this one I'm going to select
and size it again using Alt because I actually want it
lined up between F and G. Very carefully down at the
bottom, Alt and click, so it looks like it's lined up. Looks like that one label I'm
going to click once and twice. Change it to black, move it out. Now our next task
is I want this label at the top to have the earliest
and latest date from our data set. To do that, we're going to
create a text formula up here. But we first need to
extract from CS data the earliest and latest date. Well, we can use
the Min function. Come down to CS Data, and we
want to click on the sheet. This is the date column. I hover my cursor
at the top, and when I see my black downward
pointing arrow, I click. I can see up in the formula
bar, it puts the table name, and in square brackets,
the field name. Close parentheses and Enter. We'll do the same for the
Max using the Max function. Click on the sheet at
the top of the column. Click, close
parentheses, and enter. Now when we add new
data to our data set, these dates will update. And I want the label at
the top to also update. So select E2 and F2 to
put it in Edit mode. And we're going to convert
this to a text formula. Equals sign, double quotes. And at the end I'm
going to type of space and then a double quote. And now I want to join this so
that I'll say Chantel Customer Service Encounters From, and
we'll click on the Begin date. Now let's just enter this
and see what happens. And just as we studied
in video number one, dates and time use
number formatting, and formulas cannot see
that number formatting. So that's the underlined
date and time value. No problem. F2. There's a special function
that takes numbers, and for text formulas, it
will add the specified number formatting. The name of the
function is text. It takes a value, which
is some number, comma, and then you have to give it
the custom number formatting. Now custom number
formatting is complicated except for date
number formatting. So double quote, m/d/yy,
end double quote. And that custom number
formatting inside of our text formula
will format that number. Close parentheses and Enter. That is looking good. F2. Now we want to join
using the Join symbol. The word two in double quotes,
and then we'll join it. And we'll use the
text function again on the max date, comma, and
double quotes, the custom number formatting,
and double quote, close parentheses, and Enter. And so this label becomes
part of our dashboard, and when we add new records,
it will totally update. Now Chantel wanted the
ability to print this out. If we do Control P to
open up Print Preview, I could see that it's
not all on one page. Down here I can see
page one of two. So we need to do page setup. Up in Layout, Page Setup. We can use this
Dialog Launcher, or we can use the keyboard, Alt PSP. We want it portrait,
and we want to fit it to one page wide
by one page tall. Margins. We'll do horizontal. Header and footer. Down here is the
preview of the footer. We don't have any footer. We could use the
built-in dropdown, but we don't want to do that. We want to build our
own custom footer. In the center region, I want to
use the code for today's date. So I click on Insert Date. Every time we print
this out, that code says put today's date. Space, dash, space. And I'm going to use the
code that will always put the sheet name in. Click. That will be our footer. Click OK. On the sheet tab, we want
to define the print area. Very carefully, from
D1 all the way to K35. Now when we click
OK and Control P to check Print Preview,
that is looking awesome. Escape. Now the last thing is we
want to test our solution. Go back over to CS Data,
click in the date column, Control down arrow. And when I scroll
down a little bit, we're going to add
new records, and we want to check to see
if everything updates. Highlight, Control C.
Below the Excel table, Control V. The new records are
incorporated into the table. Now we come over
to our dashboard. And up in Data, I want
to click Refresh All. And just like that,
everything is updated. We have a three on Saturday now. Wow, a much bigger
meeting in office average, and Chantel is happy
because now she can simply Control P and print
out her new dashboard with her new useful information. All right. That was an epic video. We talked all about chart junk. We talked about our
table design principles. We learned how to do conditional
formatting for visualizing. We talked about
how column and bars are much better for
visualizing than the pie chart. We looked at our
cross-tab charts. We learned how to do
this amazing line chart. We talked briefly
about the XY scatter. And then we ended it
with Chantel's dashboard. All right. If you like that video, be
sure to click that thumbs up, leave a comment, and subscribe
because there's always lots more videos
to come from Excel is Fun, including
E-DAB number six. We'll learn about the
amazing Power Query. All right. We'll see you next video. [MUSIC PLAYING]
