Home Archives for Danny
June 27, 2012 1 Comment

How to Add Summary Information for Excel Workbook Properties

Almost everyone has experienced the frustration of being unable to locate an Excel Workbook on their computer. You can’t remember the name or the location of the Workbook. You waste valuable time searching in vain.

Tags for Document Search

Tags for Document Search

One way to minimize this frustration is to add Excel Workbook Properties that Summarize the content and purpose of the document. By adding Tags, Keywords, Client Names or Project Titles in the Properties Summary you make it easier for your computer to Index and Find your documents.

Fortunately, beginning with Microsoft Office 2007, it is a lot easier to add these properties to an Excel Workbook. In this video tutorial, I show you how to do this.

Displaying Recent Documents

Did you know that you can display up to 50 Recent Documents? Even better, you can “Pin” important documents so that they remain on the list. This is a valuable tool when you need to access important files, for example, once a month! I show you how to do this on the video.

Shop for Additional Resources

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can purchase all of the training materials that I have developed.

Watch Video in High Definition

Click on this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Watch this Video Now

 

Link to this post!
Filed Under: Excel Training, New in Excel 2007, New in Excel 2010, Workbooks and Worksheets in Excel Tagged: , , , , , , , , ,
June 24, 2012 Leave a Comment

How to Create Hyperlinks in Excel

Hyperlink Dialog Box in ExcelOne of my viewers – Ben from the UK – wrote to tell me that he needs to learn how to create Hyperlinks in Excel in preparation for a job interview this week. He asked me that, since he could not find a decent YouTube video on this topic, to create a tutorial demonstrating ow to create Hyperlinks in Excel.

Hey, in these tough economic times, I am happy to help as many people as I can. However, given my time constraints – and, my need to make a living from my video resources – I invite you to purchase one or more of my many resources on my secure online shopping website – http://shop.thecompanyrocks.com

Topics Covered in this Excel Video Tutorial

In this video tutorial, I demonstrate how to:

  • Create a simple Hyperlink to another worksheet in your Excel workbook
  • Create Hyperlinks that “Return you” to your main “Dashboard” worksheet
  • Hyperlink to other programs – e.g. PowerPoint
  • Create hyperlinks that direct a viewer to send you an E-Mail message
  • Hyperlinks to websites

I am the first to admit that the Insert / Edit Hyperlinks Dialog Box is confusing and “too busy” for my tastes. However, in this video tutorial, I demonstrate how to navigate through this Dialog Box and also, how to get te most out of it. For example, how to change the “text displayed” for the Hyperlink while working in the Dialog Box.

Watch  this Video in High Definition

Click here – on this Hyperlink – to watch this video in High Definition on my YouTube Channel – DannyRocksExcels

View this Totorial Now!

Purchase My Resources to improve your Excel Skills

If you are in a similar situation as Ben – where you need to demonstrate specific knowledge of MS Office Programs Skills – why trust your luck? Rather, improve your skills – and your odds for securing a new job or a promotion!

I invite you to purchase one or more of my down-to-earth video tutorials. Visit my secure online shopping website – http://shop.thecompanyrocks.com

Link to this post!
Filed Under: Excel Basics, Excel Training, Integrating MS Office Programs, Keyboard Shortcuts, Video Response to Viewers Tagged: , , , , , ,
June 2, 2012 3 Comments

Microsoft Access – A Quick Video Tour of my DVD, The 50 Best Tips for Access 2007

Microsoft Access and Microsoft Excel work together seamlessly. However, they are VERY DIFFERENT programs. It is very easy to exchange data between the two programs. But there is a world of difference when in it comes to storing, entering, and analyzing data in each program.

Recently, a number of my viewers have asked me how to create Data Forms in Excel. In almost every case, they want a simple answer – one that does not require them to learn VBA – Visual Basic for Applications. My considered response is that Access is the best way to collect and relate data via Forms. And then, use Excel to analyze the information that you collect from those data forms.

Here is a short – 6 1/2 minutes –  video tour of my DVD-ROM, “The 50 Best Tips for Access 2007.” If you have never used Microsoft Access, this video will give you an idea of how it works. If you have tried to use Access, but gave up in frustration, I am confident that my video tutorials will help you to get over the learning curve so that you can use Access to its full potential!

Nine Categories of  Video Tutorials for Access 2007

