Home Archives for Video lessons for Excel
July 16, 2012 1 Comment

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 – http://shop.thecompanyrocks.com – 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 Create a Series of 30 Minute Intervals in Excel [ 6:22 ] Play Now | Play in Popup | Download (2689)
Link to this post!
Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, Tips and Time Savers Tagged: , , , , , , , ,
July 8, 2012 1 Comment

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 – http://shop.thecompanyrocks.com – 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 Calculate the Calendar Year Quarter for a Date [ 5:19 ] Play Now | Play in Popup | Download (1131)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Functions, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers Tagged: , , , , , , ,
July 1, 2012 1 Comment

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 – http://shop.thecompanyrocks.com – 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

 

Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Formulas, Excel Functions, Excel Training Tagged: , , , , , , ,
June 27, 2012 1 Comment

How to Add Summary Information for Excel Workbook Properties

Almost everyone has experienced the frustration of being unable to locate an Excel Workbook on their computer. You can’t remember the name or the location of the Workbook. You waste valuable time searching in vain.

Tags for Document Search

Tags for Document Search

One way to minimize this frustration is to add Excel Workbook Properties that Summarize the content and purpose of the document. By adding Tags, Keywords, Client Names or Project Titles in the Properties Summary you make it easier for your computer to Index and Find your documents.

Fortunately, beginning with Microsoft Office 2007, it is a lot easier to add these properties to an Excel Workbook. In this video tutorial, I show you how to do this.

Displaying Recent Documents

Did you know that you can display up to 50 Recent Documents? Even better, you can “Pin” important documents so that they remain on the list. This is a valuable tool when you need to access important files, for example, once a month! I show you how to do this on the video.

Shop for Additional Resources

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can purchase all of the training materials that I have developed.

Watch Video in High Definition

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

Watch this Video Now

 

Link to this post!
Filed Under: Excel Training, New in Excel 2007, New in Excel 2010, Workbooks and Worksheets in Excel Tagged: , , , , , , , , ,
June 24, 2012 Leave a Comment

How to Create Hyperlinks in Excel

Hyperlink Dialog Box in ExcelOne of my viewers – Ben from the UK – wrote to tell me that he needs to learn how to create Hyperlinks in Excel in preparation for a job interview this week. He asked me that, since he could not find a decent YouTube video on this topic, to create a tutorial demonstrating ow to create Hyperlinks in Excel.

Hey, in these tough economic times, I am happy to help as many people as I can. However, given my time constraints – and, my need to make a living from my video resources – I invite you to purchase one or more of my many resources on my secure online shopping website – http://shop.thecompanyrocks.com

Topics Covered in this Excel Video Tutorial

In this video tutorial, I demonstrate how to:

  • Create a simple Hyperlink to another worksheet in your Excel workbook
  • Create Hyperlinks that “Return you” to your main “Dashboard” worksheet
  • Hyperlink to other programs – e.g. PowerPoint
  • Create hyperlinks that direct a viewer to send you an E-Mail message
  • Hyperlinks to websites

I am the first to admit that the Insert / Edit Hyperlinks Dialog Box is confusing and “too busy” for my tastes. However, in this video tutorial, I demonstrate how to navigate through this Dialog Box and also, how to get te most out of it. For example, how to change the “text displayed” for the Hyperlink while working in the Dialog Box.

Watch  this Video in High Definition

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

View this Totorial Now!

Purchase My Resources to improve your Excel Skills

If you are in a similar situation as Ben – where you need to demonstrate specific knowledge of MS Office Programs Skills – why trust your luck? Rather, improve your skills – and your odds for securing a new job or a promotion!

I invite you to purchase one or more of my down-to-earth video tutorials. Visit my secure online shopping website – http://shop.thecompanyrocks.com

Link to this post!
Filed Under: Excel Basics, Excel Training, Integrating MS Office Programs, Keyboard Shortcuts, Video Response to Viewers Tagged: , , , , , ,
April 20, 2012 Leave a Comment

How to Perform Time Period Calculations in Excel

Time Calculations in Excel

Time Calculations in Excel

Judging by the number of calls that I get from my viewers, working with Time Period Calculations is one of the most problematic challenges in Excel. My goal for this tutorial is to demonstrate how you can easily avoid the frustrations when you calculate elapsed time and total a series of time – e.g. hours worked during the week.

