Home Archives for Danny
March 9, 2009 Leave a Comment

Do You Want Copies of My Excel Workbooks?

I have a question for you?

I just added an “interactive copy” of my Excel Workbook for my video lesson – “Rearrange Your Columns – Sort from Left to Right”

Here is the link to the workbook.

My question is: Should I go back and add links to the Excel Workbooks for earlier lessons? To make it easy for you to respond, I have included a quick poll to the right sidebar on this page (at the bottom of the sidebar).

Thanks in advance for your response!

Danny

Sorry, there are no polls available at the moment.

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.

Link to this post!
Filed Under: Excel Training, Interactive Webpages, MS Excel Training Tagged: , ,
March 7, 2009 2 Comments

Rearrange Your Columns – Sort from Left to Right

Did you know that you can sort data from Left to Right in Excel? In my experience, most people do not realize that you can.

Have you ever been asked  – or wanted – to rearrange your columns in Excel? Probably, yes. This is a fairly common request. And, if you are like most Excel users, you insert a blank column and then “Cut and Paste” to arrange your columns the way that you want them.

STOP!

There is an easier way to rearrange your columns of data – Learn to “Sort Across Columns.” Sort your data from Left to Right.

Watch this short Excel Video Lesson to learn how:

Here are the steps to follow in this lesson:

  1. Insert a blank “dummy row” above your Column Headers Row (if necessary).
  2. Place a number (1, 2, 3, etc.) in each cell in the “dummy row.” This will indicate the order you want to rearrange your columns in from Left to Right.
  3. Select a cell in your data set. Select Data – Sort.
  4. Check, “MY Data Range has NO Headers.”
  5. Click, OPTIONS and select “Sort from Left to Right.” Click OK and Click OK.

Looking for help with a specific Excel problem? Search my Index of Excel Video Lessons by Category or by Keyword.

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

NEW! Download The Excel Workbook used in this video lesson:

rearrange-columns


Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , ,
February 28, 2009

Statistics Do Not Tell Complete Story

Recently, I was asked to contribute an article on persuasion to another blog. It is a text article. And, I like the way that it turned out.

Here, is a video presentation of that article. Increasingly, I am inclined to contribute video blogs – in addition to traditional text blogging. Both forms of blogging are attractive and each attracts its own audience.

I am interested in hearing your thoughts on both: the topic of video vs. text blogging and on the content of this blog on persuasion.

Here are the Words per Minute Statistics for this article.

  • @475 words.
  • Length is 3:27 minutes
  • WPM Count = 136 – This is slower than my “normal” WPM count of 160.
Link to this post!
Filed Under: Business Communications, Communications, Persuasion, Presentations, Public Speaking, Words per Minute Tagged: , , , , ,
February 28, 2009 4 Comments

Simplify Data Lookups in Excel

In this Excel Video Lesson I demonstrate a great tip that dramatically simplifies data lookups!

Yes, learning how to use the Lookup Functions (VLOOKUP, HLOOKUP, MATCH, INDEX) is important. But, there is an easier way to answer some simple questions about you data.

In this video, I show you how to use the combination of “Named Ranges” and the “Intersection Operator” – the SPACE – to return the value at the intersection of a specific column and specific row. This “tip” is guaranteed to save you time and simplify your data lookups!

Here are the steps to follow in this lesson:

  1. Create “Named Ranges” for your data set.
  2. Use the “Intersection Operator” – the SPACE – in your formula. e.g. =Region_2 Week_16
  3. It’s that simple!

NEW! Download the Excel file that I used in this video lesson:

simplify-data-lookups

Looking for the answer to your Excel Questions? Use my Index of Excel Topics to find the right video

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, Lookup Functions in Excel Tagged: , , , ,
February 24, 2009 2 Comments

Using =COUNTIF() Function to Validate Data

It is great to be back posting on The Company Rocks Excels. Thank you for your patience as I have worked to complete a series of Excel 2007 Training Videos to be published this Spring.

