Use Excel Pivot Table to Determine Frequency Distribution of Invoice Amounts

Frequency Distribution

Frequency Distribution of Invoices

Has your manager ever asked you,  “How many small invoices do we process?” Of course, you do not know that number immediately. However, you can produce the answer within about 2 minutes when you use an Excel Pivot Table.

Amazing?

Group Fields in a Pivot Table

Yes. And, easy to produce thanks to the “Group Fields” tools in an Excel Pivot Table – in any version of Excel!

In fact, I demonstrate how to produce the frequency distribution in:

  • Excel 2010 / Excel 2007
  • Excel 2003

Download My Pivot Table Video Recordings

This give me the opportunity to announce that I have published a series of Video Tutorial Recordings for Excel Pivot Tables – on my WebEx by Cisco website –  that you can download for only $9.95 USD!

Follow this link to get more information and to follow the direct links to download these recordings.

It is so easy to produce this Pivot Table report that you will be amazed – and want to learn more. That is why I have decided to produce a sequel to this lesson. I will post Part 2 tomorrow. In that segment, I will help you to answer your manager’s question – “How many of these invoices do we process each your?”

Just make me one promise! Do not tell your manager how easy it is to produce these reports!

Of course, I am joking about this. But, most people who do not work with Pivot Tables on a regular basis have no idea how easy it is to harness the analytical and presentation power of Pivot Tables. Once you watch these lessons – and download my video recordings – you will know a lot more about Pivot Tables.

Watch This Video in High Definition on YouTube

Click here to watch this video on my YouTube Channel – DannyRocksExcels – in High Definition

Click here to go to Part 2 in this series – Formatting a Frequency Distribution Report for Presentation

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

My Two Most Popular Excel Tutorials on YouTube

YouTube Logo

YouTube - DannyRocksExcels

Three years ago – in 2008 – I started to create Excel Video Tutorials, both for this website and, also, for my YouTube Channel – DannyRocksExcels.

Looking back, I am amazed to see that two of my earliest postings have been viewed over 50,000 times on YouTube!

Most Viewed Excel Videos  on YouTube

  1. The Basics of Excel 2003 – Entering Data – has been viewed 64,725 times to date! Click on this link to watch it on YouTube
  2. Introduction to Pivot Tables in Excel 2003 – has been viewed 50,477 times to date! – Click here to watch this video on my YouTube Channel

Improved Production Values Since 2008

While I may sometimes “cringe” at my production value choices from 2008, I must say that the content of each of these two viewo tutorials holds up nicely!

What I Offer Today!

I have come a long way since 2008 – both in my knowledge of Excel and How to present the Excel Training Options that you can benefit from.

Re: Excel Pivot Tables – Paradoxically, Pivot Tables are BOTH the most powerful tool in Excel AND also, one of the easiest tools to use to analyze and present the information hidden inside your data!

Here is a link to the Excel Video Tutorial Recordings that you can download for only $9.95 USD. I have customized each recording for Excel 2003, Excel 2007 and Excel 2010!

Get my DVD-ROM – “The 50 Best Tips for Excel 2007” for only $29.97!

 

Let me know what you think!

Danny Rocks

The Company Rocks

 

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Three and a Half Ways to Average Excel Data Using Criteria

AverageIf Function in Excel

AverageIf Function in Excel

It is easy to use Excel’s AVERAGE Function to get the average for an entire range of data. But, what if you are asked to produce a report that shows the average for select groups of records – for example, average test results for the female students only; or for the male students only? What approach do you take? What Functions do you use to produce this report?

Three and a Half Ways to Produce this Report

  • SUBTOTALS – Easy to use. Remember to sort your data first using the field that you want to subtotal.
  • AVERAGEIF Function – Very easy to use. Only works in Excel 2007 and Excel 2010.
  • DAVERAGE Function – Will work in any version of Excel. Easy to add or change the Criteria.
  • PIVOT TABLE – Very easy to create. No need to write a formula. Create report with @ 6 mouse clicks!

My Preferred Approach – Create a Pivot Table Report

While Pivot Tables are the most powerful tool in Excel, they are also one of the easiest tools to use in Excel! Why?

  • Because you can create a Pivot Table Report without writing a single formula!
  • You can create this Pivot Table Report with about six clicks of the mouse.
  • You cannot possibly harm your underlying data when you create Pivot Tables!

