How to Create a Summary Report from an Excel Table

[@yinmyatmoehtwe]

Thank you from the bottom of my heart for all the invaluable lessons you shared on Excel. Your channel was my go-to guide for school projects, and your teaching style made complex concepts so much easier to understand. It's with a heavy heart that I learned of your passing. You were not just a teacher on KZbin, but a mentor who made a lasting impact on my learning experience. Your legacy will live on through the knowledge you shared. Rest in peace, and thank you for everything. 💔🙏

[@paulvincentvillaplana4303]

10 years later, still really helpful. Thank you, sir!

[@crabbycrabtree8589]

11 years later still super useful. First video solving this problem awesome find.

[@Courtesytoyotaofmorgancity]

As the Internet sales manager I have to do monthly reporting & it takes me forever. I knew there was a way to count in excel. I used the =countif formulas & OMG this is going to save me so much time next month. I'm going to set it to automatically pull just about all if not all of my info & all I'll have to do is print it out. Thanks, Crystal H.

[@MaxRodz]

So helpful for people that don't want to get on the Pivot Table wagon. Thank you!

[@aaronmichael276]

Excellent tutorial. Thank you. I am an online student and my instructor only gives direction not examples. Great job. Keep up the good work...I will keep coming back.

[@Flockwithafury]

This was SO HELPFUL. This is the most informative and easy to follow excel tutorial on this subject. I used this to add up expense amounts by category for deductions on my taxes.

[@surjakishore1816]

Hi Danny. I am not writing specific to this video. I have been watching your multiple videos and find them so simple to understand and implement. Thank you so much for these wonderful tutorials. Very very helpful.

[@miqbal7032]

Thank you sir,please send KZbin videos on advance data analysis using excel 2010

[@whimsicalkw9346]

I have searched for how to do this all morning and finally this video has the best explanation. Thank you so much!

[@yazinomak3616]

This is simple, clear, and very useful tool, basically Rocks!! I'm using this method to analyse my sales and sales mix variance. your video made me able to use the sumif, avgif, countif with greater confidence at work and in my own business. Thank you Mr.Rocks.

[@DannyRocksExcels]

I am so pleased that you found my Excel Tutorial useful! Danny Rocks The Company Rocks

[@ubaidillahmuhammad20]

nice and good job. please put all excel practiced files in the description....

[@zeeshaniqbal1049]

It's very helpful 👍 but my question is that if new company add this table so same formula work or change?

[@dezzyLmc]

Thank you!!!!! I hope this helps me tomorrow when I am trying to revamp a very messy workbook

[@RobinYeasin]

would you tell me how differentaite between 5 companies product price and excel will tell which company price is lower or higer or mention the name

[@MrCfedor]

Danny is a great teacher.

[@JorgeMenendez66]

Thank you! you saved me a lot of time. God bless you for sharing.

[@DannyRocksExcels]

Hi Reginald - Thank you! I am pleased that you enjoyed my tutorial and that you will be sharing with the members of your project team. I appreciate you taking the time to add your feedback. Danny Rocks The Company Rocks

[@zippygti]

I just learned several new things! Format paintbrush, sumiff, avgiff - great! Thank you!

[@Jodaler]

Thank you! Thank you! Thank you! I had to rewatch certain parts to fully understand (the =Sumif especially) but I finally figured it out. You are a lifesaver! Cheers, Marty

[@DannyRocksExcels]

@The153Fish Thank you. I am pleased that you enjoy my teaching style. Thanks for adding your comment. Danny Rocks The Company Rocks

[@jbjones82]

This is what I been trying to figure out thanks! So helpful and I was not falling asleep. Is there a way to have it count how many times a word appears. Such as job was closed for said tech or job is assigned to said tech? That is the last step I need to find.

[@elliotsutherland6141]

Thank you so much! My uni assignment would be uselsess without this!

[@samtadlock3599]

This was helpful but how would you sort this for a specific date range? Like in your example, if you wanted all of invoice totals for January. Is there a way to do this from the report page?

[@mohamadsiddeek7912]

Hi, Siddeek from KSA excellent lecture I really appreciate and I have downloaded more than 28 videos create more videos from Advance filter function. God bless you and Thank for the efforts

[@DannyRocksExcels]

@whamases Thank you! I am so pleased that you enjoy my video tutorials. As, you have just responded - If you double-click the border between 2 columns, the width of the comlum will expand automatically to fit the current contents. Danny Rocks The Company Rocks

[@DannyRocksExcels]

