Home Archives for Danny
September 6, 2011 Leave a Comment

Keyboard Shortcuts to Enter and Edit Data Efficiently in Excel

 

Keyboard Shortcuts to Enter Data

Keyboard Shortcuts to Enter Data

This is the latest installment in my series of video tutorials where I demonstrate how to use Keyboard Shortcuts in Excel. In this tutorial, I demonstrate how to Enter and Edit Data Efficiently in Excel using Keyboard Shortcuts.

Tips Covered on Video Tutorial

Here are the main Keyboard Shortcuts that I show you how to use on this video:

  • Enter Current Date in a cell.
  • Enter Current Time in a cell.
  • Use Ctrl + Enter to accept data entry and keep the focus on the current cell.
  • Use Ctrl + Enter to populate a range of selected cells.
  • Use Alt + Enter to “Wrap the Text” in a cell.
  • Use F2 to edit data inside a cell.
  • Use Ctrl + left & right directional arrows to move to the beginning of the next / previous word while editing in the cell.
  • Use ‘ (apostrophe) as the first character in a cell to make this a “text” entry.

Download my complete Chart of Popular Keyboard Shortcuts as a PDF 

 Follow this link to download – for free – my 5 Page Chart of Popular Excel Keyboard Shortcuts as a PDF.

Subscribe to my Video Podcast on iTunes

Here is the link to subscribe to my video podcast, “Danny Rocks Tips and Timesavers” on iTunes. It is offered for free!

Watch Tutorial in High Definition on YouTube

Follow this link to view this Excel Tutorial in High Definition mode on my YouTube Channel – DannyRocksExcels

Keyboard Shortcuts to Enter and Edit Data Efficiently in Excel [ 11:43 ] Play Now | Play in Popup | Download (1304)
Link to this post!
Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, Podcast Tips & Timesavers Tagged: , , , , , ,
September 5, 2011 Leave a Comment

How to Use Criteria in Excel Functions and Filters

Criteria for Advanced Filter

Criteria for Advanced Filter

When you apply a Filter to an Excel data set, you use Criteria. In a filter, only the records that can answer “TRUE” to the criteria that you set will appear. The records that answer “FALSE” are hidden while the filter remains in place.

Logical Functions

The IF(), AND() and OR() Functions belong to a group of Logical Functions. In their function arguments, they evaluate a “Logical Test” and can perform one action is the answer is “TRUE” and a different action if “FALSE.” 

  • The AND() Function – each of the Logical Tests must be met in order to return the answer “TRUE.”
  • The OR() Function – if any condition is met, the answer that is returned is “TRUE.”
  • I frequently “Nest” an AND() Function or an OR() Function inside an IF() Function to test multiple criteria.

Advanced Filters in Excel

While many Excel users are familiar with Filtering, in my experience, I find that many people get confused when it comes to using Advanced Filters. In this video tutorial, I try to eliminate this confusion. Watch and you will learn how to:

  • Write Criteria for Advanced Filters using “AND” criteria and “OR” Criteria.
  • Copy the “Filtered Records” to another location
  • Use the Keyboard Shortcut Ctrl + Shift + L to quickly “toggle” normal filter drop-down menus on or off.

 

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Defintion on my YouTube Channel – DannyRocksExcels

Link to this post!
Filed Under: Data Filters and Queries, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
August 31, 2011 Leave a Comment

Record a Macro in Excel to Fix an Imported Text File

Macro Commands

Macro Commands

A viewer asked for my help in fixing a “Text” file that he imports into Excel each month. His IT department creates this file for him. The problem is that:

  • The Field Header Labels are arranged on two rows – He needs all of the labels to be on one row.
  • Each record is arranged on two rows – He needs each record to appear on one row.
  • There is a blank row separating each record  – He needs to remove all of the blank rows.

This is a perfect job for an Excel Macro!

In this tutorial, I first demonstrate how to create the Macro in Excel 2010. Then, I repeat the process using Excel 2003 – the version that my viewer is currently using.

Topics Covered in the Tutorial

  • Adding the Developer Tab to the Ribbon in Excel 2010
  • Creating back up copies of worksheets before recording the Macro
  • Using the Fill Across Worksheets (Grouped) to copy both content and formatting
  • Using Relative References when Recording a Macro
  • Planning the steps you will take before Recording the Macro
  • Using Keyboard Shortcuts to Run the Macro
  • Creating and Editing a Command Button to Run the Macro
  • Viewing the VBA Code that Excel creates when you Record the Macro
  • Using Step Into to see how each step in the Macro is executed
  • Viewing and Changing Security Settings for Macros
  • Storing Macros in a Trusted Location (Excel 2007 and Excel 2010)
  • Saving Worksheets that contain Macros in Excel 2007 and Excel 2010

Watch Tutorial in High Definition

Follow this link to view this Excel Tutorial in High Definition mode on my YouTube Channel – DannyRocksExcels.

Link to this post!
Filed Under: Excel Macros, Excel Training, Excel VBA Macros, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
August 24, 2011 Leave a Comment

Excel Keyboard Shortcuts to Fill a Series

Here is the latest installment in my series of video tutorials on Keyboard Shortcuts in Excel. In this lesson, I demonstrate how to Fill a Series with Values or a Formula Down or to the Right.

The more interesting Keyboard Shortcuts that I demonstrate in this lesson are:

  • Ctrl + ‘ (apostrophe) – to copy the value from the cell directly above; to copy the formula from the cell directly above while retaining exact cell references.
  • Ctrl + Shift + ” (double quote) – to copy the value from the cell directly above; to copy the “result” of the formula from the cell directly above.

Download My Free “Chart of Popular Keyboard Shortcuts in Excel”

Watch Tutorial in High Definition

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

 

 

Excel Keyboard Shortcuts to Fill a Series or Copy Values from Cell Directly Above [ 6:12 ] Play Now | Play in Popup | Download (1357)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, Podcast Tips & Timesavers Tagged: , , ,
August 23, 2011 Leave a Comment

Excel Keyboard Shortcuts to Hide, Unhide, Insert and Delete Cells, Columns and Rows

Commands in Cells Group

Commands in Cells Group

Here is the latest installment in my series of video tutorials demonstrating how to use Keyboard Shortcuts in Excel. These Keyboard Shortcuts perform actions that you find in the Cells Group of commands on the Ribbon in either Excel 2007 or Excel 2010.

Topics Taught in Video Tutorial

Here are the topics that I teach in this tutorial. Using Keyboard Shortcuts to:

  • Select all cells in current row(s) Shift + Spacebar
  • Select all cells in current column(s) Ctrl + Spacebar
  • Open Insert Dialog Box Ctrl ++ (plus)
  • Open Delete Dialog Box Ctrl + – (minus)
  • Open short / contextual menu Shift + F10
  • Hide current row Ctrl + 9
  • Hide current column Ctrl + 0 (zero)

Download My “Chart of Popular Keyboard Shortcuts for Excel”

Click here to go to my Exclusive Membership Content page and download my charts of keyboard shortcuts. They are free to members who register.

Watch Video in High Definition

Follow this link to view this tutorial on my YouTube Channel – DannyRocksExcels

For some strange reason, I could not produce this video for my Podcast. So, the only way to view it is on YouTube.

Puchase My DVD-ROM

If you enjoy these tips. If you enjoy my style of instruction. Then, you will really benefit from purchasing and learning from my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” 

Follow this link to learn more about my DVD-ROM and to open a Secure Shopping Cart to make your purchase.

Link to this post!
Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
August 22, 2011 Leave a Comment

How to Use Excel Keyboard Shortcuts to Cut, Copy and Paste Formulas and Formatting

Keyboard Shortcuts for Cut, Copy & Paste

Keyboard Shortcuts for Cut, Copy & Paste

In addition to showing you the Keyboard Shortcuts to Cut, Copy and Paste in Excel, I demonstrate how these actions affect formulas and formatting.

Topics in this Tutorial

Here are the topics that I demonstrate in this video tutorial:

  • Select multiple cells using the keyboard
  • Use Ctrl + Enter to update all cells in the selection
  • Use Ctrl + ~ to “toggle” between showing and hiding formulas in all cells
  • Use F2 to edit in the cell – and to diagram cell references for formulas
  • Use Ctrl + C to copy the “relative cell references” in a formula
  • Use Ctrl + X to cut an Excel formula that retains the original cell references
  • Open and use the Office Clipboard to store up to 24 most recent copy and cut items
  • Use Ctrl + Z to undo multiple actions
  • Use Ctrl + A to select all cells in a contiguous range
  • Use the Delete key to delete the contents of a cell or range while retaining the formatting
  • Use the Clear All command to remove both formatting and contents in a range of cells

Download My Complete Charts of Popular Keyboard Shortcuts

Follow this link to my Excelusive Membership Content Page where you can download all of my charts and tables of Keyboard Shortcuts.

Watch Video in High Definition

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

How to Use Excel Keyboard Shortcuts to Cut, Copy & Paste Formulas & Formatting [ 11:59 ] Play Now | Play in Popup | Download (1267)
Link to this post!
Filed Under: Excel Basics, Excel Training, Format Cells in Excel, Keyboard Shortcuts, Podcast Tips & Timesavers Tagged: , , , , , , ,
August 12, 2011 3 Comments

How to Use Keyboard Shortcuts in Excel to Control Basic File Operations

Excel 2007 File Operations

Excel 2007 File Operations

If you have recently upgraded to either Excel 2007 or Excel 2010, you REALLY NEED to master the Keyboard Shortcuts that I present in this lesson!

Many Excel veterans who upgrade, tell me that they initially felt “lost” with the new user interface. Some of my clients called me in a panic because they didn’t know where to look to Print their work or to open a document. If you know how to use the Keyboard Shortcuts that I present in this lesson, you will never be at a loss. As an added bonus, these Keyboard Shortcuts work with ALL Microsoft Office programs and also for most Windows-based programs.

The “N-O-W-S” Keyboard Shortcuts

I use this acronym – NOWS – to remember the keyboard shortcuts to Open, Close and Save Documents in MS Office programs. Use Ctrl + W to Close the active worksheet.

In this video tutorial, I also show you when NOT to use Keyboard Shortcuts! There are many times when you need to access the full file menu selection.

Keyboard Shortcut for Print Preview

Use Ctrl + F2 to open up the Print Preview pane. Use the “Esc” key to Exit Print Preview.

Download My Chart of Popular Keyboard Shortcuts as a PDF

Follow this link to go to the web page where I store my Keyboard Shortcut Charts and other download-able content.

These Keyboard Shortcuts work for all versions of Excel. So far, I have found only one Keyboard Shortcut that is different when used in Excel 2007 / 2010 than in Excel 2003.

More Video Tutorials for Keyboard Shortcuts

This is the first in a series of video tutorials that I have planned. Each video will focus on the Keyboard Shortcuts you can use to perform a specific task – e.g. Formatting Cells, Inserting and Deleting, etc. My plan is to post one new video in this series per week.

Watch Video Tutorial in High Definition Mode

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

 

Use Keyboard Shortcuts for Basic File Operations in Excel [ 10:21 ] Play Now | Play in Popup | Download (1139)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , ,
August 8, 2011 Leave a Comment

How to Customize the Quick Access Toolbar in Excel 2007

Quick Access Toolbar Office 2007

QUick Access Toolbar in Office 2007

Learning how to Customize the Quick Access Toolbar (QAT) in Excel 2007 or Excel 2010 is a great way to ease the “learning curve” when you upgrade versions.

In my experience, I have found that most people do not even realize that there is a Toolbar in Excel 2007, let alone that you can move it to a different location and to customize it!

What I Cover in this Video Tutorial

Here is what you will learn from this YouTube Video Tutorial:

  • To move the QAT to Show it Below the Ribbon (recommended!)
  • To add the most popular command buttons one at a time
  • To open the Customize Quick Access Toolbar Dialog Box
  • To find commands to add – including Command Not in the Ribbon!
  • To organize the commands on your QAT
  • To create a customized QAT for “specific workbooks”  – great for special projects!
  • To quickly add individual command buttons with a right-click of the mours
  • To add entire command button groups to the QAT with a right-click of the mouse – (recommended!)