Here are two ways to use the =COUNTIF() Function. First to count the number of employees in each department. And then, as a Formula in Data Valadation to ensure that employee numbers entered in a column are unique values.

Here are the steps to follow in this lesson:

  1. Use the =COUNTIF() Function to count the number of values in a Range that match the Criteria that you set.
  2. We have already named the Range $A$2: $B$27 as “Department.” Let’s use the F3 (Paste Names) Shortcut to select this for our Range Argument.
  3. For the Criteria, point and click the cell in Column D that contains the label for our first department (Accounting) and Click OK.
  4. You can now copy the formula down to the other cells in Column E – This gives you a “Head Count” by Department.
  5. For our second example, we are going to use DATA – VALIDATION to ensure that only “unique values” will be accepted in Column A – The “Employee Number” Column.
  6. On the SELECT tab “Allow” drop-down, click “Custom.” Enter this formula in cell A2:
  7. =COUNTIF($A$2:$A$27, A2)=1 This ensures that only unique values will be accepted. Note the use of “Absolute” and “Relative” references in the formula. The “Range” is stated as an “Absolute” $A2:$A$27
  8. You will need to  copy the Data Validation from cell A2 down to this rest of the cells in the range that you wish to validate. In this video I use Paste Special – Validation.

Find the Excel Training Video that you want in the Index of 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 Videos

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , ,
February 17, 2009

Will be back posting Excel Video Lessons – Soon!

Hello Friends!

The 50 Best Tips

The 50 Best Tips Series

I have been working on an Excel Training Video project for commercial release – hence, my lack of postings.

This project has required focused attention and research. I have almost completed this project – and I will be back posting additional Excel Video Lessons this week. Thank you for your patience, understanding and continuing patronage.

I have now completed all five of the Microsoft Office 2007 Training DVD-ROMs. You can purchase The 50 Best Tips for – Excel, PowerPoint, Word, Outlook and Access 2007 online using my secure shopping cart. Click here to begin shipping.

Sincerely,

Danny Rocks

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.

Link to this post!
Filed Under: Excel Training, MS Excel Training, New in Excel 2007, Online Store for The Company Rocks Tagged: , , , , , ,
December 30, 2008 3 Comments

Use =INDIRECT() Function to Connect 2 Pick-From Lists in Excel

This video is in response to a viewer who wants to join two columns containing Data Validation “Pick-from” lists. She knew that there was a Formula that could perform this, but she didn’t know how to accomplish this.

I promised to create this Excel video lesson to show her how – using the =INDIRECT() Function.

Here are the steps to follow in this lesson:

  1. Create lists for each group you want to be able to select from. Division and then in Departments (Sales, Marketing, etc.)
  2. Name each of these lists. Use either the NAME BOX or “Insert – Name – Create” (Select the Label and the Values).
  3. For the 1st Column – Division – use “Data – Validation” and in the “Allow” drop-down, choose “List.”
  4. Use the F3 shortcut to bring up the Named Range Dialog Box. Select “Division.” Type in a message for the Screen Tip and click OK.
  5. For the 2nd Column – the one whose “Pick-from” list will be based on the Values from the 1st Column (Division), we start with “Data – Validation” and in “Allow” choose “List.”
  6. Here is where we use the =INDIRECT() Function in the “Refers To” box. Click to select the cell in the Same Row in the 1st Column. Note: Be sure to make the Cell Reference Relative so that the formula can be copied down the column.
  7. I created the Validations in the Top Cell in each Column. Use Copy, Paste Special, Validation to apply the “Pick-from” validation to the remaining cells.

This is not a “Perfect” solution. As you can see, when you change the value in the first drop-down list, the value in the second list remains in place. What does change, however, is the list of values now available in the second drop-down list – waiting for you to make your selection!

Looking for a specific Excel Video Lesson? – Index of 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.

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Lookup Functions in Excel, Video Response to Viewers Tagged: , , , , ,
December 20, 2008