On my DVD-ROM, I have organized the 50 Video Tips into nine categories of lessons:

  1. Getting Started with Access 2007 – 9 Videos. Perfect introduction for new users of Access as well as a quick guide the the changes and additions in Access 2007 and Access 2010.
  2. Working with Tables in Access 2007 – 5 Videos. Tables are the essential building blocks in Access. Learn how to create many focused Tables and then create relationships between them.
  3. Using Filters & Queries in Access 2007 – 8 Videos. Filters are “temporary” or one-time questions. Queries are “saved” or “named” questions about the information in your database.
  4. Running Action Queries in Access  2007 – 4 Videos. Action queries allow you to quickly make changes to the data in Access. You can update, or delete records; append records or make a new table from a query.
  5. Working with Access 2007 Forms – 9 Videos. Forms are very easy to create in Excel. Use forms to enter data or to view your data – one record at a time. Gather data via Forms in Access and then export this data to analyze in Excel.
  6. Working with External Data in Access 2007 – 4 Videos. Did you know that you can use Access to semi-automatically update your Microsoft Outlook contact information? Learn how to integrate the programs in the MS Office Suite.
  7. Working with Access 2007 Reports – 5 Videos. Reports are designed for printing and distributing the summarized information in you Access database. Learn how to create a mailing label report for your next direct mail campaign!
  8. Managing Access 2007 Databases – 3 Videos. Learn how easy it is to back up your database. You can even “split” a larger database into smaller, more focused databases.
  9. Automating Access 2007 – 3 Videos. Learn how to use Macros and Command Buttons that allow less-experienced end-users enter and output the information in your Access database.

Table of Contents for “The 50 Best Tips for Access 2007.”

Click here to see specific data – including run times – for each of the 50 video lessons on my DVD-ROM.

Purchase my DVD-ROM – “The 50 Best Tips for Access 2007”

To learn more about my DVD-ROM, I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com I guarantee your satisfaction 100%. If you are not satisfied with any of my products, you may return them – without questions – at any time!

Watch Video in High Definition

Follow this link to watch this video lesson in High Definition – on my YouTube Channel, DannyRocksExcels

 

Video Tour of my DVD-ROM, "The 50 Best Tips for Access 2007" [ 6:32 ] Play Now | Play in Popup | Download (1268)
Link to this post!
Filed Under: Access Tips and Timesavers, Integrating MS Office Programs, Podcast Tips & Timesavers, Video Training Courses Tagged: , , , , ,
April 20, 2012 Leave a Comment

How to Perform Time Period Calculations in Excel

Time Calculations in Excel

Time Calculations in Excel

Judging by the number of calls that I get from my viewers, working with Time Period Calculations is one of the most problematic challenges in Excel. My goal for this tutorial is to demonstrate how you can easily avoid the frustrations when you calculate elapsed time and total a series of time – e.g. hours worked during the week.

Apply a Custom Format to Formula Cells

When you create an Excel Formula that subtracts a Start Time from an End Time, you are likely to get a result that looks like 2:30 AM. This is not what you want to see! The solution is to apply a Custom Time Format – e.g. h:mm – to the formula cell(s).

Time that “Spans Midnight”

Many people work the “Graveyard Shift.” The start work late in the evening and they end work early the next morning. The problem that occurs when you want to calculate the number of hours worked is that Excel believes that you are performing a “negative time” operation and the result is a cell filled with ######## (Hash Marks). This happens because Excel thinks that both the Start Time and the End Time belong in the same day. To solve this, you could use an IF() Function formula. However, there is a much easier formula to use as I demonstrate in this video. It uses the MOD() Function.

Total Hours Worked

Use the SUM() function to total 8:00 hours worked each weekday. You are expecting to see 40:00 hours worked. So why does Excel return 16:00 hours instead? By default, Excel interprets 24:00 hours as a single day. So it subtracts 24:00 from the 40:00 hours worked to return 16:00 hours. Unless you make a formatting change to the formula cell you are going to have many unhappy and under paid employees.

The solution? Apply the [h]:mm Custom Formatting to the Formula Cells. Amazingly enough, Excel does not include [h]:mm as one of the Custom Formats in the list. So, simply edit one of the other formats and you will now have the correct answer displayed for your formula!

Learn More Tips for Excel

I invite you to visit my secure online shopping website – http:shop.thecompanyrocks.com – to preview all of the resources that I offer including “The 50 Best Tips for Excel 2007” DVD-ROM.

Subscribe to the “Danny Rocks Tips and Timesavers” Video Podcast on iTunes

I offer a free video podcast for many of my video tutorials at the iTunes Store. Follow this link to see all of the Podcasts that I have posted. If you like what you see, I invite you to subscribe to this free service.

Watch Tutorial in High Definition

You can view this tutorial in High Definition on my YouTube Channel – DannyRocksExcels.

 

How to Perform Time Period Calculations in Excel [ 7:55 ] Play Now | Play in Popup | Download (1635)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers Tagged: , , , , , , ,
April 9, 2012 Leave a Comment

How to Roll Up a Summary by Month to Filter an Excel Pivot Table

Filter Using a Roll Up by Month Summary

Filter with a Roll Up by Month Summary

In this Excel tutorial, I respond to a viewer request. He likes the new “Roll Up Summary by Month” feature for filtering a field in an Excel 2007 – 2010 Field. What he finds frustrating – there seems to be no natural way to accomplish this with an Excel Pivot Table.

Natural Language Date Filters in Excel

Before I solve my readers dilemma, I demonstrate how to take advantage of the new “Natural Language” Date Filters that were introduced in Excel 2007. Date Filters allow you to filter records from “Today,” “Last Week,” “Next Month,” etc. They are available for Excel Tables and Excel Pivot Tables. These “Natural Language” Date Filters are a major improvement in Excel!

Group a Field for Pivot Tables

To solve my viewers question, I “Grouped” the original Date Field in his Pivot Table to produce “virtual” fields for “Month,” and “Year.” Now, it is a simple step to filter the “virtual” Month Field to obtain a “roll up” filter for individual months in the Pivot Table. Just select a single cell in the Pivot Table Date Field and choose Group Field. Make your choices in the Grouping Dialog Box and you are “good to go!”

I also show you how to take advantage of the Expand and Collapse Field Commands in a Pivot Table.

In-Depth Video Tutorial for Excel Pivot Tables

At my secure, online shopping website, you can purchase my 90-minute Video tutorial for Excel Pivot Tables. Available for immediate downloading or on a DVD-ROM. Version specific editions for Excel 2003, 2007, and 2010.

Watch Video Tutorial in High Definition

Follow this link to watch this tutorial in High Definition Mode on my YouTube Channel – DannyRocksExcels

 

 

 

How to Create a Roll Up by Month Filter for an Excel Pivot Table [ 5:42 ] Play Now | Play in Popup | Download (1696)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Pivot Tables, Video Response to Viewers Tagged: , , , , , , , ,
March 19, 2012 Leave a Comment

Use Conditional Formatting in Excel to Highlight Cell Values that Match Criteria

Conditional Formatting in Excel

Conditional Formatting in Excel

Conditional Formatting has been available for many years in Excel. However, beginning with Excel 2007, Conditional Formatting got a major boost in its ease-of use and new visualization tools.

In this tutorial, I demonstrate how to use Conditional Formatting to highlight – via formatting – the cells in a range that anser “TRUE” to a set of criteria. For example, is the cell value “Above Average” or is it Greater than the value in a Target Cell.

How Does Conditional Formatting Work?

Conditional Formatting is “dynamic.” If you update the values – or formulas that generate cell values” the cell that receive the Conditional Formatting may change – based upon the criteria that you set as “the condition.”

Versions of Excel Covered

For this lesson, I demonstrate Conditional Formatting in BOTH Excel 2010 and Excel 2003.

Learn More About Conditional Formatting

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to see all of the resources that I offer. This includes my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

Watch Tutorial in High Definition

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

View Tutorial Now

Link to this post!
Filed Under: Conditional Formatting, Excel Training, New in Excel 2007, YouTube Videos by Danny Rocks Tagged: , ,
March 6, 2012 1 Comment

How to Analyze Point-of-Sale Data with an Excel Pivot Table

Advantages of Pivot Tables

Advantages of Pivot Tables

This is the first in a series of tutorials that I am creating in partnership with Tri-Technical Systems – a leading provider of Point-of-sale (POS) Systems. In this video lesson, I use an Excel Pivot Table to present the information that I require from a standard “Inventory by Location” report.

Point-of-Sale Reports

Most POS Systems allow you to print out standard reports – compact, professionally formatted “snapshots” of your inventory status, sales data and customer information. Likewise, most POS Systems will allow you to easily export the data behind these reports to Excel – where you can analyze or “number crunch” the data.

Advantages of Pivot Tables

  • Pivot Tables combine the best elements of Subtotals, Outlines and Filtered Reports.
  • With a Pivot Table, you select only the Fields that you wish to focus on.
  • You can quickly reposition any field on your Pivot Table – e.g. change it from a Vertical to a Horizontal position.
  • Pivot Tables allow you to easily add multiple summaries – e.g. Sum, Average, Percentage of Total, etc. without writing a Formula!
  • It is impossible to harm your underlying data when you work with a Pivot Table because you are working with a “virtual snapshot” of your data. You cannot directly change any value in a Pivot Table Report!

Learn More About Pivot Tables

Pivot Tables are easy to learn. However, it does take practice if you want to really tap into the analytical power of  a Pivot Table Report. Fortunately, I have a great resource for you – a 90 minute focused video tutorial on Pivot Tables. Follow this link to go to the information page for my Excel 2010 Pivot Table DVD-ROM. I have also created Pivot Table videos for Excel 2007 and Excel 2003.

Watch Tutorial in High Definition

You can watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

View My Video Now

 

