Home Archives for Podcast Tips & Timesavers
May 30, 2011 5 Comments

New Keyboard Shortcuts for Paste Special in Excel 2010

Content Key on Keyboard

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!

Learn More Paste Special Options

Main Menu Essential Skills for Excel

Main Menu for “9 Essential Excel Skills”

On my latest DVD-ROM, “Nine Essential Skills for Excel 2010,” I go into greater detail about the many ways that you can use Paste Special. This IS one of the 9 Essential Skills that I have identified. Follow this link to learn more about the 4 hour training video. The DVD-ROM includes 25 individual video tutorials, a 29-page instructional manual,  and the Excel 2010 Practice Files that I used while filming the videos.

Learn about all of the training resources that I offer at my secure online shopping website – http://shop.thecompanyrocks.com

Watch Video in High Definition on YouTube

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
New Keyboard Shortcuts for Paste Special in Excel 2010 [ 8:39 ] Play Now | Play in Popup | Download (2790)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
May 28, 2011 1 Comment

Introducing Live Preview for Paste Special in Excel 2010

Paste Special Gallery

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Introducing Live Preview for Paste Special in Excel 2010 [ 7:01 ] Play Now | Play in Popup | Download (2181)
Link to this post!
Filed Under: Excel Training, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , , ,
May 13, 2011 5 Comments

How to Apply Conditional Formatting to a Pivot Table in Excel

A Pivot Table is a great way for you to summarize and present the information contained in your data set. When you apply conditional formatting to your Pivot Table, you can “visually” spot important trends or anomolies in your data.

Improvements to Conditional Formatting

Conditional Formatting in Pivot Table

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.

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 Apply Conditional Formatting to a Pivot Table [ 6:01 ] Play Now | Play in Popup | Download (1642)
Link to this post!
Filed Under: Conditional Formatting, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , ,
May 11, 2011 Leave a Comment

How to Create an Interactive Pivot Chart in Excel

While I have created many Excel Video Tutorials demonstrating the power of interactive Pivot Tables during the past four years, I have NEVER created a video lesson focused on creating interactive Pivot Charts in Excel – until now!

Interactive Pivot Table Chart

Interactive Pivot Table Chart

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

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

Watch Video Now

 

Feedback Welcome

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

Extended Length Video Training Resource for Pivot Tables

I have created 90-minutes video training resources for Pivot Tables. Follow this link to learn more about my “Master Excel in Minutes” series.

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 Create an Interactive Pivot Chart in Excel [ 9:12 ] Play Now | Play in Popup | Download (1587)
Link to this post!
Filed Under: Download Files from Video Lessons, Excel Training, Most Viewed Excel Videos, Pivot Tables, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , ,
May 5, 2011 Leave a Comment

Making Effective Use of “White Space” on PowerPoint Slides

When you hear the term “White Space,” think “Breathing Room.”  As a nation, we tend to scan a document before making the decision to go back and read it – or delete it!.

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

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 Make Effective Use of White Space on PowerPoint Slides [ 8:00 ] Play Now | Play in Popup | Download (1287)
Link to this post!
Filed Under: Podcast Tips & Timesavers, PowerPoint, Powerpoint Techniques, PowerPoint Video Training, Presentations Tagged: , , , ,
April 25, 2011 4 Comments

Free Video Recording of Excel Keyboard Shortcuts to Download

I have just published a 50 Minute Video Recording on which I demonstrate the most popular Keyboard Shortcuts for Excel. The great news – for you – is that you can download this video recording for FREE! If you prefer to view this online, you can do so – for FREE!

Download My Excel Workbooks

Download my Workbooks

Download Excel Workbooks

In addition, I have published the Excel Workbooks, Adobe PDFs and PowerPoint files that I use during this 50 minute video online. You can access them – for FREE!

I store them as “public documents” for you to download on my “Sky Drive” at Windows Live! Here is the direct link:

http://cid-644f2f857aa6afdc.office.live.com/browse.aspx/Public%20WebEx%20Documents/Excel%20Keyboard%20Shortcuts%20Part%201

In order to access these files, you will need a “free” Windows Live account (First Name, Last Name, email address). You can also use your HotMail account to login.

My Extended Length Video Recordings on WebEx

I have recently began to publish “extended length” video recordings at http://thecompanyrocks.webex.com – This 50 Minute video is my 5th recording on the site. I offer it to you “free of charge.” However, you must register (fitst name, last name, email address) with WebEx by Cisco before you can download the recording or view the recording online.