I Have 21 Excel Videos Rated 5-Stars on YouTube

YouTube Logo

YouTube Logo

Here is a listing of my 21 Excel Video Lessons that are rated “5-Stars” on YouTube.

I hvae organized the videos by category. The First Hyperlink will take you to to the videos on this site. The “indented” Hyperlink will take you to the videos on my YouTube site –  DannyRocksExcels.

I hope that you find a few tips to save you time or answer a question. I welcome your feedback. Enjoy!

Pivot Tables

“What-if” Analysis

Consolidation and SubTotals

Filter & Sort Lists in Excel

Financial Functions in Excel

Logical & Lookup Functions in Excel

Text Functions

Formula Auditing

Formatting and Conditional Formatting

Paste Special Options

Excel Charts

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 Videos

Link to this post!
Filed Under: Excel Training, Most popular articles, Most Viewed Excel Videos, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
December 19, 2008 Leave a Comment

Navigate to Excel Worksheets with Hyperlinks

I encourage my clients to organize their work in Excel workbooks by creating additional worksheets. Each worksheet should contain discrete information relating to your Excel project – e.g. Monthly or Quarterly budgets; Regional sales, or information about specific product lines.

A best practice is to rename each worksheet to reflect the information it contains. I also encourage clients to add a “Tab Color” to each worksheet. This makes it easy to identify the “Active” worksheet.

Many of my clients use 20 or more worksheets in their workbooks and they ask my help in navigating to a specific worksheet or cell range.

One strategy that I share with them is to create Hyperlinks to go to a specific worksheet. Another strategy involves naming specific cells or ranges. Once you have “named” a cell or a range, simply click the drop down arrow next to the Name box; select the named range and Excel takes you directly to that “Name.”

These are great time-savers and they boost productivity. Watch this video to see these techniques in action:

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

  1. Click a worksheet and then use Ctrl+Page Down to activate the next worksheet to the Right. Ctrl+Page Up activates the next worksheet to the Left.
  2. Create a new worksheet and give it a name like “Dashboard.” Type in text like “Link to XYZ Worksheet.”
  3. Ctrl+K opens the Create Hyperlink Dialog Box. Fist choose “Place in this Document.” Then select the name of the worksheet and click OK.
  4. The easiest way to “Name” a cell or a region is to highlight it and then go to the “Name Box” to type in a name. Names must begin with a letter and not use spaces. Click ENTER to register the name.
  5. Use the drop=down arrow next to the Name Box to find and go to a named cell or range. You can also create a hyperlink to a named cell or range.

Looking for a specific Excel Video? Click here for the Index to all Excel Video Lessons

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.

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , , , ,
December 19, 2008 Leave a Comment

Select Excel Data with Keyboard & Mouse-click Shortcuts

During a recent training class, I demonstrated several Keyboard and Mouse-click shortcuts for selecting and finding data. Several people in the class had “A-Ha” moments. So, I created this video lesson to share these shortcuts with you.

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

  1. To select all of the contiguous data cells, make one cell the “Active Cell”. Ctrl+A will then select all of the cells in that data block.
  2. If you then click Ctrl+A a second time, you will select every cell in the worksheet. This is handy when you need to “AutoFit the column width in the worksheet.
  3. Use Ctrl+End to go to the last cell in your data set. Ctrl+Home will return you to the Top cell in the data set.
  4. To find a blank cell in a column, position your mouse at the bottom of the “active cell” and double-click. This takes you to the last cell that contains data in that column.
  5. To select all of the cells w/ data in a column use the Ctrl+Shift+ Down Arrow. Use the appropriate Arrow Key to select cell containing data in a Row.
  6. Quickly copy a Formula to all of the cells in the column. Position the mouse in the lower right corner of the cell with the formula and double-click.

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 Videos

Find a specific Excel Video Lesson – Index of Excel Video Lesson Topics

NEW! Download the Excel Workbook file that I used in this lesson:

Related Video Lessons

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , , ,
« Previous Page
Next Page »