Home 2008 Archives for August 2008

Archives for August 2008

August 17, 2008 3 Comments

Keyboard Shortcuts – Part 2 – The Function Keys

We continue our series of lessons on Excel Keyboard shortcuts. In this video training, I demonstrate how each of the 12 Function keys serve as shortcuts.


The keyoard shotcuts that I use most frequently are:

  • F2 – to activate “in-cell” editing. Double-clicking a cell does the same thing – your choice!
  • F3 – To “paste a name” into a formula. (Of course you must have already created or defined named cells, ranges and constants for this to work.)
  • F5 – To go to any cell reference or named range. Explore the “Special” dialog box to “Go to” e.g. cells containing specific types of formulas – a great auditing feature!
  • f7 – To spell check you spreadsheet – great shortcut!
  • F11 – To insert a chart on a new worksheet with a 1-key shortcut. You can edit the chart once it is created.
  • F12 – Brings up the File, “Save As” dialog box – not many people know this shortcut.

Find the Excel Video Training Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , ,
August 16, 2008 1 Comment

The Company Rocks: Words Blog

Welcome to my latest blog “The Company Rocks Words!” Here, you will find short video tutorials offering you “tips & time-savers” when you use Microsoft Word. You will also find articles and commentary on our use of words to communicate ideas and to share information. We will examine which words work better than other words – and the reasons why they are effective.

I train individuals and groups to get the most out of MS Word for Windows. And, I train clients to improve their communications skills. My clients range from students to Fortune 500 executives. I have trained clients in a variety of industries; in the United States, Canada and Australia.

Most of my clients realize that they use only a fraction of Word’s powerful features. And… they want to learn how to tap into Word’s power in order to:

  • Save time
  • Be more productive
  • Present text in a clearly understood, professional manner

On this blog, I will share my tips with you via video tutorials. Each lesson will be brief (between 3 and 5 minutes) and to the point (1 concept per video.)

Many of these tips will show you how to transition from your familiar work in MS Word 2003 to the dramatically different Word 2007 interface.

The current version, 2007, is quite different – to say the least! Many clients are initially intimidated by the radically different interface. I will help you to transition to the new version – when you are ready.

So, check in often to see the latest tips that I add to the blog. And… please feel free to share your own “tips and time-savers” with our readers.

Ask questions. I will answer them individually as quickly as I can.

Sincerely,

Danny Rocks

Link to this post!
Filed Under: Communications, MS Word, Word Tips Tagged: ,
August 15, 2008 1 Comment

3 reasons to apply conditional formatting to your Excel data

In my experience, Excel’s “conditional formatting” is an underutilized feature in spreadsheet analysis. And that is a shame. In this Excel training video, I will show you how, when and why to apply conditional formatting to dynamically point out important data in your spreadsheet.

Here are the steps to follow in this video lesson:

  1. Select the cells that you want to apply conditional formatting to. From the Format menus choose Conditional Formatting.
  2. In the dialog box choose “Cell Value is” and then choose an operator (Greater than, etc.) and then either type in a value or refer to a cell. This applies your Condition.
  3. Next, choose what Formatting to apply when cells meet your Condition(s). Click OK
  4. You may also choose, “Formula is” and then type in your formula to establish the Condition. Only use Formulas that can be answered as “TRUE” or “FALSE.” If the answer (implied or explicit) is “TRUE,” then the Conditional Formatting will apply.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Videos

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged:
August 14, 2008 3 Comments

4 reasons to use Excel's Formula Auditing tools

In this Excel training video I will show you how to use the Formula Auditing Toolbar to:

  1. Learn about the relationships among your formulas and cells by tracing precedents & dependents.
  2. Finding the source of your formula errors – and correcting them!
  3. Evaluate your formulas step-by-step to better understand how they are built.
  4. Setting up a small window to “watch” your key formulas change as you enter and edit data in cells.

Here are the steps to follow in this video training lesson:

  1. Select Tools,  Formula Auditing, Show Formula Auditing Toolbar.
  2. Select a cell containing a formula and experiment with the icons to Trace Precedents and Trace Dependents. Click the icon several times to trace back as far as you can go.
  3. Select a cell and Evaluate its Formula. Step In and Step Out to learn how the formula is built.
  4. Paste a list of all of the Names in your workbook. Select Insert, Name, Paste, Paste List.
  5. Select a cell with a formula and click the Show Watch Window icon to see how the formula results change as you enter and edit data in cells that feed into that formula.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

Link to this post!
Filed Under: Excel Formulas, Excel Training, Tips and Time Savers Tagged: , ,
August 13, 2008 7 Comments

3 reasons to use named cells and ranges in Excel forumlas

I use named cells, ranges and constants in my Excel formulas and workbooks for 3 reasons:

  1. They are easier to explain (to others and to myself 6 months after I create a formula.)
  2. They are easier to adapt (to other locations in the workbook – absolute cell references not required!)
  3. They are easier to update (especially named constants in formulas when e.g. rates change.)