Apply a Custom Format to Formula Cells

When you create an Excel Formula that subtracts a Start Time from an End Time, you are likely to get a result that looks like 2:30 AM. This is not what you want to see! The solution is to apply a Custom Time Format – e.g. h:mm – to the formula cell(s).

Time that “Spans Midnight”

Many people work the “Graveyard Shift.” The start work late in the evening and they end work early the next morning. The problem that occurs when you want to calculate the number of hours worked is that Excel believes that you are performing a “negative time” operation and the result is a cell filled with ######## (Hash Marks). This happens because Excel thinks that both the Start Time and the End Time belong in the same day. To solve this, you could use an IF() Function formula. However, there is a much easier formula to use as I demonstrate in this video. It uses the MOD() Function.

Total Hours Worked

Use the SUM() function to total 8:00 hours worked each weekday. You are expecting to see 40:00 hours worked. So why does Excel return 16:00 hours instead? By default, Excel interprets 24:00 hours as a single day. So it subtracts 24:00 from the 40:00 hours worked to return 16:00 hours. Unless you make a formatting change to the formula cell you are going to have many unhappy and under paid employees.

The solution? Apply the [h]:mm Custom Formatting to the Formula Cells. Amazingly enough, Excel does not include [h]:mm as one of the Custom Formats in the list. So, simply edit one of the other formats and you will now have the correct answer displayed for your formula!

Learn More Tips for Excel

I invite you to visit my secure online shopping website – http:shop.thecompanyrocks.com – to preview all of the resources that I offer including “The 50 Best Tips for Excel 2007” DVD-ROM.

Subscribe to the “Danny Rocks Tips and Timesavers” Video Podcast on iTunes

I offer a free video podcast for many of my video tutorials at the iTunes Store. Follow this link to see all of the Podcasts that I have posted. If you like what you see, I invite you to subscribe to this free service.

Watch Tutorial in High Definition

You can view this tutorial in High Definition on my YouTube Channel – DannyRocksExcels.

 

How to Perform Time Period Calculations in Excel [ 7:55 ] Play Now | Play in Popup | Download (1635)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers Tagged: , , , , , , ,
April 9, 2012 Leave a Comment

How to Roll Up a Summary by Month to Filter an Excel Pivot Table

Filter Using a Roll Up by Month Summary

Filter with a Roll Up by Month Summary

In this Excel tutorial, I respond to a viewer request. He likes the new “Roll Up Summary by Month” feature for filtering a field in an Excel 2007 – 2010 Field. What he finds frustrating – there seems to be no natural way to accomplish this with an Excel Pivot Table.

Natural Language Date Filters in Excel

Before I solve my readers dilemma, I demonstrate how to take advantage of the new “Natural Language” Date Filters that were introduced in Excel 2007. Date Filters allow you to filter records from “Today,” “Last Week,” “Next Month,” etc. They are available for Excel Tables and Excel Pivot Tables. These “Natural Language” Date Filters are a major improvement in Excel!

Group a Field for Pivot Tables

To solve my viewers question, I “Grouped” the original Date Field in his Pivot Table to produce “virtual” fields for “Month,” and “Year.” Now, it is a simple step to filter the “virtual” Month Field to obtain a “roll up” filter for individual months in the Pivot Table. Just select a single cell in the Pivot Table Date Field and choose Group Field. Make your choices in the Grouping Dialog Box and you are “good to go!”

I also show you how to take advantage of the Expand and Collapse Field Commands in a Pivot Table.

In-Depth Video Tutorial for Excel Pivot Tables

At my secure, online shopping website, you can purchase my 90-minute Video tutorial for Excel Pivot Tables. Available for immediate downloading or on a DVD-ROM. Version specific editions for Excel 2003, 2007, and 2010.

Watch Video Tutorial in High Definition

Follow this link to watch this tutorial in High Definition Mode on my YouTube Channel – DannyRocksExcels

 

 

 

How to Create a Roll Up by Month Filter for an Excel Pivot Table [ 5:42 ] Play Now | Play in Popup | Download (1696)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Pivot Tables, Video Response to Viewers Tagged: , , , , , , , ,
March 19, 2012 Leave a Comment

