Excel Training | The Company Rocks

Danny October 15, 2011 Leave a Comment

Danny September 24, 2011 Leave a Comment

How to Take Advantage of the Go To Special Dialog Box Options in Excel

Go To Special Options

In my opinion, the Go To Special Options Dialog Box offers some of the most useful tools in Excel!

Why?

Because, you must…

Select Cells Before Performing an Action on these Cells

The “key” to understanding ANY MS Office or Windows Program is… You MUST select a single cell or a range of cells BEFORE you can perform an action on them – e.g. Formatting you selection, deleting your selection, editing your selection or auditing your selection.

Tips Presented in this Video Tutorial

Select cells with Comments – so that you can “format” these cells to make them easier to identify.
Select cells that contain Constant Numeric Values – so that you can easily “spot” cells that should – bu do not – contain formulas.
Select cells that contain Specific Types of Formulas – e.g. Text Formulas – to “audit” your formulas.
Find the “Last Cell” in your worksheet – the last cell that contains EITHER content OR FORMATTING – This can be an “eye-opener!” for you.
Select the cells that contain Data Validation – perhaps you failed to “validate” all of the cells in a range.
Select the cells that contain Conditional Formatting – perhaps your did not select ALL of the cells in a Range BEFORE you applied a Conditional Formatting RULE!
Learn how to select the “Visible Cells Only” before performing a copy and paste operation – especially helpful when copying the “collapsed cells” for an Excel Outline.

I am positive that Excel users at ANY LEVEL will be able to pick up at least one solid tip from this Video Tutorial. Please send me your comments to let me know what you learned – or what you need clarification on.

Watch Tutorial in High Definition Mode

Follow this link to view this Excel Tutorial in High Definition / Full Screen Mode on my YouTube Channel – DannyRocksExcels

Learn About My New Extended Length Excel Video Tutorials

I have just published the first in a series of “Extended Length” – 90 Minutes – Video Tutorials, “Excel Pivot Tables to Summarize, Analyze and Present Your Data.” Follow this link to learn more about this tutorial. I have created separate versions of the tutorial for Excel 2010, 2007 and 2003.

Share and Enjoy:

Filed Under: Conditional Formatting, Data Validation, Excel Formulas, Excel Training, YouTube Videos by Danny Rocks Tagged: Conditional formatting in Excel, Data Validation, Excel Video Training, Find the Last Cell in a Worksheet, Go To Special dialog box, Select Cells with Comments, Select Cells with Conditional Formatting, Select Cells with Constant Values, Select Cells with Data Validation, Select Cells with Formulas, Video lessons for Excel, Visible Cells Only in Excel

Danny September 23, 2011 1 Comment

How to Use the Solver Add-in for Excel – Part 2

Solver Parameters

This is Part 2 of my series of video tutorials where I demonstrate how to use the SOLVER Add-in Tool in Excel to produce a specific result for a formula. Remember that SOLVER is an Add-in program that you must activate before you can use it. Watch Part 1 in this series to learn how to activate SOLVER.

In this tutorial, I work with the same data set that I used in Part 1. This time, I want to achieve a specific amount of Gross Profit Sales Dollars. First, however, I must determine what is a reasonable amount to set as the Target for Solver to return.

Use SUMPRODUCT Function in Excel

I use the SUMPRODUCT Function to quickly determine my possible Revenue, Cost of Goods Sold and Gross Profit is I sell all of the Units that I have in stock. Now, I have a reasonable Target for SOLVER to reach. SUMPRODUCT is a very efficient Function that, unfortunately, very feel Excel users – in my experience – know about. Follow this link to my Index of Excel Video Lessons to find additional tutorials for SUMPRODUCT.

Best Practice Tips for Solver

Work with a copy of your worksheet. This is always a good idea, and it is most important when you are beginning to learn how to work with SOLVER.
Write out your Goal for SOLVER.

Do you want SOLVER to Maximize the Formula? For example, to Maximize your potential profit?
Do you want SOLVER to Minimize the Formula? For example, to Minimize your costs?
Do you want SOLVER to return a Specific Number? If so, what is that number?
Determine your “Variable Cells.” SOLVER calls them the “By Changing Cells.” These are the cells that SOLVER will automatically adjust.

The Variable Cells must “feed int” the formula in your Target Cell – This is the key to making SOLVER work!
Write out your “Constraints.” These are the “real-world” conditions that you want SOLVER to operate under. This is the real power in SOLVER.
Do not treat the SOLVER results as “the Gospel.” SOLVER is a powerful Business Intelligence Tool. You, however, know your own business or industry. Use SOLVER as a tool to help you to make informed business decisions.

Watch Part 1 in SOLVER Series of Video Tutorials

Here is the link to watch Part 1 of my series of Excel Tutorials for the Solver Add-in Tool.
Watch Part 1 of this Series in High Definition on YouTube – My Channel is DannyRocksExcels

