Excel Functions | The Company Rocks

Danny November 29, 2010 1 Comment

My Most Viewed and Downloaded Videos for Excel and PowerPoint

Danny on iTunes

I began to post my videos as podcasts at the iTunes store this past summer. Since then, my video lessons for Excel and PowerPoint have been viewed and downloaded many times. I am thirlled. And, I thank you for your support and encouragement.

Here are the links – on my website – to the most popular video episodes. I have organized them by category in the order of their popularity. You can click on any link to view or download that video from my website. Or, you can go to my video Podcast by clicking on this link – “Danny Rocks Tips and Timesavers” Podcast on iTunes.

Merging and Consolidating Excel Worksheets

How to Merge Multiple Excel Workbooks
Summarize Multiple Excel Worksheets – Consolidate by Position

PowerPoint Presentations

Data Visualizations

Make Excel Data Come Alive Visually

What-If Analysis in Excel

Importing Data From Other Programs

How to Import Text and Database Files in Excel 2007

Pivot Tables

Excel Tables

The Five Benefits of Using Excel 2007 Tables

Social Media

Using Social Media to Engage Your Customers

Formula Errors

How to Prevent Formula Errors from Displaying in Excel 2007

Please add your comments below – or on my iTunes Channel. I welcome your feedback.

You can learn how to “Master Excel in Minutes – Not Months!”

Sincerely,

Danny Rocks

Share and Enjoy:

Filed Under: Consolidate Excel Data, Excel Data Tables and Lists, Excel Functions, Excel Training, Goal Seek in Excel, Import Data into Excel, Integrating MS Office Programs, Most Viewed Excel Videos, MS Excel Training, New in Excel 2007, Pivot Tables, Podcast Tips & Timesavers, Powerpoint Techniques, Presentations, Public Speaking, What-if Analysis Tagged: "What-if" Analysis in Excel, Communications, Consolidate Excel data, Data Validation, Excel Video Training, Goal Seek, Group Dates in Pivot Tables, New in Excel 2007, PowerPoint Presentations, Source Data for Pivot Tables, Summarize Date Fields in Pivot Tables, Tips and Time Savers, Video lessons for Excel

Danny November 27, 2010 2 Comments

How to Use the Indirect Function to Create Excel Dashboards

You can use the Indirect Function to create an Excel Dashboard. A Dashboard gives you an overview of your data – in this case, an interactive view of the information hidden in our data set. I will create Drop-Down lists using Data Validation in the Dashboard. The Indirect Function takes a little bit of time to understand. Here are some key points:

The Indirect Function works best with Named Cell References
The syntax is =INDIRECT(reference text). In our example the “reference text” will be the label for a Named Range of cells.
You can “nest” the Indirect Function inside another function – e.g. =SUM(INDIRECT())

Later in the lesson, I show you how you can quickly look up any cell at the “intersection” of a Named Row and a Named Column – e.g. April East. You use the SPACE BAR as the “Intersection Operator” to point to the cell where “April” meets “East.”

Excel Tips

Some of the Keyboard Shortcuts that I use in this lesson are:

Ctrl + A to select all of the cells in a Data Set
F3 to bring up a list of all the Named Ranges that have been created in the current workbook
Ctrl + Shift + F3 to create Named Ranges for the cells in the selection – using as Names, the Labels in the Top Row / Left Column

Here is a link to the 1st lesson in this series on the INDIRECT Function in Excel.

Follow this link to watch this lesson in High Definition, Full Screen Mode on YouTube.

You can learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

How to Use the Indirect Function to Create Excel Dashboards [ 7:20 ] Play Now | Play in Popup | Download (1168)

Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: Data Validation, Excel Dashboards, Excel Keyboard Shortcuts, Excel Video Training, Lookup Functions in Excel, Named Cells and Ranges

Danny November 17, 2010 1 Comment

Use the Choose Function in Excel to Quickly Lookup a Value

The CHOOSE Function in Excel is an incredibly useful – albeit, relatively unknown – tool to use when you need to look up a Value in a list. In Excel 2007, you can now use CHOOSE to return up to 254 different Values in a list! (In Excel 2003, the limit is 29 values.)

Here is the Syntax: =CHOOSE(Index_Num, Value1, Value2…) where the Index_Num is a positive serial number between 1 and 254 (In Excel 2007)

In this lesson, I first demonstrate how to look up a “label” (January, February, etc.) for a cell that contains a “Number” for the month (1,2,3, etc).

Rather than struggle with “nested IF()statements,” use the CHOOSE Function when you need to return a value for any number between 1 and 254 – it is so much easier!

Click here to watch this Excel Lesson on YouTube in High Definition Full Screen Mode.

Learn how to “Master Excel in Minutes – Not Minutes!”

Share and Enjoy:

Use the Choose Function in Excel [ 5:56 ] Play Now | Play in Popup | Download (865)

Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: Choose Function in Excel, Excel Functions, Excel Video Training, Lookup Functions in Excel, Lookup Values in a List in Excel, Video lessons for Excel

