Sum Across Worksheets

This is the third episode in my series of Excel Tutorials where I share my best practices for creating an Excel Workbook that contains 52 weeks of Employee Time Cards. In this lesson, I focus on techniques to Summarize – or Consolidate – the hours worked or taken as vacation, by each employee, over a period of several weeks.

Sum Across Worksheets

This is a great tip to learn! In my experience, I find that very few experienced users of Excel know that you can do this! This will eliminate the hassle of linking to a cell in individual worksheets to create your formula. There is, after all, a limit to the number of characters that you are allowed in a formula!

Consolidate Data By Position

Since each worksheet in this workbook is identical in structure, Data Consolidation is simple. Provided, that is, that you remember to “Add” each worksheet range reference in the Dialog Box. In this example, I have “created a link” to each worksheet so that the data can be easily updated.

Links to Other Tutorials in this Series

• Use a Template to Create a 52 Week Employee Time Card Workbook in Excel
• Tips for Creating a 52 Week Employee Time Card in Excel

Shop for Additional Resources

I invite you to visit my new, 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 view this Excel Tutorial in High Definition on my YouTube Channel - DannyRocksExcels

Natural Language Date Filters

Recently, one of my viewers asked me to go into greater detail in demonstrating how the new Natural Language Date Filters work in Excel. Natural Language Filters were introduced in Excel 2007 and they are a great tool to use! Now, instead of writing complex formulas as criteria in Advanced Filters, you can simply click, “Yesterday” to see all of the records from the previous day!

Of course, you must actually have records in your data set for that date!

The Natural Language Date Filters are related, by position, to TODAY(). The TODAY() Function is a “Volatile Function” that returns the value for the current date as found in your computer systems internal clock. The result of the TODAY() Function will change each day. And so, by definition, will the result for a “Yesterday” filter!

Filter for Specific Date

There are several methods that you can use to filter for a specific date or range of dates. One method that I demonstrate in this tutorial is the “Custom Date” dialog box.

Watch Tutorial in High Definition

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

Learn About My New Extended Length Video Tutorials

I have recently released a series of extended length (90 minutes) video tutorials. They are part of my “Master Excel in Minutes” Series. Each video focuses on one topic. My first video is focused on Excel Pivot Tables. I have created Videos for Excel 2010, Excel 2007 and for Excel 2003. You also have the choice of purchasing the video for immediate downloading or shipped to you on a DVD-ROM.

Follow this link to go to the information page for my “Master Excel in Minutes” video tutorial series.

VBA Code to add Time Stamp

In this video tutorial, I respond to one of the most frequent requests that I get from my viewers – “How do I automatically add a ‘Time / Date Stamp’ whenever I add or edit a record in my Excel Worksheet?”

On the surface, this might appear to be a “simple” question to answer. However, I promise that you will be frustrated if you try to solve this problem by using the =NOW() Function or one or both of these Keyboard Shortcuts:

• Ctrl + ; (Semi-colon) to add a Date Stamp
• Ctrl + Shift + : (Colon) to add a Time Stamp

Because … NOW() is a “Volatile Function” that automatically updates to display – in all records that refer to NOW() – the current date and time. And … because, it is difficuly to automate the combination of Keyboard Shortcuts to apply a Date / Time Stamp.

So … what is the solution?

Use VBA to Create a Simple “Event Handler” Macro

Don’t get scared off! Don’t say, “I can’t write a VBA Macro!”

I PROMISE you – an Intermediate Level User of Excel – that by investing 10 minutes to watch this tutorial – that you WILL be able to automate this process to add a Time / Date Stamp whenever you add or edit a record in Excel.

I had you – the Intermediate / Early Advanced Level Excel user in mind when I created this video tutorial. I take you step-by-step through the process. I point out the potential “got’cha” steps. I am confident that I can help you to advance your skill set in Excel with this video tutorial!

Watch Tutorial in High Definition

I invite you to watch this Excel Tutorial in High Definition on my YouTube Channel - DannyRocksExcels

Visit My Online Shopping Site

I invite you to visit my new, secure online shopping site – http://shop.thecompanyrocks.com – to see all of the new products that I have created and offer for sale!

ARRAY Formulas in Excel

Array Formulas and Functions are very powerful tools to use in Excel. However, the concept of working with ARRAYs take a little bit of time and practice. In this video tutorial, I demonstrate how to:

1. Create formulas that use ARRAYS as arguments
2. Work with ARRAY FUNCTIONS – for example, the TRANSPOSE Function
3. Key “Got’cha” steps to master – for example, selecting all cells to receive formula results before creating the ARRAY Formula.
4. The importance of using Ctrl+Shift+Enter to complete the Array formula.

What is an ARRAY?

“An Array is a collection of Cells or Values that Excel treats as a single unit.”

Why Use an ARRAY Formula?

1. Automatic Level of Protection for Formula Cells – You cannot delete nor edit a single cell in an Array Formula
2. Eliminate Intermediary Calculations – For example, you can find the Grand Total without having to create a field to calculate “Extended Price.”
3. Worksheet, usually, calculates faster because you are using fewer formulas.

Visit My NEW Online Shopping Website

http://shop.thecompanyrocks.com is my new, secure online shopping website. I invite you to visit and preview my new products.

Watch Tutorial in High Definition

Follow this link to view this tutorial in High Definition on my YouTube Channel - DannyRocksExcels

Master Pivot Tables in Minutes

I am proud to announce the publication of my new series of “Extended Length” video tutorials: “Master Excel Pivot Tables in 90 Minutes!”

Now, you can can get an in-depth video tutorial, so that you can Master Excel Pivot Tables with 90 minutes of video instruction. You also receive the actual Excel Workbook file that I used while filming the video lessons as well as a printed step-by-step instructional manual.

What Makes this Product Unique?

•  Excel Versions for the Video Tutorials

• Available for Excel 2010, Excel 2007 or Excel 2003

Multiple Options for Delivery

• Order for Immediate Download
• Order a DVD-ROM to be shipped to you

Resources to Assist Your Learning

• The Excel Workbook file that I used while filming each video lesson – Included with purchase!
• PDF Manual of Step-by-Step Instructions – That you can print out – Included with purchase!

Take a Short Video Tour

Follow this link to watch a short – 5 minutes – video tour of my new products on my YouTube Channel – DannyRocksExcels

Visit My New Secure Online Shopping Website

I invite you to visit http://shop.thecompanyrocks.com This is my new online shopping website. I have created secure shopping carts to ensure that your privacy is protected. I also offer a 100% Customer Satisfaction Policy. If, for any reason, you are not 100% satisfied with any of my products, I will refund your purchase with no questions asked!

Danny Rocks

The Company Rocks

Solver Parameters

This is Part 2 of my series of video tutorials where I demonstrate how to use the SOLVER Add-in Tool in Excel to produce a specific result for a formula.  Remember that SOLVER is an Add-in program that you must activate before you can use it. Watch Part 1 in this series to learn how to activate SOLVER.

In this tutorial, I work with the same data set that I used in Part 1. This time, I want to achieve a specific amount of Gross Profit Sales Dollars. First, however, I must determine what is a reasonable amount to set as the Target for Solver to return.

Use SUMPRODUCT Function in Excel

I use the SUMPRODUCT Function to quickly determine my possible Revenue, Cost of Goods Sold and Gross Profit is I sell all of the Units that I have in stock. Now, I have a reasonable Target for SOLVER to reach. SUMPRODUCT is a very efficient Function that, unfortunately, very feel Excel users – in my experience – know about. Follow this link to my Index of Excel Video Lessons to find additional tutorials for SUMPRODUCT.

Best Practice Tips for Solver

• Work with a copy of your worksheet. This is always a good idea, and it is most important when you are beginning to learn how to work with SOLVER.
• Write out your Goal for SOLVER.

• Do you want SOLVER to Maximize the Formula? For example, to Maximize your potential profit?
• Do you want SOLVER to Minimize the Formula? For example, to Minimize your costs?
• Do you want SOLVER to return a Specific Number? If so, what is that number?
• Determine your “Variable Cells.” SOLVER calls them the “By Changing Cells.” These are the cells that SOLVER will automatically adjust.

• The Variable Cells must “feed int” the formula in your Target Cell – This is the key to making SOLVER work!
• Write out your “Constraints.” These are the “real-world” conditions that you want SOLVER to operate under. This is the real power in SOLVER.
• Do not treat the SOLVER results as “the Gospel.” SOLVER is a powerful Business Intelligence Tool. You, however, know your own business or industry. Use SOLVER as a tool to help you to make informed business decisions.

Watch Part 1 in SOLVER Series of Video Tutorials

• Here is the link to watch Part 1 of my series of Excel Tutorials for the Solver Add-in Tool.
• Watch Part 1 of this Series in High Definition on YouTube – My Channel is DannyRocksExcels

Watch Tutorial in High Definition

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

SOLVER in Excel - Key Points

I am responding to a “flurry” of requests to produce an Excel Video Tutorial that demonstrates how to use the SOLVER TOOL in Excel. Here are the key points to remember when you want to use the SOLVER Tool:

Activate the SOLVER Tool

SOLVER is an “Add-in” tool that needs to be “activated” before you can use it. In Excel 2007 / 2010, you do this via Excel Options – Addi-Ins – Excel Options. In Excel 2003, you do this via the Tools Menu – Add-ins – Solver.

Prepare Data for Solver

In order to use the SOLVER Tool, you must:

• Establish the “Target Cell.” This cell must contain a formula. This is the cell that you want to “optimize” by using SOLVER.
• Determine the “By Changing” Cells. These are the cells that SOLVER will change in order to deliver the optimized result in the Target Cell. The “By Changing” cells MUST “Feed Into” the Target Cell.
• Establish the CONSTRAINTS for SOLVER. This is the “key’ element that gives SOLVER its power. You can add as many constraints as needed. My recommendation is that you “write them out” before you start  to use the SOLVER Tool.

SOLVER is very powerful. However, you MUST “think it through” before you apply it. It will take lots of practice for you to understand how to properly set up your data to produce appropriate SOLVER results.

Part 2 in this Series of Tutorials for Solver in Excel

Here is the link to watch Part 2 in this Series of Tutorials about the SOLVER Add-in Tool in Excel

Download Excel Workbook for this Lesson

Follow this link to download the Excel Workbook that I used for this lesson. I store my uploaded Excel workbooks on my SkyDrive at www.office.live.com

View this Tutorial in High Definition

Click on this link to watch this Excel Tutorial in High definition / Full Screen Mode - on my YouTube Channel, DannyRocksExcels

Formula for Data Validation

Over time, it is easy for duplicate entries to creep into an Excel Data Set. In this video tutorial, I show you, step-by-step, how to apply Data Validation to a range of cells so that anyone who attempts to enter a duplicate value will be prevented from doing so.

Use a “Logical Formula” for Data Validation

In this lesson, I show you how to “customize” the COUNTIF Function in the Data Validation Dialog Box so that it returns the value of TRUE. Logical formulas return either TRUE or FALSE. You MUST use a Logical Formula in Data Validation.

What Else is Covered in this Tutorial?

• Use Paste Special – Validation to extend Data Validation to a wider range of cells
• Use Find – Go to Special – Validation to highlight all cells on worksheet that contain Data Validation
• Use Circle Invalid Entries from the Data Validation menu to automatically “draw a red circle” around existing cells that contain duplicate values
• How to quickly remove Data Validation from a Range of Cells

Related Topic – Conditional Formatting in Excel

Another way to highlight existing cells that contain duplicate values is to use Conditional Formatting. The improvements in Excel 2007 and Excel 2010 make this “a snap” because this is now a Menu Selection! In my next tutorial, I will show you how this is done in both Excel 2003 – with the COUNTIF Function – and from the new Menu Selections introduced in Excel 2007.

Watch Tutorial in High Definition

Follow this link to my YouTube channel - DannyRocksExcels – where you can watch this tutorial in High Definition mode.

Keyboard Shortcuts to Enter Data

This is the latest installment in my series of video tutorials where I demonstrate how to use Keyboard Shortcuts in Excel. In this tutorial, I demonstrate how to Enter and Edit Data Efficiently in Excel using Keyboard Shortcuts.

Tips Covered on Video Tutorial

Here are the main Keyboard Shortcuts that I show you how to use on this video:

• Enter Current Date in a cell.
• Enter Current Time in a cell.
• Use Ctrl + Enter to accept data entry and keep the focus on the current cell.
• Use Ctrl + Enter to populate a range of selected cells.
• Use Alt + Enter to “Wrap the Text” in a cell.
• Use F2 to edit data inside a cell.
• Use Ctrl + left & right directional arrows to move to the beginning of the next / previous word while editing in the cell.
• Use ‘ (apostrophe) as the first character in a cell to make this a “text” entry.

Download my complete Chart of Popular Keyboard Shortcuts as a PDF 

 Follow this link to download – for free – my 5 Page Chart of Popular Excel Keyboard Shortcuts as a PDF.

Subscribe to my Video Podcast on iTunes

Here is the link to subscribe to my video podcast, “Danny Rocks Tips and Timesavers” on iTunes. It is offered for free!

Watch Tutorial in High Definition on YouTube

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

The more interesting Keyboard Shortcuts that I demonstrate in this lesson are:

• Ctrl + ‘ (apostrophe) – to copy the value from the cell directly above; to copy the formula from the cell directly above while retaining exact cell references.
• Ctrl + Shift + ” (double quote) – to copy the value from the cell directly above; to copy the “result” of the formula from the cell directly above.

Download My Free “Chart of Popular Keyboard Shortcuts in Excel”

• Follow this link to download all of my Popular Keyboard Shortcut Charts from my Exclusive Membership Content page.
• You can also download this – for FREE – at my Online Shopping Website – here is the link: http://shop.thecompanyrocks.com/chart-of-popular-keyboard-shortcuts-for-excel/

