Home Archives for 2011

Archives for 2011

December 16, 2011 2 Comments

Summarizing Employee Hours Worked and Vacation Time over 52 Weeks in Excel

Sum Across Worksheets

Sum Across Worksheets

This is the third episode in my series of Excel Tutorials where I share my best practices for creating an Excel Workbook that contains 52 weeks of Employee Time Cards. In this lesson, I focus on techniques to Summarize – or Consolidate – the hours worked or taken as vacation, by each employee, over a period of several weeks.

Sum Across Worksheets

This is a great tip to learn! In my experience, I find that very few experienced users of Excel know that you can do this! This will eliminate the hassle of linking to a cell in individual worksheets to create your formula. There is, after all, a limit to the number of characters that you are allowed in a formula!

Consolidate Data By Position

Since each worksheet in this workbook is identical in structure, Data Consolidation is simple. Provided, that is, that you remember to “Add” each worksheet range reference in the Dialog Box. In this example, I have “created a link” to each worksheet so that the data can be easily updated.

Links to Other Tutorials in this Series

Shop for Additional Resources

I invite you to visit my new, secure online shopping website – http://shop.thecompanyrocks.com – where you can preview all of the resources that I offer you.

Watch Tutorial in High Definition

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

Summarize 52 Weeks of Employee Time Cards in Excel [ 9:58 ] Play Now | Play in Popup | Download (2430)
Link to this post!
Filed Under: Consolidate Excel Data, Excel Training, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , , , , ,
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 28, 2011 Leave a Comment

How to AutoFill the Letters in the Alphabet in Excel

 One of my viewers would like to be able to AutoFill the Letters in the Alphabet in his Excel Workbooks – he creates Alpha-Numeric Part Numbers for his products.

Two Ways to AutoFill Letters in the Alphabet

  1. Use a Formula – =CHAR(ROW(A65)) and AutoFill Down in the Column – This is not ideal
  2. Create and Use a Custom List in Excel. – So, use the Formula to create your list of 26 Alphabetical Letters. Copy the List and use Paste Special Values to convert the formulas to values. Then create your Custom List.

Using the CHAR() Function in Excel

There are 255 ASCI Characters that you can use in Formulas. For example, =CHAR(169) will return the © Symbol. In this lesson, I share a great Keyboard Shortcut that you can use to add this Symbol into the Footer of your printed Excel worksheet – Watch the video to discover this!

Visit My Secure Online Shopping Site

I hope that you take a few minutes to visit my new, secure online shopping website. The address is: http://shop.thecompanyrocks.com

 

Watch Tutorial in High Definition

On my YouTube Channel – DannyRocksExcels – I have posted this tutorial in High Definition Mode. Click here to go directly to this video.

 

Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers, 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: , , , , , ,
Next Page »