Home Archives for Excel Video Training
December 14, 2011 2 Comments

Use an Excel Template to Customize a Worksheet with 52 Weeks of Employee Time Cards

Template for Time Cards in Excel

Time Card Templates in Excel

This is Part Two in my new series of Excel Tutorials where I share my best practice tips for creating 52 weeks of Employee Time Cards – for hours worked. In this segment, I focus on starting the process with an Excel Template.

Finding Templates for Time Cards

As I demonstrate in the video, there are at least two easy sources for finding a Time Card Template:

  1. Sample Templates Installed on your Computer
  2. Templates that you can preview and download from the Microsoft Office Templates Website

Examining Templates that You Download

Before you even think about duplicating the Excel Template that you just downloaded, it is important for you to take a few minutes to explore:

  • The Formulas used in the Template
  • The Formatting used in the Template – especially the formatting for Dates and Times
  • The Protection Applied (if any) to the cells or the entire worksheet

Creating Drop-down Menus for your Template

Since our premise here is that we will copy the template to 51 additional worksheets, it makes sense to use Data Validation to create a series of “drop-down menus” for Employee names and the Start Date for each week. I demonstrate how to do this in the video tutorial.

Links to Additional Video Tutorials in this Series

Additional Resources for Excel

I invite you to visit my new, secure, online shopping website – http://shop.thecompanyrocks.com – to discover the many training resources that I offer you!

Watch Tutorial in High Definition

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

Click to Watch Video Now

Link to this post!
Filed Under: Data Validation, Excel Training, Names for Cells and Ranges, Work with Multiple Worksheets, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
December 12, 2011 3 Comments

Tips for Creating 52 Weeks of Employee Time Cards in Excel

Insert & Copy Worksheets

Insert & Copy Worksheets

We are now winding down the end of the year 2011. Several of my viewers and clients have asked my for help in creating one Excel Workbook that will contain 52 weeks of employee time card information:

  • Name and Department of Employee
  • Hours Worked each day of the week – Regular and Overtime
  • Number of Holiday / Sick Days that were used / that remain, etc.

First Tutorial in this Series

Rather than trying to create each individual – and some are very specific – request, I have decided to combine multiple requests into a series of Best Practice Tips for my general audience to consider.

In this lesson, I demonstrate:

  • Two methods for adding new blank worksheets to the Excel workbook
  • Two Methods for inserting an exact copy of an existing – and formatted – worksheet
  • My favorite tip – Fill Across Worksheets – either the formatting only, the contents only or the default setting – both formatting and contents.

Links to Additional Tutorials in this Series

Watch Tutorial In High Definition

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

Additional Resources at My Online Shopping Website

I invite you to visit my new – secure – online shopping website – http://shop.thecompanyrocks.com – and preview the many tutorials and resources that I offer you.

Watch Tutorial Now

Link to this post!
Filed Under: Excel Training, Format Cells in Excel, Tips and Time Savers, Work with Multiple Worksheets, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
December 9, 2011 Leave a Comment

Excel Keyboard Shortcuts for Navigating Between Cells, Worksheets, Documents and Programs on your Computer

Excel Keyboard Shortcuts

Excel Keyboard Shortcuts

This is the latest installment in my series of Video Tutorials for Keyboard Shortcuts in Excel. This time the focus is on Keyboard Shortcuts for Navigating between:

  • Cells in a worksheet. For example, to move to the last “occupied” cell ( a cell with data) in a column or row
  • Worksheets in the active Workbook
  • Other Excel workbooks or documents that are open on your computer
  • Other programs that are open on your computer

Links to Other Videos in Keyboard Shortcuts Series

Here is the link that will take you to the other Video Tutorials that I have created for Excel Keyboard Shortcuts.

Download the Complete – FREE –  “Chart of Popular Keyboard Shortcuts for Excel.”

There are two ways for you to download – for FREE – My “Chart of Popular Keyboard Shortcuts for Excel:”

  1. Click here to download this – for FREE – at my new, secure online shopping website.
  2. Click here to download this – for FREE – from my Exclusive Membership Content page.

