Home 2011 Archives for November 2011

Archives for November 2011

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: , , , , , , , ,