In this format, I can offer “specialized instruction” for Excel Pivot Tables, Macros, Public Speaking Courses, etc. I can also offer instruction in older (Excel 2003) and newer (Excel 2010) programs. In addition, I can create a specialized recording “just for you.” Contact me to learn about how to organize this!

Landing Page for More Information

Links for Downloads

Information about Links and Downloads

Rather than repeat what I have already written, here is the link to the special Landing Page that I have created for this 50 minute video recording. On the Landing Page, I have included all of the direct links to the download pages for the video, the Excel files, PowerPoints and Adobe Acrobat documents that I used on this recording.

I invite you to view or download these new resources that I have made available. When you have a chance send me your comments, questions and suggestions for future recordings!

Danny Rocks

The Company Rocks

Watch This Video Lesson in High Definition

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Free Video Recording of Keyboard Shortcuts to Download [ 8:51 ] Play Now | Play in Popup | Download (1317)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, Podcast Tips & Timesavers, Video Training Courses, WebEx by Cisco Tagged: , , , , , , ,
April 22, 2011 Leave a Comment

How to Use SUMIF in Excel to Total Year-to-Date Sales

One of my viewers asked for my help to use the SUMIF Function in Excel to total Year-to-Date (YTD) Sales. Generally, the SUMIF Function is very easy to set up and use. However, when you need to refer to a “date” in the criteria argument, there is a “Got’cha” step.

How to Write the Formula for Year-to-Date Sales

=SUMIF(Date, “<=”&TODAY(), Sales) Notice how the <= comparison operator is enclosed within ” ” (Quotation Marks) and I use the & (ampersand) to join the TODAY() Function. Be careful when you write this fomula. In my experience, when I try to write THIS formula in the “Functions Argument” Dialog Box, I almost always get an Error Message.

Create and Use Named Cell Ranges in SUMIF Function

As a best practice, I encourage you to create and use “Named Cell Ranges” in your formulas. This is especially helpful with the SUMIF, SUMIFS, COUNTIF, COUTIFS functions because you are usually looking inside extensive ranges of data.

Another Way to Total Year-to-Date Sales

For many years, I did not realize that there was a SUMIF Function. So, I used to create an extra column and write to formulas to compute the YTD Sales. If you do want to or need to see YTD sales in each cell, I show you how to do this by writing just one formula.

Watch Lesson in High Definition

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

Download My Free Video Lessons on Pivot Tables

Click here to learn about my 24 minute video recordings “Introducing Pivot Tables.” I have two versions – One for Excel 2003 and one for Excel 2007. I am offering both of them as a “free of charge” downloadable product!

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 Use the SUMIF Function to Total Year to Date Sales [ 5:09 ] Play Now | Play in Popup | Download (1424)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Training, Podcast Tips & Timesavers, SUMIF and SUMIFS Functions Tagged: , , , , , ,
April 16, 2011 Leave a Comment

Using Paragraph Styles in Word to Improve Readability

50 Best Tips for Word

50 Best Word Tips

I am proud to announce the launch of a new series of Video Tutorials for Microsoft Word!

In addition to the major changes in the user interface from Word 2003 to Word 2007 and Word 2010, there was another change that many people who use Word on a regular basis have some difficuly in understanding:

In Word 2007 the default setting adds 10 pts of space after each paragraph. This means that you only have to hit the “Enter” key once at the end of each paragraph. By contrast – In Word 2003, the default setting is 0 pts of space when you hit the “Enter” key. That is why – for years – we have developed the habit of hitting the “Enter” key twice at the end of a paragraph.

In addition, in Word 2007 and Word 2010, there is an automatic 1.15 line spacing within each paragraph versus the “single line spacing” in Word 2002.

More “White Space” on the Page

As a result, the pages in Word 2007 and 2010 appear to have more “breathing room” because more “white space” has been built in to the default paragraph settings. Learn to take advantage of this to make it easier for your readers to quickly “scan” you pages – to quickly find out “what you have to offer them!”

I know that you will pick up many great tips during this video tutorial. One of them is to use the Keyboard Shortcut Ctrl + Shift + S to bring up the “floating” Apply Styles Dialog Box.

Getting Names and Addresses to Fit on Labels in Word 2007

Do you have difficulty getting your “Address Block” to print correctly when you perform a Mailing Label Merge in Word 2007? Many people do. Watch this lesson to learn how to make 2 changes to ensure that your names and addresses print correctly on your labels the first time – and everytime!

New Extended Length Video Tutorials on My WebEx Site

 I have just posted two extended length, in-depth Word Video Tutorials at http:thecompanyrocks.webex.com

Follow these links to view online or download:

Watch This Video Lesson in High Definition on YouTube