Watch Tutorial in High Definition

Click here to go to my YouTube Channel – DannyRocksExcels – where you can watch this tutorial in High Definition mode.

Keyboard Shortcuts for Cut, Copy & Paste

In addition to showing you the Keyboard Shortcuts to Cut, Copy and Paste in Excel, I demonstrate how these actions affect formulas and formatting.

Topics in this Tutorial

Here are the topics that I demonstrate in this video tutorial:

• Select multiple cells using the keyboard
• Use Ctrl + Enter to update all cells in the selection
• Use Ctrl + ~ to “toggle” between showing and hiding formulas in all cells
• Use F2 to edit in the cell – and to diagram cell references for formulas
• Use Ctrl + C to copy the “relative cell references” in a formula
• Use Ctrl + X to cut an Excel formula that retains the original cell references
• Open and use the Office Clipboard to store up to 24 most recent copy and cut items
• Use Ctrl + Z to undo multiple actions
• Use Ctrl + A to select all cells in a contiguous range
• Use the Delete key to delete the contents of a cell or range while retaining the formatting
• Use the Clear All command to remove both formatting and contents in a range of cells

Download My Complete Charts of Popular Keyboard Shortcuts

Follow this link to my Excelusive Membership Content Page where you can download all of my charts and tables of Keyboard Shortcuts.

Watch Video in High Definition

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

Excel 2007 File Operations

If you have recently upgraded to either Excel 2007 or Excel 2010, you REALLY NEED to master the Keyboard Shortcuts that I present in this lesson!

Many Excel veterans who upgrade, tell me that they initially felt “lost” with the new user interface. Some of my clients called me in a panic because they didn’t know where to look to Print their work or to open a document. If you know how to use the Keyboard Shortcuts that I present in this lesson, you will never be at a loss. As an added bonus, these Keyboard Shortcuts work with ALL Microsoft Office programs and also for most Windows-based programs.

The “N-O-W-S” Keyboard Shortcuts

I use this acronym – NOWS – to remember the keyboard shortcuts to Open, Close and Save Documents in MS Office programs. Use Ctrl + W to Close the active worksheet.

In this video tutorial, I also show you when NOT to use Keyboard Shortcuts! There are many times when you need to access the full file menu selection.

Keyboard Shortcut for Print Preview

Use Ctrl + F2 to open up the Print Preview pane. Use the “Esc” key to Exit Print Preview.

Download My Chart of Popular Keyboard Shortcuts as a PDF

Follow this link to go to the web page where I store my Keyboard Shortcut Charts and other download-able content.

These Keyboard Shortcuts work for all versions of Excel. So far, I have found only one Keyboard Shortcut that is different when used in Excel 2007 / 2010 than in Excel 2003.

More Video Tutorials for Keyboard Shortcuts

This is the first in a series of video tutorials that I have planned. Each video will focus on the Keyboard Shortcuts you can use to perform a specific task – e.g. Formatting Cells, Inserting and Deleting, etc. My plan is to post one new video in this series per week.

Watch Video Tutorial in High Definition Mode

Follow this link to view this video tutorial in High Definition on my YouTube Channel - DannyRocksExcels

Show Values As in Pivot Table

I participate in a number of Group Discussion Boards on LinkedIn. In this video tutorial, I address one question that was recently asked:

“Is it possible to show a Percentage of Running Total formula in a Pivot Table?”

The answer is, “Yes, if you are using Excel 2010. If you are using earlier versions of Excel, you can use a work-around outside the Pivot Table.”

New in Excel 2010

A great new feature introduced in Excel 2010 is the drop-down menu for the “Show Values As’ in a Pivot Table Calculation. In earlier versions of Excel, the Show Values As option was not so obvious. And, I found that many of my clients did not know how to use it – or could not locate the drop-down menu.

Work-Around for Show % of Running Total

While the “Running Total in…” calculation has been available for quite some time, the “% of Running Total in …” formula is brand new in Excel 2010. The work-around that I demonstrate is to first, create the Show As “Running Total in …” formula. Then change the Font and background of these cells to “White.” Next, write a standard formula “outside of the Pivot Table” that references these cells. Be sure to turn off the “GetPivotData” Formula when you write this formula.

Download Extended Length Pivot Table Video Tutorials

Click on this link to learn about my new “extended length” video tutorials for Pivot Tables. They are availabe for Excel 2003, Excel 2007 and Excel 2010.

Watch Video in High Definition on YouTube

Follow this link to watch this video in High Definition on my YouTube Channel - DannyRocksExcels

Master Excel Pivot Tables in 90 Minutes

Follow this link to learn about my new “Extended Length” – 90 Minutes – Video Tutorials

AverageIf Function in Excel

