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

Category:

Education

Tags:

• 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

[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]

@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

[The153Fish]

Cool video! Thank you! I like the way you teach.

[DannyRocksExcels]

@The153Fish

Thank you. I am pleased that you enjoy my teaching style.

Thanks for adding your comment.

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?

[DannyRocksExcels]

@davebatista001

I apologize for the delay in responding - I am just now finding your question.

My suggestion is that a Pivot Table would be best for the question that you ask. With the Pivot Table, you can "Filter" by a date and arrange the fields - or calculated fields as you wish.

Danny Rocks

The Company Rocks

[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

[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

[whamases]

Ahh...double-click!

[whamases]

At 2:11 how did you quickly adjust the column width? (As always, a superbly useful video!)