Jan 25 2015

The Second Most Used Excel Keyboard Shortcut Might be ‘Ctrl’ ‘Z’.

Let’s face it, using keyboard shortcuts in Excel, as well as the other Microsoft applications is one way to increase efficiency. Why reach for the mouse, move it to the ribbon of choice, and select a command, when you can simply hit ‘Ctrl’ ‘Z’? ‘Ctrl’ ‘C’, ‘Ctrl’ ‘V‘, ‘Ctrl’ ‘Y’ and ‘F4′ are some of the most used keyboard commands, and they are not used just by Lotus 123 users, but newbies alike.

Microsoft Excel Ctrl Z KeyBoard ShortCut

So what does the keyboard shortcut combination of the Ctrl key and the Z key do?  Simple, it undoes what was just done.  Now you have to admit that if we work in Excel for any period of time, we could use Ctrl Z easily ten times a day. I mean personally I could use it 20 or more times a day, and I often find myself wishing that there was an undo key for life.

 

So there you have it, Ctrl Z will allow you to erase an action just performed.  Undo the deletion of a row, the formatting of a cell, the pasting of data, etc. (Please post a comment on the ways you usually use the short cut.)

 

So what are some of the sites that list these shortcuts as well as others?  Look below and see a few of our favorites.

 

According to ShortCutWorld.Com (A wiki-style reference database for keyboard shortcuts). This is one of the most complete pages on Microsoft Excel Keyboard shortuts.  I have it as a favorite.

3. Insert and Edit Data

minusplusUndo / Redo Shortcuts
Ctrl+z Undo last action (multiple levels).
Ctrl+y Redo last action (multiple levels).

 

 

Microsoft also has some pretty amazing pages on Keyboard shortcuts in Excel. This article describes what Key Tips are and how you can use them to access the ribbon. It also lists Ctrl combination shortcut keys, function keys, and some other common shortcut keys for Microsoft Excel 2013. At The VBAX Group we not only help the world with the Microsoft applications, for free via forums, videos, etc., we also do work for Microsoft itself.  Jacob just completed a PowerPivot project for the internal Microsoft team.  In terms of the keyboard shortcut ‘Ctrl’ ‘Z’, Microsoft says:

Ctrl+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

 

But as always, the site that is defintly my favorite is Contextures by Debra. We are happy to say that we actually work with several of Debra’s clients.  Debra and Ken are both Microsoft Excel MVPs, and both are up north in Canada.  Ken is also active on VBAExpress.Com, one of the top forums on Microsoft Applications. Ken runs ExcelGuru.Ca, which is the only Excel forum in Canada that I know about.

 

Below is a short video we did on the Microsoft Excel KeyBoard ShortCut ‘Ctrl’ ‘Z’.  Take a look.

See the Excel VIDEO Hint on this topic, on YouTube.

 

We at the VBAX Group hope this post helps you in your daily Excel efforts.  If not please pst a comment or contact us directly.  If you are not already a member of VBAExpress.Com you should be. It is one of the largest forums on the Microsoft applications, not only Excel, but the rest of MS Office, in the USA.

Jan 19 2015

Favorite Excel Keyboard Shorcut Survey Results

This survey simply asked what are your five favorite keyboard shortcuts for Microsoft Excel.  So far 31 people have responded, their detailed results are below. Take the time to review them, and you will learn at least one more shortcut that you can use. Note, this is a raw data dump so you can see what is actually submitted.  We will present a clean summary when the survey closes.

Still want to take the survey, then click here.

 

First Preference
1 alt-=
2 Ctrl-C Ctrl-V
3 strg+a
4 ctr + z
5 alt-i-r
6 cntl-c (and v)
7 Control & c
8 cut
9
10 <ctrl>;
11 ctrl c
12 Alt + F11
13 ctrl-c
14 Ctrl-S
15 ctrl *
16 ctrl+c
17 ctrl c / ctrl v
18 Ctrl+D
19 Ctrl + C
20 ctrl + ;
21 Ctrl+C & Ctrl+V
22 CTRL S
23 control +
24 Cntrl+Shift+*
25 ctr + c
26 CTRL+SHIFT+L
27 Ctrl+C
28 ctrl+c
29 Crl+C/Crl+V copy/paste
30 control+c
31 Ctrl+1 (Format Cells)

 

 

Second Preference
1 alt-f11
2 Ctrl-T
3 strg+c
4 ctr + c
5 alt-i-c
6 end-cursor up   (and down, left, right)
7 control & v
8 copy
9
10 <shift><ctrl>:
11 ctrl v
12 Ctrl + C
13 ctrl-v
14 Ctrl-F6
15 ctrl home
16 ctrl+p
17 ctrl shift end
18 Ctrl+C
19 Ctrl + V
20 ctrl + c
21 F4 & F2
22 F12
23 control space
24 Cntrl+Shift+Down arrow
25 ctr + v
26 CTRL+Arrow keys
27 Ctrl+V
28 ctrl+v
29 Crl+shift+L to turn on/off filters
30 control+v
31 Alt+F11 (VBA Editor)

 

