Home 2010 Archives for April 2010

Archives for April 2010

April 29, 2010 1 Comment

How to Create a Fiscal Year Summary Pivot Table Report in Excel 2007

This video lesson is related to my previous lesson – “How to Create a Month, Quarter Year Pivot Table Report.” This time, we are using a Fiscal Year.

I get asked this question frequently. So watch to see how this is done. One hint – your data source must include two fields that contain the Fiscal Year and the FY Quarter.

You can view and download the Excel video lesson – for free – as a Podcast. Click here to go to my Podcast, Danny Rocks Tips and Timesavers, at the iTunes store.

If you enjoyed this video, I invite you to shop at my secure online bookstore – http://shop.thecompanyrocks.com – You can purchase my DVD s in the series, “The 50 Best Tips for …”

Watch this Excel Video Lesson on YouTube

Related Excel Video Lessons:

Link to this post!
Filed Under: Excel Training, MS Excel Training, Pivot Tables Tagged: , , , ,
April 29, 2010 1 Comment

How to Create a Month, Quarter, Year Pivot Table Report in Excel 2007

Would you believe that with fewer than 10 mouse clicks you can transform a long list of daily sales transactions into a useful summary report – showing sales by Month, Quarter & Year?

Yes, you can do this with a Pivot Table report in Excel 2007 – using @ 10 mouse clicks.

Watch how I do this.

Now, many people will want to know how to customize this report to show Quarters that follow a Fiscal Year (e.g. April, May, June is FY Quarter 1) I demonstrate how to do this in my next lesson. Click here to view that video lesson.

If you liked this video, I invite you to shop for my DVD, “The 50 Best Tips for Excel 2007.” I have a secure shopping cart here on my website. Shop now!

Click here to watch this Excel Video Lesson on YouTube

Related Excel Video Lessons:

Link to this post!
Filed Under: Excel Training, MS Excel Training, Pivot Tables Tagged: , , ,
April 27, 2010 1 Comment

Create a Calculated Field in an Excel 2007 Pivot Table

Pivot Tables are very flexible. Yes, they are based on the fields in your source data, but you can also created new “virtual fields” – Calculated Fields.

In this lesson I show you how to create a new, Calculated Field, in a Pivot Table. This will be a “Price-per-Case” field that does not reside in the original source data. You will also learn how to hide the Formula Error #DIV/0! that results when we create this new field.

If you enjoyed this tip and my style of training, I invite you to visit my online store to purchase my DVD, “The 50 Best Tips for Excel 2007.”

You can watch this Excel Video Lesson on YouTube

Related Excel Video Lessons:

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , ,
April 26, 2010 3 Comments

Introducing Pivot Tables in Excel 2007

If you are already familiar with Pivot Tables, Excel 2007 makes Pivot Tables easier to create – you do not have to drag fields onto the template with the mouse! There are other improvements as well – and I cover the highlights in this lesson.

If you have never created Pivot Tables – or if you gave up in frustration – then this short, 9 minute and 48 second lesson will get you started. I explain that Pivot Tables help you to get answers about your data.

Let’s get started to create a Pivot Table in Excel 2007!

You can view and download this Excel video lesson – for free – as a Podcast. Click here to visit my Podcast, Danny Rocks Tips and TImesavers, at the iTunes store.

Learn how to “Quickly Create Pivot Tables and Charts.”

You can watch this Excel Training Video on YouTube

Related Excel Video Lessons:

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , ,
April 25, 2010 1 Comment

Using the Go To Special Command in Excel 2007 to Spot Formula Errors

The “Go To Special” Commands is one of my favorites. And, Excel 2007 places it in a prominent position on the Home Tab of the Ribbon.

I use Go To Special to quickly highlight all of the cells that contain Formulas. This way I can see if someone has accidentally “hard-coded” a Constant Value in a cell that should contain a Formula.

In this short video lesson I also show you another way that I use Go To Special – to reuse a Budget Worksheet or a worksheet for Expense Account Reports.

I you enjoy this tip and my style of training, I invite you to visit my online bookstore. It is a secure shopping site where I sell my “The 50 Best Tips for Excel 2007” DVD as well as DVD s for PowerPoint 2007, Outlook  2007,  and Word 2007.

Click here to watch this Excel Video Lesson on YouTube.

Related Excel Videos:

Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training Tagged: , , ,
April 24, 2010 1 Comment

Preventing Formula Errors from Displaying

Formula Errors in Excel – e.g. #DIV/0! or #NULL! – are ugly! And, when you send a spreadsheet that contains formula errors to your client or to your boss … Well, that is not good.

It plants the seed of doubt in the minds of your client or your boss that you have not thoroughly checked the accuracy of your assumptions. Why are there errors showing? What else is missing?

Excel 2007 makes it easy to prevent these formula errors from displaying. We have a new Function in Excel 2007 – The =IFERROR() function and it only requires two arguments. Be warned, however, that if you share your Excel 2007 worksheet with colleagues who are using earlier versions of Excel, this new function will not – well, function! (I could not resist using this pun)

Watch this video to learn how to use the new =IFERROR() function and also the nested =ISERROR() function that is required for people who use Excel 2003 or older versions.

If you enjoyed this Excel tip and my style of training, I invite you to visit my online store to shop securely for my DVD, “The 50 Best Tips for Excel 2007.”

Click here to watch this Excel Video Lesson on YouTube

You can view and download this video – for free – as a Podcast from the iTunes store. Follow this link to my Podcast, Danny Rocks Tips and Timesavers at the iTunes store.

Related Video Lessons:

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , , , ,
April 24, 2010 Leave a Comment

Visualizing Your Data in Excel 2007

Excel 2007 makes it easy to “visualize” your data – directly in the cells of your worksheet! You do not need to take the time and effort to create a chart to visually represent your information.

You just take advantage of the tools in the newly-engineered Conditional Formatting command in Excel 2007. In this lesson I show you how to visualize your data as:

  • Data Bars – you get the equivalent of a Bar Chart drawn inside the data cells.
  • Icon Sets – pick from groups of 3, 4 or 5 Icon sets – for example, to show the data in the top third of your data range.
  • Color Scales – to add gradations of colors as numbers increase or decrease.

If you liked this tip and my style of Excel training, I invite you to visit my online store to purchase my DVD, “The 50 Best Tips for Excel 2007.”

You can view and download this Excel video lesson – for free – as a Podcast. Follow this link to visit my Podcast, Danny Rocks Tips and Timesavers” at the iTunes store.

You can also watch this Excel video lesson on YouTube.

Related Excel Videos:

Link to this post!
Filed Under: Excel Training Tagged: , , , ,