These are the steps to follow in this Excel training video:

  1. Select the cells that you want to name and also the labels in the left column and top row.
  2. Choose, Insert, Name, Create. Make sure that Left column and top row are selected.
  3. To verify that your names have been created, click the Name box drop down list of names. Select a name and Excel takes you directly to that cell or range.
  4. To substitute Name cells and ranges in existing formulas: Choose Insert, Name, Apply, OK
  5. When you are writing a new formulas and you want to use a name, use the F3 “Paste Name” shortcut combination to select the name you want in the formula and click OK.
  6. A “Named Constant” does not refer to a cell. Select Insert, Name, Define and then Name the Constant and in the “Refers to” part of the dialog box type in e.g. =0.0825 if you want your Named Constant to refer to a Sales Tax rate of 8.25%

Find the Excel Video Training Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Training Topics

Link to this post!
Filed Under: Excel Formulas, Excel Training, MS Excel Training, Tips and Time Savers Tagged: , ,
August 12, 2008 Leave a Comment

Watch My Excel Training Videos on YouTube

I have now joined the YouTube community by uploading several of my Excel Training Videos. Here is the link:

http://www.youtube.com/DannyRocksExcels

YouTube offers a unique opportunity for viewers worldwide to access and share video content with their friends and other members of their community. I want to be able to offer my Excel Video Training resources to them – the way that they want to access and share them.

The video experience that I offer on my The Company Rocks Excels website is quite different from the YouTube experience. Let us celebrate the difference!

News! My new DVD, “The 50 Best Tips for PowerPoint 2007” is available for purchase. Visit my online store for details.

Link to this post!
Filed Under: Powerpoint Techniques, Training Tagged: ,
August 12, 2008 2 Comments

Now You Can Watch My Excel Training Videos on YouTube

I have loaded many of my Excel Training Videos to YouTube. Here is the link:

http://www.youtube.com/DannyRocksExcels

YouTube is a powerful resource with a very wide reach. Many viewers enjoy being able to access and share YouTube content with their friends and other members of their community. I want to give them the opportunity to view and share my Excel Training videos the way the they like to do so.

There video experience on my The Company Rocks Excels site and on my YouTube site are different. Let’s celebrate the difference!

Let me know what you think. Take a minute to add cour comments below.

Link to this post!
Filed Under: Business Communications, Communications, Training Tagged:
August 12, 2008 Leave a Comment

Watch My Excel Training Videos on YouTube

I have posted several of my Excel Training Videos on YouTube. Here is the link:

http://www.youtube.com/DannyRocksExcels

YouTube is an incredible resource. I want to let as many people as possible know about the Excel training resources that I offer and YouTube will help me to accomplish this.

Some viewers find it easier to access and share videos via YouTube and I want to make it possible for them to do so.

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, MS Excel Training Tagged: ,
August 12, 2008 5 Comments

3 ways to minimize data entry errors in Excel

Excel is a great program for organizing and analyzing the information that you have in your system. But what if you have lots of data entry errors and inconsistencies?

In this Excel Training Video, I show you 3 ways to minimize data entry errors. You will learn how to employ Excel’s Data Validation feature to prevent accidental and intentional data entry errors!

These are the steps for this Excel Video Training Lesson:

  1. Choose the cell or range of cells that you want to apply Data Validation to.
  2. Select Data, Validation and start with the “Settings” tab.
  3. To control a range of dates, select “Date” in the Allow drop-down menu. Choose the range of dates that you wish to control. In this video I chose <= =TODAY()
  4. To create a “Pick from list” validation: 1st step is to create the list (in a separate workbook) and then “Name” the list. In the Allow menu, choose “List” and in the Source, use the F3 shortcut to paste in your named list.
  5. To prevent Duplicate entries, in the Allow menu choose “Custom” – This allows you to write a Formula. E.g. = COUNTIF($E$ :$E$30,E2)=1 where E2 is the 1st cell in your Validation range.
  6. In the “Error Alert” tab, you can pick from 3 different “Styles. Only the “Stop” style will prevent a data entry that violates your Data Validation rules. The other 2 styles are warnings and information – you can over-ride the validation rules with either of these 2 styles.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , , , ,
August 11, 2008 4 Comments

Use Vlookup function to prepare a payroll statement

A viewer wrote to me, asking for help in preparing his company’s payroll statement. I found an Excel Template using the =VLOOKUP() function to help him. In this Excel Video Training lesson, I demonstrate how to effectively use Lookup functions from 2 different worksheets.

Here are the steps to follow in this Excel Video Training Lesson:

  1. For the 1st part of this lesson I use an Excel Template called Payroll which I downloaded from the MS Excel website.
  2. The Payroll Statements draw information from two different worksheets via the =VLOOKUP() Function.
  3. Make sure that your “table arrays” have the key values in the 1st (leftmost) column.
  4. To return an “exact match” type in FALSE for the 4th argument.
  5. If you want to copy your =VLOOKUP() formulas, use “Absolute Cell” references for the table array.

Find the Excel Video Training Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Functions, Excel Training, MS Excel Training, Video Response to Viewers Tagged: , , ,
« Previous Page
Next Page »