Home Archives for Danny
September 26, 2010 Leave a Comment

How to Create and Modify Excel Pivot Tables

How is it possible that the most powerful tool in Excel is also one of the easiest tools to use? An Excel Pivot Table is, in my opinion, the most powerful tool for analyzing data and presenting it as valuable and actionable information. And, an Excel Pivot Table is also one of the easiest tools to use.  

Summarize Data with Pivot Tables

Create a Pivot Table

Create a Pivot Table in six steps:  

  1. With one cell in the data table selected, I clicked on the Insert Tab of the Ribbon and clicked Pivot Table.
  2. I made one change to the default settings – I clicked a cell on the current worksheet to place my Pivot Table.
  3. On the Pivot Table Field List, I placed the Division Field in the Columns area and the Products Field  in the Rows area,
  4. Also on the Pivot Table Field List, I placed the Sales Field in the Values area.
  5. To format the Sales field Subtotals, I Right-clicked a single number and chose Value Field Settings – Number Format.
  6. I also changed the name from “Sum of Sales” to “Sales Revenue.”

It’s that easy! Try it yourself. You will be amazed at how easy it is to create your first Pivot Table. And… as an added bonus, with a Pivot Table you can not harm the underlying data! Pivot Tables work with a “virtual snapshot” of your actual data. Because a Pivot Table uses a “memory cache”  of your data, you can quickly swap your data fields from Rows to Columns to Filters, etc. This is done quickly and efficiently. 

Learn how to “Quickly Create Excel Pivot Table Reports and Charts.”

Here, on the website of The Company Rocks, I offer many free Excel Training Videos to help you get started to create and modify Excel Pivot Tables. Here are the links to the Introductory set of Pivot Table video lessons: 

You can also search for free Excel Pivot Table Video Lessons by following these links: 

“The 50 Best Tips for Excel 2007” DVD

Excel Tip List

Excel DVD Tips

You can also purchase my DVD, “The 50 Best Tips for Excel 2007.”It is affordable and it offers over 5 1/2 hours of focused Excel training tips, tricks and techniques. 

Follow this link to start shopping with my secure shopping cart. I guarantee that you will enjoy my Excel Video Tips. If you are not 100% satisfied, I will refund your purchase price with no questions asked!

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , ,
September 24, 2010 Leave a Comment

List of My Most Popular Posts and Pages

Summer – in the Northern Hemisphere – is now behind us. I went back to survey the videos and pages on my website that had the most views during the Summer of 2010. 

Here is a listing of my “Top 10 Video Posts” and my “Top 5 Pages.” Click on the Links for each title to go directly to the Video Lesson or Page.

The “Top 10 Video Posts” on The Company Rocks Website:

  1. Simplify Data Lookups in Excel
  2. Make Excel Come Alive with Visualization Tools
  3. Build an Accounts Receivable Aging Report
  4. Summarize Multiple Excel Worksheets – Consolidate Data by Position
  5. How to Merge Multiple Excel Workbooks into a Master Budget
  6. Use Pivot Tables to Summarize by Year, Quarter and Month
  7. Perform Break Even Analysis with Excel’s Goal Seek Tool
  8. 2 Reasons to Format Cells Before You Enter Data in Excel
  9. Hide and Show Details in Excel Pivot Tables
  10. Use Vlookup Function to Prepare a Payroll Statement

The “Top 5 Pages” Viewed on The Company Rocks Website:

  1. The Excel Home Page
  2. Index to Excel Video Lesson Topics
  3. List of “The 50 Best Tips for Excel 2007”
  4. Excel Training Videos by Category
  5. The 50 Best Tips for PowerPoint 2007

The Danny Rocks Tips and Timesavers Podcast

During the Summer of 2010, I started my own video podcast at the iTunes Store. These podcasts are free of charge. The video resolution is optimized for viewing on an iPad, iPod or other portable media device.

The DannyRocksExcels Channel on YouTube

Over the past two years I have posted 104 video lessons on my “DannyRocksExcels” Channel on YouTube.

The DannyRocksPowerPoint Channel on YouTube

I maintain a separate channel on YouTube – “DannyRocksPowerPoint”– to post my PowerPoint Tips and Narrated PowerPoint Presentations. I invite you to vist my channel to view, download and share these videos with your friends and colleagues. Click here to go to my YouTube Channel.

“The 50 Best Tips for …” series of DVDs

I offer five DVDs for sale on this website. Click here to start your shopping cart. I guarantee your satisfaction. If you are not 100% satisfied with my DVDs, I will refund your purchase with no questions asked!

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

Here is a listing of the DVDs that I offer for sale. Click on the link for each title to go to the list of the tips that I offer on each DVD:

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, Powerpoint Techniques Tagged: , , , , , , , ,
September 23, 2010 Leave a Comment

Review of My DVD – The 50 Best Tips for PowerPoint

Here is a link to a review of my DVD, “The 50 Best Tips for PowerPoint 2007.”  The review is by PowerPoint MVP, Geetesh Bajaj on the Indezine website.