Third Preference
1 alt+shift+drag border to move/insert range
2 Alt-N V T
3 strg+v
4 ctr + v
5 F2
6 end-home (with shift as needed)
7 end & arrow
8 past
9
10 <ctrl>c
11 ctrl d
12 Ctrl + V
13 ctrl-tab
14 Ctrl-C
15
16 ctrl+f
17 ctrl home
18 Ctrl+V
19 Shift + END + Arrow Keys
20 ctrl + v
21 Ctrl+A
22 CTRL D
23 shift space
24 Cntrl+Shift+D
25 ctr + x
26 ALT+N+V+T
27 Ctrl+F
28
29 F2 to edit cell
30 control+A
31 F12 (SaveAs)

 

Fourth Preference
1 ctrl-arrows
2 Alt-W-I
3 strg+f
4 ctr + x
5 F4
6 cntl-home (with shift as needed)
7 control & h
8 print
9
10 <ctrl>v
11 ctrl :
12 F4 in absolute range cycling
13 alt ind
14 Ctrl-V
15
16 ctrl+a
17 ctrl page down / ctrl page up
18 Ctrl+Z
19 Ctrl + Z
20 ctrl + x
21 Alt+F11 & Alt+F8
22 ALT U U
23 control L
24
25
26 CTRL+-
27
28
29 Crl+1 to format
30 control+F
31 Ctrl+Shift+v (Format Paste)

 