Hello Paul - Yes, Ctrl + F3 works in all versions of Excel for Windows that I have ever used. I use this all of the time to bring up the list of Named Ranges in my Excel workbooks. Danny Rocks The Company Rocks

[@rrw1250]

Danny, Found this video today and must say that I am very impressed with the content and your teaching style. Plan to use these tips on a upcoming project with my department. Thanks Reggie

[@MsSimplicity30]

Thanks for this video, I have a report to create and this is the best explanation I have gotten for the month, A pivot table can't be used for everything

[@nancysimpson1405]

Danny I am totally lost on excel,power point. I have to learn how to use for school. can you recommend a video I can start the basics?

[@danielao6822]

Very helpful and well explained! , now my question is, how can i sumarize the totals... is not letting me.. i want to know how many units shipped in total with all the companies combined

[@danielao6822]

nvm i was able to get it not sure why i wasnt before , i noticed i hat to select all plus one more cell to sum all

[@MyHighlyFavored]

Danny, This is a great video. I follow the steps and create the summary on another sheet but when I add to the original list the summary sheet does not update. Any suggestions?

[@moesadr3342]

I have a dynamic table with many columns and rows and would like to have a Macro that can extract certain columns and filter certain records that can be printed for reporting purposes. Wondering if you might have a template to share. Please advise.

[@nycollassilva3558]

Fyygefggefryvrwq11gvffyuucy11cug

[@simplydoing9596]

sir, this set of procedure is to be done everytime, dataset changes in some situation it need not to repeat above video instructions but most of time, should we waste time by following your set of instruction everytime data changes or with dynamic data coming from ODB/Access/Web Sql etc... ? pls. solve my query

[@nadiagarcia5723]

I liked it but can moved the data to another sheet i'm working with excell 2007, do you think it has something to do.

[@salahhashem4573]

Hello and thanks for taking the time to read this. I have a table with a list of vendors with all the invoices received by them. I applied an advance filter to sum and count every vendor and it worked perfectly (thanks to your helpful video). Now I am encountering a case where few vendors are submitting some of their invoices in USD and some in Euro. I would like to advance filter to segregate them with their respective currency (sum and count) . I tried to select the currency field while re-applying the advanced filter but I failed. can you please help me with this ? and if you have already covered this case in one of your videos , could you please provide me the link ? Thanks a lot.

[@DannyRocksExcels]

Yes, the SUBTOTAL() Function will work and it is quite useful. Thanks for adding your feedback. Danny Rocks The Company Rocks

[@msdamd829]

Thank you very much Danny, you made a very huge help. well done

[@damolah38733]

Sir Dannny, Thank you very much. I have been looking for this function for such a long time. Very much apprecate.

[@terridaleiden9812]

Danny, I need a unique summary sheet that is almost like when you order something and have it go into a shopping cart, so when the order is done, you have a summary of what you ordered. My book has 15 sheets in it, along with pictures and divisions of different parts. Is this something you can help me with if I send a copy of the excel book?

[@DannyRocksExcels]

Terri - I am a "one-man shop.!" As much as I would like to help you, I can't. I do not have the time nor the resources to help you with this. I suggest that you look up Bill Jelen (Mr. Excel) or Mike Girvin (ExcelisFun). They may be able to help you. Danny Rocks The Company Rocks

[@nycollassilva3558]

Gwoboqqdjl, qqrj

[@nycollassilva3558]

@@DannyRocksExcels ghvtfevevrdt111cgv

[@falanshah]

Hi, Nice Video, however, wanted to understand, what if the range goes extended, would this still work taking additional rows added?

[@stevengergi6781]

Great video. I have a question do you know how to I use rage from two or more excel sheets?

[@pamuji_alfaruq]

10 years latter and still relevant

[@breed8246]

Danny still rocks in 2022

[@FunnyVideos-bj6ww]

Hello sir, how are you. Sir I have tried your tutorial. That was all good but if a new customer added in the data then how can we added that customer in existing summary report???

[@anujrana30]

Many Many thanks.... It saves me atleast 1 hour of research :)

[@lescooper389]

Hi This is just what I wanted. However, I've done this in my application but when I add a new customer name this name isn't added to the named range in the table. As I have lots of different customers with different names to be added throughout a period I can't seem to get new customers to display in the customer column. The sales will also need to be dynamic. I'm running Windows 10 and also office 365. I may have misunderstood the tutorial but I'm sure I haven't.

[@gazikoucher2634]

Rocks you are really Rock.......... very useful and save my time, thanks & best wishes.......

[@swaroopguleria5368]

Hello this is what I was looking since last 2 years. Thanks lot.

