Home
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: , , ,
August 10, 2008 4 Comments

Use Excel's =VLOOKUP() function to produce an invoice

Excel’s =VLOOKUP() function is used in almost every business form that we encounter – Customer invoices or Employee Payroll statements, etc. This short training video will show you how and when to use the Vertical Lookup function.

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

  1. The key to understanding the =VLOOKUP() function is to look for a “value” in the 1st column (leftmost) of a table array. So bear this in mind when you construct tables for Price lists, Employee Information, Inventories, etc.
  2. 3 Arguments are required. The 1st argument – “What value do you want to look up?” You can either enter a value or point to a cell reference.
  3. The 2nd argument asks for the location of the table array. Select the entire table, but NOT the column headers.
  4. The 3rd argument asks, “Once I have found the value in the table, what information do you want returned in your formula?” This is the Column indes i.e. counting from left to right, what is the 3 of the column that contains the information.
  5. The 4th argument is optional – enter “FALSE” if you require an exact matching value.

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 available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , ,
August 9, 2008 Leave a Comment

Review Custom List Sorting, Subtotals, and Consolidation in Excel

This video reviews 4 Excel topics. Creating a Custom List and then sorting according to the Custom List; Creating Subtotals and also Consolidating Data according to Category.

These topics are consistently the most viewed on my website.

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

  1. Enter the values for your Custom List in either a column or row. Select the list and then Spell Check it (F7 key is the shortcut.)
  2. With the list still selected, go to Tools, Options, Custom List Tab, Import, OK.
  3. To sort data using the Custom List, be sure to click Options and then select the custom list from the drop-down in “First key sort order.”
  4. Sort your list prior to creating Subtotals. Data, Subtotals and then make selections in the dialog box.
  5. Consolidate Data by Category: First, select the top cell where your Consolidation Report will appear. Then select Data, Consolidation. Select the Reference Range to be consolidated. When consolidating “By Category,” be sure to select your Top Row (Labels) as well as the data. Click Add.
  6. Be sure to check the “Use labels in:” Top Row and Left Column boxes. Click OK.

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

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

Related Excel Video Lessons

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos Tagged: , , ,
August 8, 2008 Leave a Comment

Write Excel formulas that refer to multiple worksheets

Learn how to create formulas in Excel that link to cells in multiple worksheets – or other workbooks. Then, if a value in one of your linked cells changes, your formula updates to reflect that change!

In this Excel video lesson we started by creating a template which we copied to create multiple worksheets (for Jan, Feb, Mar, etc.)

Let’s write a formula to SUM the sales from the Jan, Feb & Mar worksheets to create our 1st Quarter Summary Report. We will also review the Data, Consolidation method to accomplish the same goal.

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

  1. For Summary or Consolidation reports it is best to start with a template.
  2. In Cell D4 of the “1st Quarter…” worksheet we type “=sum(” and then click the worksheet tab for “January” and select cell D4.
  3. Hold down the “Shift” key and click the March worksheet tab.
  4. Notice that in the Formula Bar, Excel has selected the range from January:March for cell D4.
  5. After you complete the formula with a “)” you can copy the formula to the other cells in the report.
  6. Data, Consolidation is easy to calculate if you have organized your data based on a Template. You are consolidating “by Position.”

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

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

Related Excel Video Lessons

Link to this post!
Filed Under: Excel Formulas, Excel Training Tagged: ,
August 7, 2008

Can customers find you when they need you?

Can customers find you when they need you? When you have a toothache, you call your dentist? Right? When your drain won’t drain, you call your plumber? Correct?

But what if you don’t have a dentist or a plumber? Who would you call? Why would you call them? How would you know if they were reputable? That they could do the job?

Would you call a friend and ask for a recommendation? Would you consult an on-line service such as Craig’s list or Angie’s List?

If you have the time, yes. But if your tooth aches badly enough…? Or if your drain is overflowing and you are knee-deep in…?

Who you gonna call? Ghostbusters?

Seth Godin has, as always, a very good post on this topic – “My tooth doesn’t hurt.”

Plumbers and dentists usually don’t have trouble attracting customers – as long as there are lots of “aching teeth” and “overflowing drains.”

“…On one hand, dentists have no trouble whatsoever getting business from people with toothaches. They hardly have to try. Just show up, I’ll find you. On the other hand, when my teeth don’t hurt, you’re invisible. No amount of signs, service and wonderful marketing is going to get me to pay you to drill my teeth when they don’t hurt. ” – Seth Godin

 

Increase your visibility