1 Fifth Preference
2 Application.Onkey shortcuts for various macros in PERSONAL.XLSB
3 Ctrl Shft L
4 strg+1
5 shift + ctr + enter
6 alt-e-d
7 cntl-break
8 control & home
9 save
10
11 <ctrl>home
12 ctrl s
13 Ctrl + +/- to insert/delete
14 alt dp
15 Alt-F8
16
17 alt+F4
18 ctrl tab
19 Ctrl+Y
20 F2
21 ctrl + p
22 Alt+Enter while editing cell
23 CTRL F
24 control `
25
26
27 ALT+H+B+A
28
29
30 F4 to change cell references
31
Ctrl+H (Replace)

 

Open-Ended Response
1 Also: highlight range + ctrl-. ; f3; shift-space & ctrl-space & ctrl-a; alt-n-v; alt-a-s-s; ctrl-t; alt-e-s-v;
2
3
4
5
6
7
8
9 I don’t use any keyboard shortcuts.   I would love to know the results of this survey so I can learn to use the most common/popular ones.
10
11
12
13 alt ind for names (old menus), alt dp for pivot table
14 The first and second were easy. It was fun to figure out the order of the second three since I use so many keyboard shortcuts.
15
16
17
18
19 I use a lot of keyboard shortcuts as well as mouse shortcuts. These 5 are the ones I use the most.
20 I also have several keyboard shortcuts for macros I developed.
21
22 and anorher and another
23 Hard to choose, I extensively use keyboard shortcuts as they make your work in Excel so much faster.
24 In no particular order of preference.   Can’t recall any others I use regularly.
25 now I ussially use only copy-past shortcuts
26
27
28
29 There are so many…
30 all these very usefl
31 I love excel and still I feel I know little about the power of excel. Among the many applications which I used, no doubt, it is excel that excels everything. Hats off.

 

Here are a few of our favorite pages on the web on this topic.

Excel Made Easy

Vbaexpress.Com

ExcelConsultant.Net

 

 

 

 

 

 

 

 

 

Jan 19 2015

This may be the best keyboard shortcut in Excel – F4

This may be the best keyboard shortcut in Excel F4 (Repeat last action)

There are so many useful functions and hotkeys available in Microsoft Excel that many are not known nor used. That said, most intermediate to advanced users use at least one or two of these, such as ‘Ctrl‘ ‘C‘ to copy, ‘Ctrl‘ ‘V‘ to paste, ‘Ctrl‘ ‘Z‘ to undo, ‘F2‘ to edit a formula or what I use frequently, ‘F4‘ to repeat an action. It is said that the ‘F4‘ key is the most used keyboard shortcut in Excel.

Today’s Excel hint is on the ‘F4‘ hotkey. There are various ways to use it, they save keystrokes and mouse movement.  While it is incredibly useful it is important to note that the ‘F4‘ key does not repeat every action possible, and that there are also reported problems with the hotkey.  The best way to know what it will and will not do is to try it as you work on your workbook. But before you do, read what is below, look at the links that take you to some of the best resources on the web when it comes to Microsoft Excel. Then you will have an idea of what it possible.

————————————-

Examples of how to use the ‘F4” hotkey:

 

Repeat last action:

If you want to insert a row in Excel, you have several ways to do so, some use the mouse, some use the keyboard.  The way I do it is to activate the cell where I want to insert rows.  I then hit these keys:  ‘Alt’ ‘I’ ‘R’ = Insert new row in Excel. That inserts the first row.  For each additional row I then hit ‘F4’ key and it will repeat the action. If I want to insert 6 rows, I hit it 6 times. Quick, straightforward and simple.

 

Set Cell References: 

F4‘ Cycles through all 4 types of cell references (absolute, mixed reference (2x) and relative). For example, select a cell with a formula and press ‘F4‘. Notice what happens to the formula each time you it the ‘F4‘ key.  Why show this to you?  Because if you want to take the formula from one cell and place it in other cells, and still have it display the correct result, you will need to understand this, like it or not. Cell references should be your friend.

 

————————————-

 

There are also reported problems on the use of the hotkey, both Jon Peltier and Microsoft address the issue.

While the ‘F4 shortcut to “repeat last command” seems to not work in Excel 2010/2013 like it did with Excel 2003, I still use it often when I work in Excel, for example after I hit ‘Alt‘ ‘I‘ ‘R‘ (Insert Row), I often hit the ‘F4‘ key to repeat the action, and each time I hit the ‘F4′ key, it inserts another row.  However, in Excel 2010/2013 it does not seem to work anymore for charts.  To learn more about the problems, See what Jon Peltier – Microsoft Excel MVP has to say about it.

The Microsoft website says that the ‘F4‘ keyboard shortcut does not work as expected in Microsoft Excel 2013, Excel 2010, or Excel 2007. For example, when you sort a row (row A), and then you press F4 to repeat the action on another row (row B), row B is not sorted. Or, when you copy and paste a cell, and then you press F4, the cell is pasted. However, when you press F4 again, the cell is not pasted.  This is a known issue in Excel 2013, Excel 2010, and Excel 2007.  If you cannot use the F4 keyboard shortcut to repeat a command or action, manually perform the command or action that you want to repeat. See what Microsoft has to say about this.

 

————————————-

 

Good sources of information on keyboard shortcuts and Excel hotkeys.

 

 

Easy-Excel has some great examples of Microsoft Excel functions keys, such as the F4 key, click here to learn more.

 

————————————-

 

http://theexceladdict.hubpages.com/hub/F4-One-Of-Microsoft-Excels-Hidden-Gems Has great coverage on this hotkey.  You won’t want to skip this.

 

The Excel Addict says: This may be the best keyboard shortcut in Excel, according to the Excel Addict.

This is one of those rare shortcuts in Excel that is so useful, it’s amazing that it is never mentioned in basic training. As a matter of fact, I’ve rarely heard it mentioned anywhere. That’s why few Excel users ever use it.

When you want to repeat the last action that you’ve just taken, just select the range of cells where you want to repeat it an press F4.

For example, if you have just deleted a row and you want to delete another row, simply select the range where you want to delete the next row and press F4. If you have just added cell borders to a range and you format another range the same way, F4 will repeat the previous action for you. This is often most useful when you want to repeat an action over and over.

————————————-

 

Chandoo is one of the best resources on Microsoft Excel.  He presents topics in an easy to understand style.

http://chandoo.org/wp/2011/08/08/must-have-excel-keyboard-shortcuts

Chandoo says: 5. F4 – Change cell reference style while editing formulas

Change cell referencing style - F4 - Excel

Excel runs a pretty tight mafia when it comes to cell references. You must pay it a couple of $s if you want to change the reference style from relative (A2) to absolute ($A$2). But whos got the time to carefully navigate between A and 2 and then type SHIFT+4? So I use F4 key and Excel changes the referencing style for me.

————————————-

 

Have a question, either post a comment here or post your question on our Microsoft help forum, VBAExpress.Com

 

Please post your favorite uses of the ‘F4‘ key in the comments section.

 

844-VBA-Help

 

 

Jan 15 2015

Insert the current date in Excel – Keyboard Shortcut

There are so many useful keyboard shortcuts in Microsoft Excel.  Most of us have heard of ‘Ctrl‘ ‘Z‘ for undo. Perhaps ‘Ctrl‘ ‘C‘ for copy, or ‘Ctrl‘ ‘V‘ for paste.  But one of the most useful ones I personally use is ‘Ctrl‘ ‘;‘ to enter the current date.  Why type ‘01/15/2015‘ when you can hit two keys?  And unlike =Now() or =Today(), the ‘Ctrl‘ ‘;‘ will paste a value which will not change.

Not complicated, but definitely useful.  Once you learn this shortcut you will never type the current date again.

Please post your favorite keyboard shortcuts in the comments section.

 

Want to read more?  Here are three sites that have quality content on Excel keyboard shortcuts.

https://exceljet.net/keyboard-shortcuts/insert-current-date

http://www.lifehacker.com.au/2012/10/insert-todays-date-in-excel-with-a-handy-keyboard-shortcut

http://www.techrepublic.com/blog/microsoft-office/the-10-most-useful-excel-keyboard-shortcuts

 

 

Jan 7 2015

Microsoft Excel 2013 VLookup – Part One

 

This is the first of several upcoming blog posts we are writing as a followup to the original post John did on the Microsoft VLookup Function way back in 2007.

 

VLookUp Function – Familiarizing the Function

Aside from Microsoft Word, one of the most commonly used computer programs nowadays is Microsoft Excel. It is used by so many people, both on the Mac and on the PC, to input information, to perform calculations on that data as well as creating charts, reports, and Pivot Tables. In order to efficiently use the program it is essential to know the functions that it can carry out. With the so many functions available, currently over 400, one of the most important is the VLOOKUP. Many feel that this is also one of the functions that many users find difficult to understand. But in reality, once you take the time to learn it, it is a snap to use and you too will wonder how you could have got your work done without it.  The other option, learn Index and Match which is even more confusing and intimidating.   And what it is able to do for the user is so profound that it is one of the most used functions in accounting and finance.  That is why it is vital to know its basics to become familiar with it easily.

 

See the One-Hour Video on the Microsoft Excel 2013 VLookup

SNAG_Program-0008

 

 

 

 

 

 

 

 

 

 

 

 

 

The Basics – VLookup Function Definition

The VLookup function is a function that searches for the value located in the table, data range, or array. It does so by looking at the contents of the cells of the left most columns in the data set, and it returns the value that is in the same row, but a different column, in accordance to the index_number, zero or more columns to the right. To use it look at the table, data range or array that contains the data you are looking for, by first looking at the first column for the value to be identified. Once the value or the next closest value is found, based on your selection in the 4th parameter, look to the right, a specific number of columns and return any value that is found there. That is it.  Super simple.

 

Syntax for VLookup function

The syntax that is used in utilizing the function is “=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).

The bracket on the syntax means that the argument is optional. So, it is not necessary to use it, but it does need to be understood, otherwise the results may not be accurate. More will come on that later.

 

Understanding the Syntax VLookup Function

Lookup_value – this is the value you want to lookup in the data range’s first column. It can be a value, a formula or a cell reference. It is very important that the column has no repeated values. If this is so, then the VLookup will use the value that was first detected and will ignore the other(s). The values are either entered in logical values, number or text. Even if text is present, the case is not sensitive.

Table_array – the array could be a range of data. Once it consists of two or more columns of information, it is identified as range. The range or array can be determined through reference range. The headers of the information can be included once they do not obstruct the searching of lookup_value.

Col_index_num – this is the number of the column in the range that has the value desired to be returned. A great example of this is the two columns of information situated in columns E and F. Column E identified as the lookup column, has the values to search for and Column F has the value to return. Hence, Column E is determined as column 1 and hence the other hand, column F is column 2. Once the VLookup function searches the values in column E, and it finds the value at row 5, Excel returns the value in row 5 and column 2. Always keep in mind that the system of the number of column used is related to the array of data and not the location of the column in the worksheet. Once the column started at G then it is identified as the column 1 in VLookup. If ever the proper column is not entered in the syntax there is an error that will be returned.

Range_lookup – the function can search for approximate and exact match within the first column of the information being searched. Better use the term TRUE if searching for the approximate match, and the term FALSE for the exact match.

 

Things to Keep in Mind in VLookUp Function

In searching for the value of the text in the first column of the data, make sure that the information located within the first column does not contain leading spaces, inconsistent utilization of straight quotation marks, nonprinting characters and trailing spaces. With these, the function might give unexpected or incorrect value.

In looking for date or number values, make sure that the information located in the first column are not entered in text values. If it is in text values, then the function will give incorrect results.

Once the range_lookup case is FALSE and the lookup_value is text, wildcard characters should be used such as asterisk and question mark. One question mark is equals to any single character and one asterisk could match whatever character sequence.

These are the important things to know about VLookUp functions. It’s important to be familiar first with all the function of Microsoft Excel, for you to be able to use it efficiently.

 

Quality Resources when it comes to the Microsoft Excel VLookUp Function

Previous blog post on ExcelHints.Com: http://excelhints.com/2007/04/20/vlookup-in-excel

One of the Best Resources on a Forum Type Site: http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

One of the Best Sites for Excel Help: http://www.contextures.com/xlFunctions02.html

A One Hour Video on the VLookup Function, by Zack Barresse, Microsoft Excel MVP: http://www.excelandaccess.com/excel-and-access-videos.htm

Our YouTube Video on the VLookup Function:  http://youtu.be/5wqgS-_tygc

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives