How to Create a Summary Report from an Excel Table

One of my viewers asked for my help in creating an Executive Summary Report - because her manager will not allow her to use a Pivot Table. Here are the tips and techniques that I demonstrate in this lesson:

1) Use Excel's Advanced Filter to Extract a list of unique customer names from a filed with over 4,000 records.
2) Convert a normal range of data cells into an Excel 2007 / 2010 Table (as a List in Excel 2003) - so that range references will update automatically when you append records.
3) Create Named Ranges of Cells that you can use in Formulas & Functions.
4) Use the SUMIF, AVERAGEIF and COUNTIF Functions in the Summary Report.

I invite you to visit my online shopping website - http://shop.thecompanyrocks.com - to view all of my video tutorials.

Danny Rocks
The Company Rocks

---

Closed Caption:

hello and welcome back to tips and time
savers
i'm danny rocks in today's lesson i'm
going to demonstrate several techniques
that you could use when you want to
create a summary report from an Excel
table or an Excel range now I have one
viewer in mind as I create this lesson
the viewer wrote means of Danny
management needs of summary report
periodically during the month however
they refused to allow me to use a pivot
table to create the report can you help
me
well let's examine the data so we have
fields for date
customer unit shipped an invoice total
let's see how many records we have if i
use the keyboard shortcut control and
the down arrow is going to take me down
to the last row that contains data so we
have 4309 records and one row which
contains the headers first order of
business from the customer field we need
to be able to extract the list of the
unique customer values
one way to do that is to go to the data
tab on the ribbon and over here in the
sort and filter group choose advanced
filter now from the advanced filter what
we want to do is we want to be able to
extract this list so we want to copy it
to another location
the unique records only now you may have
automatically the entire day rain
selected so in this case what we want to
do for our list range as we want to
select only column be
now the important got your step here is
remember that when you're going to
extract unique records from the field
begin by selecting the field header for
that column then hold down ctrl shift +
down arrow to take you down to that last
row because we're selecting or extract
the unique records only from one field
we do not need a supply criteria range
now where do we want to copy it - I want
to copy it to the first row in column
if so i'll just type in their f1 and now
when i click ok
there you go I'll move the cursor up
here so I've now use the advanced filter
to be able to extract from column be a
list which is sorted in the sequence
that they appear in the list
all right now what I want to be able to
do is I want to be able to provide a
summary before I do that when I
recommend that you do is over here in
your date arranged first converted to an
Excel 2007 table in Excel 2010 table if
you use in Excel 2003 earlier converted
to a list so with one cell selected in
the original data set come up here to
insert and say insert a table notice
that there's also a keyboard shortcut
ctrl T so now what you do when you click
OK of course you get shading over here
which I'm going to change on the table
tools which is a contextual tab on the
ribbon on the design gallery i'm going
to select the more moderate range for my
table
all right now that we have that one of
the main advantages of the table
remember presently we have four thousand
and 310 records but this data set will
grow as my viewer has said so when you
create a table or in Excel 2003
converter range to a list as you append
records then your definitions for your
formulas will automatically expand our
next step is that I want to be able to
create named ranges for the customer
field and for the invoice total
so what I'm going to do is I'm going to
begin by selecting including the label
and use ctrl shift down arrow so now
i've been i've selected all of the
values in the customer field
i'm going to use a keyboard shortcut
ctrl shift function three to open up the
create names from the selection so the
label that I'm going to use is in the
top row it's called customer so my name
range is going to be called customer
as you'll see if I come over here to the
named range box and there it is there's
the customer field so I base the name of
this field on the customer field level
I'll do the same thing over here for the
invoice total control shift down and I
want to be able to use ctrl shift f3 to
open up create names from the selection
once again from the label that's in the
top rope
click ok and let's again test out so the
named ranges that I've created customer
which you just saw and from the name
drop-down invoice total
all right now i'm going to use them in
the sum if function so i'm going to put
over here
a label called a total let's call this
average and let's call this count
all right now I want to be able to copy
over the formatting and easy way to do
that is to right mouse click use your
format paintbrush and then click over
here to copy the formatting
all right let's use the sum if function
so equals some if and i like to use the
function wizard dialog box so at this
point either click this button up here
the insert function to open up the
wizard or control a to open up the
function arguments dialog box
all right we want to be able to
summarize the sales for only the ABC
company that's why we're using some if
the range that we're going to look in is
going to be that name range call
customer so if you have named a range if
you use the f3 key board shortcut then
it opens up all of the names that we've
created in this worksheet so in this
case it's the aid of the customer field
label of field and the criteria that I
want to look for in the customer field
is for this customer now when i find the
records for the ABC company
then what do I want to some I want to
some the invoice total so f 3 brings up
that range so the invoice total over
there
click OK and now click ok now let's do
some formatting over here let's again
right mouse click i want to use the
currency symbol with zero decimal places
so now i can simply copy this down so
now i have a nice sum for each of the
customers I first extracted the unique
customer names using the advanced data
filter that i use the sum if function so
I looked inside a range and i had
already named that range customer to
find the records that meet this criteria
they are the value which i have over
here in F to the ABC company or in f3
the easy car electronics and then notice
optionally that the third argument is
that when you find those customers in
that range what do you want to do so I
want to some the range over here for the
invoice total
all right now I want to use average if
now if you've been following along and
you using Excel 2003 average if is not a
function that you have available average
it was introduced in Excel 2007
nevertheless i'll show you how we can
produce an average later on in the
lesson so similar i'm going to type
equals average if and I can use the
function autocomplete when i come down
to the function
press the tab key and i use control a
once again the range that I want to look
for is in the customer range f3 brings
up the dialog box so i select customer
and then other criteria that I want to
match i want to match the individual
customer name and then what do I want to
average well I want to average the
invoice total and again remember that's
a named range so f 3 brings that up the
invoice total click ok click ok and
there you go
let's copy over the formatting so again
i use the format paintbrush
and now I'll use the autofill to copy
down the average if to each of the
customers
we also have count if which is slightly
different so equals count if and i use
control a now notice that with count if
there is there are only two required
arguments there is not a third argument
so once again we want to look inside the
named range customer use f 3 customer
and then what
Matt criteria i want to look for how
many times the ABC company in this case
appears in that data range 925 autofill
down and again let's use the thousands
separator zero decimal places
now I mentioned that average if was
introduced in Excel 2007
so if you're using Excel 2003 and you
want to follow along you're able to do
count if you're able to do some if but
if you wanted to be able to get the
average all you have to do is I put in
here
excel what you would do is create a
formula that would take equals the total
which we use some it for / the countif
so they are you going to get the same
number so there is your average and
again we can copy over the formatting
with the format paintbrush and now that
we have that formula up here let's use
the autofill to copy it down
alright so there you've seen how to use
the data tab on the ribbon the advanced
filter to be able to extract a list of
unique names
now in this case when and people who
have done this before so Danny you know
the problem is when I got to extract the
names
I it's saying I can't put it on another
worksheet
yes you can there is a trick so in this
case i want to begin on a worksheet
where I want to create my report
so just have a blank worksheet over here
and begin over here in cell a1 doesn't
matter which one
once again come over here to data
over and sort and filter and the
advanced filter what we want to do is we
want to make sure that we're copying to
another location or the list range
remember is on another worksheet so
let's close this minimizes down to come
over here to the original data
remember that when you are extracting a
list of unique names begin with your
first sell in the selection as the field
header and then i use ctrl shift down
arrow
now let's expand that and then we don't
need criteria where we want to copy it
to is on this new worksheet extract in
cell a1 the unique records only click OK
so you see the trick is that if you
begin over here where you have your data
and say extract it to another worksheet
it will give you an error message the
trick here is the begin on the worksheet
in this case a blank worksheet where you
want to have the data extracted - and
then open up the data advanced and then
follow the steps
copy it to a different location unique
records only and again remember my trick
when you're extracting unique records or
any other field make sure that you
select the field label and then you've
learned how to use the sum function
the average of function which is only
available in Excel 2007 2010 and the
count function very valuable for
producing neat summary reports and it's
typical of the tips that I offer my DVD
rom series the 50 best tips for excel
2007 and I look for you in the next
lesson