Download a One Hour- Plus / In-depth Video Tutorial on Pivot Tables – for $9.95

I have created a series of Excel Video Tutorials that you can download for $9.95 US Dollars each. Here is a link to my informational page that gives you the details.

  • Are you Using Excel 2003? – I created a video recording for Pivot Tables using Excel 2003 that you can download for $9.95!
  • Are you Using Excel 2007? – I created a video recording for Pivot Tables using Excel 2007 that you can download for $9.95!
  • Are you Using Excel 2010? – I created a video recording for Pivot Tables using Excel 2010 that you can download for $9.95!

As with all of my products, I guarantee that you will be 100% Satisfied or I will refund your purchase price – no questions asked!

My Extended length Excel Training Video Recordings are hosted at: http://thecompanyrocks.webex.com – I use the secure shopping cart at WebEx by Cisco Systems, so you can be sure that your personal information is safe and secure.

Watch Video in High Definition on YouTube

Follow this link to watch this video tutorial in High Definition mode on my YouTube Channel – DannyRocks Excels

Get My DVD-ROM, “The 50 Best Tips for Excel 2007” for only $29.97

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to Embed an Interactive Excel Workbook from a Sky Drive in Windows Office Live!

For the past two or three months, I have been uploading the Excel Workbooks that I use during my video tutorials to my Sky Drive at Windows Office Live! – www.office.live.com Just this past week, I learned that I can also “embed” those stored files here – in a blog post – on my website! So, here is my first attempt to share an “interactive Excel Workbook” with you.

Embedded Excel Workbook

This Excel worksheet is one of the basic data sets that I use when I demonstrate how to create Excel Pivot Tables. Scroll horizontally or vertically to see the Pivot Table. Notice that you can use the drop-down filters in the Pivot Table!

Click the Icon in the lower right corner of the Excel WebApps frame and you will be taken to my Sky Drive online where you can view or manipulate the worksheet in Full Screen More. Or, you can choose to download this file to your computer to work on it with the full range of Excel commands.

Download Excel Video Tutorial Recordings for $9.95

If you want to learn more about Pivot Tables, click on this link to gather information on my new series of Extended Length (one hour +) Excel Video Tutorials. You can download these videos to your computer for only US $9.95 from my WebEx by Cisco site – http://thecompanyrocks.webex.com – I store all of the Excel Workbooks that I use in the lessons on my Sky Drive online!

Watch This Video Tutorial in High Definition on YouTube

Follow this link to watch my step-by-step instructions to accomplish this task – in High Definition – on my YouTube Channel, DannyRocksExcels.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How a Single Blank Cell Affects an Excel Pivot Table Report

As you will see in this Excel Video Tutorial, having a single blank cell in a data set will greatly affect how Excel Functions and Pivot Tables are designed and calculated.

Single Blank Cell in Data

Single Blank Cell in Data

Imagine, how a single blank cell, in a field of 848 records, can change the way your Pivot Table views the data type (text vs. the expected numeric data type) and subtotal function (Count vs. the expected SUM function).

Seeing is believing!

Preventing a Blank Cell from “Creeping into” your data set

Use the Keyboard Shortcut Ctrl + Down Directional Arrow to go to the last cell that contains data in a column. This is a great way to isolate “stray” blank cells that will have a great impact on your Pivot Tables and other calculations in Excel.

Also, in Excel 2007 and Excel 2010, look for where a “numeric” field is placed by default. A single blank cell will cause an (intended) numeric field to be placed in the Row (text) labels area. Even dragging this field into the VALUES Drop zone does not solve the problem because the COUNT Function will be used to Subtotal this field. This is a “disaster waiting to happen!”

Introducing My Extended Length Excel Video Recordings

This is my opportunity to introduce you to my new, extended-length (one-hour +) Excel Video Tutorials that you can download for only US $9.95!

Here is the link that gives you more information about this exciting new service:

http://www.thecompanyrocks.com/pivot-table-tutorial-videos-to-download-practice-files-instructional-manual-included/

I offer you multiple options:

Let me know what you think!

Let me know which topics you would like me to record for you to download.

Send me an email with your comments and suggestions: danny@thecompanyrocks.com

