Home Archives for Danny
August 19, 2008 6 Comments

Hide and Show Details in Excel Pivot Tables

I continue to explore the many great features of Excel Pivot Tables. In this video lesson, I show you how to place fields in the PAGE area of the template; how to hide details in order to see the big picture and to reveal the hidden details in various formats. You will also learn how to automatically generate multiple pivot table reports!

Watch This Video on YouTube

Here is the link to watch this video tutorial on YouTube .

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

  1. For this lesson I am moving the data sheet to a new workbook. Select Edit, Move or Copy Sheet (Click the “Copy” check box) and select “To a new workbook.
  2. Create the Pivot Table (Data, Pivot Table Report). In step 3, choose the “Layout” tab.
  3. On the Template, move “Territory” to the “Page” area; “Sales Reps” to the “Row” area; and move “YTD Sales” to the “Data” area twice. Click OK and then Finish.
  4. Rename the new worksheet – e.g. Pivot Table.
  5. Format the Numbers in the 1st Sum of YTD Sales Field – Right Mouse Click, choose Field Settings and the Number Tab.
  6. For the 2nd Sum of YTD Sales, Right Mouse Click, Field Settings and then “Options.” In the Options tab select “% of Total” from the drop-down “Show Data as:” box.
  7. Filter the Territories in the “Page” area.
  8. Move the Territories from the “Page” area to the “Row area.” You now have 2 Row Fields. Territory is the “Outer Row” and Sales Rep is “nested” as the “Inner Row.”
  9. Experiment with “Show Details” and “Hide Details” on the Pivot Table Toolbar.
  10. To create individual worksheets for the territories: Move Territory back to the Page area. From the drop-down options on the Tool Bar, select Show Pages.

NEW! My DVD Training Series, “The 50 Best Tips … ” is available for sale at the online store for The Company Rocks

Learn How to Quickly Create Pivot Tables – With Real World Business Examples!


Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , , ,
August 18, 2008 8 Comments

Introduction to Pivot Tables in Excel 2003

The majority of my clients have not yet used Pivot Tables. They are curious about them, but they are also afraid to get started with them on their own.

This is the first in a series of training videos designed to guide you in your discovery of Pivot Tables.

Here are the steps to follow in this video lesson:

  1. Start with an Excel data list that has clearly defined Column / Field Headers. With one cell active, Choose Data, Pivot Table Reports.
  2. You can accept all of the default settings to create a Pivot Table on a new worksheet.
  3. On the new worksheet you see: a) Floating Pivot Table Tool Bar b) Blank Pivot Table Template c) Pivot Table Field List
  4. Experiment by dragging & dropping Fields on to either the Row or the Column areas. Remember that Ctrl+Z (Undo) and Ctrl+Y (Redo) are handy tools to help you as you experiment with the layout that you want to see.
  5. To format numbers in Pivot Tables, double-click the “Sum of YTD Sales” header and in the dialog box select the Number tab to choose your format. This is different from the usual way you format cells. In Pivot Tables, you do NOT format numbers as cells. Rather you format the “Field Settings.”
  6. You can drop the YTD Sales into the Data area a second time. Then in Field Settings, choose a different Summarize by function – e.g. AVERAGE.

Free Excel 2003 Video Lesson, Workbook and Manual

 I invite you to download a free 28 minute Excel Video Tutorial, Workbook and Instructional Manual for Pivot Tables in Excel 2007 – Follow this Link to my WebEx by Cisco site.

Find the Excel Training Video that you want –

Index to all Excel Topics

My DVDs are now available for sale at my new online store !

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , ,
August 17, 2008 3 Comments

Keyboard Shortcuts – Part 2 – The Function Keys

We continue our series of lessons on Excel Keyboard shortcuts. In this video training, I demonstrate how each of the 12 Function keys serve as shortcuts.


The keyoard shotcuts that I use most frequently are:

  • F2 – to activate “in-cell” editing. Double-clicking a cell does the same thing – your choice!
  • F3 – To “paste a name” into a formula. (Of course you must have already created or defined named cells, ranges and constants for this to work.)
  • F5 – To go to any cell reference or named range. Explore the “Special” dialog box to “Go to” e.g. cells containing specific types of formulas – a great auditing feature!
  • f7 – To spell check you spreadsheet – great shortcut!
  • F11 – To insert a chart on a new worksheet with a 1-key shortcut. You can edit the chart once it is created.
  • F12 – Brings up the File, “Save As” dialog box – not many people know this shortcut.

Find the Excel Video Training Lesson 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

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , ,
August 16, 2008 1 Comment

The Company Rocks: Words Blog

Welcome to my latest blog “The Company Rocks Words!” Here, you will find short video tutorials offering you “tips & time-savers” when you use Microsoft Word. You will also find articles and commentary on our use of words to communicate ideas and to share information. We will examine which words work better than other words – and the reasons why they are effective.

I train individuals and groups to get the most out of MS Word for Windows. And, I train clients to improve their communications skills. My clients range from students to Fortune 500 executives. I have trained clients in a variety of industries; in the United States, Canada and Australia.

Most of my clients realize that they use only a fraction of Word’s powerful features. And… they want to learn how to tap into Word’s power in order to:

  • Save time
  • Be more productive
  • Present text in a clearly understood, professional manner

On this blog, I will share my tips with you via video tutorials. Each lesson will be brief (between 3 and 5 minutes) and to the point (1 concept per video.)

Many of these tips will show you how to transition from your familiar work in MS Word 2003 to the dramatically different Word 2007 interface.

The current version, 2007, is quite different – to say the least! Many clients are initially intimidated by the radically different interface. I will help you to transition to the new version – when you are ready.

So, check in often to see the latest tips that I add to the blog. And… please feel free to share your own “tips and time-savers” with our readers.

Ask questions. I will answer them individually as quickly as I can.

Sincerely,

Danny Rocks

Link to this post!
Filed Under: Communications, MS Word, Word Tips Tagged: ,
August 15, 2008 1 Comment

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

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged:
August 14, 2008 3 Comments

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

Link to this post!
Filed Under: Excel Formulas, Excel Training, Tips and Time Savers Tagged: , ,
August 13, 2008 7 Comments

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

Link to this post!
Filed Under: Excel Formulas, Excel Training, MS Excel Training, Tips and Time Savers Tagged: , ,
August 12, 2008 Leave a Comment

Watch My Excel Training Videos on YouTube

I have now joined the YouTube community by uploading several of my Excel Training Videos. Here is the link:

http://www.youtube.com/DannyRocksExcels

YouTube offers a unique opportunity for viewers worldwide to access and share video content with their friends and other members of their community. I want to be able to offer my Excel Video Training resources to them – the way that they want to access and share them.

The video experience that I offer on my The Company Rocks Excels website is quite different from the YouTube experience. Let us celebrate the difference!

News! My new DVD, “The 50 Best Tips for PowerPoint 2007” is available for purchase. Visit my online store for details.

Link to this post!
Filed Under: Powerpoint Techniques, Training Tagged: ,
August 12, 2008 2 Comments

Now You Can Watch My Excel Training Videos on YouTube

I have loaded many of my Excel Training Videos to YouTube. Here is the link:

http://www.youtube.com/DannyRocksExcels

YouTube is a powerful resource with a very wide reach. Many viewers enjoy being able to access and share YouTube content with their friends and other members of their community. I want to give them the opportunity to view and share my Excel Training videos the way the they like to do so.

There video experience on my The Company Rocks Excels site and on my YouTube site are different. Let’s celebrate the difference!

Let me know what you think. Take a minute to add cour comments below.

Link to this post!
Filed Under: Business Communications, Communications, Training Tagged:
August 12, 2008 Leave a Comment

Watch My Excel Training Videos on YouTube

I have posted several of my Excel Training Videos on YouTube. Here is the link:

http://www.youtube.com/DannyRocksExcels

YouTube is an incredible resource. I want to let as many people as possible know about the Excel training resources that I offer and YouTube will help me to accomplish this.

Some viewers find it easier to access and share videos via YouTube and I want to make it possible for them to do so.

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.

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, MS Excel Training Tagged: ,
« Previous Page
Next Page »