Danny October 27, 2010 Leave a Comment

How to Lookup a Value Using Multiple Columns in Excel

There are times when the VLOOKUP Function will not return the value that you want to lookup. Suppose, for example, that you need to lookup the “Title” for an employee where the First Name and Last Name are in separate columns. A VLOOKUP() will not help you in this example. Let me show you how to accomplish this.

Key Concepts Covered

Use the “&” (Ampersand) to Join two cells that contain Text Values
Use the Index() and Match() Functions for the Lookup
Create an Array Formula to perform the Lookup – Use “Ctrl+Shift+Enter” with Array Formulas
Create and use “Named Cell Ranges” in your formulas to make it easier to enter and understand the formula

Be sure to check out all of the other Excel Video Lessons on Lookups. Click here to go to the Archive of Lookup Function Videos.

I invite you to subscribe to my “Danny Rocks Tips and Timesavers”Video Podcast on iTunes – it is free!

You can learn how to “Master Excel in Minutes!”

Share and Enjoy:

How to Lookup a Value Using Multiple Columns [ 6:00 ] Play Now | Play in Popup | Download (1001)

Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: Ampersand Operator, Array Formulas in Excel, Excel Video Training, Lookup Functions in Excel, Match and Index Functions, Named Cells and Ranges

Danny October 25, 2010 Leave a Comment

How to Use the VLOOKUP Function in Excel

The VLOOKUP Function is one of the essential Excel functions that you need to understand in order to work with data tables, invoices and product listings. In response to numerous email requests, I created this video lesson to walk you through each step.

I share my best practices which include:

Use a “named cell range” for your “array.” The “array” is the table that contains the information that you are looking up.
Make sure that the “Leftmost” column in your array is sorted in ascending order and that this column contains the values that you are looking for.
Use “FALSE” as the optional fourth argument when you need to find an “exact” match in the Leftmost Column.

Work with Excel Workbook Used in This Lesson

Purchase DVD-ROM

You can learn more about essential Excel Functions on my DVD, “The 50 Best Tips for Excel 2007.” You can buy it now!

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

How to Use the VLOOKUP Function in Excel [ 5:36 ] Play Now | Play in Popup | Download (1006)

Filed Under: Excel Functions, Excel Training, Interactive Webpages, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: Absolute cell reference, Excel Video Training, Lookup Functions in Excel, Named Cells and Ranges, Vertical Lookups, Video lessons for Excel, VLOOKUP Function

Danny October 20, 2010 1 Comment

Create Interactive Excel Forms Using Option Button Controls

This is the third in my series of video lessons that show you how to add Form Controls to an Excel worksheet. Option Buttons, inside a Group Box, are a great way to make your forms “interactive” – to give your end-user multiple options. For example, a choice of shipping methods for their order. Or, to see how different “down payment” options effect their mortgage payments.

The key to understanding how Excel Form Controls operate is to see how the value in the “cell link” changes when you choose an option. The cell link for an Option Button translates a “text expression” into a numeric value. For example, “Surface” translates to “1” for a shipping option.

In this Excel Training Video, I introduce the =CHOOSE() Function. It is rare that I find a client who has ever used this function. Once you see how useful it is, you will start to use it instead of struggling through multiple =IF() Functions. It is really a great “hidden gem” of an Excel Function!

Click on this link to go to my Archive of Excel Video Lessons on Forms.

You can also find additional groups of related videos by selecting a “Category” from the drop-down menu on the right side bar of any page on my website.

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Create Interactive Excel Forms Using Option Button Controls [ 8:33 ] Play Now | Play in Popup | Download (1641)

Filed Under: Excel Forms, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: "What-if" Analysis in Excel, Cell Link in Excel Forms, Choose Function in Excel, Excel Form Controls, Excel Functions, Excel Video Training, Lookup Functions in Excel

Danny October 18, 2010 Leave a Comment

Create User Friendly Excel Forms Using Spin Button Controls

An Excel Form is great way to distribute an Excel worksheet to your customers or field representatives. Most of the time, you do not want them to type values into a cell. You want to make this Form as “user friendly” as possible! You want to add “Spin Buttons” to your form, so that the user can simply click to change cell values up or down in the increments that you want to display.

A perfect example: A Mortgage Loan Scenario where – by clicking the Spin Buttons – your client can see what their monthly payment will be. This is my second lesson covering Excel’s Form Control Tools. Watch as I demonstrate how to “work around” the Form Controls limits of Maximum Amount for a Spin Button (30,000) and also how to increment interest rates by 1/4 of a Percent.

From personal observation, I know that many people start to create an Excel Form and then just give up in frustration. I did too – in the beginning! Watch me demonstrate how to avoid the traps of the “got’cha” steps in this short Excel Training Video.

Check out the great tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” Click here to start shopping.

Learn how you can “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Create User Friendly Excel Forms with Spin Button Controls [ 8:54 ] Play Now | Play in Popup | Download (1298)

Filed Under: Excel Financial Tools, Excel Forms, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: "What-if" Analysis in Excel, Cell Link in Excel Forms, Excel Form Controls, Excel Video Training, PMT() Function, Video lessons for Excel

