Home Archives for Excel Keyboard Shortcuts
February 10, 2012 Leave a Comment

3 Reasons to Create and Use Named Ranges in Excel Formulas

Use Named Ranges in Formulas

Use Named Ranges in Formulas

In my opinion, there are three reasons to use Named Range references in Excel Formulas:

  1. They are easier to write. Particularly, if you are referencing cells in another worksheet.
  2. They are easier to remember. Using =Sales – Cost of Goods Sold to determine Gross Profit vs. =A1 – B1
  3. They are easier to explain. Especially, if you are sending an Excel Workbook to a client or a colleague.

Creating Named Ranges in Excel

In this tutorial, I demonstrate two methods for creating a named range:

  1. Select the cells in the range and then type the name in the “Name Box” in the Upper Left Corner of the worksheet.
  2. Select both the cell with the “Name” and the adjacent cells for the range. Then use the Keyboard Shortcut Ctrl + Shit + F3 to open the Create Names from Selection Dialog Box

Remember that all Named Ranges MUST begin with a Letter or an Underscore and they CANNOT contain any Spaces!

Paste Named Ranges into Formulas

If you are using Excel 2007 or Excel 2010, you can take advantage of Formula AutoComplete to quickly and accurately include named ranges in your formulas. In ALL versions of Excel you can use the F3 Keyboard Shortcut to open the Paste Names Dialog Box and select the named range that you wish to paste into your formula.

Additional Resources for Excel

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can preview all of the resources that I offer you.

Watch Tutorial in High Definition

Follow this link to watch my Excel Video in High Definition. My YouTube Channel – DannyRocksExcels – has received over 1 million views!

Watch Video Now

Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
February 2, 2012 Leave a Comment

The Basics for Creating and Copying Formulas and Functions in Excel

Relative and Absolute Cell References

Relative and Absolute Cell References

Formulas and Functions are the “core elements” of Excel. It is vital that you have a solid grounding in understanding how formulas and functions work; especially when you need to copy and paste them into other cells.

Relative and Absolute Cell References

When you use Relative Cell References – the default setting in Excel – the Row numbers and Column letters adjust automatically when you copy and paste a formula.

There are, however, situations where you need to “freeze in place” part of an Excel Formula. For example, you need to “freeze” or use an Absolute Cell Reference to the cell with “Total Sales,” when creating and copying a formula to determine Product Sales as a Percentage of Total Sales.

Copying Excel Formulas

In this tutorial, I demonstrate two methods for copying and pasting formulas and functions:

  • Standard Practice is to select the cell with the formula and use the Ctrl + C Keyboard Shortcut to place the formula cell on the Excel clipboard. Then, after selecting the destination cell(s), use the Ctrl + V Keyboard Shortcut to paste the formula in the new location(s)
  • AutoFill Tool.If you are copying the formula cell into adjacent cells, use the AutoFill tool to do this quickly and accurately!

Tips that You May Not Know

In my experience, many Excel veterans are not familiar with these tips and tricks which I demonstrate in this tutorial:

  • The Ctrl + ~ (tilde) Keyboard Shortcut to “toggle” the Show Formulas view for the active Excel Worksheet.
  • The Alt + Enter Keyboard Shortcut to automatically use the =SUM() Function – for adjacent cells.
  • The F4 Key to automatically add Absolute Cell Reference when creating or editing a formula. For example, converts A1 to $A$1.

Learn More Excel Tips and Tricks

50 Best Tips for Excel 2007

50 Best Tips DVD-ROM

If you enjoy the tips and techniques that I demonstrate in this lesson, then you will really benefit from purchasing my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” You can learn more about the resources that I offer by visiting my secure online shopping website – http://shop.thecompanyrocks.com

Watch Tutorial in High Definition

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

Play Video Now

Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , , , , , , , ,
December 9, 2011 Leave a Comment

Excel Keyboard Shortcuts for Navigating Between Cells, Worksheets, Documents and Programs on your Computer

Excel Keyboard Shortcuts

Excel Keyboard Shortcuts

This is the latest installment in my series of Video Tutorials for Keyboard Shortcuts in Excel. This time the focus is on Keyboard Shortcuts for Navigating between:

  • Cells in a worksheet. For example, to move to the last “occupied” cell ( a cell with data) in a column or row
  • Worksheets in the active Workbook
  • Other Excel workbooks or documents that are open on your computer
  • Other programs that are open on your computer