It is easy to use Excel’s AVERAGE Function to get the average for an entire range of data. But, what if you are asked to produce a report that shows the average for select groups of records – for example, average test results for the female students only; or for the male students only? What approach do you take? What Functions do you use to produce this report?

Three and a Half Ways to Produce this Report

• SUBTOTALS - Easy to use. Remember to sort your data first using the field that you want to subtotal.
• AVERAGEIF Function – Very easy to use. Only works in Excel 2007 and Excel 2010.

• DAVERAGE Function – Will work in any version of Excel. Easy to add or change the Criteria.
• PIVOT TABLE – Very easy to create. No need to write a formula. Create report with @ 6 mouse clicks!

My Preferred Approach – Create a Pivot Table Report

While Pivot Tables are the most powerful tool in Excel, they are also one of the easiest tools to use in Excel! Why?

• Because you can create a Pivot Table Report without writing a single formula!
• You can create this Pivot Table Report with about six clicks of the mouse.
• You cannot possibly harm your underlying data when you create Pivot Tables!

Download a One Hour- Plus / In-depth Video Tutorial on Pivot Tables – for $9.95

I have created a series of Excel Video Tutorials that you can download for $9.95 US Dollars each. Here is a link to my informational page that gives you the details.

• Are you Using Excel 2003? – I created a video recording for Pivot Tables using Excel 2003 that you can download for $9.95!
• Are you Using Excel 2007? – I created a video recording for Pivot Tables using Excel 2007 that you can download for $9.95!
• Are you Using Excel 2010? – I created a video recording for Pivot Tables using Excel 2010 that you can download for $9.95!

As with all of my products, I guarantee that you will be 100% Satisfied or I will refund your purchase price – no questions asked!

My Extended length Excel Training Video Recordings are hosted at: http://thecompanyrocks.webex.com – I use the secure shopping cart at WebEx by Cisco Systems, so you can be sure that your personal information is safe and secure.

Watch Video in High Definition on YouTube

Follow this link to watch this video tutorial in High Definition mode on my YouTube Channel - DannyRocks Excels

Get My DVD-ROM, “The 50 Best Tips for Excel 2007″ for only $29.97

The Problem with Time in Excel

When it comes to entering Time Values, Excel will only accept a short list of Time Formats. For example, 6:00 AM is a valid Time Entry in Excel. However, Excel will treat 6:00 A.M. as a “text entry!” 6:00 AM is stored as the decimal value 0.25 in Excel. Decimals are numbers that Excel can calculate and increment. In this video, I demonstrate a shortcut that ensures that you always enter a Time Value as a Number.

How to Increment in Two-Hour Intervals

As I demonstrate in this video, use this formula to increment a series of two-hour time intervals:

=starting_cell + 2*(1/24)

In this case the (1/24) portion of the formula indicates a “one-hour segment.” 1 hour out of a 24 hour day. The +2 part of the formula indicates that you want to increment by 2 – of what? The answer is +2*(1/24) – by 2 hours.

To indicate a 15 minute increment use this formula:

=starting_cell + 15 * (1/24/60) – That is, divide each one hour segment (1/24) by 60 minutes (1/24/60) and multiply this by the number of minutes that you wish for your schedule.

Watch Part 1 of this Series – Increment Dates in Excel

Follow this link to watch Part 1 of this two-part series – “Use Excel’s AutoFill Tolls and Excel Functions and Formulas to Increment Date Intervals.”

View this Tutorial in High Defintion on YouTube

Click on this link to watch the video lesson in High Definition on my YouTube Channel - DannyRocksExcels

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007 for only $29.97

Excel AutoFill Options

I begin this Excel Video tutorial by first, demonstrating how to use Excel’s AutoFill Options to increment a series of dates. Some of these AutoFill Options are:

• Fill Weekdays Only (Monday through Friday)
• Copy Formatting Only
• Increment by Adding one month to the previous cell
• Increment by Adding one year to the previous cell

Here’s a Tip: If you use your Right-mouse button to AutoFill a series, a menu of options will pop up automatically when you release the mouse. Try it!

Excel Functions and Formulas to Increment a Series of Dates

Two of my favorite Excel Date Functions are part of the “Analysis ToolPak” Add-in:

1. The EDATE() Function – returns a “serial number” for a Month that is X number of months away from the starting date.
2. The EOMONTH() Function – is similar to EDATE. However, it always returns the last Calendar Day of the Month that is X  number of months away from the starting date.

In Excel 2007 and Excel 2010, the Analysis ToolPak Add-in is activated by default. In this video, I demonstrate how to activate it if you are using Excel 2003 or older.

Other Functions Used in this Video Tutorial

1. The DATE() Function. Remember that if you use this function to increment by one-year intervals that you need to “nest” the Year(), Month() and Day() functions in the arguments. This is a “tricky” function to use.
2. The WEEKDAY() Function. In the video, I nest this function inside an IF() Function in order to get a series of dates that include Monday through Friday only!

Related Video Tutorial

My next video in this series demonstrates how to use Formulas to increment a series of times by “minutes” or by “hours.”

Watch This Video in High Definition

Follow this link to view this video tutorial in High Definition on my YouTube Channel - DannyRocksExcels

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007″ for only $29.97!

Edit Custom Lists in Excel

Custom Lists in Excel are great because the help to ensure accuracy and consistency when entering data on a worksheet. Excel comes with several Custom Lists built-in to the program – e.g. Days of the Week and Months of the Year. Now, you can quickly use Excel’s AutoFill handle to add Jan, Feb, Mar, Apr, etc. in any direction (vertical or horizontal) on the active worksheet.

Edit Custom Lists

You can quickly create – or edit – your own Custom List. Follow these steps:

1. Type your list in a contiguous group of cells – either vertically or horizontally.
2. Select the cells with the values that you just entered.
3. Spell check this list – use the F7 Keyboard Shortcut
4. Open up the Edit Custom List Dialog Box. (Watch this video to see how this is differs between Excel 2010, Excel 2007 and Excel 2003.)
5. With your new Custom List selected, click the “Import” button to add your Custom List to the current version of Excel on this computer.

Sort Data Using a Custom List

On this video tutorial, I demonstrate how to sort a list of data using a Custom List – e.g. to get the “Month” field sorted in chronological order (January, February, etc.) This will save you a great deal of time!

Create a Custom List for Letters of Alphabet

Having a Custom List for the 26 letters of the alphabet comes in handy on many occasions. I show you how to AutoFill down the initial list using =Char(Row() + 64) beginning in Row 1 – a really usefuly function!

View This Excel Video Tutorial in High Definition

Follow this link to watch this video tutorial in High Definition on my YouTube Channel - DannyRocksExcels

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007″ for only $29.97!

Single Blank Cell in Data

Imagine, how a single blank cell, in a field of 848 records, can change the way your Pivot Table views the data type (text vs. the expected numeric data type) and subtotal function (Count vs. the expected SUM function).

Seeing is believing!

Preventing a Blank Cell from “Creeping into” your data set

Use the Keyboard Shortcut Ctrl + Down Directional Arrow to go to the last cell that contains data in a column. This is a great way to isolate “stray” blank cells that will have a great impact on your Pivot Tables and other calculations in Excel.

Also, in Excel 2007 and Excel 2010, look for where a “numeric” field is placed by default. A single blank cell will cause an (intended) numeric field to be placed in the Row (text) labels area. Even dragging this field into the VALUES Drop zone does not solve the problem because the COUNT Function will be used to Subtotal this field. This is a “disaster waiting to happen!”

Introducing My Extended Length Excel Video Recordings

This is my opportunity to introduce you to my new, extended-length (one-hour +) Excel Video Tutorials that you can download for only US $9.95!

Here is the link that gives you more information about this exciting new service:

http://www.thecompanyrocks.com/pivot-table-tutorial-videos-to-download-practice-files-instructional-manual-included/

I offer you multiple options:

• If you are using Excel 2003, you can download the video recording for that version.
• If you are using Excel 2007, you can download the video recording for that version.
• If you are using Excel 2010, you can download the video recording for that version.
• You can quickly download the PDF of Step-by step instructions for each version.
• You can download the Excel Workbooks that I use in each recording.

Let me know what you think!

Let me know which topics you would like me to record for you to download.

Send me an email with your comments and suggestions: danny@thecompanyrocks.com

Watch This Video in High Definition

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

Multiple Nested IF Functions

Do you get tied up in a knot trying to write – let alone explain – a formula that has one IF Function nested inside another, and another…? I know that I do! And, what happens when your sales manager wants to revise the tiers for the quantity discounts? How many hours will this take you to review your logic and to make all of those multiple changes?

Usually, this will mean a lot of time, a lot of frustration and possibly some resentment thrown in as well!

Well, relax. I am going to demonstrate how easy it is to write a custom function in Excel using the “Select Case” Construct to replace the “rat’s nest” of multiple IF Functions in your formulas!

Scenario for this Function

You have been asked by your Sales Manager to write a formula to grant a discount, based upon the quantity of units ordered. There are 5 Tiers: Below 5 units ordered, no discount. From 6 to 24 units, a 10% discount, etc. Rather than spending a half hour nesting IF() functions and using AND() functions to create this formula, spend less than 10 minutes creating a Custom Excel Function instead.

Create the Custom Excel Function