Watch Tutorial in High Definition

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels.

Share and Enjoy:

How to Use Solver Add-in for Excel - Part 2 [ 11:48 ] Play Now | Play in Popup | Download (2137)

Filed Under: Excel Financial Tools, Excel Functions, Excel Training, What-if Analysis, YouTube Videos by Danny Rocks Tagged: "What-if" Analysis in Excel, Data Analysis in Excel, Excel Video Tutorials, Gross Profit Calculations in Excel, Set Constraints for SOLVER in Excel, SOLVER in Excel, SUMPRODUCT Function, Variable Cells for Solver, Video lessons for Excel

Danny September 21, 2011 4 Comments

How to Use the SOLVER Tool in Excel to Produce the Optimal Result for a Formula

SOLVER in Excel – Key Points

I am responding to a “flurry” of requests to produce an Excel Video Tutorial that demonstrates how to use the SOLVER TOOL in Excel. Here are the key points to remember when you want to use the SOLVER Tool:

Activate the SOLVER Tool

SOLVER is an “Add-in” tool that needs to be “activated” before you can use it. In Excel 2007 / 2010, you do this via Excel Options – Addi-Ins – Excel Options. In Excel 2003, you do this via the Tools Menu – Add-ins – Solver.

Prepare Data for Solver

In order to use the SOLVER Tool, you must:

Establish the “Target Cell.” This cell must contain a formula. This is the cell that you want to “optimize” by using SOLVER.
Determine the “By Changing” Cells. These are the cells that SOLVER will change in order to deliver the optimized result in the Target Cell. The “By Changing” cells MUST “Feed Into” the Target Cell.
Establish the CONSTRAINTS for SOLVER. This is the “key’ element that gives SOLVER its power. You can add as many constraints as needed. My recommendation is that you “write them out” before you start to use the SOLVER Tool.

SOLVER is very powerful. However, you MUST “think it through” before you apply it. It will take lots of practice for you to understand how to properly set up your data to produce appropriate SOLVER results.

Share and Enjoy:

How to Use the Solver Tool in Excel [ 10:20 ] Play Now | Play in Popup | Download (1570)

Filed Under: Download Files from Video Lessons, Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, What-if Analysis Tagged: "What-if" Analysis in Excel, Business Intelligence Reports in Excel, Excel Video Training, SOLVER in Excel, Video lessons for Excel

Danny September 19, 2011 2 Comments

How to Create a Summary Report from an Excel Table

Functions for Executive Summary

I created this Excel Video Tutorial in response to a viewer who, in her company, is not permitted to use a Pivot Table to create a report. Unfortunately, this happens more often than you might think. Nevertheless, I promised to share my tips and advice. I think that many of this tips will help Excel users at all levels.

Tips Covered in this Video Tutorial

Use Advanced Filter to Extract a list of unique customer names from a range with @ 4,300 records.
Later in the lesson, I show you how to extract this list to a different worksheet – a tip that will save you time when preparing your reports!
How and why to convert a normal range of data into a TABLE in Excel 2007 / 2010 or into a LIST in Excel 2003.
How to create “Named Ranges” for your key data cells and to use them in your Formulas and Functions.
How to use the SUMIF, AVERAGEIF and COUNTIF Functions in your summary report.

Keyboard Shortcuts Used in this Tutorial

Ctrl + T to convert to a TABLE in Excel 2007 / Excel 2010
Ctrl + L to convert to a LIST in EXCEL 2003
Ctrl + A to open up the Function Arguments Dialog Box
Ctrl + Shift + F3 to open the Create Names Dialog Box
F3 to open the Paste Names Dialog Box

I decided that I will only make this video available on YouTube – I think that you will enjoy the clarity that you get from the High-Definition / Full Screen Mode.

Watch Tutorial in High Definition on YouTube

Follow this link to view this Excel Tutorial in High Definition on my YouTubeChannel – DannyRocksExcels

Visit my new secure online shopping website

I invite you to visit http://shop.thecompanyrocks.com – my new online shopping website.

Watch Video Now

Share and Enjoy:

Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Functions, Excel Training, Keyboard Shortcuts, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: Advanced Filter for Excel data, AverageIf Function in Excel, COUNTIF Function, Excel 2007 Tables, Excel 2010 Tables, Excel Keyboard Shortcuts, Excel Video Training, Lookup Functions in Excel, Named Cells and Ranges, SUMIF Function, Video lessons for Excel

Danny September 15, 2011 1 Comment

How to Identify and Then Delete Duplicate Records in Excel

Identify & Delete Duplicates

It is almost inevitable that, over time, your Excel data set will contain duplicate records. This can be costly – if the duplicate records are in your mailing list. And it can also result in errors when you count and calculate data.

Three Methods to Identify and Delete Duplicate Records