Links to Other Videos in Keyboard Shortcuts Series

Here is the link that will take you to the other Video Tutorials that I have created for Excel Keyboard Shortcuts.

Download the Complete – FREE –  “Chart of Popular Keyboard Shortcuts for Excel.”

There are two ways for you to download – for FREE – My “Chart of Popular Keyboard Shortcuts for Excel:”

  1. Click here to download this – for FREE – at my new, secure online shopping website.
  2. Click here to download this – for FREE – from my Exclusive Membership Content page.

Watch Tutorial in High Definition on YouTube

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

YouTube Tutorial for Excel Keyboard Shortcuts

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

How to Distinguish Cell Content from Cell Formatting in Excel

Cell Content vs Cell Formatting

Cell Content vs. Cell Formatting

Many Excel Users are surprised to discover that the Delete Key does not clear the formatting for the selected cell or range of cells. This can cause some unusual “surprises” you you or one of your colleagues later uses this “Deleted” Cell Range!

On the other hand, some cells must be formatted properly in order to return the correct result. This is especially true for cells that contain Time and Date Calculations. For example, in order to properly calculate a Sum of Hours that exceeds 24 hours, you need to apply the Custom Format [h]:mm to the formula cell.

Topics Taught in this Tutorial

  • Adding the Clear Command to the Quick Access Toolbar (QAT).
  • Using Go To Special – Last Cell, to find the last cell in the worksheet that contains either content or formatting – or both!
  • Understanding how Excel Stores Dates (as a Serial Number) and Times (as a Decimal Number) vs. How you use Cell Formatting to make Dates & Times easier to understand.
  • Using Keyboard Shortcuts to add the Current Date – Ctrl + ; (semi-colon) or Current Time -Ctrl + Shift + : (colon), or to “Toggle” between Formulas and Displayed Results  -Ctrl + ~ tilde).

Visit My New, Secure Online Shopping Website

Stop by http://shop.thecompanyrocks.com to see the new products that I have created. You can also download – for FREE – my Chart of Popular Excel Keyboard Shortcuts when you visit.

Watch Tutorial in High Definition

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

YouTube Tutorial for this Post

Link to this post!
Filed Under: Excel Basics, Excel Training, Format Cells in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , ,
September 19, 2011 2 Comments

How to Create a Summary Report from an Excel Table

Functions for Executive Summary

Functions for Executive Summary

I created this Excel Video Tutorial in response to a viewer who, in her company, is not permitted to use a Pivot Table to create a report. Unfortunately, this happens more often than you might think. Nevertheless, I promised to share my tips and advice. I think that many of this tips will help Excel users at all levels.

Tips Covered in this Video Tutorial

  • Use Advanced Filter to Extract a list of unique customer names from a range with @ 4,300 records.
  • Later in the lesson, I show you how to extract this list to a different worksheet – a tip that will save you time when preparing your reports!
  • How and why to convert a normal range of data into a TABLE in Excel 2007 / 2010 or into a LIST in Excel 2003.
  • How to create “Named Ranges” for your key data cells and to use them in your Formulas and Functions.
  • How to use the SUMIF, AVERAGEIF and COUNTIF Functions in your summary report.

Keyboard Shortcuts Used in this Tutorial

  • Ctrl + T to convert to a TABLE in Excel 2007 / Excel 2010
  • Ctrl + L to convert to a LIST in EXCEL 2003
  • Ctrl + A to open up the Function Arguments Dialog Box
  • Ctrl + Shift + F3 to open the Create Names Dialog Box
  • F3 to open the Paste Names Dialog Box

I decided that I will only make this video available on YouTube – I think that you will enjoy the clarity that you get from the High-Definition / Full Screen Mode.

Watch Tutorial in High Definition on YouTube

Follow this link to view this Excel Tutorial in High Definition on my YouTubeChannel – DannyRocksExcels

Visit my new secure online shopping website

I invite you to visit http://shop.thecompanyrocks.com – my new online shopping website.

Watch Video Now

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Functions, Excel Training, Keyboard Shortcuts, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
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 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: , , , , , , ,
« Previous Page
Next Page »