Follow this link to watch this video lesson on my YouTube Channel – DannyRocksExcels

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Using Paragraph Styles in Word to Improve Readability [ 9:41 ] Play Now | Play in Popup | Download (954)
Link to this post!
Filed Under: Podcast Tips & Timesavers, Word Tips Tagged: , , , , , , , ,
April 14, 2011 2 Comments

Video Tutorials for Both Excel 2007 and Excel 2003

Many of my clients and viewers continue to use Office 2003 Programs (Excel 2003, Word 2003, PowerPoint 2003, etc.) and they have asked me to produce more video lessons that use these programs.

Finally, I have found the solution!

WebEx Recorded Sessions

WebEx Recorded Sessions

Extended Length Video Recordings on WebEx by Cisco

I have just started to create a series of extended length, in-depth instructional video tutorials as part of my “Master Business Skills in Minutes!” Series. I am making these recorded video lessons available on my WebEx by Cisco Systems website: http://thecompanyrocks.webex.com

My goal is to offer a “mix” of recorded video lessons where some are offered “free of charge,” while others will carry a modest fee. Each lesson that I publish will be offered in two versions – one for Office 2007 and a parallel version for Office 2003. Eventually, I will publish versions that use Office 2010.

Registration is Required – and Simple!

Each of the lessons – free or paid – will require you to register at WebEx before you can have access to download the video or to view it online. I have made the registration process as simple as possible: all that you need to supply is First Name, Last Name and a valid email address. That’s it! I value your privacy – click here to read or download the Privacy Policy for The Company Rocks.

Create a Pivot Table with Fewer than 10 Clicks of Your Mouse

Free Video Lessons on My Website, Video Podcast and YouTube

I will continue to post free Excel Video Lessons here on my website – www.thecompanyrocks.com and I will include links to my YouTube Channel – DannyRocksExcels  These videos will be short and focused – under 9 minutes!

What You Get with My WebEx Video Recordings

The videos that I publish at http://thecompanyrocks.webex.com -are designed to be longer than what I post here on my website or to YouTube. On the “extended length” videos, I will pay much more attention to the details and the “reasons why” aspects of the key steps. With WebEx, I can also offer you the opportunity to download the actual Excel Workbook that I used to create the lesson and give you a PDF of the “Step-by-step” instructions for each lesson.

I am excited about this new development. I want to hear what you think. Kindly add your comments and feedback in the section below this post.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Podcast Tips & Timesavers, WebEx Recordings & Webinars Tagged: , , , , , ,
April 10, 2011 Leave a Comment

Create a Web Query in Excel to Gather Updated Information

Web Query in Excel

Web Query in Excel

When you select, copy and paste data from a website into an Excel document, you get static images and data. When you want that data to be updated – or refreshed – with the latest information, you create a Web Query in Excel.

Follow the process that I demonstrate in this video and you will learn how to gain access to current information without having to leave Excel!

Create a Web Query

  • On the Data tab of the Excel 2007 Ribbon, select Get External Data – From Web.
  • In the Dialog Box – that resembles a “Web Browser” – type in the URL of the Website you want to search. Notice that the “Browse” Button is missing. My Best Practice is to make a copy of and paste in the URL of the website you want to search.
  • After you press the “GO” Button, you will see some “Yellow Arrow” markers on the website. These are the HTML Tables that you can select for your Web Query.
  • When you however your mouse over the Yellow Arrows, you will see a Blue Border around the HTML Table. Click the Yellow Arrow and a “Green Check Mark” indicates that you have selected this table. Continue to make your selections and then click “Import.”

Import Web Query

The final step is to decide where to place your Web Query, I usually accept the default selection of cell $A$1. There is an “Properties” Button that you can access now – or later. Press the “Add” Button and after a few seconds, you have imported the data from your Web Query.

As you will see in the Video for this lesson, it it easy to go back and Edit your Web Query or Change the Properties of the Query – e.g. to automatically Refresh every 60 minutes. You can also choose to Refresh the Query now.

Best Practice Suggestions

  • Use a separate session of Excel when setting Properties of your Web Query to automatically refresh. In this way, you will not slow down your work on another Excel project.
  • Gather your favorite Website URLs and paste them as Hyperlinks on a Worksheet. Select the Hyperlink prior to initiating a new Web Query. This will save you time as the Web Query Dialog Box does not have a Browse Button!

Watch Video in High Definition

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

Learn How to Master Excel in Minutes – Not Months!

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Create a Web Query in Excel [ 6:11 ] Play Now | Play in Popup | Download (1008)
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Podcast Tips & Timesavers Tagged: , , , , , ,
« Previous Page
Next Page »