[@truthseeker5496]

Thank you. How would I be able to get this information between two specific dates I type in? Like I want to search for a vendor, how much that vendor charged me and between what dates I choose?

[@nickphilavong992]

Mr. Rocks, Is it possible for the summary report to automatically populate new unique data if there were hypothetically new 'customers' added that have no been previously accounted for? Or must we re extract the data and start over. Cheers, Nick

[@hariskhurshid7940]

danny its greats ,there is will be data update if we have some changes or add,,,,, in advance option

[@DavidTorres-nm6sx]

HI, What about if you want the sales for the month of January or any other month how would you extract it

[@studdbadboyy1359]

Danny really rockz. That's ridiculous amounts of awesomeness.

[@tombocs1563]

Many appreciate for your video, it helps to solve our problem!

[@D88Realty]

Hello good day, your instructions are good but i want to know how to compute the sum of the visible cells, if filtered the table by dates? and how to automatically update the customer list of summary report if i add a new customer in the customer table. Hope you can help me Thank you.

[@surekarishav]

The Video is Good. However I have one doubt regarding the advance filter. If we add new entries which contains unique customers, the unique customer list created by advance filter doesn't automatically update itself. Help me on that. Thank You.

[@DannyRocksExcels]

Rishnav - I agree - the "Advanced Filter - Copy Unique Values" is clunky. What I recommend: 1) Convert your data into an Excel 2007 + Table. This gives you the ability to quickly "append records" or "add fields." 2) Name this Table. 3) Learn how to use a Pivot Table to quickly update the results. Of course, you will have to "refresh" the Pivot Table to reflect the appended records! Danny Rocks The Company Rocks

[@WenSaiPanther]

Danny Rocks, how do you do that without a pivot table?

[@briandieckman3409]

Somehow I always forget about named ranges... Something I learned from following this tutorial (and then experimenting on my own) : the range to which a name applies in a table will expand along with the table. You can add data to the table without re-defining the name of the range each time! (This is not true of traditional ranges but may be true of "lists" I have not tested it)

[@johnmaldonado1374]

How do I group rows in a MS Excel 2010 spreadsheet and keep them together after sorting? The customer I have does not use MS Project, I have repetitive sub tasks for each task.

[@artl.7531]

Thanks for the video! It's exactly what I needed Danny!

[@DannyRocksExcels]

@Jodaler My pleasure. I am glad that I could help you. Several of the topics in Excel WILL take time - and repetition - to fully understand. I appreciate you taking the time to add your comments. Danny Rocks The Company Rocks

[@XRayRecordings]

Hi Danny this is great although I need a little extra help. How do I work out a way of showing how many units shipped with orders over say 100 only?

[@sarusedain3484]

how to select number only from the particular cell. for example if there is "HOHOUI1GH3T5GR6we" in a cell but i need only 1356. how can it possible in easy way?? Please suggest

[@sheetalpatil1440]

Is this trick of excel can be used for more than 2 lacs data row

[@cynthiaallen2670]

Hi Danny, I work with online course completions and each week. I am looking for a faster way than a pivot table to give me the % complete of each manager's team. My data list a Count Actual, the name of the course (1 to 7 diff courses), person's name, score, complete score status. Then I also list the hierarchy of that person (Ex VP, Dir, Manager). Once I create the pivot, I add a target and a % complete column. My data (online course completions) do not include the hierarchy so I manual input this information. How can I add automatically. Please help and thank you for your time.

[@PrayagDhumal]

Hello Danny, I have seen many of your videos, they are really nice. I have to create a report for five years. I have one worksheet(model) for all calculations which requires multiple inputs and produces multiple outputs. Now I want to run that model for each month's input data and provide output for each month accordingly. I have searched online to find solution but I'm unable to find solution. Can you help me out ? Thanks, Prayag

[@nikunjkrishnarayal]

Awesome, Great Video. Very helpful. Thanks a lot.

[@bcBiz]

Thank you, one of the best simple tutorials

[@DannyRocksExcels]

Thank you Sidney. I appreciate your feedback. I like to say that "I work hard to make it simple for you to learn Excel!" Danny Rocks The Company Rocks

[@DannyRocksExcels]

Scott - My pleasure! Danny Rocks The Company Rocks

[@SwingDanceBand]

When I select advanced in the DATA tab, the entire row is selected, not just the cells that have data. Excel cant run that. What to do?

[@roni5620]

What if you add a new customer, does that page automatically updates the summary?

[@heatherclar]

how do you count the customer sales by date as well as the example shown