Watch Tutorial in High Definition on YouTube

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

YouTube Tutorial for Excel Keyboard Shortcuts

Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, YouTube Videos by Danny Rocks Tagged: , , ,
December 7, 2011 Leave a Comment

How to Distinguish Cell Content from Cell Formatting in Excel

Cell Content vs Cell Formatting

Cell Content vs. Cell Formatting

Many Excel Users are surprised to discover that the Delete Key does not clear the formatting for the selected cell or range of cells. This can cause some unusual “surprises” you you or one of your colleagues later uses this “Deleted” Cell Range!

On the other hand, some cells must be formatted properly in order to return the correct result. This is especially true for cells that contain Time and Date Calculations. For example, in order to properly calculate a Sum of Hours that exceeds 24 hours, you need to apply the Custom Format [h]:mm to the formula cell.

Topics Taught in this Tutorial

  • Adding the Clear Command to the Quick Access Toolbar (QAT).
  • Using Go To Special – Last Cell, to find the last cell in the worksheet that contains either content or formatting – or both!
  • Understanding how Excel Stores Dates (as a Serial Number) and Times (as a Decimal Number) vs. How you use Cell Formatting to make Dates & Times easier to understand.
  • Using Keyboard Shortcuts to add the Current Date – Ctrl + ; (semi-colon) or Current Time -Ctrl + Shift + : (colon), or to “Toggle” between Formulas and Displayed Results  -Ctrl + ~ tilde).

Visit My New, Secure Online Shopping Website

Stop by http://shop.thecompanyrocks.com to see the new products that I have created. You can also download – for FREE – my Chart of Popular Excel Keyboard Shortcuts when you visit.

Watch Tutorial in High Definition

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

YouTube Tutorial for this Post

Link to this post!
Filed Under: Excel Basics, Excel Training, Format Cells in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , ,
December 4, 2011 Leave a Comment

How to SUM Values in One Field Based on Criteria from Multiple Fields in Excel

SUMIFS and DSUM Functions

SUMIFS and DSUM Functions

The new SUMIFS() Function was introduced in Excel 2007. With SUMIFS, you can sum the values in one field based up criteria that comes from multiple fields. This is a very valuable Function.

SUMIFS Function

The key to understanding SUMIFS, is that you “pair” a criteria range with the criteria for that range. As you watch my tutorial, the importance of this concept will become clear to you.

DSUM Function

If you are using – or need to create workbooks that are compatible with – older versions of Excel – e.g. Excel 2003, you can use the DSUM Function to achieve the same results. The DSUM belongs to the Database Functions set in Excel.

Use Named Cell Ranges in Formulas

I highly recommend that you learn how to create – and then use – named cell references in your Excel Formulas and Functions. In this tutorial, I show you how to do this. Once you have created a named cell reference, you can use the F3 Keyboard Shortcut to show a dialog box that lists all of the named Ranges that you can post into your formulas. This will save you time and help to ensure accuracy in your formulas – especially when you cop a formula to another location.

Bonus: Create Drop-down Menu with Data Validation

When using Multiple Criteria, I like to be able to select my criteria values from a drop-down list. In this lesson, I demonstrate how to do this using Data Validation in Excel.

Learn More Excel Tips

I invite you to visit my new, secure, online shopping website – http://shop.thecompanyrocks.com. Here, you can learn more about the tips on my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

 

Watch Video in High Definition

Here is the link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

YouTube Video

Link to this post!
Filed Under: Data Validation, Excel Functions, Excel Training, Names for Cells and Ranges, New in Excel 2007, New in Excel 2010, SUMIF and SUMIFS Functions Tagged: , , , , , , , ,
December 2, 2011 Leave a Comment

How to Return Either an Approximate or Exact Match for VLOOKUP Function

Matches with VLOOKUP

Matches with VLOOKUP