Indezine Website

Review of my PowerPoint DVD

If you have not yet visited the Indezine website, I encourage you to do so. There are numerous resources available that will help you to produce better slides and to deliver better presentations. You will also find:

  • Interviews with the leading members of the presentations community.
  • Hundreds of PowerPoint Templates.
  • A Blog with great articles to help you to improve your presentations.
  • Links to other websites devoted to presentations.

If you design or deliver presentations, please bookmark the www.indezine.com website.

Link to this post!
Filed Under: PowerPoint, Powerpoint Techniques, PowerPoint Video Training, Presentations Tagged: , , ,
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:

  1. The INDEX() Function
  2. The MATCH() Function
  3. 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!”

How to Use Excel Lookup Functions to Create a Customer Invoice [ 5:24 ] Play Now | Play in Popup | Download (789)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers, Uncategorized Tagged: , , , , , , , ,
September 19, 2010 Leave a Comment

How to Share Excel Data with Word Documents

Microsoft Excel is tightly integrated with MS Word. There are several ways to share data that you create in Excel with a Word Document. In this lesson I demonstrate how to copy part of an Excel worksheet and then paste it into a Word document. Here are the techniques that I cover in this short Excel Video Lesson:

  • Paste as a Word Table
  • Paste as a Picture
  • Paste Special as a different type of Picture
  • Paste a Link between the Excel and Word documents – to update the latest sales figures

This is the first in a series of videos where I demonstrate how easy it is to share information between the different programs in the Microsoft Office Suite.

You can see a list of “The 50 Best Tips for Word 2007” by following this link.

You can view and download this video – for free – at the iTunes Store. I invite you to subscribe to my podcast, “Danny Rocks Tips and Timesavers.”

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

How to Share Excel Data with Word Documents [ 5:28 ] Play Now | Play in Popup | Download (711)
Link to this post!
Filed Under: Excel Training, Integrating MS Office Programs, Podcast Tips & Timesavers, Word Tips Tagged: , , , , , , , , , , ,
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:

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

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

  1. Select all of the cells that will contain the results before your enter your Array Formula.
  2. 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!”

Two Ways to Create a Frequency Distribution Report in Excel [ 5:45 ] Play Now | Play in Popup | Download (818)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , ,
September 15, 2010 Leave a Comment

My Published Articles Available on Ezine Articles

I am proud to announe that I currently have six articles published on the Ezine Articles Website. As you can see, I have earned the title, “Expert Author.” Simply click on the “Icon” below to go directly to my author page. Or, you can click this link to go to my main page.


Danny Rocks, EzineArticles.com Basic Author
I will be publishing one article each week on the Ezine Articles website, so check back frequently to see what I am writing about. You can download the articles and you can also forward them to a friend.

Here are the direct links to the individual articles that you can read right now:

Link to this post!
Filed Under: Business Communications, Ezine Articles, How to Lead Effective Meetings, Presentations, Public Speaking
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!”

How to Set up Excel Data Tables for "What-if" Analysis [ 6:43 ] Play Now | Play in Popup | Download (617)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers, What-if Analysis Tagged: , , , , , , ,
September 13, 2010 Leave a Comment

When to Use Absolute, Relative & Mixed Cell References in Formulas

In my experience, I find that many Excel users get easily confused when it comes time to use an Absolute or a Mixed Cell Reference in a formula. Let me demonstrate how and when to use the proper cell references, so that you always get the correct result.

Here is what each type of cell reference looks like in a formula:

  • Relative Cell Reference – =A2*B2 when copied down one row becomes = A3*B3
  • Absolute Cell Reference – =A2*$B$2 when copied down one row becomes =A3*$B$2 (Notice the $B$2 Absolute cell reference in the formula)
  • Mixed Cell Reference – =$A2*B$3 when copied down one row becomes = $A3*B$3 and when copied one cell to the right becomes =$A*C$3 (Notice how one part of the cell reference is relative – it moves; and the other part of the reference is Absolute – it remains fixed in place)

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

How to Use Absolute, Relative & Mixed Cell References in Formulas [ 5:09 ] Play Now | Play in Popup | Download (788)
Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
September 12, 2010 Leave a Comment

Prevent Duplicate Cell Entries with Excel Data Validation

Unfortunately, it is much too easy for a data entry clerk to assign duplicate account numbers. And this is a disaster waiting to happen. To prevent this from happening, use Excel’s Data Validation tool. In this short video lesson, I show you how to use the =COUNTIF() function in the Data Validation dialog box to ensure that only unique account numbers can be assigned to your customers.

I also show you how to extend the range of cells that contain this data validation rule: You use the Paste Special – Validation command.

This is one of “The 50 Best Tips for Excel 2007.” Click here to purchase the DVD using my secure shopping cart.

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

Use Data Validation to Prevent Duplicate Entries [ 5:38 ] Play Now | Play in Popup | Download (761)
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
« Previous Page
Next Page »