Excel Training | The Company Rocks

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

Select the cells that you want to apply conditional formatting to. From the Format menus choose Conditional Formatting.
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.
Next, choose what Formatting to apply when cells meet your Condition(s). Click OK
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.

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:

Learn about the relationships among your formulas and cells by tracing precedents & dependents.
Finding the source of your formula errors – and correcting them!
Evaluate your formulas step-by-step to better understand how they are built.
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:

Select Tools, Formula Auditing, Show Formula Auditing Toolbar.
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.
Select a cell and Evaluate its Formula. Step In and Step Out to learn how the formula is built.
Paste a list of all of the Names in your workbook. Select Insert, Name, Paste, Paste List.
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.

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:

They are easier to explain (to others and to myself 6 months after I create a formula.)
They are easier to adapt (to other locations in the workbook – absolute cell references not required!)
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:

Select the cells that you want to name and also the labels in the left column and top row.
Choose, Insert, Name, Create. Make sure that Left column and top row are selected.
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.
To substitute Name cells and ranges in existing formulas: Choose Insert, Name, Apply, OK
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.
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.

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.

Share and Enjoy:

Filed Under: Excel Training, Most Viewed Excel Videos, MS Excel Training Tagged: Excel Training Videos on YouTube, The Company Rocks Excel Training

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

Choose the cell or range of cells that you want to apply Data Validation to.
Select Data, Validation and start with the “Settings” tab.
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()
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.
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.
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.

Share and Enjoy:

Filed Under: Excel Data Tables and Lists, Excel Training Tagged: Data entry errors in Excel, Data Validation, Excel 2003 Training Video, Excel Video Training, GIGO

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

For the 1st part of this lesson I use an Excel Template called Payroll which I downloaded from the MS Excel website.
The Payroll Statements draw information from two different worksheets via the =VLOOKUP() Function.
Make sure that your “table arrays” have the key values in the 1st (leftmost) column.
To return an “exact match” type in FALSE for the 4th argument.
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.

Share and Enjoy:

Filed Under: Excel Functions, Excel Training, MS Excel Training, Video Response to Viewers Tagged: Excel Video Training, Payroll statements in Excel, Templates, VLOOKUP Function

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

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.
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.
The 2nd argument asks for the location of the table array. Select the entire table, but NOT the column headers.
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.
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.

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:

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.)
With the list still selected, go to Tools, Options, Custom List Tab, Import, OK.
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.”
Sort your list prior to creating Subtotals. Data, Subtotals and then make selections in the dialog box.
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.
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.

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:

For Summary or Consolidation reports it is best to start with a template.
In Cell D4 of the “1st Quarter…” worksheet we type “=sum(” and then click the worksheet tab for “January” and select cell D4.
Hold down the “Shift” key and click the March worksheet tab.
Notice that in the Formula Bar, Excel has selected the range from January:March for cell D4.
After you complete the formula with a “)” you can copy the formula to the other cells in the report.
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.

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:

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.
Select the 1st cell to receive consolidated data in your 1st Quarter Consolidated report. Then, choose Data, Consolidate.
Point to the January worksheet and select the range of cells that you want in the 1st Qtr consolidated report. Then click ADD.
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.”
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.
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.

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

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

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

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

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.

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.

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.

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

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

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

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

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Tags