One of my viewers asked me to explain – with examples – how and when to choose either an Approximate or an Exact Match using the VLOOKUP Function in Excel. .

Approximate Match for VLOOKUP

The Approximate Match is the Default setting for Vlookup. This means that you can either omit the 4th, optional, argument or use the word TRUE. However, as you will see in this video tutorial, there are other considerations to consider in order to get the correct result that you are looking for.

Exact Match for VLOOKUP

In the example that I use on the Video Tutorial, I am setting up an Employee Payroll worksheet. I definitely want to have an Exact Match for each employee to ensure that they are getting the correct amount of compensation for the hours that they worked during this period. Watch the tutorial to see how I set this up.

Watch Tutorial in High Definition

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

Get More Excel Tips

I invite you to visit my online shopping website – http://shop.thecompanyrocks.com – to preview the many resources that I offer. Here is the link to go directly to the information page for my DVD-ROM, “The 50 Best TIps for Excel 2007.”

 

Link to this post!
Filed Under: Excel Training, Lookup Functions in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
November 24, 2011 Leave a Comment

How to Use the New Natural Language Date Filters in Excel

Natural Language Date Filters

Natural Language Date Filters

Recently, one of my viewers asked me to go into greater detail in demonstrating how the new Natural Language Date Filters work in Excel. Natural Language Filters were introduced in Excel 2007 and they are a great tool to use! Now, instead of writing complex formulas as criteria in Advanced Filters, you can simply click, “Yesterday” to see all of the records from the previous day!

Of course, you must actually have records in your data set for that date!

The Natural Language Date Filters are related, by position, to TODAY(). The TODAY() Function is a “Volatile Function” that returns the value for the current date as found in your computer systems internal clock. The result of the TODAY() Function will change each day. And so, by definition, will the result for a “Yesterday” filter!

Filter for Specific Date

There are several methods that you can use to filter for a specific date or range of dates. One method that I demonstrate in this tutorial is the “Custom Date” dialog box.

Watch Tutorial in High Definition

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

Learn About My New Extended Length Video Tutorials

I have recently released a series of extended length (90 minutes) video tutorials. They are part of my “Master Excel in Minutes” Series. Each video focuses on one topic. My first video is focused on Excel Pivot Tables. I have created Videos for Excel 2010, Excel 2007 and for Excel 2003. You also have the choice of purchasing the video for immediate downloading or shipped to you on a DVD-ROM.

Follow this link to go to the information page for my “Master Excel in Minutes” video tutorial series.

How to Yse Natural Language Date Filters in Excel [ 6:59 ] Play Now | Play in Popup | Download (3451)
Link to this post!
Filed Under: Data Filters and Queries, Date Functions and Formats in Excel, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , ,
November 21, 2011 Leave a Comment

How to Automatically Add a Time / Date Stamp Whenever You Add or Edit a Record in Excel

VBA Code to add Time Stamp

VBA Code to add Time Stamp

In this video tutorial, I respond to one of the most frequent requests that I get from my viewers – “How do I automatically add a ‘Time / Date Stamp’ whenever I add or edit a record in my Excel Worksheet?”

On the surface, this might appear to be a “simple” question to answer. However, I promise that you will be frustrated if you try to solve this problem by using the =NOW() Function or one or both of these Keyboard Shortcuts:

  • Ctrl + ; (Semi-colon) to add a Date Stamp
  • Ctrl + Shift + : (Colon) to add a Time Stamp

Because … NOW() is a “Volatile Function” that automatically updates to display – in all records that refer to NOW() – the current date and time. And … because, it is difficuly to automate the combination of Keyboard Shortcuts to apply a Date / Time Stamp.

So … what is the solution?

Use VBA to Create a Simple “Event Handler” Macro

Don’t get scared off! Don’t say, “I can’t write a VBA Macro!”

I PROMISE you – an Intermediate Level User of Excel – that by investing 10 minutes to watch this tutorial – that you WILL be able to automate this process to add a Time / Date Stamp whenever you add or edit a record in Excel.

