Archives for August 2008

Video Blogging

Over on my “Excels Blog” I have just posted my 50th video blog. The fact that I have accomplished this in less than 2 months still amazes me!

I had a vision for posting short video tutorials on MS Excel topics. Frankly, I couldn’t imagine demonstrating a software program without incorporating video! It would be too dry and dull.

Of course, most of the things that we do today on the Internet are incredible. Technology advances so quickly. What I accept today as “state of the art” will be considered “old school” in just a few months.

So, dear readers, I will start to add some video and audio posts to this blog. The written word will remain in place. I am simply going to add a few new media elements when appropriate.

Meanwhile, I invite you to visit the two Video Blogs that I now have up and running:

Thank you for your continued patronage. I welcome your feedback.

Speak at a comfortable rate during your presentation

I just recorded a new video PowerPoint Presentation for one of my websites. Since I intended to post this video on the website, I wanted to be sure that the total length did not exceed 4 minutes. In Internet-time, that can seem like an eternity!

I had a story to tell – promoting my services as a trainer. So I wanted to make it interesting. I want viewers to watch the entire video – it comes in at 3 1/2 minutes. And, of course, I wanted to conclude the video with a strong “call to action” – contact me to discuss my training services.

Here are the steps that I followed to create and record the PowerPoint video:

  1. I wrote a short story board – Opening slide, Introduce me, What I do, How I do it, etc.
  2. This came out to 8 slides. I then created them in PowerPoint.
  3. I made sure that I followed the “Four-by-Four” rule with my bullet points. Only 3 of the 8 slides contain bullet points.
  4. I wrote my script. This was an easy step since I had been writing my script “in my head” as I created the PowerPoint slides.
  5. The script come in at 570 words. I read it aloud several times and then I timed it.
  6. My first reading – at performance level – came in at 3 1/2 minutes. That is a speaking rate of 163 words-per-minute (wpm). A little fast – but it felt comfortable to me.
  7. I made a test (audio) recording and I was happy with the script. I made a few changes – where I stumbled over a word or two.
  8. Now it was time to record the PowerPoint presentation (video & audio together.) I added “Heading 2” styles to my script at each point where I was to advance the slide.
  9. I had 2 false starts. Because I had written my script and rehearsed prior to the recording, I finished the project in just 2 takes.

Give me your feedback after watching the video. What do you think? Did I speak too fast? Was my story clear? etc.

Add your comments below.

And, yes, you can contact me if you want to engage my services!

danny@thecompanyrocks.com

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

My 50th Free Excel Training Video is On-line!

Wow! In just under two months’ time, I have posted 50 free Excel Training Videos!

I hope that you have been enjoying them half as much as I have in creating them. Please drop me an email – danny@thecompanyrocks.com – to give me your feedback. Tell me what you like. Tell me what you don’t like – and why not. And, also tell me what you would like to see more of (or less of, for that matter!)

I will answer your questions as quickly as possible. Either privately or in a public Excel Video training lesson (no names mentioned!)

To celebrate, this milestone, I have added a short ( 3 1/2 minute) video introduction to The Company Rocks Excels. I explain my training process and teaching style. I also – “hint, hint” – demonstrate how you will benefit when you engage me to train you and/or your staff!

I love training people to master the “Tips & Time-savers” in Excel! I would love to train you!

Please call me @ (310) 215-0678 or email me – danny@thecompanyrocks.com – so that I can start to design the best Excel training program to meet your present needs!

Thank you for your support.

Danny

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.

Learn how Excel calculates dates

If you create invoices or track time-sheets, you use date calculations in Excel. If you earn interest from an investment or pay interest on a loan, you need to understand how Excel handles dates. Once you grasp the concept that a date is a serial number, then you will start to understand how spreadsheets calculate dates. In this lesson, Danny will demonstrate the key concepts that will enable you to work smarter when you use dates in Excel formulas.