• Begin by opening the Visual Basic Editor. One way to do this is right-click any worksheet tab and select “View Code.”
• Ensure that you have inserted a Module in the Project Explorer and that you have selected the module.
• In the example that I use on this video tutorial, my first line of code is: “Function DiscQty(quantity)” and this is matched to an “End Function” statement
• For this Custom Function, I am using the “Select Case” Construct in Visual Basic (VBA). Think of the keyword “Case” as a substitute for the =IF() Function.
•  Rather than nesting the Excel =AND() Function inside the IF() Function, you will substitute – e.g. “Case 6 To 24″ and then “DiscQty = 0.1″ to indicate a 10% discount for quantities between 6 AND 24.

Watch the video to see how easy is is to create – and use – this custom Excel function. Invest the 7 minutes and 47 seconds to watch it – I guarantee that this investment of time will pay you many dividends for years and years to come!

View This Video Tutorial in High Definition

Follow this link to watch this Excel Video Tutorial in High Definition on my YouTube Channel - DannyRocksExcels.

Subtotal in Excel 2010 Table

This is Part 2 of my series of video tutorials demonstrating how to use the SUBTOTAL Function in Excel.

• In Part 1, I showed you the value of using the Subtotal Function to summarize the results of applying a Data Filter to a range of cells.
• In this part, I show you how to use an Excel 2007 or Excel 2010 Table to ensure that your Subtotal Formulas are automatically updated when you append records or add additional fields to your original data set.

I strongly recommend basing Filtered Lists and Pivot Tables on an Excel Table (in Excel 2007 or 2010) or an Excel List in Excel 2003. This way, any formulas, filters and references that you make will be automatically updated when you append additional records or otherwise change the structure of your data set.

Function Numbers 101 through 111

Notice that when you “toggle on” the Total Row for a Table or List that Excel uses this formula = SUBTOTAL(109, Table1, [Sales]). Function 109 will use the SUM Function(109) to total the values in the “Sales” field ([Sales]) of a Table named “Table1.” These Function Numbers + 100 were introduced in Excel 2003 and the are automatically applied whenever you are using a Total Row in an Excel Table.

I think that you will learn some cool tricks in this lesson. Let me know what you think!

Watch This Video in High Definition

Click on this link to watch this video tutorial in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels.

Invitation to Visit My New Online Shopping Site

I invite you to visit my new, secure online shopping website – http://shop.thecompanyrocks.com

Once there, you can get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007″

Content Key on Keyboard

In Excel 2010, you can now use special keyboard shortcuts to control your Paste Special Options – e.g. Paste Values Only, Transpose, Paste Link, Paste Formatting, etc.).  There is a new technique to learn, however, before you can become proficient with these keyboard shortcuts in Excel 2010. Let me demonstrate.

Live Preview for Paste Special in Excel 2010

In my previous video tutorial, I demonstrated how to take advantage of this great new feature – “Live Preview for Pasting” – in Excel 2010. Click here to watch that video.

Two Ways to Use Keyboard Shortcuts to Paste Special in Excel 2010

1. Press and Release the Ctrl Key, then press the “letter key” for the shortcut.
2. Press the “Context Key” on your keyboard (to the right of the Space-bar; between the Alt & Ctrl keys) and then press the “letter key” for the shortcut.

Each of these techniques is easier to explain through a video demonstration than to write out the instructions. So, I encourage you to watch my video if you want to learn these new techniques for Excel 2010!

Watch Video in High Definition on YouTube

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

Gallery of Paste Special Options

I have always loved the many “Paste Special Options” available in Excel. Now, in Excel 2010, there is a great new tool available - “Live Preview of a Gallery of Paste Special Options” – to help you to discover or to choose the best way to paste items copied to the clipboard.

From either the Paste Menu on the Home Tab of the Excel 2010 Ribbon or from the right-click shortcut menu, you can hover your mouse over one of the Gallery Icons to see a “Live Preview” of your Paste Special Option Selection.

When I demonstrate this feature  to my clients, they are amazed to discover the many options that are available! Of course, some of these same clients are also “overwhelmed” by all of the choices on the menu!

That is why I created this video – to help you to discover and to select the best Paste Special Option for your needs.

Paste Between MS Office Programs

Later in this video tutorial, I also show you how to use the Microsoft Office Clipboard and the Gallery of Paste Special Options to copy a data set from Excel and Paste it as a Picture in a Word document. With the Office Clipboard, you can cut, copy and paste up to 24 items between Excel, PowerPoint and Word. However, you must first open up the Office Clipboard for this tool to be available to use.

Additional Paste Special Features in Excel 2010

I have decided to produce an addition video tutorial to demonstrate how to use some new Keyboard Shortcuts in Excel 2010 to take advantage of the Paste Special Options.