Use Conditional Formatting in Excel to Highlight Cell Values that Match Criteria

Conditional Formatting in Excel

Conditional Formatting in Excel

Conditional Formatting has been available for many years in Excel. However, beginning with Excel 2007, Conditional Formatting got a major boost in its ease-of use and new visualization tools.

In this tutorial, I demonstrate how to use Conditional Formatting to highlight – via formatting – the cells in a range that anser “TRUE” to a set of criteria. For example, is the cell value “Above Average” or is it Greater than the value in a Target Cell.

How Does Conditional Formatting Work?

Conditional Formatting is “dynamic.” If you update the values – or formulas that generate cell values” the cell that receive the Conditional Formatting may change – based upon the criteria that you set as “the condition.”

Versions of Excel Covered

For this lesson, I demonstrate Conditional Formatting in BOTH Excel 2010 and Excel 2003.

Learn More About Conditional Formatting

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to see all of the resources that I offer. This includes my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

Watch Tutorial in High Definition

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

View Tutorial Now

Link to this post!
Filed Under: Conditional Formatting, Excel Training, New in Excel 2007, YouTube Videos by Danny Rocks Tagged: , ,
March 1, 2012 Leave a Comment

How to Protect Cells that Hold Formulas in Excel

Protect Cells w Formulas

Protect Excel Formula Cells

Learning how to Protect the Excel Worksheet Cells that contain Formulas is a three-step process. This process tends to confuse and frustrate many Excel users. Some of the steps seem counter-intuitive when you first begin the process. That is why I created this video tutorial – To demonstrate each of the steps; to remove the confusion.

First, I show you how this is done in Excel 2010 / Excel 2007. Next, I show you how to protect the Formula Cells in Excel 2003.

Key Concept for Protecting Formula Cells

By default, all cells in an Excel Workbook are “Locked!”

So, if you are going to allow a user to input current values in the worksheet, you must first “select” the input cells and then, “Unlock” them.

Protecting Excel Worksheet

The final step in the process is to Protect the Worksheet. I rarely use a password for the worksheet because I tend to forget it and then I cannot even access the protected worksheet!

Take care in the Protect Sheet Dialog Box to select only the options that best suit your purpose. For example, in this video tutorial, I choose to limit users to select only the cells that I unlocked in steps 1 and 2.

Go To Special Dialog Box

The quickest way to select the cells that you wish to “unlock” is to open the Go To Special Dialog Box. There, choose “Constants.” You can limit the constants to “Numbers” as I do in this tutorial.

Danny Rocks Tips and Timesavers Podcast on iTunes

I invite you to subscribe to my “free” video podcast on iTunes. Click on this link to access all of my Podcast Videos.

Watch Tutorial in High Definition

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

 

 

How to Protect Cells with Formulas in Excel [ 7:32 ] Play Now | Play in Popup | Download (2394)
Link to this post!
Filed Under: Excel Formulas, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , , , , , , ,
February 19, 2012 Leave a Comment

How to Use Named Ranges in Existing Excel Formulas

Paste Named Ranges in Formula

Paste Named Ranges in Formula

I am a strong proponent for using Named Cell Ranges when creating Excel Formulas. But what if you have already created formulas – formulas that use cell references. How do you insert or apply a newly created named range into an existing Excel Formula?

Simple answer – watch this short video to see me demonstrate how this is done.

Better answer:

Follow These Steps

  1. Activate in-cell formula editing by either double-clicking the formula cell or using the Keyboard Shortcut F2.
  2. Highlight the cell reference that you wish to replace with a named range.
  3. Choose the Name from the “Use in Formulas” drop-down menu. You can also use the F3 Keyboard Shortcut to open the Paste Names Dialog Box.
  4. Repeat these steps to complete replacing additional cell references with named cell ranges.

Learn to Get the Most from Excel

On my DVD-ROM, “The 50 Best Tips for Excel 2007,” I offer 5 1/2 hours of video instruction. You will be amazed at how much more you can get out of Excel when you invest in this valuable resource. I invite you to visit my secure, online shopping website to learn more about the resources that I offer.

Watch Video in High Definition

You can view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Tutorial Now

Link to this post!
Filed Under: Excel Formulas, Excel Training, Names for Cells and Ranges Tagged: , , , , , ,
« Previous Page
Next Page »