Danny September 21, 2010 Leave a Comment

How to Use Excel Lookup Functions to Create a Customer Invoice

Excel has many powerful Lookup Functions. You can use them, for example, to lookup an Item Number when you know the Product Description. Or to automatically look up the List Price for an Item Number on your customer invoice. In this lesson I show you how to use three Excel Lookup Functions:

The INDEX() Function
The MATCH() Function
The VLOOKUP() Function

I hope that you enjoy learning – and applying – these techniques. I would like to hear from you, so you can either add a comment below or send me an email.

You can receive my new Tips and Timesavers Videos automatically by subscribing to my RSS Feed or to my free podcast on iTunes. Click here to view, download or subscribe to my podcast.

You can purchase any of my 5 DVDs in “The 50 Best Series …” for Excel 2007, PowerPoint 2007, Word 2007, Outlook 2007 and Access 2007 – Click here to open your shopping cart!

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

How to Use Excel Lookup Functions to Create a Customer Invoice [ 5:24 ] Play Now | Play in Popup | Download (789)

Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers, Uncategorized Tagged: Data Validation, Excel Video Training, Lookup Functions in Excel, Match and Index Functios, Named Cell in a formula, Named Cells and Ranges, Tips and Time Savers, Video lessons for Excel, VLOOKUP Function

Danny September 17, 2010 Leave a Comment

Two Ways to Create a Frequency Distribution in Excel

What is a “Frequency Distribution?” A good example is to see how frequently daily sales are “under $75.00” or how often they range “between $75.00 and $150.00.” Once you know how frequently a result occurs, you can better focus your attention on that particular segment of your business.

In this short 5 minute and 45 second Excel Training Video Lesson, I demonstrate two approaches to creating a Frequency Distribution Report:

Use the =FREQUENCY() Function – this is an ARRAY Function.
In a Pivot Table, Group the Row Labels to produce a Frequency Distribution.

An ARRAY Function has two “Got’cha steps:”

Select all of the cells that will contain the results before your enter your Array Formula.
Use the Ctrl + Shift + Enter keyboard combination to enter your Array Formula.

You can watch this video here on my website, you can download it via an RSS Feed or you can watch it as a Podcast at the iTunes Store.

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Two Ways to Create a Frequency Distribution Report in Excel [ 5:45 ] Play Now | Play in Popup | Download (818)

Filed Under: Excel Formulas, Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: Array Formulas in Excel, Excel Pivot Tables, Excel Video Training, Frequency Distribution Report, Group Labels in Pivot Tables, Pivot Table Calculations, Video lessons for Excel

Danny September 14, 2010 Leave a Comment

How to Use an Excel Data Table for “What-if” Analysis

Excel has many tools that give you answers to “What-if” questions. An Excel Data Table allows you to feed a series of “substitute values” into two arguments in a formula. For example, with a Data Table you can:

See a table of monthly payments on a loan by substituting both the “Interest Rate” and the “Amount Borrowed” simultaneously.
See a table of “Net Payments” by changing both the “Sales Price” and the “Quantity” simultaneously.
See the amount that you “tip” a service professional by changing both the “Tip Percentage” and the “Food Total” simultaneously.

As you will see in this Excel Video Lesson, the trick is to understand which series of values are the “Row Inputs” and which series of substitute values are the “Column Inputs.”

Trust me on this: It is easier to “see” how to set up a Data Table than it is to “write a description” of a Data Table. So, I invite you to “see for yourself” how easy it is to set up an Excel Data Table and get answers to two simultaneous “What-if” questions!

Click here to see a Listing of “The 50 Best Tips for Excel 2007.”

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

How to Set up Excel Data Tables for "What-if" Analysis [ 6:43 ] Play Now | Play in Popup | Download (617)

Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers, What-if Analysis Tagged: "What-if" Analysis in Excel, Array Formulas, Change Two Variables in Excel Formula, Data Validation, Excel Data Tables, Excel Video Training, Substitute Values in Formulas, Video lessons for Excel

Danny Rocks “Training You to Excel”

How to Use the Indirect Function to Create Excel Dashboards

Use the Choose Function in Excel to Quickly Lookup a Value

How to Lookup a Value Using Multiple Columns in Excel

Key Concepts Covered

How to Use the VLOOKUP Function in Excel

Work with Excel Workbook Used in This Lesson

Purchase DVD-ROM

Create Interactive Excel Forms Using Option Button Controls

Create User Friendly Excel Forms Using Spin Button Controls

How to Use Excel Lookup Functions to Create a Customer Invoice

How to Use an Excel Data Table for “What-if” Analysis

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

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Merging and Consolidating Excel Worksheets

PowerPoint Presentations

Data Visualizations

What-If Analysis in Excel

Importing Data From Other Programs

Pivot Tables

Excel Tables

Social Media

Formula Errors

Key Concepts Covered

Work with Excel Workbook Used in This Lesson

Purchase DVD-ROM

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