Home Archives for Danny
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: , , , , , ,
November 18, 2011 Leave a Comment

20 Tips for Delivering a Successful Presentation

Presenting to Group

Presenting to Group

Have you been asked to speak or deliver a presentation at an upcoming event? Once you have accepted the invitation to speak or present, you need to start preparing. Where do you begin?

Successful presenters follow a process. They have a system which they have developed over the years. They use a checklist of Best Practices to help ensure that they deliver successful presentations every time!

So, you may be asking … “Where do I get a list of tips? Has someone created a checklist that I can use?”

Fortunately, you have come to the right location! I have created a checklist of “20 Tips for a Successful Presentation” that you can download – for free!

Click on this link to download my 20 Tips for a Successful Presentation as an Adobe PDF.

Here is my list of the 20 Tips that I use whenever I speak or deliver a presentation. These are also the tips that I share with my clients when I coach them to deliver successful presentations.  You will learn about:

  • How to discover your “Words-per-Minute” (WPM) Count
  • The “B-L-O-T” (Bottom Line On Top) Technique
  • The “Power of the Pause”

And 17 additional tips. Be sure to download the complete checklist  of  “20 Tips for a Successful Presentation” to get more information about each tip:

Tips While Preparing Your Presentation

  • Tip #1 – Prepare with your audience in mind
  • Tip #2 – Begin your presentation with “The End in Mind”
  • Tip #3 – Use the classic model to structure your presentation
  • Tip #4 – Prepare by writing out your presentation

Click on this link to download your copy of my 20 Tips for a Successful Presentation

Tips for Delivering Your Presentation

  • Tip #5 – Memorize two sections of your presentation
  • Tip #6 – Communicate and confirm your equipment needs in advance
  • Tip #7 – Get comfortable with the physical setting
  • Tip #8 – Your audience will “Tune-in / Tune out” during your presentation
  • Tip #9 – Remember the “Power of the Pause!”
  • Tip #10 – Maintain Eye-contact with your audience
  • Tip #11 – Pace your presentation

Follow this link to download a PDF of my 20 Tips for a Successful Presentation

Tips for PowerPoint

PowerPoint Tips

Tips for Creating PowerPoint Slides

  • Tip #12 – You are the Presentation!
  • Tip #13 – Do not write complete sentences on your slides!
  • Tip #14 – Take advantage of the “White Space” on your slides
  • Tip #15 – Choose appropriate visual images

Want to get a copy of this checklist – complete with details for each of these tips? Click here to download 20 Tips for a Successful Presentation as a PDF.

Do you want to learn how to use PowerPoint effectively? Click on this link to learn more about my DVD-ROM, “The 50 Best Tips for PowerPoint 2007”

Tips for Engaging Your Audience

  • Tip #16 – Phrases to avoid
  • Tip #17 – Phrases to substitute
  • Tip #18 Encourage audience questions
  • Tip #19 – Audiences remember stories

Get your free copy of this checklist. Click on this link to download 20 Tips for a Successful Presentation

Tip for Delivering a Successful Presentation

  • Tip #20 – Visualize your Successful Presentation!

So, there is the checklist that I use. Be sure to download your copy to learn more about each of these “20 Tips for a Successful Presentation.”

Do you have a favorite tip? What works for you? Add your comment in the area below this article. Or you can send it to me via email: danny@thecompanyrocks.com

Read or Forward this Article from Ezine Articles Website

A variation of this article is purclished on the Ezine Articles Website  – www.ezinearticles.com where you can read it or forward it to your friends and colleagues. Click here for the direct link to the article.

Invitation to Visit My New Online Shopping Website

I have just opened my new, secure online shopping website. I invite you to visit http://shop.thecompanyrocks.com to see all of the products and resources that I offer.

 

 

Link to this post!
Filed Under: Ezine Articles, Powerpoint Techniques, PowerPoint Video Training, Speaking, Words per Minute, Words per Minute 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 »