Link to this post!
Filed Under: Excel Training, MS Excel Training, Pivot Tables Tagged: , , , , ,
March 1, 2012 Leave a Comment

How to Protect Cells that Hold Formulas in Excel

Protect Cells w Formulas

Protect Excel Formula Cells

Learning how to Protect the Excel Worksheet Cells that contain Formulas is a three-step process. This process tends to confuse and frustrate many Excel users. Some of the steps seem counter-intuitive when you first begin the process. That is why I created this video tutorial – To demonstrate each of the steps; to remove the confusion.

First, I show you how this is done in Excel 2010 / Excel 2007. Next, I show you how to protect the Formula Cells in Excel 2003.

Key Concept for Protecting Formula Cells

By default, all cells in an Excel Workbook are “Locked!”

So, if you are going to allow a user to input current values in the worksheet, you must first “select” the input cells and then, “Unlock” them.

Protecting Excel Worksheet

The final step in the process is to Protect the Worksheet. I rarely use a password for the worksheet because I tend to forget it and then I cannot even access the protected worksheet!

Take care in the Protect Sheet Dialog Box to select only the options that best suit your purpose. For example, in this video tutorial, I choose to limit users to select only the cells that I unlocked in steps 1 and 2.

Go To Special Dialog Box

The quickest way to select the cells that you wish to “unlock” is to open the Go To Special Dialog Box. There, choose “Constants.” You can limit the constants to “Numbers” as I do in this tutorial.

Danny Rocks Tips and Timesavers Podcast on iTunes

I invite you to subscribe to my “free” video podcast on iTunes. Click on this link to access all of my Podcast Videos.

Watch Tutorial in High Definition

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

 

 

How to Protect Cells with Formulas in Excel [ 7:32 ] Play Now | Play in Popup | Download (2394)
Link to this post!
Filed Under: Excel Formulas, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , , , , , , ,
February 19, 2012 Leave a Comment

How to Use Named Ranges in Existing Excel Formulas

Paste Named Ranges in Formula

Paste Named Ranges in Formula

I am a strong proponent for using Named Cell Ranges when creating Excel Formulas. But what if you have already created formulas – formulas that use cell references. How do you insert or apply a newly created named range into an existing Excel Formula?

Simple answer – watch this short video to see me demonstrate how this is done.

Better answer:

Follow These Steps

  1. Activate in-cell formula editing by either double-clicking the formula cell or using the Keyboard Shortcut F2.
  2. Highlight the cell reference that you wish to replace with a named range.
  3. Choose the Name from the “Use in Formulas” drop-down menu. You can also use the F3 Keyboard Shortcut to open the Paste Names Dialog Box.
  4. Repeat these steps to complete replacing additional cell references with named cell ranges.

Learn to Get the Most from Excel

On my DVD-ROM, “The 50 Best Tips for Excel 2007,” I offer 5 1/2 hours of video instruction. You will be amazed at how much more you can get out of Excel when you invest in this valuable resource. I invite you to visit my secure, online shopping website to learn more about the resources that I offer.

Watch Video in High Definition

You can view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Tutorial Now

Link to this post!
Filed Under: Excel Formulas, Excel Training, Names for Cells and Ranges Tagged: , , , , , ,
February 10, 2012 Leave a Comment

3 Reasons to Create and Use Named Ranges in Excel Formulas

Use Named Ranges in Formulas

Use Named Ranges in Formulas

In my opinion, there are three reasons to use Named Range references in Excel Formulas:

  1. They are easier to write. Particularly, if you are referencing cells in another worksheet.
  2. They are easier to remember. Using =Sales – Cost of Goods Sold to determine Gross Profit vs. =A1 – B1
  3. They are easier to explain. Especially, if you are sending an Excel Workbook to a client or a colleague.

Creating Named Ranges in Excel

In this tutorial, I demonstrate two methods for creating a named range:

  1. Select the cells in the range and then type the name in the “Name Box” in the Upper Left Corner of the worksheet.
  2. Select both the cell with the “Name” and the adjacent cells for the range. Then use the Keyboard Shortcut Ctrl + Shit + F3 to open the Create Names from Selection Dialog Box

Remember that all Named Ranges MUST begin with a Letter or an Underscore and they CANNOT contain any Spaces!

Paste Named Ranges into Formulas

If you are using Excel 2007 or Excel 2010, you can take advantage of Formula AutoComplete to quickly and accurately include named ranges in your formulas. In ALL versions of Excel you can use the F3 Keyboard Shortcut to open the Paste Names Dialog Box and select the named range that you wish to paste into your formula.

Additional Resources for Excel

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can preview all of the resources that I offer you.

Watch Tutorial in High Definition

Follow this link to watch my Excel Video in High Definition. My YouTube Channel – DannyRocksExcels – has received over 1 million views!

Watch Video Now

Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
« Previous Page
Next Page »