Home Archives for Bins for Frequency Report
August 9, 2012 1 Comment

How to Use Excel’s Histogram Tool and The 80/20 Rule of Analysis

Histogram Charts in Excel

Excel Histogram Charts

This is the Third – and final – episode in my “Creating Frequency Distribution Reports in Excel” series of tutorials.  In this episode, I demonstrate a very powerful – and very easy to use – analysis tool – The Histogram Tool.

Excel’s Analysis ToolPak “Add-in”

This tool is included in the Analysis ToolPak which is an “Add-in” program within Excel. Beginning with Excel 2007, the Analysis ToolPak is automatically included in a basic installation of Excel. However, if you are using an earlier version of Excel or if, somehow, this Add-in has been disabled, I show you how to “activate it.”

Advantages of Using Excel’s Histogram Tool

  • No Formulas to write
  • Results are Numeric Values – not Formulas
  • Chart(s) can be added automatically
  • Multiple Chart Options
  • Pareto Chart Option

What is a Pareto Chart?

Pareto Analysis is commonly known as “The 80 / 20 Rule.” A brief explanation is: Roughly 80% of your sales come from only 20% of your customers or 20% of your products. I like to say, “You can focus on the Vital Few and not on the Trivial Many,” when you use Pareto Charts. As you will see in my video, when you choose the Pareto Chart option, the Histogram automatically sorts the Frequency Field in descending order and charts this field.

Links to All Videos in Frequency Report Series

Improve your Excel Skills – My Video Tutorials

I invite you to visit my secure online shopping website to see the many training resources that I offer. For example, click here to learn about my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

Follow The Company Rocks on Facebook

You can now follow me on Facebook – www.facebook.com/TheCompanyRocks

 

Use the Histogram Tool in Excel [ 7:46 ] Play Now | Play in Popup | Download (2696)
Link to this post!
Filed Under: Excel Charts, Excel Training, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
July 20, 2011 Leave a Comment

Create a Pivot Chart to Present a Frequency Distribution Report

Combination Chart for Frequency Report
Combination Chart for Frequency Report

Quite a few of my viewers requested that I continue my series of creating and presenting a “Frequency Distribution Report” using an Excel Pivot Table. So, here is the new Part 3 in the series!

In this Excel Video Tutorial, I demonstrate – using Excel 2010 – how to create a Pivot Chart for the Frequency Distribution Pivot Table that I created and modified in Parts 1 and 2 of this series.

Key Point: A Pivot Chart Must be based on a Pivot Table Report!

  • Any structural changes that you make to the Pivot Table are reflected in the Pivot Chart.
  • If you “filter” the Pivot Chart, those filters apply to the Pivot Table that is the basis for your Pivot Chart – and vice versa.

Focus on the Chart

As I demonstrate in this video – posted on YouTube – I prefer to focus on one task at a time. I prefer to get the formatting and structure of the Chart “right” before I think about using Filers and other “goodies to add .” This “Best Practice” helps me to get more accomplished in less time!

My bottom line is: “Focus on the task at hand. Eliminate any possible distractions from that primary task!”

A correctly designed Pivot Chart will help you to present your information accurately and interactively! For this presentation, I decided that a “Combination Chart” would be the best way to present the information that I wanted to convey. A “Combination Chart” allows you to have a Primary Axis (Revenue) that uses a Column Chart Type and a Secondary Axis (Invoice Count) that uses a Line Chart Type.

When to Use a Secondary Chart Axis

In this example, there is a wide difference between the two fields that we are charting:

  • Revenue for the Invoices in the “Bin Range” is in “the millions!
  • Count of Invoices in the “Bin Range” falls into the hundreds and low thousands.

Using a “Combination Chart” in this example – (Column Chart for Revenue in Millions) and Count of Invoices (Line Chart for Count in Hundreds) allows you to present the “relationship” between both metrics. I show you step-by-step how to create this combination chart!

 

Watch This Video in High Definition on YouTube

Follow this link to view this video tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Click this link to view Part 1 of the 3 Part series – Creating a Frequency Distribution  Report with Excel Pivot Table

Click this link to view Part 2 of the 3 Part series – Formatting a Frequency Distribution Report with Excel Pivot Table

Link to this post!
Filed Under: Excel Charts, Excel Training, New in Excel 2010, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
July 19, 2011 1 Comment

Formatting an Excel Pivot Table Frequency Report

Show % in Frequency Report

Show % of in Frequency Report

This is Part 2 of my 2-part series on Excel Pivot Table Frequency Reports. A Frequency Report is a great way to answer these questions:

  • How many invoices did we generate for values under $5,000?
  • How many invoices did we gererate for values over $15,000?

In this part, I show you how to format the Frequency Report to make it easier to present the information.

Formatting the Frequency Report

Here are the changes that I made to improve the look of the Frequency Report:

  • Reduced the number of “bins” in the frequency range by changing the “Grouping.”
  • Changed the formatting to make large numbers (millions) easier to read – e.g. as $3.2M
  • Made a copy of the Pivot Table to show values as “% of Total.”

Click on this link to go to Part 1 in this series of video tutorials for Pivot Table Frequency Reports

Learn How to Use Pivot Tables

I have created a series of video tutorials that you can download to learn how to create and modify Excel Pivot Tables. You can choose to download the recording that fits your version of Excel (2003, 2007 or Excel 2010). The cost is $9.95 for the video recording and that includes the Excel workbooks that I used and a step-by-step instructional manual. Follow this link to get more information about these recordings.

Watch this Video in High Definition on YouTube

Follow this link to watch this video tutorial on my YouTube Channel – DannyRocksExcels

Link to this post!
Filed Under: Excel Training, Format Cells in Excel, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
July 17, 2011 2 Comments

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

Link to this post!
Filed Under: Excel Training, Pivot Tables, WebEx Recordings & Webinars Tagged: , , , , , ,