Since this video is a little longer than usual, I am making it available exclusively on my YouTube Channel.

Watch Video in High Definition Mode

Follow this Link to view this tutorial in High Definition on my YouTube Channel – DannyRocksExcels

“The 50 Best Tips for Excel 2007” DVD-ROM

Purchase my DVD-ROM, “The 50 Best Tips for Excel 2007” – on sale now at http://shop.thecompanyrocks.com for only $29.97 USD!

Link to this post!
Filed Under: Excel Training, New in Excel 2007, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , ,
August 6, 2011 Leave a Comment

Tips to Help You Remain Productive When Upgrading to Excel 2007

Beginning Level Videos for Excel

Videos at the Beginning Level - 50 Best Tips for Excel

I enjoy participating in several discussion and sharing groups on LinkedIn. In one group, the question was raised, “The Ribbon – Love it or Leave it?” Many group members wrote to say that they just cannot figure out the new user interface – The Ribbon. Others expressed frustration that they were much less productive in their work as they tried to make the transition to either Excel 2007 or Excel 2010. Some said that they simply gave up and returned to using Excel 2003.

Certainly, I felt frustrated and lost when I first began to use Excel 2007. So, let me share my best tips for quickly adjusting to and mastering the commands and tools in Excel 2007 – and Excel 2010.

Topics Covered in this Video

  • The Office Button in Excel 2007 to open, save and print Excel workbooks and to change Excel Options
  • 7 Standard Tabs on the Ribbon
  • Groups of related Command Buttons
  • Launchers to open up Dialog Boxes
  • Command Buttons with Menus
  • Showing the Quick Access Toolbar below the Ribbon
  • Customizing the Quick Access Toolbar
  • Working with the Mini Toolbar

Watch Video in High Definition on YouTube

Follow this link to watch this Excel Video Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Purchase My DVD-ROM, “The 50 Best Tips for Excel 2007”

Here is the link to learn more about my best-selling DVD-ROM. You can purchase this from my online store using a secure shopping cart. On sale now for only $29.97 USD.

 

Link to this post!
Filed Under: Excel Training, MS Excel 2007 DVD, New in Excel 2007, Tips and Time Savers, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
July 31, 2011 Leave a Comment

Use the Show Values As Formulas in Excel Pivot Tables

Show Values As

Show Values As in Pivot Table

I participate in a number of Group Discussion Boards on LinkedIn. In this video tutorial, I address one question that was recently asked:

“Is it possible to show a Percentage of Running Total formula in a Pivot Table?”

The answer is, “Yes, if you are using Excel 2010. If you are using earlier versions of Excel, you can use a work-around outside the Pivot Table.”

New in Excel 2010

A great new feature introduced in Excel 2010 is the drop-down menu for the “Show Values As’ in a Pivot Table Calculation. In earlier versions of Excel, the Show Values As option was not so obvious. And, I found that many of my clients did not know how to use it – or could not locate the drop-down menu.

Work-Around for Show % of Running Total

While the “Running Total in…” calculation has been available for quite some time, the “% of Running Total in …” formula is brand new in Excel 2010. The work-around that I demonstrate is to first, create the Show As “Running Total in …” formula. Then change the Font and background of these cells to “White.” Next, write a standard formula “outside of the Pivot Table” that references these cells. Be sure to turn off the “GetPivotData” Formula when you write this formula.

Download Extended Length Pivot Table Video Tutorials

Click on this link to learn about my new “extended length” video tutorials for Pivot Tables. They are availabe for Excel 2003, Excel 2007 and Excel 2010.

Watch Video in High Definition on YouTube

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

Master Excel Pivot Tables in 90 Minutes

Follow this link to learn about my new “Extended Length” – 90 Minutes – Video Tutorials

 

 

How to Use the Show As Values Formulas in Excel Pivot Tables [ 8:13 ] Play Now | Play in Popup | Download (1325)
Link to this post!
Filed Under: Excel Training, New in Excel 2010, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
« Previous Page
Next Page »