I had you – the Intermediate / Early Advanced Level Excel user in mind when I created this video tutorial. I take you step-by-step through the process. I point out the potential “got’cha” steps. I am confident that I can help you to advance your skill set in Excel with this video tutorial!

Watch Tutorial in High Definition

I invite you to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Visit My Online Shopping Site

I invite you to visit my new, secure online shopping site – http://shop.thecompanyrocks.com – to see all of the new products that I have created and offer for sale!

How to Use a VBA Macro to Automatically Create a Time - Date Stamp Whenever a Record is Changed or Added in Excel [ 9:24 ] Play Now | Play in Popup | Download (3794)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Training, Excel VBA Macros, Podcast Tips & Timesavers Tagged: , , , , , ,
November 13, 2011 1 Comment

Tips for Working with Array Formulas and Functions in Excel

Using ARRAY Functions in Excel

ARRAY Formulas in Excel

Array Formulas and Functions are very powerful tools to use in Excel. However, the concept of working with ARRAYs take a little bit of time and practice. In this video tutorial, I demonstrate how to:

  1. Create formulas that use ARRAYS as arguments
  2. Work with ARRAY FUNCTIONS – for example, the TRANSPOSE Function
  3. Key “Got’cha” steps to master – for example, selecting all cells to receive formula results before creating the ARRAY Formula.
  4. The importance of using Ctrl+Shift+Enter to complete the Array formula.

What is an ARRAY?

“An Array is a collection of Cells or Values that Excel treats as a single unit.”

Why Use an ARRAY Formula?

  1. Automatic Level of Protection for Formula Cells – You cannot delete nor edit a single cell in an Array Formula
  2. Eliminate Intermediary Calculations – For example, you can find the Grand Total without having to create a field to calculate “Extended Price.”
  3. Worksheet, usually, calculates faster because you are using fewer formulas.

Visit My NEW Online Shopping Website

http://shop.thecompanyrocks.com is my new, secure online shopping website. I invite you to visit and preview my new products.

Watch Tutorial in High Definition

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

Tips for Working with Array Formulas and Functions in Excel [ 12:54 ] Play Now | Play in Popup | Download (1721)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
November 5, 2011 Leave a Comment

How to Use the Offset Function Inside a VLookup in Excel

Vlookup in Excel

VLOOKUP in Excel

Each month, I get 5 to 6 emails of phone calls from viewers who who are having trouble using the VLOOKUP Function in Excel. In the majority of cases, the problem is that their Data Table is setup so that the “Key” field that they need to search in is NOT the Left-most or first field in the table. It is not always practical – nor desirable – to use “Cut and Paste” to rearrange the fields in the data set. So, what techniques can you use to fix this?

Use the OFFSET Function Inside a VLOOKUP Function

In this scenario, our IT Department sends us a daily report of the products that we sell including fields for the current cost and quantity on-hand. We use this report to generate additional reports and filters. This report gets saved automatically as an Excel 2010 Table with the name “Inventory.” The problem is, whenusing a VLOOKUP, that the first – or Left-most – field is NOT the “Key” field that we want VLOOKUP to use to search for a Match in order to return the current price or inventory for individual product.

The OFFSET() Function in Excel, makes it easy to reference a “starting field” that, in this case, is one column to the Right. This is perfect for our situation. We can continue to use our “named range” with the VLOOKUP!

Watch Video in High Definition

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

Visit My New Secure Online Shopping Website

I invite you to visit my new online shopping website at: http://shop.thecompanyrocks.com I have created new, extended length video tutorials that you can either purchase to download immediately or order them on a DVD-ROM. In addition, I have published versions of my 90 minute Pivot Tables Video Tutorials for Excel 2003, Excel 2007 and Excel 2010. So, I have the tutorials that you want for the Excel version that you are currently using!

Click to Play Excel Video Tutorial

Link to this post!
Filed Under: Data Validation, Excel Data Tables and Lists, Excel Training, Lookup Functions in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
« Previous Page
Next Page »