[MUSIC PLAYING] Welcome to E-DAB
video number three, Data Analysis and
Business Intelligence Made Easy with
Excel Power Tools. And in this episode, we get to
talk about Excel spreadsheet formulas for data analysis. Now, we'll do two main things. We'll talk about
when in the world do you actually use Excel
spreadsheet formulas given that there's so many tools, and
we will compare and contrast the old-school method of
using Excel spreadsheet formulas and the amazing
new-school dynamic array formulas. Now, below this
video in the links, you can download
this Start file. There is also a file with the
same name that says Finished. That has all the
examples from the videos after the video is finished. There is also PDF notes. Now, here are all of our
objectives for this video. And of course, these
objectives are always listed 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. Now, I want to go
over to the sheet, and we're going to
start on y formulas. Here's our data set, and this is
called a cross-tabulated report because the intersecting
cell is adding sales based on the auto Ford and
the sales rep Jaeyoung. Now, this cross-tabulated
report is done with formulas. This cross-tabulated report
is done with a pivot table. Now, how do you decide
which tool to use, formulas or pivot tables? Well, reports built with
Excel spreadsheet formulas update instantly when
the source data changes. Reports built with
pivot tables require that you refresh the report
when the source data changes. Well, wait a second. If that's true, I'm using
formulas all the time. Forget pivot tables. But the problem is, as
we'll see next video, it's much easier to
create pivot table reports than it is to create
formula reports. As an example of the difference
in how the two tools refresh when we see new data, I want
to highlight this down here, and I want you to notice Honda,
Alma, 33,415, Honda, Alma, 33,415. I'm going to copy
with Control-C. And we learned the last
video this is an Excel table. So when I click directly
below and paste, Control-V, instantly
those new records are incorporated into
the Excel table object. My report sees the
new data and updates. But look at this--
the poor pivot table, it's still stuck on 33,415. But no problem. All we have to do is refresh. Right-click anywhere in the
pivot table, point to Refresh, and instantly it
sees the new data. So for each of these tools,
the reason to use it-- formulas update instantly. Reason to use it-- pivot tables are easy,
as we'll see next video. All right. We want to see how to use
formulas to create reports. We're going to go to the next
sheet, Formula and References. Now, here's our data set. And I want to start looking at
formulas with an easy report. I want total sales for
each one of these autos. Now, before we
create our formulas, I want to change
the default setting because, if you
remember last video, if I were to create a
formula and highlight a column in an Excel
table, it gives me table formula
nomenclature, table name and, in square brackets,
the field name. Now, most of the time, for
data analysis, that's fine. But for our formulas, I
want actual cell references. I want E9 all the
way down to E25. So to change the
default setting, I'm going to go to
File, down to Options, in Excel Options
dialog box, Formulas. In the Working
Formulas area, I want to uncheck Use Table
Names in Formulas. Now, most of the
time we have this on because it's very convenient. But in this
particular exercise, I want to look at the
actual cell references. So we uncheck that and click OK. Now, if I were to reference
this whole column, it puts exactly what I want. Escape. Now, our goal is to add,
which means we're summing, and we're summing not
all of the numbers, but only the numbers if the
record is for Chevy, only the numbers if the
record is for Ford. So with H13 selected,
I need some formula that sums if the condition
at the head of the row is found in the data set. Now, we start all formulas
with an equal sign. And since we're summing
if something is true, Microsoft named the
function smartly, SUMIFS. Now, there's three things we
have to put into the SUMIFS-- sum_range, criteria_range1,
and criteria1. Now, each of these is
called an argument. We enter them
separated by commas. Now, sum_range-- we want to
highlight the whole Sales column. Since this is an Excel table, I
point to the top of the header, and when I see my
downward-pointing black arrow, I click. Now, that's exactly
what we want, H9 to H25. However, there are
different types of cell references
in Excel formulas. Right now, if I copy
this formula down to the next row and
then the next row, this range will actually
move one cell down and then another cell down, and
that's not what we want. As I copy the formula,
I want that range locked from E9 to E25. And the way we lock a
range is we use the F4 key. The F4 key puts the
dollar signs in. Those dollar signs
are the secret code that tells the cell
reference, as we copy it, to remain locked on
that particular range. Now, when you do
an absolute range, you want to make sure that
there are four dollar signs. We see the E column and
the ninth row is locked-- that's that cell right there-- and E25-- that's that
cell right there. So no matter where
I copy this formula, it remains locked or absolute. Now I type a comma to
get to the next argument. Now I see criteria_range1. I select above the Auto field,
click, and I want to lock this. So I hit the F4 key. Now I type a comma, criteria1. That's the particular
condition that SUMIFS will use to find
matches in this column and then pick out the numbers
for adding from the Sales column. So criteria1-- I
click on the condition at the head of the row. We do not want
dollar signs here. We want this to be what
is called a relative cell reference. That means, from the point
of view of the formula, as I copy down, where
am I always looking? One cell to the left. Now I close parentheses,
Control-Enter to put the formula in the cell
and keep the cell selected. Now we need to copy it. And the best way to copy it is
to use that little green box in the lower right-hand corner. That's called the fill handle. Now, right now, I see
my selection cursor. That's not the one I want. That's the move cursor. I definitely don't
want that one. I want to hover my cursor. And when I see that cursor,
that's the crosshair cursor, or I like to call it
the angry rabbit cursor. Left-click, drag down, let go. And just like that,
we've copied our formula. Now, any time you copy a
formula, click in the last cell and hit the F2 key
because you need to verify with this
color-coded range finder that the ranges you
wanted locked are locked and the ones you wanted
to move as relative cell references moved as
you copied the formula. And sure enough,
everything is working. Now I'm going to hit
Enter, and we want to add some number formatting. So I'm going to
highlight those numbers, go up to the Home ribbon
tab, over to Number, click the dropdown,
and there it is. I'm going to use currency. Oh, maybe I don't
want two decimals, so I can decrease the decimals. Now we have our report. Now, remember,
these are formulas. So if I change Alma's
first record for Chevy to 43,000, before I
hit Enter, watch there. When I hit Enter,
instantly updates. Now, if I click back in the
top cell and hit the F2 key, this is called an
old-school formula because we had to
carefully consider what type of cell
references we wanted, and then we had to enter it
and copy the formula down. Well, if you have
Office 365, there's something new called
dynamic array formulas. Now, I'm shooting
this in February 2019. Right now, these dynamic array
formulas are only in preview. If you have Office
365 Insider Edition and if you want to just Google
it, it's an easy search. That's the only
version that has it. But Microsoft says that
these new dynamic array formulas will be available
only in Office 365 sometime early in 2019. But why would I show you
something only in preview? Because you're not going
to believe how easy it is compared to these
old-school formulas. Now, another thing
about this solution, we had to know what all the
names of the autos were, and we actually type
these in the cell-- not with dynamic arrays. The first thing we do
for a report like this is we type equals,
and we're going to use one of these new dynamic
array functions called UNIQUE. I can simply highlight
the Auto column and it will deliver a unique
list of every single auto in that column. I don't have to worry
about cell references, and I don't have to
enter it and copy it. I just hit Enter, and
it automatically spills. This is an example
of a spilled array. Now, when you spill
an array, I want you to click in the top cell
and look up in the Formula bar. I see the dark gray font color. If I click in one of the
spilled cells, it's grayed out. That means it's just using
those cells to spill. The formula doesn't actually
live there because, remember, we just entered it into
one cell and it spilled. That means if we want to edit
it, we click in the top cell and hit F2. Now I'm going to use
another dynamic array. I want this as a
sorted unique list. So I'm going to type SORT,
close parentheses at the end, and Enter. That is absolutely amazing. What it means is when we're
creating a report like this, we don't even necessarily have
to have some list of what's in that column. We just use sort and unique
and we get our conditions or criteria for adding. Now, let's see how to use
SUMIFS as a spilled array. Sum_range-- I'm clicking
at the top of that column. And we do not have to worry
about what type of cell references because if SUMIFS
delivers multiple answers, it will automatically spill. So I simply type a comma,
criteria_range, Autos, comma. Now, criteria_1-- whereas
with the old school we clicked on a single cell,
and then when we copied it down, it knew to move, in
the new dynamic array spilled formulas in
criteria1, I just highlight all the conditions. Now, something
interesting happened. Remember, we said the formula
only lives in the top cell. So it's only pointing to the
cell where the formula lives. That pound sign is
the syntax which says, hey, no matter how this
spilled array changes, I'm always going to get
the full spilled array. Now, how does SUMIFS
here know to spill but doesn't spill over here? Well, over here, the
criteria1 argument over here had one single cell. Over here, it has 1,
2, 3, 4 different items all in criteria1. Any time you put multiple items
into the criteria1 or criteria2 argument, it instructs SUMIFS
to deliver four different items. This is called a
function argument, because that's a function
argument, array operation. We put an array of items in
here and it instructs SUMIFS to deliver an array of answers. Close parentheses. And now, of course,
when we hit Enter, it just automatically spills. That is amazing. Now as I said, Microsoft
said sometime in early 2019-- they haven't specified a date. But go out and get
Office 365 because this is the next big thing
in Excel formulas. Now, we do need to add
some number formatting. So I'm going to highlight
that range, Home, dropdown for Currency. Now we have our
two auto reports, and we want to see what
happens to both solutions when we add new data. And we want to notice
there is a new auto here. So I'm going to highlight
this, Control-C, and below the
table-- by the way, when we have an Excel
table and it's expanding, I would never keep anything
down below the table. But for this small example,
I'm going to click in one cell below, and we're going
to watch up here. Control-V. And you
got to be kidding me. Look at that-- the Autos
unique list and sorted. There's Subaru. Formulas totally spill down. Now, these are formulas
that sit in cells. So the formatting isn't
automatically copied down. If you anticipate new records,
sometimes what I like to do is highlight a bunch
of additional cells and add formatting. But in this case,
I'm simply going to click the cell, Home
dropdown, Currency. But our old-school method
did not see that new product. No problem. I'm going to hopefully
spell it right. Tab. Now, actually, that
formula did automatically copy down with the
formula, but that comes from a setting in
Options called Auto Complete. But wait a second. F2. It got all the dynamic ranges
in the table over here. Oh, look at that. I'm a bad typer. I spelled it wrong. So now Subaru and Tab. Now, it's out of order. So I could simply come up
and right-click, Sort A to Z, and now Subaru's
in the right order. All right, let's look
at another example. But for these reports,
we want to count how many sales for each auto. Now, just like there's a
SUMIFS, there's a COUNTIFS. Now, up above here, we
saw this same option. There's one with an S
and one without an S. The one with the S is
the one we want to use. This function used to be
the only option we had, but it's very limited. It can only count or, up here,
only add with one condition. We always want to use
the one with the S because then it
gives us the option to count with one
or more condition and, up here, to add with
one or more condition. Also, whenever you see
your function highlighted in blue from the
dropdown list, you use the Tab key to enter it. Now, criteria1, I
highlight all the autos. I need to lock it,
so I hit the F4 key. That's an absolute
cell reference. Now I type a comma. Click on the criteria
at the head of the row. That's a relative
cell reference. Close parentheses. Control-Enter. We have to copy it down. Then we always have to go to the
last cell and hit the F2 key. We want to verify that all the
cell references are looking in the correct location. Now, think about that. Old school, we had to worry
about cell references. We had to enter it, copy it, and
then verify in the last cell. So Office 365 makes
our life easy-- equals, SORT, Tab, UNIQUE,
Tab, highlight Autos, close parentheses,
close parentheses. I don't have to worry about
what type of cell references. I hit Enter, and it
automatically spills. And I don't even have to go
to the last cell to check it. All right. Now, equals COUNTIFS,
the one with the S-- I'm highlighting the
column, not going to worry about cell references-- comma. I put my condition of Auto in. Oh, but wait a second, I want
to do a function argument array operation, so I click and
drag all the way to the end. Cell reference where the
formula lives, pound, close parentheses-- and when I hit Enter, it spilled
down and I have my report. All right. So we saw adding and counting
with a single condition, old school and new school. Now let's go look
at cross-tab reports where we do an AND logical test. Now, our goal is that we want a
cross-tabulated report showing total sales by auto and
sales rep, average sales by auto and sales rep,
and count of transactions by auto and sales rep. We have three different reports. We'll do it old
school and new school. Now, this example
here will illustrate that the new-school
dynamic array formulas are much easier than our formulas. And next video when
we do pivot tables, we'll see that having to deal
with a cross-tabulated formula is pretty complicated. But again, there are absolutely
some reporting data analysis situations where you
want to use formulas. Now, one of our criteria here
is we are entering data each day and we want the reporting
solution to update instantly when we add that new sales data. So we're going to do formulas. Now, this one is
adding, so we do SUMIFS, the sum range,
all of the sales, F4 to lock it, comma,
criteria_range1. Well, we have two conditions. And because we're doing
an AND logical test, it doesn't matter at all in
which order we put the columns. I'm going to start with
criteria_range1 sales rep. Hit the F4 key, comma. Well, criteria1, that's
Alma all right, but here's the difficult part-- because this is a formula
that we're copying down and then over to the side. In our last example, we
were only copying it down. So what does that mean
for cell references? Well, we have two
directions that we need to consider for
either locking, making absolute, or leaving relative. Now let's think about this. That's H11. Well, as I copy down from
H11 to H12 and then 13, do I want the 11 to move? No, I don't. I absolutely want H11 to
be H11 all the way down. So that means I'm going to
hit the F4 key not one time, because that puts in two
dollar signs, but two times. That puts the dollar sign
just in front of the number. By the way, let's hit F4
again and then F4 again. The F4 key is actually
the merry-go-round key that toggles between the
four different types of cell references. But I'm going to
stop there because I want to stop the 11 from
moving to 12 and then 13. That H, however-- notice
there's the H. Well, when I move from Alma and copy
the formula over to Jaeyoung, I want the H to
move to an I. That's why we do not put a dollar
sign in front of the H. Now, I come to the end,
comma, criteria_range2. There's the Autos. F4 to lock it in all
directions, comma, criteria2. There's the Chevy. This is different than our H11
because G12- there it is, G12. Well, of course, G12,
I want it to move-- notice there's the 12 row. I want it to move to
G13, then G14 and so on. I do not want a dollar sign
in front of the number. But when I copy the
formula from the H column over to the I column, I
do not want G to move. I want G to remain locked
on G. So in this case, I hit the F4 key
1, 2, and 3 times. Lock the column reference
but not the row. And that's our formula. Close parentheses,
Control-Enter. You can either copy it
to the side or down, but you have to
pick one direction. I'm going to copy
it down, let go, re-grab the fill handle
with your angry rabbit, and click and drag to the side. Because we're
copying the formula through a rectangular range, you
go diagonally furthest across and hit the F2 key. We want to verify that
all of the cell references are pointing to the correct
locations, and they all are. That's how to create a
cross-tabulated report with what are called absolute
cell references and mixed cell references. That's a mixed cell reference
because, in this case, column reference is not locked,
but the row reference is-- absolute, mixed with the
column lock, but not the row. Now let's hit Enter. Now we want to see how to do
the same cross-tab report, but with our dynamic
array formulas. Now, I accidentally left
typed-out values here. So I'm going to
create my formula here and we'll learn something
important about dynamic arrays and how they spill. Equals SORT, Tab, UNIQUE,
Tab, and I will select Autos. Close parentheses,
close parentheses. I'm going to hit Enter. Uh oh, that spill error
is when the formula bumps into something it can't spill. I forgot that these
are values there. As soon as I delete them,
it spills correctly. Now we'll come up
here and delete. And in the first cell,
SORT, UNIQUE, Sales Rep, close parentheses,
close parentheses. But watch what happens. Whoops. I need these to not
spill vertically. I need them to
spill horizontally. No problem. F2, an old-school array
function, comes to the rescue. TRANSPOSE-- all TRANSPOSE does
is takes something vertical and converts it to
horizontal or vice versa. Now, the UNIQUE function is
one of the new array functions. New? Been around as long
as I can remember. But they will all work
together to spill horizontally. Equals SUMIFS, the sum_range,
comma, criteria_range, comma. I highlight the spilled array. There's that pound sign. By the way, if
you type these in, you could highlight
these four cells and it would show
up as a regular cell reference like that. And it would work. It just wouldn't be
referencing a dynamic array formula that's being spilled. Comma. We get our Sales Rep column. Comma. There's our spilled array. I love this. Close parentheses and Enter. That is just magic. Now, for average, we
saw COUNTIFS and SUMIFS, but here's AVERAGEIFS. It doesn't say sum_range, like
SUMIFS did, because we still need to put the numbers in. But this time, it's
going to average them. F4, comma, Autos, F4, comma. There's the auto. F4 1, 2, 3 time. Lock the column,
but not the row. I need 20 to move to
21, 22, and so on. Comma, Sales Rep, F4. Actually, I'm going
to forget to F4. Comma, Alma, F4 twice to lock
the row but not the column, close parentheses,
Control-Enter. This time, we'll copy it to
the side and then copy it down. Now, already you
could see the problem. But if you go to the
last cell and hit F2, this is why we do that. We're verifying, are all of
the cell references and ranges pointing in the
correct location? No, they're not at all. What's that green one doing? It's way over here. That green one should
be locked on Sales Rep. So when we see one of the
ranges out of place, Escape, you have to go back up to
wherever the top is and hit F2. There it is. The easiest way to
reselect something inside of a function like this
is to use the screen tip. I click on criteria_range2
to highlight it. Now, with the whole
range selected, I can hit F4 to lock
in all directions. Control-Enter, copy to the side,
double-click and send it down. Divide by zero error. That means there weren't
any because average has a count of zero and
you can't divide by zero. Now, there's a few
ways we can fix this. The easiest way, since this is
a really small data set, is F2. We simply, after the equals
sign, type IFERROR, Tab. IFERROR will take
whatever's in value and run it every single time. When it comes out
to be an error, then I very carefully click at
the end with my I-beam cursor. I type a comma. And then it says,
hey, what do you want me to put in if it's an error? Zero, close parentheses,
Control-Enter. Copy it to the side. And here's a cool trick. Since we're copying
something down and there's stuff all the way
on the left of the formula, I can simply
double-click that fill handle with my angry rabbit. I go to the last
cell and hit F2. I just want to verify that
all of the cell references are working. Now, I already created
the spilled arrays for our condition or criteria. I see my AVERAGEIFS. This is going to spill, so
I simply put the numbers in, comma, criteria_range. There's the auto, comma. There's the reference to
the spilled array, comma. Sales rep, comma, reference
to the spilled array, close parentheses. And when I hit Enter,
that is amazing. F2. We could say IFERROR, comma,
zero, close parentheses. And when I spill it,
that is beautiful. That's the formula
we use if we want to create a report for
counting cross tab. Control-Enter. Copy it to the side,
double-click and send it down. Check that last corner cell, F2. Over here, this is our
spilled array formula. When I hit Enter, just like
that, I have my report. And one last interesting thing-- let's just say I want to
delete all the formulas. If I click in the top cell
for our old-school formulas, I hit Delete. It's just that one cell. You actually have to
highlight them all and hit the Delete key. Now, I'm going to
Control-Z, Z. But when we have a spilled
array, remember, it lives in just that cell. Delete. All right. I'm going to Control-Z. Wow, that was a
pretty epic video all about data
analysis with formulas. Hey, there are some
homework problems. There's one and two
homework problems. The red sheet shows you
the answer, but don't look. Don't look there
till you try it. Let yourself have some
fun and try it first. All right. In this video, we talked
about why sometimes we have to use formulas
and data analysis, and it's pretty simple. If you want your solution
to update instantly, formulas are the only
tool that does that. We did a single-condition
report using old school and new school. We did adding and counting. And of course, we did
our cross-tab reports, adding, averaging, and counting. All right. If you liked that video, be
sure to click that thumbs up. Leave a comment and subscribe
because there's always lots more videos to come
from ExcelIsFun, including next E-DAB number four
all about pivot tables. All right. We'll see you next video. [MUSIC PLAYING]