These are the steps to follow in this video lesson:

  1. Dates, in Excel, are serial numbers. Serial number 1 is January 1, 1900. (Apple Macintosh computers use January 1, 1904 as serial number 1.)
  2. Use the Keyboard shortcut Ctrl+Shift+~ to reveal the serial number of any date.
  3. The =TODAY() function is very useful. It will update to reflect your system’s date. If you want to “date stamp” a cell, use the keyboard shortcut Ctrl+; to enter today’s date (this will not update)
  4. Many formulas use the =DATE() function. To understand this Function, experiment with the =YEAR(), =MONTH() and =DAY() functions. These functions are frequently used inside the =DATE() function.
  5. For the Formula =End_Date-Start_Date, decide if you worked, (earned interest, paid interest, etc.) on either date. If so, the formula is =End_Date-Start_Date+1
  6. To calculate the # of Years between dates use a formula like; =YEAR(C7)-YEAR(B7)
  7. Excel has a “secret” formula =DATEDIF() – you can not get help for this formula. Use it to determine a person’s age, e.g. =DATEDIF(B11, C11, “y”)
  8. To determine the “Day in the year” use this formula =B15-DATE(YEAR(B15), 1, 0) – where cell B15 contains the date that you want to use.
  9. To see how many day remain in the year, use this formula =DATE(YEAR(B19), 12, 31)-B19 – where cell B19 contains the date that you want to use.
  10. When you want to write a formula to increment cells by 1 month (assuming that the starting date will change, use this formula =DATE(YEAR(F16),MONTH(F16)+1,DAY(F16))
  11. When you want to write a formula to increment cells by 1 year (assuming that the starting date will change, use this formula =DATE(YEAR(F17)+1,MONTH(F17),DAY(F17))

If you have questions on this lesson, please send me an email danny@thecompanyrocks.com

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 availabe to purchase. I invite you to visit my online bookstore for more details.

Related Videos

Practice Preventive PowerPoint – How to Avoid Reading Your Slides

Book Cover - Why Most PowerPoint Presentations Suck...

Buy this book!

I just started reading Rick Altman’s totally irreverent – and totally relevant book this past weekend. I love this book! Even better, I love the advice that Rick share with his audience.

How many time have you sat in the audience while the presenter proceeded to read the slide – “word-for-word.?”

Better question – How many times have you – the presenter – read your slides “word-for-word?”

The answer to both questions – “Way too many times!”

Now, we can’t directly change how other people choose to present – unless they are are one of our direct reports; or, they come to us seeking coaching on their presentations. However, we can implement “Preventive PowerPoint Practices” to improve our own presentations.

Rick helps us with 2 “Universal Axiom’s:

Universal Axiom #1:

“If a slide contains complete sentences, it is practically impossible for even the most accomplished presenters to avoid reading the entire slide word for word.”

Universal Axiom #2:

“When you read your slides word for word, you sound like an idiot.”

So what is the solution? How can you implement”Preventive PowerPoint Practices?”

Simple – Do not put complete sentences on your slides!

I can attest to this first hand. From time to time, I deliver training using PowerPoint slides created by my client. Many of the slides contain complete sentences hiding as “bullet points.” I have to really force myself to not look at the slides lest I succumb to the temptation to “read the slide word-for-word.” And I am an experienced presenter. I have to consciously remind myself to look at the audience and not at the slide!

It is so much easier when you implement “Preventive PowerPoint Practices” – Do not put complete sentences on your slides!

The Four-by_Four Rule

In an earlier post, I demonstrate the “Four-by-Four Rule for PowerPoint”

  1. No more than “Four” Bullet Points per Slide.
  2. No more than “Four” words per Bullet Point.

Read Rick Altman’s Book – “Why Most PowerPoint Presentations Suck… And How You Can Make Them Better.”

Go back to ALL of your PowerPoint Presentations and remove Every Complete sentence. Now – before you forget.

There is one exception to this rule: If you are using a quotation on your slide. A good quotation is usually brief. Just make sure that you choose a quotation that complements your point.

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

Paste Special Saves You Time – Explore Your Options

I love the Paste Special options in Excel (and Word and PowerPoint.) They save me time. They help to ensure that I am always referring to the latest data. And they help me to be more productive. What’s not to like?

In this video lesson, Danny will demonstrate how to get the most out of each Paste Special option.

Here are the steps to follow in this video lesson:

  1. When you want to copy the “results” of a formula and not the actual formula, choose “Edit, Paste Special, Values.”
  2. If you want to copy the “source formatting” as well as the Values, choose “Edit, Paste Special, Values and Number Formatting.”
  3. To change the orientation (from Vertical to Horizontal, e.g.) choose “Edit, Paste Special, Transpose.”
  4. To ensure that your copied data remains current, choose “Edit, Paste Special, Paste Link.”
  5. You can also use the Paste Special Dialog Box to choose Paste Special “Formats” and “Comments.”
  6. Experiment with Paste Special Add, Multiply, Subtract & Divide.

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

Create a Calculated Field in Your Pivot Table

When someone wants to see an additional field in your Pivot Table – e.g. to show the “Price per Units Sold,” you create a “Calculated Field.” This is a fairly simple process. In this video, Danny will show you how. This is yet another example of how you can use the power of a Pivot Table to present data the way that you – or your audience – want to see it.

Here are the steps to follow in this lesson:

  1. On the Pivot Table drop-down menu select “Formulas, Calculated Field.”
  2. In the dialog box, write in a Name for your new Calculated Field.
  3. Enter the Formula. Use the Fields in your Pivot Table and any operators (+,-,*,/) Click OK.
  4. Use the Field Settings to change any formatting, etc.

Find the Excel Training Video you want

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.

Get a Free 28 Minute Excel Video Tutorial

Follow this link for information to download – “Introduction to Pivot Tables in Excel 2003”

Use Pivot Tables to Summarize by Year, Quarter and Month

Can you imagine trying to use Excel Subtotals to summarize a long column sales – listed by date? What would you subtotal?  At every change in date?

This would be a nightmare! This would produce a useless report!

Pivot Tables, on the other hand, can provide a summary of dates by month, quarter, and year with just a few mouse clicks!

In this video lesson I show you how to produce that summary. The Pivot Table will transform a long list of data into information that you can use to analyze trends.

Here are the steps to follow in this lesson:

  1. Create a Pivot Table using all of the default settings. Drag the “Date” field into the ROW area and the “Sales” field into the DATA area of the Template.
  2. With 1 cell in the “Date” row selected, choose “Group & Show Detail, Group.” Select Year, Quarter and Month and click OK.
  3. You now have 3 ROWS in your Pivot Table – Year, Quarter & Month. Drag the Year field from the ROW up to the COLUMN area. Hide on of the “Grand Totals.”
  4. Click the Pivot Chart icon to create a Pivot Chart on a new worksheet. Filter the data fields.
  5. Move the Year field back to the Row area. Ungroup the fields to return to the starting point.
  6. To Group by WEEK: Choose “Group & Show Detail, Group” and select DAY. Then select 7 for the number of days in the week.

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

Additional Pivot Table Resources

Pivot Table Training Resources

Pivot Table Training Resources

I have published a 90 minute focused video tutorial for Pivot Tables. It is available for each version of Excel -2010, 2007, and 2003. You can purchase it as either a DVD-ROM that I will ship to you or as a Downloadable version that you can work with immediately.

In addition to the 90 minutes of video instruction on Pivot Tables, the package includes:

  • The Excel Practice Files that I used while filming the video tutorial – so that you can practice your new skills using the same files that are on the video.

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to learn about the many training resources that I offer for sale.

  • A Step-by-step Instructional Guide that you can print out – so that you can use it to take notes as you watch my video tutorial.

Related Video Lessons

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!



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 !