Excel Keyboard Shortcuts

When I first began using Excel I was married to my mouse and used it for almost everything.  I did not see the value in using the keyboard shortcuts.  But over time I have discovered how much time i can save using the keyboard shortcuts.

In the next few posts I will list many of the keyboard shortcuts.  Many you will already be familiar with, but many will be new to you.

I encourage you to try these shortcuts and see if they are more convenient and save time.

Shortcuts To Navigate Inside a Worksheet

tab / shift+tab	Moves one cell to the right or to the left in a worksheet
ctrl+arrow keys	Moves to the last cell that contains data
home	Moves to the beginning of a row in a worksheet
ctrl+home	Move to the beginning of a worksheet (A1)
ctrl+end	Move to the last cell populated with data in the worksheet.
ctrl+f	Displays the Find and Replace dialog box
ctrl+h	Displays the Find and Replace dialog box
shift+f4	Repeats the last find operation
ctrl+g (Also F5)	Displays the ‘Go To’ dialog box.
ctrl+arrow left / ctrl+arrow right	Inside a cell, this shorcut combination moves one word to the left or to the right
home /end	Inside a cell, this shortcut combination  moves to the end of a cell entry
alt+arrow down	Displays the AutoComplete list in cells that contains dropdowns or autofilter.
end	This shortcut turns the ‘End’ mode on, or if in the “End” mode, pressing the arrow keys will move to the next nonblank cell in the same column or row as the active cell.

UPDATE

Update:  I have not posted recently as I am working on a new Excel Book.  This book will discuss Formulas and Fucntions in detail, using real world and real life examples to illustrate and demonstrate the use of Formulas and Functions.  It is one thing to know a formula or function, but it is another thing to know how to use and apply the formula or function to solve real life problems in Excel.

The book begins by explaining and demonstating how to construct a formula.  Then, in great detail with lots of real life examples, the book describes and details most of the functions available in Excel.

I will have more information on the book when it is available.  In the meantime, I will post to this blog as time permits.  Sorry about any inconvenience.

David

Excel Tip #9: Finding The Last Value in a Row or Column

Many times you need to return the last value in a row or column.  This can easily be done in Excel with the following formula:

=LOOKUP(1E+100,A1:A23)

This formula assumes the data range is from A1 through A23.  But you can also use A:A if you want to search the entire A column, or even 1:1 if you want to search horizontally in row 1.   The range listed in the formula can be changed to suit your needs.  Even if you add new data to the range, the result will change in the destination cell where this formula is located.

Keep this formula handy and the next time you need to return the last value in a row or column, this simple formula will do the trick.

Enjoy!

Tip #8: Using Data Validation to Prevent Duplicate Entries

Using Data Validation to Prevent Duplicate Entries

 Have you ever needed a method in Excel to prevent duplicate entries from being entered in a row or column? Of course you have! This tip will explain in detail how to use Data Validation to PREVENT duplicate entries from being entered.   Excel has tools to identify and remove duplicate entries in data, but this method prevents them from even being entered.

First, let’s assume the range we want to prevent a duplicate entry is the range A1:A100.

1) Select the range A1:A100, starting from Cell A1. It is important to ensure the active cell is A1 by starting the selection with Cell A1 and then proceeding to the last cell in the range you want to protect.

2) Go to the Data Tab on the Ribbon, and then select Data Validation in the Data Tools group. When the dialog box opens, on the Settings Tab, select “Custom” in the “Allow:” box.

3) Then in the Formula box type the formula as show below:

 =COUNTIF($A$1:$A$100,A1)=1

You can make adjustments to these procedures if your data is in a different column or starts in a row other than row 1. For example if your data is from E4 to E200, select the range E4:E200 starting from E4. Also you would need to adjust the formula as well.

4) You can also click the “Input Message” page tab and enter a message that the user will see each time they select any cell that has this Validation applied. However, this may be more of an annoyance than a benefit.

5) Finally, click the “Error Alert” page tab and set the level according to your needs and the list below.

Stop: No user will be allowed to enter data that already exists. They will be presented with the choice to either Retry or Cancel. Duplicate data entry is entirely prevented. This option allows you to enter a message to display when a duplicate entry is attempted. 

Warning: The user will be provided a message that the entry is a duplicate and has to select either Yes, No, or Cancel. By selecting “Yes,” the user will be allowed to enter data that already exists. This option does not prevent the entry of duplicate data, but does warn the user they are about to enter duplicate data. The default button on the pop-up box is “No,” but the user can select “Yes” or “Cancel.”

Information: The user will be allowed to enter duplicates after confirming the entry by clicking “OK” on the message box which advises the user they are about to enter duplicate data. Again, this does not prevent the entry of duplicate data, but merely warns the user. The default button is the “OK” button and the user also has the choice of clicking “Cancel”.

Once you have decided which level of Alert you desire and entered an error message to display, if desired, you are done. Simply click OK and you are finished. Now you can test it by attempting to enter duplicate data in the selected range.

Now, it will be impossible to enter the same data twice in the range A1:A100, or at least without warning, depending upon the choices you made above. Note the Absolute Reference of $A$1:$A$100 and the Relative Reference of (A1) in the COUNTIF function formula. This is why step 1 requires you to begin with cell A1 when you select the range to apply the data validation, thus ensuring that Cell A1 becomes the active cell. The COUNTIF formula in the Data Validation for A2 will change automatically to:

=COUNTIF($A$1:$A$100,A2)>1

Note the $A$1:$A$100 never changes due to the use of Absolute on both the column (“A”) and the rows (1:100).

Tip #7: Absolute & Relative References in Excel

One of the basic skills an Excel user needs to know is the difference between Abosoute and Relative References.  Once a user knows the difference, they need to know when they must use absolute references and when relative references are acceptable.

Excel uses tow types of cell references, absolute and relative references. Absolute ranges have a $ (dollar sign) character before the column portion of the reference, the row portion of the reference, or both. Relative ranges do not use the $ character.  By “default,” when you type a cell reference in a formula, it is a relative cell reference, without the $ (dollar) sign. One can either manually type the dollar sign in before the row or column reference, or press F4 to make the cell reference absolute.  As you will see below, there are three types of Absolute Cell references.

The $ character tells Excel that it should not increment the column and/or row reference as you fill or copy a range with a formula or data.   The syntax used is as follows.  The cell reference “A1″ is a relative range, while $A$1 is an absolute range. If you enter =A1 in a cell and then fill that cell down the column, the ’1′ in the reference will increment in each row. Thus, the formula in the 50th row would be =A50. However, if you enter =$A$1 in a cell and fill down, the range reference will remain $A$1 — it will not increment as you fill or copy down a column.

There are three absolute styles: 

Reference Style	Meaning
$A$1	Both the column and row reference are fixed. Neither will be incremented or changed during a copy or fill operation.
$A1	Only the column reference is fixed. The column will not change during a fill or copy, only the row will change.
A$1	Only the row reference is fixed. The row will not change during a fill or copy, only the column will change.

If you select all or any part of a formula in the formula, pressing F4 will cycle the range references between the 4 types.

What happens if you insert or delete a row or column?  Does it affect your absolute references.  The answeris no.  When using an absolute reference, Excel will adjust the row and column references when you insert a row or column so the formula still points to the data just as it did before the insertion or deletion of a row or column.

On occassion, you may need to have an absolute reference that will not change under ANY circumstances, even if you insert a row or column.  In those cases, you can use the INDIRECT function. For example =INDIRECT(“A1″) will always refer to cell A1, regardless of any changes made to the worksheet.  The reason this works is because Excel does not interpret the string “A1″ as an address. Instead, it treats it as plain text and therefore does not change it.

Hope this helps understand Absolute and Relative Cell References.  It is important the Excel user have a firm grasp on how the absolute reference works and when it is necessary to use it as oppossed to just using the relative reference.

Tip #6: Count Number of Unique Items In A List

This is a neat tip that demonstrates how to count the number of unique items in a list, regardless of how many times each item is in the list.

For example, if your list contains the names: Tom, Harry, and Joe, and their names are repeated seveal times, there are three unique names in the list: Tom, Harry, and Joe. 

This is most useful when you have a long list containing many text items that are repeated.  This formula will quickly tell you how any unique items are in the list.

The formula is an array formula, so it must be confirmed with Control+Shift+Enter.  Assuming your data is in column A, copy and paste this formula in any cell outside of Column A:  =SUM(IF(FREQUENCY(IF(LEN(A:A)>0,MATCH(A:A,A:A,0),”"), IF(LEN(A:A)>0,MATCH(A:A,A:A,0),”"))>0,1))

Test it and see how cool this formula is.

Tip #5: Shading A Cell Until Data is Entered (Intermediate Tip)

 Shading a Cell Until Something is Entered

Have you ever had a spreadsheet that you wanted to highlight or shade one or more cells to show the user which cells require their input?  Doing so makes it easy for users to know exactly where they need to enter their information.  However, once they enter the information, the cell is still shaded.

When creating a worksheet in which a user will enter  information into specific cells, it can be helpful to shade the cells if they are blank, but have the shading removed once something is entered into the cell.  This can easily be accomplished by using Excel’s the conditional formatting feature.  

With conditional formatting you can format an input cell so that it shows up in a desired color until someone enters data in it. This not only makes it easy for the user to identify where they need to enter the information, but removes the shading or highlighting  once their data is entered in the cell.  

Follow these steps to accomplish this task:

1. Select the cells to which the conditional formatting should apply.
2. Display the Home tab of the ribbon.
3. Click the Conditional Formatting tool in the Styles group. Excel displays a list of conditional formatting options.
4. Choose New Rule. Excel displays the New Formatting Rule dialog box.  
5. In the list of rule types, select Use a Formula to Determine which Cells to Format. 
6. In the formula area, enter the following formula, replacing A1 with the address of the active cell selected in step 1:
7.  =ISBLANK(A1)
8. Click Format to display the Format Cells dialog box.
9. Click the Fill tab. 
10. Select the color you want used for shading the cell if it is blank.
11. Click OK to dismiss the Format Cells dialog box. The shading color you selected in step 9 should now appear in the preview area for the rule.
12. Click OK.