In this Excel Tutorial, I demonstrate three approaches to identify duplicate records in a data set:

Circle Invalid Data – This tool works in coordination with Data Validation.
Remove Duplicates – This powerful command was introduced in Excel 2007. It does not “preview” the records to be deleted, so my advice is to use this command with a copy of your data.
Conditional Formatting – Greatly improved for Excel 2007 and Excel 2010. Now, you have a menu choice to apply conditional formatting to either Duplicate or Unique Records. If you are still using Excel 2003 or earlier, I show you how to write the formula needed to use Conditional Formatting to highlight duplicate records.

Watch The Tutorial in High Definition on YouTube

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Share and Enjoy:

Filed Under: Conditional Formatting, COUNTIF and SUMIF Functions, Data Validation, Excel Training, New in Excel 2007, YouTube Videos by Danny Rocks Tagged: Circle Invalid Data in Excel, Conditional formatting in Excel, COUNTIF Function, Custom Data Validation Formulas, Data Validation, Excel Video Training, Logical Formuls, Remove Duplicates in Excel, Video lessons for Excel

Danny September 8, 2011 1 Comment

How to Prevent Duplicate Entries in Excel with Data Validation

Formula for Data Validation

Over time, it is easy for duplicate entries to creep into an Excel Data Set. In this video tutorial, I show you, step-by-step, how to apply Data Validation to a range of cells so that anyone who attempts to enter a duplicate value will be prevented from doing so.

Use a “Logical Formula” for Data Validation

In this lesson, I show you how to “customize” the COUNTIF Function in the Data Validation Dialog Box so that it returns the value of TRUE. Logical formulas return either TRUE or FALSE. You MUST use a Logical Formula in Data Validation.

What Else is Covered in this Tutorial?

Use Paste Special – Validation to extend Data Validation to a wider range of cells
Use Find – Go to Special – Validation to highlight all cells on worksheet that contain Data Validation
Use Circle Invalid Entries from the Data Validation menu to automatically “draw a red circle” around existing cells that contain duplicate values
How to quickly remove Data Validation from a Range of Cells

Watch Tutorial in High Definition

Follow this link to my YouTube channel – DannyRocksExcels – where you can watch this tutorial in High Definition mode.

Share and Enjoy:

How to Prevent Duplicate Entries in Excel with Data Validation [ 10:23 ] Play Now | Play in Popup | Download (1824)

Filed Under: COUNTIF and SUMIF Functions, Data Validation, Excel Training, Podcast Tips & Timesavers Tagged: COUNTIF Function, Data Validation, Excel Video Training, Prevent duplicate entries, Remove Duplicate Entries, Show duplicate records, Video lessons for Excel

Danny September 6, 2011 Leave a Comment

Keyboard Shortcuts to Enter and Edit Data Efficiently in Excel

Keyboard Shortcuts to Enter Data

This is the latest installment in my series of video tutorials where I demonstrate how to use Keyboard Shortcuts in Excel. In this tutorial, I demonstrate how to Enter and Edit Data Efficiently in Excel using Keyboard Shortcuts.

Tips Covered on Video Tutorial

Here are the main Keyboard Shortcuts that I show you how to use on this video:

Enter Current Date in a cell.
Enter Current Time in a cell.
Use Ctrl + Enter to accept data entry and keep the focus on the current cell.
Use Ctrl + Enter to populate a range of selected cells.
Use Alt + Enter to “Wrap the Text” in a cell.
Use F2 to edit data inside a cell.
Use Ctrl + left & right directional arrows to move to the beginning of the next / previous word while editing in the cell.
Use ‘ (apostrophe) as the first character in a cell to make this a “text” entry.

Download my complete Chart of Popular Keyboard Shortcuts as a PDF

Follow this link to download – for free – my 5 Page Chart of Popular Excel Keyboard Shortcuts as a PDF.

Subscribe to my Video Podcast on iTunes

Here is the link to subscribe to my video podcast, “Danny Rocks Tips and Timesavers” on iTunes. It is offered for free!

Watch Tutorial in High Definition on YouTube

Follow this link to view this Excel Tutorial in High Definition mode on my YouTube Channel – DannyRocksExcels

Share and Enjoy:

Keyboard Shortcuts to Enter and Edit Data Efficiently in Excel [ 11:43 ] Play Now | Play in Popup | Download (1304)

Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, Podcast Tips & Timesavers Tagged: Enter Dates in Excel, Enter Time Values in Excel, Excel Keyboard Shortcuts, Excel Video Training, Tips and Time Savers, Video lessons for Excel, Wrap Text in a Cell

Danny September 5, 2011 Leave a Comment

How to Use Criteria in Excel Functions and Filters

Criteria for Advanced Filter

