Archives for July 2012

How to Group People by Age Bracket Using an Excel Pivot Table

Pivot Table for Age Brackets

Pivot Table to Group Age Brackets

In my previous Excel Tutorial, I demonstrated how to use the Group Field command in a Pivot Table to summarize time periods by the Hour. Several viewers wanted to know what other grouping possibilities are available in Pivot Tables. One viewer wanted to know how to group people who responded to a survey by Age Brackets. That is why I created this tutorial!

Use DATEDIF Function to Calculate Age

From my perspective, the DATEDIF() is an invaluable function. However, it is not documented in Excel. Watch how I use its three arguments to calculate the age of each person in the data set.

Group Field by Age

Select a single value in the “Age” Field and then select the “Group Field” Command. In the dialog box, choose the “step-value” for your groups. In this case, I chose the Default Setting of 10 years.

Show Pivot Table Values As

Some of the most powerful Pivot Table tools are found on the “Show Values As” tab of the “Value Field Settings” Dialog Box. Watch me demonstrate how to show each Age Bracket as a Percentage of the Column.

Apply Conditional Formatting to Pivot Table

Take advantage of the greatly improved Conditional Formatting Commands in Excel 2007 and Excel 2010 to focus attention on the key information in your Pivot Table. In this case, I use the “Top 10 Items” rule to apply Conditional Formatting to the two highest Age Brackets- by percentage – for each gender.

Learn Excel Pivot Tables Quickly

Follow this link to learn about the focused 90 minute video tutorials that I have published to help you to really learn how to get the most out of Pivot Tables. Available in versions for Excel 2003, Excel 2007, and Excel 2010.

Watch Tutorial in High Definition

Click on this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels


How to Group an Excel Pivot Table Field to Summarize By Hours

Pivot Table Summary Report By Hour

Pivot Table Summary Report By Hour

After viewing my previous video tutorial, one of my subscribers asked me to demonstrate how to actually summarize a minute-by-minute Excel report into an hour-by-hour report.

My viewer was unfamiliar with Pivot Tables – as are many Excel users – so I created this video to show him – and you how to do this.

Group a Pivot Table Field

Begin by right-clicking a single cell in the Time Field and choosing Group Field. In the dialog box, deselect Month and choose Hour. That’s it – it’s that simple!

Apply Conditional Formatting to the Top 10%

Beginning with Excel 2007, Conditional Formatting has been tremendously improved. Watch how I apply a “Top 10%” Conditional Format Rule with three mouse clicks!

Purchase My 90 Minute Focused Pivot Table Tutorial

Whether you are a novice or an experienced user of Excel Pivot Tables, you will learn some great Tips and Techniques when you purchase my 90 minute video tutorial: “Summarize, Analyze and Present Information with Excel Pivot Tables.”

Follow this link to learn more about my video tutorial for Pivot Tables. Available for ALL versions of Excel as either a DVD-ROM or as a Downloadable Product.

Watch Video Tutorial in High Definition

Click on this link to watch this Excel Video Tutorial in High Definition on my YouTube Channel – DannyRocksExcels


How to Create a Series of 30 Minute Time Intervals in Excel

How Excel Stores TimeOne of my readers tracks data points at 30 minute (half-hour) periods throughout the day. He asked me for some help in streamlining this process.

AutoFill the Step-value for a Series

You can use AutoFill in Excel to create this series. Here are the steps to take:

  1. In two adjacent cells, enter the beginning time value and the time that is 30 minutes later than the beginning time value.
  2. Select BOTH cells and AutoFill the cells in the range that you want to “populate.”

Calculate Decimal Number that Represents 30 Minutes

You can calculate the numeric value for a half-hour (30 minute) period:

  1. Use a Formula to subtract the Start Time from the End Time.
  2. Format the result as a Number with 6 Decimal Places.

In this case, the result is: 0.020833 which you can use as a Constant Value in a Formula.

Keyboard Shortcut to Enter the Current Time

Ctrl + Shift + : (Colon) will “time stamp” a cell.

How Excel Stores Time

While you can Format Time in a variety of ways, Excel must be able to recognize and store Time entries as Numeric entries! This means that correctly entered times align to the right-side of the cell. Time values are stored as a “decimal part of one” day. For example, 12:00 PM (Noon) is stored as 0.50 – 50% of the day has elapsed

Shop at My Secure Online Website

I invite you to visit my secure online shopping website – – to view all of the resources that I offer you to improve your Excel skills.

Watch Tutorial in High Definition

Click on this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

How to Calculate the Calendar Year Quarter for a Date in Excel

Formulas to Calculate a Calendar Year Quarter

Formulas to Calculate a Calendar Year Quarter

After watching my previous video tutorial, “How to Place Dates in a Fiscal Year Quarter,” several viewers requested that I demonstrate how to calculate the calendar year quarter for a date.

This requires “nesting” the MONTH() /3 Function inside either the ROUNDUP() or the CEILING() Function. I break these formulas down step-by-step in this video so that you can see how they work.

Formatting Numbers to Include Text

As a bonus, I demonstrate how to format the formula result cells to include the text “Quarter.” This is really simple to create as a custom format.

Download this Video Podcast from iTunes

You can watch and download this – and many of my – video tutorials for free at iTunes. Here is the link to my iTunes video podcast, “Danny Rocks Tips and Timesavers.”

Watch Video in High Definition

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

Visit my Secure Online Shopping Website

I invite you to visit my online shopping site – – to preview and purchase my training resources. I guarantee your satisfaction 100%. I will refund your purchase if, for any reason, you are not satisfied with my products!



How to Place Dates in a Fiscal Year Quarter

Many organizations operate in a Fiscal Year – for example, April 1 through March 31 of the next calendar year. I get many requests from viewers for help with placing calendar dates into the proper Quarter for their Fiscal Year.

Excel Functions Used in Video TutorialWhile researching a question from one viewer, I cam across a very interesting formula that nests the “undocumented” DATEDIF() Function inside the CEILING() Function. I found this formula in the book, “Formulas and Functions with MS Excel 2003,” written by Paul McFedries. It is a very efficient formula.

What is Covered on the Video

Watch this video tutorial as I break down each step. As a Best Practice, I recommend that you break down a complex formula by working from the “inside out.” I start with the “nested” DATEDIF() function and then demonstrate the results of each formula detail.

Learn More About the “Undocumented” DATEDIF() Function

For various reasons, Microsoft does not offer any help or documentation for the very useful DATEDIF() Function. In fact, it is not even listed as a Function! However, it is very useful and often found in VBA (Visual Basic for Applications) coding. Here, are links to two videos that I created to help you understand how to use DATEDIF():

Learn How to Calculate a Calendar Year Quarter

In response to viewer requests, I created a companion video tutorial -” How to Calculate a Calendar Year Quarter in Excel.”

Resources Available at The Company Rocks

I invite you to visit my secure online shopping website – – to preview the many resources that I offer for helping you to improve your software skills.

Watch Video Tutorial in High Definition

If you would like to watch my video tutorial for “Finding the Fiscal Year Quarter for any Date,” click on this link. I have posted it on my YouTube Channel – DannyRocksExcels

Watch this Video Now