[@JosephStevenSAG]

As a novice, I want to do something very simple: add up all the sales to each particular client. I know the answer is somewhere in this video, if I can find it. I'm dancing all around it. However I'm in Excel 16 on Mac, so many hotkey shortcuts --F3--don't work or function differently, and I'm lost when the narrator introduces them (not using traditional menus). Plus doing the Advanced Filtered Unique Entries, I get an error message when trying to copy to another area: Missing or inappropriately named field. Can anyone help?

[@nycollassilva3558]

Elsfmdsdb

[@SoulJoana]

Can it be used for reporting to a fund investor?

[@karynjeneen253]

I am following your directions in Excel 2007. When trying to use the advanced feature to filter data, I am getting an error indicating this command requires at least two rows of data. How do I handle this error?

[@andylawmengtoh3014]

Hi Danny, How if I want to create the summary report in the separate worksheet?

[@mujtabaamir9159]

Thank you so much sir.....very help full tutorial.

[@XRayRecordings]

THANKS SO MUCH DANNY ROCKS!

[@boschista]

Can you pu a link to download your workbook for exercise?

[@marloesundo587]

Hello Mr. Danny, I am Marloe from Philippines, I hope you can help me how to create a table the size of which is dependent on the number of selected items from a series of check box options (say names of cities) to be included in the Table. Example, If have to check name of cities ,say A, B, C, from the check box, then by clicking a "generate table" button, it will generate a 3x3 Origin-Destination Matrix Table (OR 4x4 if we include the label of the column and row by names of the city A, B & C) automatically to the next empty worksheet. Thank you very much!

[@tonyhomewood6370]

Danny, How would I count (abstract) the number of shipments made to each customer. (Not the total of Units)

[@emperoy8]

Good job. well done Danny

[@DannyRocksExcels]

@whamases Yes. Another trick is: 1) Use Ctrl + A to select all cells in the workbook. Depending on where the active cell is, you may need to repeat this a 2n time. 2) Double-click any border between any 2 columns and EVERY Column in the current worksheet will expand automatically to fit the width of the current contents. Danny Rocks The Company Rocks

[@davebatista001]

what if i want to update my report by putting a certain date. is it possible it auotomatically update the rest of record?

[@NorhanizaRomzan]

I'm need to analyze a raw data & this is very helpful

[@hassannooh]

كم انت رائع نشكرك من عميق قلبى على شرحك المفيد

[@joihn445]

When I set up the summary on a new worksheet, the table loses the filter tabs and is not a table anymore. Why?

[@michaelmaganzo1934]

hello. could I get a copy of the data for practice purposes

[@shivarajbale]

Yeah man, you saved my time.

[@know1squarter307]

Excellent information,Thanks a lot for posting this vid..

[@PaperTowel1101]

Awesome! Exactly what I needed. Thanks for the video

[@indiraganess4861]

Can you do 1 with cell phone ... using 3 providers..monthly cost.... basic plan... for the year.

[@rujirekb]

Thank you very much, It's easy explanation for me.

[@DNPascall1663]

Excellent tutorial

[@MohdAbdHafizAwang]

Danny, its an awesome video. Great job!

[@rahuladne]

How does "Paste Here" window appears into range selection in Function argument .. Please help

[@DannyRocksExcels]

Rahul - After you have created a "Named Range" in your workbook, you can use the F3 Keyboard Shortcut to bring up a list of named ranges in this workbook that you can use in your formula. Danny Rocks The Company Rocks

[@DannyRocksExcels]

Hi Scott - Thanks for your comment. You have at least two choices when using multiple criteria: 1) Use the =COUNTIFS() Function 2) Use the Database Function =DCOUNT() I have KZbin videos that cover both of these functions. Simply search for them on my channel. Danny Rocks The Company Rocks

[@Ali-uz8qg]

Excellent lesson, thanks.

[@DannyRocksExcels]

If you follow my best practice to "Create and Use Named Ranges" in your formulas, this is easy. Named Ranges can be used on any worksheet in the current workbook by default. The only (small) challenge is to Extract the Unique Names to another worksheet. Too difficult to explain here - simply extract the unique names and copy them to another worksheet. So use Named Ranges with SUMIF(), etc. and you will not have a problem. Danny Rocks The Company Rocks

[@WenSaiPanther]

how do you create a table of based on the Dates without filtering the source data or pivot table?

[@donnaal312]

How do I write the below as one formula?=sumif(date,">12/31/16",Total)=sumif(sales?Person,AB3,Total)I need these both in the same formula.