When you apply a Filter to an Excel data set, you use Criteria. In a filter, only the records that can answer “TRUE” to the criteria that you set will appear. The records that answer “FALSE” are hidden while the filter remains in place.

Logical Functions

The IF(), AND() and OR() Functions belong to a group of Logical Functions. In their function arguments, they evaluate a “Logical Test” and can perform one action is the answer is “TRUE” and a different action if “FALSE.”

The AND() Function – each of the Logical Tests must be met in order to return the answer “TRUE.”
The OR() Function – if any condition is met, the answer that is returned is “TRUE.”
I frequently “Nest” an AND() Function or an OR() Function inside an IF() Function to test multiple criteria.

Advanced Filters in Excel

While many Excel users are familiar with Filtering, in my experience, I find that many people get confused when it comes to using Advanced Filters. In this video tutorial, I try to eliminate this confusion. Watch and you will learn how to:

Write Criteria for Advanced Filters using “AND” criteria and “OR” Criteria.
Copy the “Filtered Records” to another location
Use the Keyboard Shortcut Ctrl + Shift + L to quickly “toggle” normal filter drop-down menus on or off.

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Defintion on my YouTube Channel – DannyRocksExcels

Share and Enjoy:

Filed Under: Data Filters and Queries, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: Advanced Filter for Excel data, AND / OR Criteria for Filters, AND() Function, Criteria for Advanced Filter in Excel, Excel Keyboard Shortcuts, Excel Video Training, Filter a List, IF() Function, Logical Functions, OR() Function, Video lessons for Excel

Danny August 31, 2011 Leave a Comment

Record a Macro in Excel to Fix an Imported Text File

Macro Commands

A viewer asked for my help in fixing a “Text” file that he imports into Excel each month. His IT department creates this file for him. The problem is that:

The Field Header Labels are arranged on two rows – He needs all of the labels to be on one row.
Each record is arranged on two rows – He needs each record to appear on one row.
There is a blank row separating each record – He needs to remove all of the blank rows.

This is a perfect job for an Excel Macro!

In this tutorial, I first demonstrate how to create the Macro in Excel 2010. Then, I repeat the process using Excel 2003 – the version that my viewer is currently using.

Topics Covered in the Tutorial

Adding the Developer Tab to the Ribbon in Excel 2010
Creating back up copies of worksheets before recording the Macro
Using the Fill Across Worksheets (Grouped) to copy both content and formatting
Using Relative References when Recording a Macro
Planning the steps you will take before Recording the Macro
Using Keyboard Shortcuts to Run the Macro
Creating and Editing a Command Button to Run the Macro
Viewing the VBA Code that Excel creates when you Record the Macro
Using Step Into to see how each step in the Macro is executed
Viewing and Changing Security Settings for Macros
Storing Macros in a Trusted Location (Excel 2007 and Excel 2010)
Saving Worksheets that contain Macros in Excel 2007 and Excel 2010

Watch Tutorial in High Definition

Follow this link to view this Excel Tutorial in High Definition mode on my YouTube Channel – DannyRocksExcels.

Share and Enjoy:

Filed Under: Excel Macros, Excel Training, Excel VBA Macros, YouTube Videos by Danny Rocks Tagged: Attach a Macro to a Command Button, Excel Macros, Excel Video Training, Macro Security Settings, Record a Macro in Excel, Run Excel Macros, Use Relative References for Macro, Video lessons for Excel

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Viewer’s Question

My Solution

Watch Video Here on My Website

Watch Excel Tutorial in High Definition

Master Excel Pivot Tables in 90 Minutes!

Select Cells Before Performing an Action on these Cells

Tips Presented in this Video Tutorial

Watch Tutorial in High Definition Mode

Learn About My New Extended Length Excel Video Tutorials

Use SUMPRODUCT Function in Excel

Best Practice Tips for Solver

Watch Part 1 in SOLVER Series of Video Tutorials

Watch Tutorial in High Definition

Activate the SOLVER Tool

Prepare Data for Solver

Part 2 in this Series of Tutorials for Solver in Excel

View this Tutorial in High Definition

Tips Covered in this Video Tutorial

Keyboard Shortcuts Used in this Tutorial

Watch Tutorial in High Definition on YouTube

Visit my new secure online shopping website

Watch Video Now

Three Methods to Identify and Delete Duplicate Records

Watch The Tutorial in High Definition on YouTube

Use a “Logical Formula” for Data Validation

What Else is Covered in this Tutorial?

Related Topic – Conditional Formatting in Excel

Watch Tutorial in High Definition

Tips Covered on Video Tutorial

Download my complete Chart of Popular Keyboard Shortcuts as a PDF

Subscribe to my Video Podcast on iTunes

Watch Tutorial in High Definition on YouTube

Logical Functions

Advanced Filters in Excel

Watch Tutorial in High Definition

Topics Covered in the Tutorial

Watch Tutorial in High Definition

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