Tips And Time Savers | The Company Rocks

Danny March 22, 2009 1 Comment

My Favorite Excel Keyboard Shortcut

I use Keyboard Shortcuts in Excel everyday. However, the one that I count on the most; The one that has saved me from near disaster on multiple occasions is:

Ctrl + Z – The UNDO Command.

Ctrl + Z can Undo my last 16 actions (one at-a-time.) In Excel 2007, you can Undo up to 100 of your last actions!

Watch this short Excel Training Video as I demonstrate how to take advantage of the Ctrl + Z Shortcut.

Here are the steps to follow in this lesson:

Type an entry or apply formatting to a cell or range of cells.
Hold down the “Ctrl” Key while you press the “Z” key. Excel will Undo your last action.
To Restore the Action that you just UNDID, use the “Ctrl + Y+ Keyboard Shortcut.
You can Undo your last 16 actions in Excel 2003. Use the drop-down menu next to the UNDO Command Button on the Standard Toolbar.
Follow a similar procedure to RESTORE up to your last 16 actions in Excel 2003 with the drop-down menu next to the Restore Command button on the Standard Toolbar.
In Excel 2007, you can UNDO and RESTORE up to your last 100 Actions!

Are you looking for Excel Video Training for a specific topic? Search my Index of Excel Video Topics.

Navigate to Excel Worksheets with Hyperlinks

I encourage my clients to organize their work in Excel workbooks by creating additional worksheets. Each worksheet should contain discrete information relating to your Excel project – e.g. Monthly or Quarterly budgets; Regional sales, or information about specific product lines.

A best practice is to rename each worksheet to reflect the information it contains. I also encourage clients to add a “Tab Color” to each worksheet. This makes it easy to identify the “Active” worksheet.

Many of my clients use 20 or more worksheets in their workbooks and they ask my help in navigating to a specific worksheet or cell range.

One strategy that I share with them is to create Hyperlinks to go to a specific worksheet. Another strategy involves naming specific cells or ranges. Once you have “named” a cell or a range, simply click the drop down arrow next to the Name box; select the named range and Excel takes you directly to that “Name.”

These are great time-savers and they boost productivity. Watch this video to see these techniques in action:

Here are the steps to follow in this Excel Video Lesson:

Click a worksheet and then use Ctrl+Page Down to activate the next worksheet to the Right. Ctrl+Page Up activates the next worksheet to the Left.
Create a new worksheet and give it a name like “Dashboard.” Type in text like “Link to XYZ Worksheet.”
Ctrl+K opens the Create Hyperlink Dialog Box. Fist choose “Place in this Document.” Then select the name of the worksheet and click OK.
The easiest way to “Name” a cell or a region is to highlight it and then go to the “Name Box” to type in a name. Names must begin with a letter and not use spaces. Click ENTER to register the name.
Use the drop=down arrow next to the Name Box to find and go to a named cell or range. You can also create a hyperlink to a named cell or range.

Looking for a specific Excel Video? Click here for the Index to all Excel Video Lessons

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:

Filed Under: Excel Training, Tips and Time Savers Tagged: Excel Video Training, Hyperlinks, linking, Named Cells and Ranges, Navigate to Worksheets in Excel, Orgaanize Excel Workbooks with Worksheets

Danny December 19, 2008 Leave a Comment

Select Excel Data with Keyboard & Mouse-click Shortcuts

During a recent training class, I demonstrated several Keyboard and Mouse-click shortcuts for selecting and finding data. Several people in the class had “A-Ha” moments. So, I created this video lesson to share these shortcuts with you.

Here are the steps to follow in this Excel Video Lesson:

To select all of the contiguous data cells, make one cell the “Active Cell”. Ctrl+A will then select all of the cells in that data block.
If you then click Ctrl+A a second time, you will select every cell in the worksheet. This is handy when you need to “AutoFit the column width in the worksheet.
Use Ctrl+End to go to the last cell in your data set. Ctrl+Home will return you to the Top cell in the data set.
To find a blank cell in a column, position your mouse at the bottom of the “active cell” and double-click. This takes you to the last cell that contains data in that column.
To select all of the cells w/ data in a column use the Ctrl+Shift+ Down Arrow. Use the appropriate Arrow Key to select cell containing data in a Row.
Quickly copy a Formula to all of the cells in the column. Position the mouse in the lower right corner of the cell with the formula and double-click.

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.