Watch Video in High Definition Mode

Click here to watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Improvements to Conditional Formatting

Condition Formatting for Above Average

Beginning with Excel 2007, Conditional Formatting has been dramatically improved. In addtion to pre-built formula rules, you can also apply Data Visualizations (Icon Sets, Data Bars and Color Scales).

Conditional Formatting for Above Average

In this lesson, I apply the pre-built “Above Average” rule. I want Excel to dynamically format the summary values that answer, “TRUE” to this question: “Is this value “Above the Average” in the range of cells selected?

This is the key to understanding how Conditional Formatting works. Special Formatting is applied only when the answer to your CONDITION (a rule or a formula) is TRUE.

Dynamically Filter Conditional Formatting

In this lesson, I use the Report Filter zone in my Pivot Table to see how conditions change when I change the time period in my report. Values that meet the Condition, “Above the Average,” receive the formatting that I established in my rule. Try this on your Pivot Table. It is a wonderful tool to use in an Excel Dashboard.

View This Video Tutorial in High Definition

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

My Extended Length Pivot Table Video Tutorials

I offer “free of charge,” a 24 minute in-depth video tutorial for creating your first Pivot Table in Excel. Follow this link to learn how to view it online or to download it to your computer.

Vist My New Online Shopping Site

My new, secure online shopping website – http://shop.thecompanyrocks.com/ - is now open. I invite you to visit and learn more about the new products that I have added.

Pivot Charts in Excel 2007

In this lesson, I show you how to create a Pivot Chart in Excel 2007. Starting with Excel 2007, there are several major changes:

• By default, Pivot Charts are embedded on the same worksheet as the Pivot Table Report that is linked to the chart.
• A new, PivotTable Chart Filter Dialog box is available – this works “in tandem” with the filters available in the PivotTable Field List” Dialog box.
• It is very easy to “Move” a Pivot Chart to its own worksheet tab – or vice versa – to move the Pivot Chart to an existing worksheet.

 Pivot Charts and Pivot Table Reports are Connected

A Pivot Chart MUST be connected to a Pivot Table Report. Any changes that you make to the chart are reflected in the report and vice versa. If you filter the Pivot Table Report, the filter is automatically applied to the Pivot Chart. This is an important concept to understand. This is the reason why it is so easy to make BOTH Pivot Table Reports and Pivot Charts “interactive!”

Explore My Index of Free Excel Video Lessons

I was surprised to discover that I had not yet created a video tutorial for Excel Pivot Charts when I looked at my list of “free” Excel Video Lessons. This became the impetus for creating this lesson. I invite you to explore all of my free Excel Video Tutorials. Click on this link to go to my Index of Excel Video Lessons. I have organized the free video lessons by topic. Simply click on the title of any lesson and you will arrive at the posting and the video for that lesson.

Download Excel File for This Lesson

• Click this link to access the Excel file that I used in this lesson. It is stored on my Sky Drive at www.office.live.com

View this Lesson in High Definition

I invite you to view this Excel Video Tutorial in High Definition, Full-screen Mode on my YouTube Channel – DannyRocksExcel

I always welcome your feedback. Let me know what you think about this – or any other – video lesson. Add your comments below!

When you are able to understand the strategic use of creating “white space”  on your PowerPoint Slides, you take the first step towards getting your audience to “pay attention” to what you have to say – to what you have to offer to them!

Use Paragraph Spacing to Make Bullet Points More Effective

In this video tutorial, I demonstrate how to effectively use “paragraph spacing” to make your bullet points more effective during your presentation – by effectively using “white space” between each bullet point.

Do NOT hit the “Enter” key repeatedly to add spacing between bullet points!

Rather, learn how to use the “Paragraph Spacing” dialog box to control the amount of space before and / or after each paragraph.

Each time that you press the ENTER key you create a new paragraph! So, effectively, each Bullet Point is a single paragraph!

Learn how to use this fact  strategiclally to create the message that you want to convey to your audience.

Watch – as I create “Before” and “After” slides to illustrate how creating more “white space” between your bullet points makes each point on the slide more effective.

One great Keyboard Shortcut to use as you fine-tune your paragraph spacing is the F4 key which repeats your last action. This is a powerful time-saver!

SmartArt Diagrams

Watch – as I show you how to convert a series of bullet points into a SmartArt Diagram to more effectively communicate your message to your audience. SmartArt Diagrams offer a welcome relief to an endles – or mindless – parade of bullet point ridden slides.  You can quickly convert a series of bullet points into a Smart Art Diagram as you will see on this video.

View this Video Tutorial In High Definition Mode

Click on this Link to view my video tutorial in High Definition on my YouTube Channel - DannyRocksPowerpoint