Watch This Video in High Definition

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

My Extended Length Pivot Tables Video Recordings Available for Excel 2003, 2007 and Excel 2010

On my WebEx site – http://thecompanyrocks.webex.com – I have posted three, one-hour-plus video recordings to help you to learn how to create and customize Excel Pivot Tables:

Download “Free of Charge” Resources for Each Recording

Click on the hyperlinks above to go to a special landing page for each Excel version. From the landing page, you will find links to download the resources that accompany each version:

  • The Excel Workbook – in each version of Excel (2003, 2007, 2010) that I use during the video recording.
  • The Step-by-Step Instructional Manual that I created for each Excel version of my recordings.
    • Available as an Adobe Acrobat PDF or as a Word Document

Very Attractive Pricing for Recordings

I have decided to price each of these recordings – which you can download to your computer – at US$9.95

  • Imagine what you would have to pay to hire a trainer – per hour –  to teach you these Pivot Table lessons!
  • Imagine how many pages in a book you would have to wade through to read about Pivot Tables!
  • Now, imagine being able to:
    • Watch a video demonstration of the steps to take to create or customize a Pivot Table.
    • Read – and make notes in – a written document that follows the steps that I demonstrate.
    • Practice the techniqes that I demonstrate on the video with the actual Excel worksheets that I use.
    • Skip back and forth to the chapter that you want to watch or review on the video recording.
    • Download the recording for the exact version of Excel that you are using.
    • Get all of this for only US$9.95

What You Will Learn on My Video Recordings

I have taken care to organize each of these topics “by chapter” – with starting points and ending points – on the recordings:

  • Create a Pivot Table in the Excel version that you are using.
    • Spotlight the uniqe features of each version
  • Filter and Sort the Fields in your Pivot Table
  • Change the Data Source & Refresh your Pivot Table
  • Use Multiple Functions and Views to Calculate your Pivot Table
  • Create a Calculated (“virtual”) Field in your Pivot Table
  • Group Fields – e.g. turn daily transactions into a Month, Quarter, Year Pivot Table Report
  • Create and Filter Pivot Charts
Download WebEx Recording

Download WebEx Recording

Download My Recordings at WebEx

  • Go to http://thecompanyrocks.webex.com and on the left side of the screen click on “Attend a Session – Recorded Sessions” to find the recording that you wish to download.
  • Register and enter your payment information – this is a secure shopping site.
  • Download the recording – also download the WebEx Recording Player so that you can play the ARF recording.
    • I recommend that you “Save” your recording on the download page. Play it from your directory of choice after it has downloaded
    • From the ARF Player for the WebEx Recording, you can convert the recording to another video format (MP4, AVI, WMV or SWF)

I welcome your feedback. Send me your questions, comments and suggestions for future recordings.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Still Using Excel 2003? Download My Video Tutorial Pivot Tables 2003 to Summarize, Analyze and Present Data

Video Tutorial for Excel 2003 Pivot Tables

Excel 2003 PivotTables Video

By popular demand, I have created an “Extended Length” ( 69 minutes) video tutorial in Excel 2003 that you can download – for US$9.95  “Create Pivot Tables in Excel 2003 toe Summarize, Analyze and Present the Information in Your Data.”

Quite a few of my customers and viewers are still using Excel 2003. Are you one of them? If so, this is welcome news for you! If you can even find a video tutorial for Excel 2003, chances are it was created “ages ago.” I incorporate current video technology to view online or to download  69 minute video tutorial that will teach you everything that you need to know to create, modify and present a PivotTable or PivotChart Report using Excel 2003.

What You Will Learn from this Video Recording

Here are the topics that I cover – each as an individual chapter on the recording:

  • Creating a PivotTable in Excel 2003
  • Filtering and Sorting Fields in an Excel 2003 Pivot Table
  • Refreshing your PivotTable; Changing the Source Data for your Excel 2003 PivotTable
  • Changing the Functions and Type of Calculations in your Excel 2003 PivotTable
  • Creating a Calculated Field in an Excel 2003 PivotTable
  • Grouping an Excel 2003 PivotTable Field (e.g. Month, Quarter, Year from a series of Daily Transactional Data)
  • Creating and Modifying and Excel 2003 PivotChart

Skip to Individual Chapters on the Video Recording

If you have ever purchased a recording of a “live” Excel Webinar, you know that one frustration is: “How do I get to the specific part of the recording that I want to learn or repeat?”

The last thing that I want you to worry about is “getting stuck” with watching my video recording from start to finish! NO! NO! NO!

That is why I recorded this video webinar with you in mind – I created an easy to follow Table of Contents on the recording so tht you can skip to or repeat an indivual chapter or starting point!

I am confident that you will enjoy this feature!

How to Get Started

  1. Go to http://thecompanyrocks.webex.com and click on “Recorded Sessions” to see the list of all of my Recorded Video Lessons on WebEx by Cisco.
  2. Click on this link to open the Video Recording, “Excel 2003 Pivot Tables to Summarize and Analyze Data”
  3. Register for this recording (First Name, Last Name and email address) so that I can assist you quickly if you need help with downloading the recording or accessing the support materials.
  4. Enter your payment information US$9.95 using PayPal or a Credit Card – This is a SECURE WEBSITE.
  5. Download this video recording. I recommend that you: 
    1. Download the WebEx by Cisco ARF Recorder/ Player. Once you have opend the Video Recording, you can convert it to a different format (e.g. WMV (Windows Media), SWF (Flash Format) or MPEG4 (MP4 Format)
    2. Save the File to a Folder on your hard disk drive.
  6. Watch the video recording.

Download the Support Material for this Video Recording

Here is a link to the web page that I created for this video recording. Follow the hyperlinks to view online or download the Excel Workbook that I used in this recording as well as the Step-by-Step Instructional Manual that accompanies this recording.

Give Me Your Feedback

As I develop these “Extended Length” video recordings, I seek your feedback. Tell me what you liked. Tell me what you did not like – any why. Send me your suggestions for future topics for my video tutorial recordings.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Download My 75 Minute Video Tutorial on Excel 2007 Pivot Tables

Topics Covered

Table of Contents for Recording

I have just recorded and published a 75 Minute Video Tutorial titled, “How to Create and Modify a Pivot Table in Excel 2007 to Summarize, Analyze and Present the Information in your Data Set.”

It is published on my WebEx by Cisco site: http://thecompanyrocks.webex.com Follow this link to go directly to this recording.

There is a charge for this recording – US$9.95 In addition to the recording you get:

  • A Step-by-step instructional manual
  • Access to the Excel workbook files that I used for this recording

Not to brag , but I think that this video tutorial is excellent! I spent many hours planning out the exercises, working through the timing of each chapter of the video and practicing my delivery. I am happy with the result. And, more important, I think that you will also be satisfied with what you will learn from this recording!

What You Will Learn on My Recording

My goal is to give you a comprehensive understanding of Excel 2007 Pivot Tables. But to give you the opportunity to learn this in “bite-size chunks” that can replay when necessary. I created realistic exercises to illustrate each topic.  Also notice that I have created a Table of Contents for the recording so that you can go to a specific chapter on the recording. You can stop and start at any point.

How to Download this Recording

  1. Go to http://thecompanyrocks.webex.com or click on this link to go to the main menu.
  2. Click on “Recorded Sessions.”
  3. Click the link for the recording, “Excel 2007 Pivot Tables to Summarize Data.”
  4. Register for this session (First Name, Last Name and email address).
  5. Enter payment information (PayPal or Credit Card). This is a secure site!
  6. Follow the instructions to Download my recording.
  7. Recommended – you can also download the ARF Recorder to view the recording.
  8. At this point you can decide to convert the recording to a different format. E.g. WMV (Windows Media Player), SWF (Flash) or MP4 (MPEG4)
  9. At the end of the recording, you are taken to a web page with links to download the Instructional Manual and to download the Excel Workbook that I used during this recoring.

Give Me Your Feedback

I welcome your comments and suggestions for future video tutorial recordings. You can add your comments below or send them to me via email.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Download My Excel Workbooks and Step-by-Step Instructional Manuals for Free!

Finally! I have figured out a way to allow my viewers to download the Excel Workbooks, Word Documents, PowerPoint Presentations, etc. that I use in my video lessons. For the past few months, I have been able to give you a direct link to download the Adobe Acrobat files that I create. For example:

My problem has been that – Starting in Office 2007 –  the underlying file structure of each program (Excel, Word, PowerPoint, etc.) has changed and it is no longer possible to “Publish to the Web” the worksheets, documents and presentations that I offered in the past. I will spare you the boring details of why and how this has happened, because I have good news for you!

Enter, “The Cloud!”

Cloud Computing

You have probably seen or heard this advertising slogan recently. Here is how I have just discovered how “The Cloud” can work for both you and me:

Initially, I am uploading – and sharing – the workbooks, documents, PDFs, etc. that accompany the extended video recordings that I offer on my http://thecompanyrocks.webex.com site.

This is a new development for me and The Company Rocks. I offer a series of extended length ( 25 minute to 60 minutes) video tutorials.

  • Intro to Pivot Tables in Excel 2007

    Information about this Video Recording

     

  • As I write this posting, I have 4 modules – each offered “free of charge” – for you to view and / or download.
  •  
  •  

Go to http://thecompanyrocks.webex.com to see what I currently offer you!

  • As I gather feedback, activity, statistics, etc. I may extend the range of downloadable content that I create – My decision will depend upon your feedback, so please add your comments below in this posting!
  • My Next Level of Product Offerings 

    I am on a journey to evolve the range and detail of the products and services that I offer you, my loyal viewers and subscribers. I wnat to be responsive to your needs. So, that meanrs that the more feedback that I get from you – my viewers and subscribers – the better I can structure my offerings. I welcome your feedback!

    Based on some great feedback from my viewers and subscribers, I have just begun to create a series of extended length ( 25 to 45 minutes) video tutorials. The initial tutorials will be offered “free of charge.” Other video tutorials will be carry a modest – I might add, aggressive – pricing model! My goal is to give you the product and content that you need at a price that is mutually acceptable! You, and your level of supposrt will tell me what is acceptable – please give me your feedback!

    Extended Video Tutorials Now Online

    You can now view or download – after registering – at WebEx by Cisco – for these video tutorials that I have posted:

    

    I hope that you find these links and videos helpful. Please add your feeback via the comments section below this post.

    Sincerely,

    Danny Rocks

    www.thecompanyrocks.com

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    Video Tutorials for Both Excel 2007 and Excel 2003

    Many of my clients and viewers continue to use Office 2003 Programs (Excel 2003, Word 2003, PowerPoint 2003, etc.) and they have asked me to produce more video lessons that use these programs.

    Finally, I have found the solution!

    WebEx Recorded Sessions

    WebEx Recorded Sessions

    Extended Length Video Recordings on WebEx by Cisco

    I have just started to create a series of extended length, in-depth instructional video tutorials as part of my “Master Business Skills in Minutes!” Series. I am making these recorded video lessons available on my WebEx by Cisco Systems website: http://thecompanyrocks.webex.com

    My goal is to offer a “mix” of recorded video lessons where some are offered “free of charge,” while others will carry a modest fee. Each lesson that I publish will be offered in two versions – one for Office 2007 and a parallel version for Office 2003. Eventually, I will publish versions that use Office 2010.

    Registration is Required – and Simple!

    Each of the lessons – free or paid – will require you to register at WebEx before you can have access to download the video or to view it online. I have made the registration process as simple as possible: all that you need to supply is First Name, Last Name and a valid email address. That’s it! I value your privacy – click here to read or download the Privacy Policy for The Company Rocks.

    Create a Pivot Table with Fewer than 10 Clicks of Your Mouse

    Free Video Lessons on My Website, Video Podcast and YouTube

    I will continue to post free Excel Video Lessons here on my website – www.thecompanyrocks.com and I will include links to my YouTube Channel – DannyRocksExcels  These videos will be short and focused – under 9 minutes!

    What You Get with My WebEx Video Recordings

    The videos that I publish at http://thecompanyrocks.webex.com -are designed to be longer than what I post here on my website or to YouTube. On the “extended length” videos, I will pay much more attention to the details and the “reasons why” aspects of the key steps. With WebEx, I can also offer you the opportunity to download the actual Excel Workbook that I used to create the lesson and give you a PDF of the “Step-by-step” instructions for each lesson.

    I am excited about this new development. I want to hear what you think. Kindly add your comments and feedback in the section below this post.

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn