Basic Excel Business Analytics #15: Count Transactions by Web Site Report & Chart

Welcome to Highline BI348
Class Video Number 15. If you want to download this
workbook, BI348Chapter02start or the finished file, click
on the link below the video. In this video we
have a data set. And we’re actually going
to use this data set for the next couple videos to
create frequency distributions and the appropriate charts. Now in this video we want to
look at a categorical variable, Website, and count
how many transactions for each one of the websites. Now this data set is for the
company Boomerang Incorporated. And this company
sells boomerangs online through four
different websites. Now this is categorical data. So we will simply count using
either the COUNTIFS function or a pivot table. And then we’ll make
a column chart. Now let’s go ahead and click
here and Control down arrow. So this is like
26,000 records here. Control up arrow. Now I want to click
in a single cell. And we’ll do pivot tables
first, put it on a new sheet, and then we’ll see
how to do the COUNTIFS functions on that sheet. Now the keyboard is
Alt, N, V as we’ve seen in the earlier videos. But notice, the default
is New Worksheet. Not only that, but any time
you have a dialogue box, if a button is
highlighted, that means the Enter key will enact it. So it’s really Alt,
N, V, and Enter. It puts it on a new sheet. I’m immediately going to
double click this and call this Cat for categorical, FD
for frequency and distribution, and Column Chart, and Enter. Now here’s our field list. Now I’m going to
actually drag this. I’m going to point to
the top and click– and it’s kind of hard to
drag– and drag it over here. Resize this. So ready? We’re going to drag
Website to rows. And then Website down to Values. And look at that. Now I don’t like Row label. So Design, Report
Layout, Tabular. Click right here. And we’ll type
Frequency and Enter. Now we want to drag
Websites a second time, because what we’d also like
to do is do percent frequency. Here we can clearly
see that Amazon is where we sell most
of our boomerangs, then our own base website,, and then some at eBay, and
some at So there’s our
frequency, our count. The total, of course, tells
us how many transactions we have in that data set. Hopefully we don’t
have any empty cells. Actually when we
Control down arrow, we would have bumped in
one of those empty cells, if there was an empty cell. But now we want to do percent
frequency, which is just a fancy way of saying, take each
one of these as the numerator and then divide it on top
of the denominator total. That’ll give us the percentage
of the column total. So guess what? Right click. And oh, the ever so
powerful Show Values As, and we want Percent
of Column Total. And just like that we have
Frequency and Percent Frequency and Enter. Now I want to do the same
calculation with formulas. And in general, the pivot table
is much faster and easier. The only time we want to
switch over to formulas is if the data is
going to change it and we want it to instantly
update without having to use right click, Refresh. Now right click, Refresh,
doesn’t seem like much. And it’s not. But sometimes we need everything
to update automatically, so that’s where
formulas come in. Now one thing about
formulas is that you do not have a feature that
will automatically create a unique list. Well, if you don’t already
have the pivot table, here’s a great trick. You can go up to Data
and Advanced Filter. Now Advanced Filter we’re going
to copy to another location. But notice, with all of
our Data Analysis features, we didn’t have a single cell
selected inside our data set. But no problem,
the Collapse button will let us go to
a different sheet. I’m going to click
on Sales Data. Click in the actual
field name, and then do Control Shift down arrow. And what we want is this
awesome little checkbox, Unique Records Notice it
jumps us back to the sheet. I also need to tell it
where I want that to go. And when I click OK. Boom. There it is. A unique list using
Advanced Filter. Now I’m going to
type Frequency, tab, and Percent Frequency, tab. Highlight the two columns. Click and drag. Right click. And I’m going to grab
that Format Painter to copy just the formatting,
and then select those two cells. And boop, highlight
these two cells, add some formatting
for formulas. And now we want to
click in the cell. And we’re going to use COUNTIFS. Equals COUNTIFS. And guess what? The criteria range is
on a different sheet. But no problem. We’re going to do
a sheet reference. Well, if the range is
on a different sheet, how do we get there? We simply click on the sheet. Oh, look at this. I’m way down at the bottom. I’m going to use the
keyboard Control Home just to jump to the top, A1. And then I can get in the top
cell, Control Shift down arrow. And F4 to lock it. Now you could see
the formula– here’s the screen tip right here
formula– evolving up in the formula bar. Now there’s a crazy
thing that happens when I comma and go back to our sheet. It puts in the
actual sheet name, even though I’m on the sheet. And watch this. Even if you were to delete
this, once you go over to a new sheet,
you’re permanently in sheet reference mode. So we don’t really need
that. so I’m simply going to highlight and delete. And so now there’s our formula. Close parentheses,
Control Enter. Double click and send it down. Now we’re in a slightly
different order than over here. The pivot table unique
list alphabetized it. The Advanced Filter
Unique Records Only gives us the first ones
it runs into, but no problem. If I want it the
same, right click. Notice this is the field names
at the top, records and rows. So I can use the Sort feature. Notice I’m using right click. And I’m going to sort A to
Z. And so there we have it. Now we can simply come below,
Alt Equals to add it up. And Enter. Total. Control Enter,
shift arrow, arrow. And I’m going to use Control
B. And then add some formatting for our formulas. And now we can calculate
our percent frequency. Equals relative cell
reference, one to my left. I use the left arrow. Divided by left arrow,
Control down arrow, F4. Control Enter. And click and drag it down. Now I can click below and
Alt equals Control Enter. Now I can highlight and
add a percentage number formatting with two
decimals showing. Now we have our frequency
percent frequency with either a pivot
table or formulas. Now let’s talk about
making a chart. This is categorical data. So we have Insert. And in the Charts group
for categorical data we have three choices,
Pie, Column, or Bar. Now in recent years, the
trends in data analytics is to use column
charts over pie charts. Not only that,
but research shows that humans can
understand differences between column heights better
than they can pie pieces. So I’m going to click on
the column and use 2D. Oh, wait a second. Look at that. It’s thinking I want
the whole table. I’m going to have to highlight. I only want the labels at the
top and the actual categorical data and count. Now I can go up to column
and use 2D, not 3D. 3D tends to be chart junk. Now one thing about long labels
when you have a column chart, is they don’t fit so well. So that’s where the
bar chart comes in. I’m going to go to
Change Chart Type. And then over here click on Bar. And click OK. Much better. Now we can click on the columns. Control 1. And in 2013 and later
we have this task pane. You can adjust the gap width,
but you do not want to do that. Zero gap width is reserved for
continuous quantitative data. But you can change the gap
width a little if you’d like. I’m going to go up to the
Paint Bucket and Fill. You can change it to
whatever you want. I’m going to vary
colors by point. Now I can close the Task pane. And I actually can
come over here. And in the green
plus, I love this. Data labels. It used to be so much
harder, in earlier versions, to add data labels. That’s looking good there. But now we have chart junk. I want to click down on the
horizontal axis and hit Delete. Now I’d like to come up
and click on the chart title and just type
my chart title, Boomerang Inc 2013 Sales
Frequency by Website. Notice when I type, the
chart label didn’t update. But as soon as I
hit Enter, boom. There we go. Now here’s a crazy thing. I want to change the font. And if I Control 1, it’s
going to open this over here. But there’s no font. And there’s no font over here. You actually have to right
click and point to Font or use the keyboard, Control
Shift F, to get to Font. Or actually just go up to
Home and then Font Size. I’m going to bring
it down to 12. Now one last thing. We want to go over to the tab
called Discrete Quantitative data. And here we have a
frequency distribution. This is a discrete
random variable. There’s gaps between
these numbers. This is counting. Zero people came in
in a one-minute period to our fast food restaurant
at a busy lunchtime. one person, two people,
three people, four people. You could see the biggest
frequency– from our sample data– 266 times
four people came in during a one-minute period. Well, how do we visually
articulate this data? Well, this discrete
random variable goes along the horizontal axis. And, of course, the
heights of the column represent the frequency. But notice, there’s
gap widths here to visually indicate
that no numbers can fit between 3 and 4. There’s a gap. Back here on our Cat FD,
the length of these bars articulates the
frequency or the count. And then the labels
are off to the side. So in this video we talked
about building frequency distributions and percent
frequency distributions for categorical data with
formulas and with pivot tables. And then we saw an
appropriate chart. Now in next video we’ll talk
about frequency distributions and the appropriate charts for
continuous quantitative data. All right. We’ll see you next video.

, , , , , , , , , , , , , , , , , , , , , , , , , , ,

Post navigation

4 thoughts on “Basic Excel Business Analytics #15: Count Transactions by Web Site Report & Chart

  1. Thanks for the videos and the enthusiasm you put in your work.

    How do you do the automatic formating for formulas at 5:07?

    Thanks again 🙂

  2. How I can select invoices with foreign currency and converted in US dollars with out affecting de rest of the invoices en US dollars.
    I love your videos

Leave a Reply

Your email address will not be published. Required fields are marked *