Functions for Executive Summary
I created this Excel Video Tutorial in response to a viewer who, in her company, is not permitted to use a Pivot Table to create a report. Unfortunately, this happens more often than you might think. Nevertheless, I promised to share my tips and advice. I think that many of this tips will help Excel users at all levels.
Tips Covered in this Video Tutorial
- Use Advanced Filter to Extract a list of unique customer names from a range with @ 4,300 records.
- Later in the lesson, I show you how to extract this list to a different worksheet – a tip that will save you time when preparing your reports!
- How and why to convert a normal range of data into a TABLE in Excel 2007 / 2010 or into a LIST in Excel 2003.
- How to create “Named Ranges” for your key data cells and to use them in your Formulas and Functions.
- How to use the SUMIF, AVERAGEIF and COUNTIF Functions in your summary report.
Keyboard Shortcuts Used in this Tutorial
- Ctrl + T to convert to a TABLE in Excel 2007 / Excel 2010
- Ctrl + L to convert to a LIST in EXCEL 2003
- Ctrl + A to open up the Function Arguments Dialog Box
- Ctrl + Shift + F3 to open the Create Names Dialog Box
- F3 to open the Paste Names Dialog Box
I decided that I will only make this video available on YouTube – I think that you will enjoy the clarity that you get from the High-Definition / Full Screen Mode.
Watch Tutorial in High Definition on YouTube
Follow this link to view this Excel Tutorial in High Definition on my YouTubeChannel – DannyRocksExcels
Visit my new secure online shopping website
I invite you to visit http://shop.thecompanyrocks.com – my new online shopping website.
Watch Video Now
Link to this post!
hi Danny:
sorry if I might have left a comment already on Youtube, but since I’m not sure that worked, I’ll leave the same question here.
Followed your great tutorial, unfortunately I’m not getting the summaries to update as I enter new data in the data input spreadsheet. Went over the methods a few times, and my whole data input range is defined as a table so I’m not sure why it’s not updating.
Thanks for your help
nick
Hi Nick –
From your comments, I imagine that you are appending records to your Excel Table.
In the video that I created for this lesson, I created “Named Ranges” for the current Data Set.
When you want to extend the scope for your named ranges, go into the “Name Manager” dialog Box – on the Formulas Tab of the Ribbon. Select the named range you want to edit and change the right side of the definition to e.g. B2:B1048576 to cover the entire range of cells for that column.
Danny Rocks
The Company Rocks