Find a specific Excel Video Lesson – Index of Excel Video Lesson Topics

NEW! Download the Excel Workbook file that I used in this lesson:

select-data-w-shortcuts

Consolidate Data as a Quick Alternative to Subtotals

I have a great tip for you! When you need to produce a quick summary report of your data, use Consolidate in place of Subtotals. Here’s why:

You don’t have to sort the data when you consolidate.
You can use multiple functions (SUM, AVERAGE) in adjacent columns in your report.
You can easily copy and paste the Consolidated Summary Report to another workbook or into Word.

Watch this short video as I demonstrate how to use Data Consolidate in place of Subtotals.

Steps to follow in this lesson:

Select an empty cell. Choose Data – Consolidate.
In the dialog box, choose the Function that you want (SUM is the most common) and then select your data range (including the Column Headers) and click ADD.
Be sure to check the Use Labels in “Top Row” and “Left Column.” Click OK
A quirk – The Label for the Category does not appear when you Consolidate Data – Simply copy & paste it from cell A1.
Repeat these steps to add an additional Function – e.g. AVERAGE to your Consolidation Report.
Copy and paste the Consolidated Data to another workbook or to another application – e.g. MS Word.
Contrast this quick alternative to creating Subtotals.
The real “Time Saver” comes from the ability to quickly copy the Consolidated Summary.
The “Gotcha” to remember with Subtotals is to choose “Edit – Go To – Special – Visible Cells Only” before you copy the Level 2 Subtotals. If you omit this step, you will discover that you have copied ALL of the data and not just the summary!

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

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Filed Under: Consolidate Excel Data, Excel 2003 Video Lessons, Excel Training, Tips and Time Savers Tagged: Consolidate data by Position, Consolidate Excel data, Create Subtotals in Excel, Excel 2003 Training Video, Tips and Time Savers in Excel 2003

Danny October 5, 2008 Leave a Comment

Generate Random Numbers for Excel Spreadsheets

When I create the examples for this podcast I frequently use the =RANDBETWEEN() function to generate random numbers. Then, I usually copy the formulas and use Paste Special to convert them into values.

Recently, I watched Bill Jelen. Mr. Excel demonstrate an absolutely amazing Excel Trick – use your Right-mouse button to move a selected block of cells over the the right. And then, without releasing the mouse button, move the block of cells back into place. A menu pops up and asks if you want to “Copy here as Values.” Try it! It is amazing! What a time-saver!

Here are the steps to follow in the Excel Video Lesson:

Use the =RANDBETWEEN() Function to generate random numbers in your Excel spreadsheet. The function takes two arguments – a Top Number and a Bottom Number (both of your choosing.)
The RANDBETWEEN Function is part of the Analysis ToolPak Add-IN. Activate it by choosing Tools – Add-Ins- Analysis ToolPak.
Select the block of cells before you write the formula and then use Ctrl+Enter to populate all of the cells in your selected range with the formula.
To convert the formulas to values, use Copy, Paste Special Values.
OR… try this incredible trick (courtesy of Bill Jelen, Mr. Excel: Select the block of cells. With the RIGHT-Mouse Button, move the Border of the cell range over the the right and then back into place. Then, when you release the mouse a menu pops up. Choose, “Copy as Values Here.” It is amazining!

Find the 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.

Share and Enjoy:

Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: Bill Jelen, Convert Formulas to Values, Excel Analysis Toolpak Add-in, Excel Tricks, Mr. Excel, Paste Special Values, RANDBETWEEN() Function

Danny September 26, 2008 15 Comments

Excel’s QuickSum Calculator – Get Results Without Writing a Function

MS Excel 2003 has a great feature – The QuickSum Calculator – but you have to know where to find it. It is on the Status Bar – that area at the lower right of your screen. Simply select at least two data cells. They can be contiguous or non-contiguous. But remember where to look for the QuickSum Calculator – it is on the Status Bar.

Want to change the function? It simple! Just right-click on the name of the Function. You can choose Average, Count, Count Numbers, Min or Max – Excel’s most common Functions.

This is a great way to perform an Ad-hoc Query. I recommend that you use this for spreadsheets with Subtotals. Watch this short video lesson to see how this works – and how you can save time!

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

Share and Enjoy:

Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: AutoSum, Average Function, Excel Functions, Excel Subtotals, QuickSum Calculator, Status Bar

Danny September 23, 2008 2 Comments

The NASCAR Approach to Saving Time in MS Excel

This morning, while I was teaching a class to use MS Excel 2003, a young man in the class called out, “I get it!”

I replied, “That’s great, Ian. What did you get?”

“It’s NASCAR!”

“NASCAR?” I replied – somewhat puzzled.

He explained. “In NASCAR Racing, the pit crews save a second here and a quarter of a second there. That’s how they win the race.”

So, there you go – “It’s NASCAR!” Saving a few seconds here and a few seconds there is how you win the business race. Watch this video to see how to save time when creating multiple formulas.

Here are the steps to follow in this video:

To AutoSum BOTH the Columns AND the Rows with one-click of the mouse:
Select the range of cells containing the values that you want to total PLUS the blank cells immediately to the RIGHT and immediately BELOW.
Click the AutoSum ICON. Amaze your friends.
If you like Keyboard Shortcuts. Instead of clicking the AutoSum Icon, use Alt+=

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.

Share and Enjoy:

Filed Under: Excel Training, Tips and Time Savers Tagged: AutoSum, Excel Keyboard Shortcuts, Excel Video Training, NASCAR, productivity, Time-saving tips, Tips and Time Savers

Danny September 21, 2008 3 Comments

Calculate Data in Excel and Present it in PowerPoint

You can’t beat MS Excel when it comes to organizing, calculating & graphing data. However, when it comes to presenting data – especially to a large gathering of people – then MS PowerPoint is the best choice.

The 50 Best Tips Series

However, too many people simply re-type their Excel data into PowerPoint. This wastes time and it is not the most effective way to present the data.

In this video lesson, I demonstrate five ways to Paste, Link, and Embed Excel data in PowerPoint:

Here are the steps to follow in this video lesson:

In Excel, select and copy the data to the clipboard.
In PowerPoint, select the slide to receive the data and Right Mouse Click to “Paste.”
Look to the lower right-hand corner of the pasted data and click the Paste Options Menu.
Paste Option – Keep Text Only. You can move and re-size the Text Box. You can edit and format any text entry.
Paste Option – Picture of Table. You can move and re-size the picture. The Picture Toolbar is activated. This allows you to format the picture. You can not edit individual values – you only have a “picture” of your Excel data.
Paste Option – Table. This is the Default option. Each entry is in it’s own cell. You can choose Rows and Columns to format, insert or delete.
Paste Option – Excel Table (Entire Workbook) – With this option you actually “Embed” the Excel application. Double-click on the data to activate Excel. Make any changes you desire.
Edit-Paste Special – Link. With this option you create a “live link” to the source data in Excel. Any changes to the Excel file are reflected on the PowerPoint slide. Be careful to keep the Excel file and the PowerPoint file in the same directory to prevent “losing the link.”

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.

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:

When you want to copy the “results” of a formula and not the actual formula, choose “Edit, Paste Special, Values.”
If you want to copy the “source formatting” as well as the Values, choose “Edit, Paste Special, Values and Number Formatting.”
To change the orientation (from Vertical to Horizontal, e.g.) choose “Edit, Paste Special, Transpose.”
To ensure that your copied data remains current, choose “Edit, Paste Special, Paste Link.”
You can also use the Paste Special Dialog Box to choose Paste Special “Formats” and “Comments.”
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.

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.

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Related Videos:

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.

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

NEW! Download the Excel Workbook file that I used in this lesson:

Related Video Lessons

Steps to follow in this lesson:

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.

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.

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.

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

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

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

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Tags