3 reasons to apply conditional formatting to your Excel data

In my experience, Excel’s “conditional formatting” is an underutilized feature in spreadsheet analysis. And that is a shame. In this Excel training video, I will show you how, when and why to apply conditional formatting to dynamically point out important data in your spreadsheet.

Here are the steps to follow in this video lesson:

  1. Select the cells that you want to apply conditional formatting to. From the Format menus choose Conditional Formatting.
  2. In the dialog box choose “Cell Value is” and then choose an operator (Greater than, etc.) and then either type in a value or refer to a cell. This applies your Condition.
  3. Next, choose what Formatting to apply when cells meet your Condition(s). Click OK
  4. You may also choose, “Formula is” and then type in your formula to establish the Condition. Only use Formulas that can be answered as “TRUE” or “FALSE.” If the answer (implied or explicit) is “TRUE,” then the Conditional Formatting will apply.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Videos

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

4 reasons to use Excel's Formula Auditing tools

In this Excel training video I will show you how to use the Formula Auditing Toolbar to:

  1. Learn about the relationships among your formulas and cells by tracing precedents & dependents.
  2. Finding the source of your formula errors – and correcting them!
  3. Evaluate your formulas step-by-step to better understand how they are built.
  4. Setting up a small window to “watch” your key formulas change as you enter and edit data in cells.

Here are the steps to follow in this video training lesson:

  1. Select Tools,  Formula Auditing, Show Formula Auditing Toolbar.
  2. Select a cell containing a formula and experiment with the icons to Trace Precedents and Trace Dependents. Click the icon several times to trace back as far as you can go.
  3. Select a cell and Evaluate its Formula. Step In and Step Out to learn how the formula is built.
  4. Paste a list of all of the Names in your workbook. Select Insert, Name, Paste, Paste List.
  5. Select a cell with a formula and click the Show Watch Window icon to see how the formula results change as you enter and edit data in cells that feed into that formula.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

3 reasons to use named cells and ranges in Excel forumlas

I use named cells, ranges and constants in my Excel formulas and workbooks for 3 reasons:

  1. They are easier to explain (to others and to myself 6 months after I create a formula.)
  2. They are easier to adapt (to other locations in the workbook – absolute cell references not required!)
  3. They are easier to update (especially named constants in formulas when e.g. rates change.)

These are the steps to follow in this Excel training video:

  1. Select the cells that you want to name and also the labels in the left column and top row.
  2. Choose, Insert, Name, Create. Make sure that Left column and top row are selected.
  3. To verify that your names have been created, click the Name box drop down list of names. Select a name and Excel takes you directly to that cell or range.
  4. To substitute Name cells and ranges in existing formulas: Choose Insert, Name, Apply, OK
  5. When you are writing a new formulas and you want to use a name, use the F3 “Paste Name” shortcut combination to select the name you want in the formula and click OK.
  6. A “Named Constant” does not refer to a cell. Select Insert, Name, Define and then Name the Constant and in the “Refers to” part of the dialog box type in e.g. =0.0825 if you want your Named Constant to refer to a Sales Tax rate of 8.25%

Find the Excel Video Training Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Training Topics

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Format your Subtotal title lines

Here are the stepes to follow for this video lesson:

  1. Create your subtotals – remember to SORT your data first!
  2. Choose Subtotal Outline 2 – in most cases this shows the Subtotal Title Lines only.
  3. Select the data that you wish to format.
  4. Click Edit, Go to, Special, Visible Cells Only (Or the keyboard shortcut Alt+;) and apply your formatting.

Find the Excel Video Lesson that you want – Index to 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.

Related Video Lessons

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Perform "What-if" analysis with a 2 input Data Table

Here are the steps to follow in this video lesson:

  1. Create a formula that references cells – e.g. =PMT(B4/12,B5,B3)
  2. To the right of this formula cell, enter a series of “substitute” values – e.g. 36, 48, 72 months.
  3. Directly below the formula cell, enter another series of “substitute” values – 4%, 4.5%, 5%, etc.
  4. Select the range of cells for the Data Table – starting with the formula cell.
  5. From the Excel Menu, choose Data, Table.
  6. For the “Row Input Cell,” click the cell in the original formula for which you want to “substitute” the row values in your Data Table.
  7. For the “Column Input Cell,” click the cell in the original formula for which you want to “substitute” the column values in your Data Table. Then click OK.

NEW! Download the Excel Workbook that I Use in this Video:

Find the video lesson that you want – Index to 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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Goal Seek to optimize the results of the =PMT() function

Here are the steps to follow in this video lesson:

  1. Calculate your monthly payment on a loan using =PMT() function
  2. =PMT() requires three arguments: “Interest Rate” / # of payments per year, “# of payments” over the course of your loan, the “Amount Borrowed.”
  3. For Goal Seek: Select the cell that contains your formula and then choose Tools – Goal Seek.
  4. In the “To Value:” box type in your GOAL – e.g. The amount that you want your =PMT() to return.
  5. In the “By Changing Cell:” select a cell that is referenced in your =PMT() formula.

Find the video lesson that you want – Index to all Excel Video Lessons

“The 50 Best Tips for Excel 2007” DVD is now available to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to reveal the formulas in all cells

Here are the steps to follow for this lesson:

  1. To reveal the formula in a single cell, double-click the cell or press the F2 key.
  2. To reveal all of the formulas in the worksheet use Ctrl + Tilde (Ctrl+˜) – This is a “toggle.”
  3. There are 4 ways to write a formula to show the discounted price:
    1. If the cell containing the discount % is a negative (-20%) number =B5+(B5*$D$2)
    2. If the cell containing the discount % is a positive (20%) number =B5-(B5*$G$2)
    3. If the cell containing the discount % is a negative (-20%) number =B5*(1+$D$2)
    4. If the cell containing the discount % is a positive (20%) number =B5*(1-$G$2)

Find the video lesson that you want – Index to 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.

Related Videos

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Keyboard Shortcuts – Part 1

Here are the steps to follow in this lesson:

  1. All shortcuts in this lesson require you to hold down the “CTRL” key while you press a single Letter.
  2. Ctrl+A will select either all of the cells in the current worksheet or just the range of cells where you are working.
  3. Ctrl+B (Bold), Ctrl+I (Italic) and Ctrl+U (Underline) will “toggle” the formatting on or off.
  4. Ctrl+D (Fill Down) and Ctrl+R (Fill Right) require you to select a range of cells beforehand.
  5. The “Office Clipboard” allows you to retain 24 items in memory. You can use them in all of the applications across the MS Office Suite. (Ctrl+F1 brings up the Task Pane for the Clipboard)
  6. Ctrl+Z (Undo) and Ctrl+Y (Redo) apply to the last 16 actions (provided you have not “saved” the workbook.)

Find the video lesson that you want – Index to 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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Use Paste Special, Add to combine data from 2 columns

Here are the steps to follow for this lesson:

  1. Copy the data in the new column.
  2. Select the top cell in the original column and click Paste Special, Add
  3. To preserve any special formatting in the original column take this extra step:
  4. Click Paste Special, Values, Add

Find the video lesson that you want – Index to 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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to identify and convert text values to numbers

Here are the steps to follow in this lesson:

  1. Very that all values are actually numbers e.g. use the =COUNT() and =ISTEXT() functions.
  2. To convert a series a text values to numbers, first select a blank cell and copy it to the clipboard.
  3. Highlight your text values and choose Edit, Paste Special. In the Paste Special Dialog Box choose ADD.
  4. All text values are now converted into numbers that can be summed, etc.

Find the video lesson that you want – Index to 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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn