How to Create a Summary Report from an Excel Table

hello and welcome back to tips and time-savers I'm Danny rocks and 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 viewer wrote commands of Danny management needs a summary report periodically during the month however they refuse 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 and invoice total let's see how many records we have if I use the keyboard shortcut control and the down arrow it's going to take me down to the last row that contains data so we have four thousand three hundred and nine 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 data range selected so in this case what we want to do for our list range is we want to select only column B now the important gotcha step here is remember that when you're going to extract unique records from a field begin by selecting the field header for that column then hold down ctrl shift and down arrow to take you down to that last row because we're selecting or extracting unique records only from one field we do not need a supply criteria range now where do we want to copy it to I want to copy it to the first row in column F so I'll just type in their f1 and now when I click okay there you go I'll move the cursor up here so I've now used the advanced filter to be able to extract from column B 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 what I recommend that you do is over here in your date of range first convert it to an Excel 2007 table in Excel 2010 table if you use in Excel 2003 or earlier convert it 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 a more moderate range for my table alright now that we have that one of the main advantages of the table remember presently we have four thousand and three hundred and ten records but this data set will grow as my viewer has said so when you create a table or in Excel 2003 convert a range to a list as you append records then your definitions or your formulas will automatically expand alright 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 began I've selected all of the values in the customer field now I'm going to use a keyboard shortcut ctrl shift function 3 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 name range box and it is there's the customer field so I based 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 control shift every to open up create names from the selection once again from the label that's in the top row click OK and let's again test out so the name ranges that I've created customer which you just saw and from the name drop-down invoice total alright now I'm going to use them in the sum if function so I'm going to put over here a label call it total let's call this average and let's call this count alright now I want to be able to copy over the formatting an easy way to do that is to right mouse click use your format paintbrush and then click over here to copy the formatting alright let's use the sum if function so equals sum 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 ctrl a to open up the function arguments dialog box alright 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 called customer so after you've named a range if you use the f3 keyboard shortcut then it opens up all of the names that we've created in this worksheet so in this case it's the 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 sum I want to sum the invoice total so f3 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 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 f2 the ABC company or in f3 the easy 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 sum 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're using Excel 2003 average if is not a function that you have available average if 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'll use ctrl 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 f3 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 countif which is slightly different so equals count if and I'll 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 f3 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 thousand 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 Li if you're able to do some if but if you want it to be able to get the average all you'd have to do is I'll put in here Excel what you would do is create a formula that would take equals the total which we use some if four divided by the count if so there you're 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 and people who have done this before side any you know the problem is when I go 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 in 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 all right the Lestrange remember is on another worksheet so let's close this minimize this down to come over here to the original data remember that when you are extracting a list of unique names begin with your first cell 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 and in this case a blank worksheet where you want to have the data extracted to 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 if function the average if function which is only available in Excel 2007 2010 and the countif function very valuable for producing neat summary reports and it's typical of the tips that I offer in my DVD ROM series the 50 best tips for Excel 2007 and I'll look for you in the next lesson

Channel: Danny Rocks

Views: 1,387,798

Rating: 4.74406 out of 5

Keywords: Excel 2007 Tables, Advanced Filter, Copy Unique Records, Named Ranges in Formulas, SUMIF, AVERAGEIF, Microsoft Excel, Software, Summary, Management, Office, Computer Software, Technology, Training, Business, Information, Data

Id: qPKFlVnzv5c

Length: 12min 6sec (726 seconds)

Published: Mon Sep 19 2011