Are you invisible? If you sell a product or offer a service, how do you gain visibility? How do you let potential clients know about you and what you offer?

  • How do you gain “top of mind share” with your target audience?
  • How do find ways to stay in front of customers and clients when they don’t need you?

If you are visible enough, you will increase your value.  And the probability that customers will call you when they need you. But… your visibilty must offer value. Visibility, alone, is not sufficient.

  • Billboards and radio ads? – Not enough value. Recognition, perhaps.
  • Yellow Page ads? I doubt it. I throw mine into the recycle bin as soon as they are delivered.

Can you create new products or services – for those times when your customer’s teeth don’t ache? Or when their drains drain? Seth Godin cites the “Teeth Whitening” services that dentists now offer. 

What additional services can you offer? 

What services can you offer? How can you get clients to come into your store or to visit your website? At times when they don’t need your products or services?

Blogs are one way – to get customers to learn more about you. And for you to offer something of value to your customers and potential customers.

As I develop my training and speaking business, I am finding that offering short, free, and focused video lessons is a great way to build awareness of what I offer – and how I offer it. I have also started posting these Excel video lessons on YouTube. I’ll also open up MySpace and Facebook sites soon.

People ask me, “Why are you giving away something for free?” Because I believe that by doing something good to others (offering something of value with no expectation of repayment) you will end up doing good for yourself.

Increase your value. Increase your visibility!

Link to this post!
Filed Under: Business Communications, Customer Service, Main Page, Marketing, Sales, Training Tagged: , ,
August 7, 2008 3 Comments

Consolidate Excel data by position

Most companies use Excel to produce budget reports. In this video lesson you will learn how to consolidate data from January, February and March to produce a 1st Quarter Consolidated Budget report – based off of an Excel Template.

Here are the steps to follow in this video lesson:

  1. Start with a Template – either on your computer or download one from the Microsoft website. Modify as necessary and then make multiple copies of it. e.g. 1 ea. for Jan, Feb, Mar and 1st Qtr, etc.
  2. Select the 1st cell to receive consolidated data in your 1st Quarter Consolidated report. Then, choose Data, Consolidate.
  3. Point to the January worksheet and select the range of cells that you want in the 1st Qtr consolidated report. Then click ADD.
  4. Repeat this process pointing to the February and March worksheets. Because we based our monthly reports on a Template, the range of cells in each worksheet is exactly the same – i.e. “By Position.”
  5. If you do not check the “Create links to source data” box, you will simply add (SUM) the data from the selected ranges into the Consolidation worksheet. SUM is the most common function but you are free to choose others.
  6. If you did check the “Create links to source data” box your consolidation worksheet will be outlined and each cell will be linked back to its source cell.

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

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

Related Excel Video Lessons

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , ,
August 6, 2008 1 Comment

Learn to filter and copy unique records in an Excel list

Frequently, you need to find and copy the unique records in an Excel data list. For example, you might be preparing to send out a direct mail piece and you want to clean you list first.

Having the unique records copied to another location might help you to spot glaring errors from faulty data entry.

Here are the steps to follow in this video lesson:

  1. Highlight the data that contains the records you wish to filter. This could be the entire data lis or just one column / field.
  2. Select Data, Filter, Advanced Filter. Be sure to select “Copy to another location and to check the “Unique records only” box.
  3. Click the starting cell where you want to place the copied “Unique records” and then click OK.
  4. You may wish to use the AutoFilter to select the data entry errors for correction.

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

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

Related Video Lessons

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , ,
August 6, 2008 1 Comment

Create Advanced Filters for your data list

In this Excel Video Lesson we will teach you to use Advanced Filtering of your data list.

Here are the steps to follow in this video lesson:

  1. Apart from your data table write your Criteria. Criteria must consist of at least 2 rows – a Header and at least 1 row of Criteria.
  2. With an active cell selected in the data table, click Data, Filter, Advanced Filter.
  3. Accept the default setting to Filter the Data in-place and also the range for the data list. Select the cells for your Criteria and click OK.
  4. The Advanced Filter applies the Criteria to show only those records that meet your Criteria.
  5. When you write your Criteria on 1 row, ALL of the criteria must be met – i.e. the “AND” option.
  6. When you write your Criteria on multiple rows, each criteria is met independently – i.e. the “OR” option.

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

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

Related Video Lessons

  • AutoFilter your list
  • Apply Custom Filters to your data list
Link to this post!
Filed Under: Data Filters and Queries, Excel Data Tables and Lists, Excel Training Tagged: , , , ,
« Previous Page
Next Page »