# What are some of the lesser known but very useful Excel shortcuts and functions?

## Answers

CHOOSE is a fantastic function when used in concert with a set of option buttons. This combination really blows the lid off of what-if analysis and dynamic spreadsheets.

ROUND is decently popular but not used often enough to prevent 1+1=3 errors.

I use EOMONTH all the time to shortcut the process of creating a row filled with sequential months which I might need to be dynamic.

IFERROR is a recent favorite which I use to show a blank cell in case of error.

LEFT, RIGHT, LEN, and FIND are my favorite text functions to dynamically separate out account numbers or first and last names.

I often use TEXT, MIN and MAX to create dynamic report headers.

I like N to add notes to a formula so I can later tell what I meant by a certain part of the formula.

Let's not forget TODAY() and Ctrl+; as well!

That's enough for now.

Why do you ask?

I just posted in Proformative Resources my spreadsheet containing my favorite

F1 Help CTL A Highlight sheet

F2 Edit CTL B Bold

F2, F9 Paste Spec, Val CTL C Copy

F3 Paste Range CTL D Fill Down

F4 $ CTL F Find

F5 Go to Range CTL G Go to

F6 CTL H Replace

F7 Spelling CTL I Italic

F8 Step thru MACRO CTL K Insert Hyperlink

F11 Chart CTL N New Wkbk

F12 Save As CTL O Open

CTL P Print

ALT D Data B, F, GG/GU,P, S CTL R Fill Right

ALT E Edit A, D, L, M CTL S Save

ALT F File O, U, V CTL U Underlline

ALT H Help CTL V Paste

ALT I Insert R, C, M CTL W Close

ALT O Format C-A, R-E CTL X Cut

ALT R Respond to email CTL Y Redo

ALT S Send email CTL Z Undo

ALT T Tools

ALT U Page Setup CTL 1 Format Cell

ALT V View N, P CTL 8 Grp'g: show, hide

ALT W Window N CTL 9 Hide row

ALT Tab Select file CTL 0 Hide column

ALT= Sum CTL ; Date

ALT ; Select Visible Cells CTL - Delete cell, row, col

ALT+Enter Wrap text CTL + Add [copied] col before selected col(s)

ALT + F4 Close Appl CTL Space Select Column

ALT ^| Pivot Dropdown SHIFT Space Select Row

ALT 0162 ¢ CTL + % strikethrough

CTL+SHFT+! x,xxx.00

travelling CTL+SHFT+$ $

ALT Page Up/Down one screen CTL+SHFT+% %

CTL Home/End CTL+SHFT+&

END up/down arrow CTL+SHFT+- remove box

CTL+SHFT+O Select cells w Comments

SHIFT + F2 Comment CTL + ALT + TAB Indent

SHIFT + F3 Insert Function CTL + drag tab Copy wksht

SHIFT + F11 Insert New Sheet CTL + ~ Show Formulas

CTL + ' Copy formula to cell below

custom shortcuts CTL + [ Trace Precedent

CTL + M comma, no decimals CTL + ] Trace Dependent

CTL + Q center CTL + F2 Print Preview

CTL + F3 Define Name

INDEX-content (range,row,col)

INDEX-cell ref (range,row,col,area)

OFFSET cell ref (ref, row, col, ht, wdth)

OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

OFFSET($A$1,0,0,MATCH(A1),1)

Thank you for your post. Extremely good information here. Your workbook is much better laid out.

Moshe's workbook can be found here - https://www.proformative.com/resources/excel-keyboard-shortcuts

Dear Moshe,

Can you please share the spreadsheet via email?

my email address: [email protected]

Thanks for sharing.

Nodir

Good stuff. I have to highlight the powerful "Ctrl + [" and it's sister "Ctrl + ]." These obscure tools, cryptically called "Trace Precedent" and "Trace Dependent", respectively, are especially valuable if you're in a situation where there are many, many excel files that are linked across large and many networks and you're trying to find the source file and cell in a link with a very long path of folders. If you come across a formula with a link and the path is long and ponderous and you need to get to that linked source file, the obvious choice is to "File", "Open", then carefully search for each folder in the link path. This can be hugely painful when there are thousands of folders, files and multiple networks. Ctrl+[ avoids this pain as it will actually go and find the file, open it, and land on the cell that is referenced, all in one keystroke. You don't have to sit through a click process of following a path to the final file, then try to find the cell. I introduced this tool to a leading clothing retailer a couple of years ago (who was far too reliant on spreadsheets, but that's another lesson.) I think I may have saved thousands of hours of wasted time. Of course, the links need to be intact!

Use ctrl+arrows constantly. It takes you the end of a contiguous set of filled-in cells, so you can skip straight to the bottom of a 50K row data set for example, or to the right most header of a set of columns. Combine with ctrl+shift+arrow and you've selected the whole set. So if you're in the top-left cell of a huge data set, hit ctrl+shift+right-arrow / ctrl+shift+down-arrow and you just selected the whole data set in two seconds.

good for copying formulas too: If you added a formula in a new column on the right of a huge data set, and you want to copy that formula down without scrolling:

-go to right-most column that has data (the column to the left of the new column with the formula)

-ctrl+down-arrow to get to bottom

-move one cell to the right (with arrow key naturally)

-ctrl+shift+up arrow to select the new column, at the top of which is the formula you just created

-ctrl+D to fill down the formula

If you have lots of data, control-arrows make you way more efficient!

Agreed, Doug! Huge time-saver. I stay in the zone of the larger problem to be solved as I zip around.

=IF we use all the time during reconciling our carrier's records against ours. We take their data, merge it to ours and use =IF to look for matches quickly.

=CONCATENATE is awesome if you have two different columns of data but want to put them together in one column

Text to Columns will take an address for example that may be all in one column and split it out into multiple columns

I use what Doug says above all the time as well! Very helpful!

I use concatenate often too I love it.

Some of my favorite functions are if(isblank), sumifs, and countif. These really add to making my spreadsheets more dynamic.

Simpler way than using concatenate is use the "&" sign.

=CONCATENATE(A1,B1) replaced by =A1&B1

You may use the & symbol to concatenate. =a&b&.... instead of =concatenate(a,b,...)

=Index(array,match(lookup,array,type),match(lookup,array,type))

If you don't know or understand this set of three functions, it could change your life! :) It truly makes short work out of querying a table based on two different different variables. Lookup on steroids. Glad to explain further, if anyone needs help.

Jim, would you care to give an example?

Tell me more....!

Take the time to customize the Quick Access toolbar. Saves time having to search or navigate for frequently used actions. For example, my toolbar includes set print area, paste special, paste value, paste format, trace precedent & dependent, filter, freeze pane, create pivot table, insert & delete comment, switch windows, and exit Excel.

Not sure if SUMIF and VLOOKUP/HLOOKUP qualify as lesser known functions but I use those heavily.

EDATE returns a date exactly X months from another date.

Building on what Jaime says, MID, sometimes combined with using FIND or LEN, can help break up a text field like addresses.

OFFSET can be useful for things like calculating YTD numbers or creating formulas that take data in rows and using in columns.

All of the above are great. One more I find myself using more is SUMPRODUCT, which multiplies two columns or rows pairwise.

I agree, John. And SUMPRODUCT has some wacky, awesome uses when preceded by a double minus --SUMPRODUCT and such.

Jaime, Can you explain a little the other uses of sumproduct. I also used it as a reference lookup when there are more than one conditions, but SUMIFS came to the rescue. Are there any other benefit of SUMPRODUCT when used with the double minus?

I've found the shortcut that saves me the most time when editing my spreadsheets is simply by customizing the quick access toolbar. (It's the small row of mini-icons above or below the Ribbon that usually has save, undo, etc as defaults)

By customizing that toolbar you create simple shortcuts to commands that you would otherwise have to find in the Ribbon tabs, or macros you've created yourself. The keyboard shortcut is simply selecting Alt+(the number of the command you wish to select).

For Example: If you have customized your quick access toolbar to have Calc Sheet, Save, Open. To calculate sheet you would hit Alt+1, for save Alt+2, and for open Alt+3.

I've found that a lot of people are unaware of this useful function, and it's a great time saver.

Great comments from all previous posters, found some new ones I can use. I agree customizing the shortcut bar saves a lot of time.

Two simple shortcuts that I use all the time are as follows:

Ctrl+; to enter the current date

To copy a cell to all cells in that column, click on the cell to be copied, place the cursor on the lower right corner of that cell, when the cursor turns into a bold plus sign, double click.

Yes, that is called the "fill handle." If you ever want to cause actual oohs and aahs from your audience...

Don't forget about Filtering, an powerful way to slice, dice, and sort through a large table of information. It's amazingly effective when you're participating in a meeting to discuss something like a sales forecast, and everyone is looking in real-time at your spreadsheet projected on a screen (or on their monitors). To some, you will be seen as the God of Spreadsheets.

My other favorites, but already mentioned earlier, are customizing your Quick Access Toolbar and

Here are some of my favorites that I don't think have been mentioned:

1. If you select any object in Excel--a cell, a chart, a chart axis, a drawing object--then press Ctrl+1, you'll get the Properties dialog for that object. This shortcut offers a very quick and easy way to format whatever object you're working with.

2. If you use range names (which I strongly recommend) and you want to select the range that a specific name references, press either Ctrl+g or the F5 key, which launches the GoTo dialog. If the name is simple, you can click on it in a list in that dialog. But if it's at all unusual, Excel won't list it; so you'll need to type in the name. Then choose OK.

3. Suppose you want to use a range name in a formula. For example, suppose you want to sum the Sales range. Enter...

=sum(

...and then press F3. When you do so, Excel launches the Paste Name dialog. Just choose "Sales" from the list, press the dialog's OK button, then enter the SUM function's closing ")" to complete the formula.

4. Suppose you want to check the help topic for a worksheet function. For example, suppose you want to read about the MATCH function. In a cell, type...

=match(

...and then press Ctrl+a, or click the Insert Function ("fx") button to the left of the formula bar. When you do so, Excel displays the Function Arguments dialog, which might offer all the help you need. But if you still want to see the complete help topic, click the blue "Help on this function" hyperlink in the lower-left corner of the dialog. This technique works with all documented Excel functions.

Charley

INDIRECT makes it easy to set up tables which reference larger tables without a lot of referencing work or cutting and pasting; especially for dynamic spreadsheets.

"&" is a shortcut for CONCATENATE.

SUMIF uses less computing power/memory compared to VLOOKUP/HLOOKUP. (speeds up file)

Sumif, Iferror, V or H Lookups, concatenation ("&"). (combine the iferror, with lookup for fantastic data merger)

This allows you to essentially build your own custom pivot table. Paired with concatenation criteria, you can build a clean summary page that looks against massive data sets. It allows you to "idiot proof" reporting functions using excel. There are better tools out there, but for smaller companies, the investment into MS Suite is sunk cost, and a fancy ERP is just unreachable.

Just learning to customize your quick access toolbar will save you a ton of time. Learning CNTRL navigation features is HUGE.

As far as functions go, LEFT, RIGHT, INDEX, "&" (Concatenate), IFERROR (along with V and H lookups) will open up a world of possibilities for you.

If you have a lot of procedures that you do with similar data sets (more than one step, more than one time, involving large data sets) I suggest you learn a little about creating macros and models.

Some great postings by people, thus I will try to not replicate these useful suggestions ...

In terms of Excel functions:

1) Data Validation - Amazing but under-utilized tool in Excel, which can be used for a variety of things:

- Create dependent drop-down lists

- Create drop-down lists

- Protect or restrict data input of specific cells (without the need for VBA macros)

2) Conditional Formatting - Various ways this can be of value:

- Color format or cell format of cells, rows or columns based on dependent cell values or formats

3) Formula auditing - A tools to analyze & trace precedent or dependent cells, check errors and evaluate formulas.

The "Watch Window" is a feature to keep a snapshot of an area of the spreadsheet, and then move to another area of the workbook - particularly valuable if you're managing large spreadsheets or don't have a second screen.

4) Scenario Manager (under "What-if Analysis") enables users to generate high-level, summary outputs of a spreadsheets - without the need to replicate the entire workbook. It will present multiple scenarios of a spreadsheet in a succinct, high-level summary worksheet.

Regards Simon

SUMPRODUCT as part of a weighted average calculation

Camera icon to set up dashboards

Data Validation to create drop down lists so users enter specific items from a defined list.

Pivot Tables: I found a lot of excel users who think they know excel do not use pivot tables. I take a data dump from an account and quickly analyze it with a pivot table.

Grouping: by clicking on multiple spreads sheets you can enter the same information/formulas in all spread sheets.

F4: Repeats your last action. Makes copying in a filter spread sheet easy.

Great posts everyone!. I like using slices in my pivot tables. This feature is very user friendly and is excellent for quick filters within pivot tables.

Good to explore ctrl-G and then click on the Special button. In there you will find "Visible cells only" so if you highlight a range, and some rows are hidden and you don't want the hidden rows, this will do it for you. In that same dialogue there are other buttons like precedents, where it will select all the precedent cells, then you can click on a highlight color.

Regarding precedents, the formula auditing toolbar is a must. Draws lines to all precedent or dependent cells. Click multiple times and it follows the chain further upstream or downstream.

I use the Visible cells only function when I am copying to/from a filtered list. Very useful yet rarely used as it's buried a few layers.

INDEX

SUMPRODUCT

CONCATENATE

Also, the TABLE function to do two variable analysis is one of the best kept secrets.

Great thread...and reminds me to sharpen the saw in a few areas.

One or two to add....

*Text-to-columns: invaluable for taking not-quite-CSV data and converting it into something manageable.

*The "Table" function for doing what-if charts.

VALUE( ) turns text numbers into values. Useful with database downloads.

MID ( ) pull text out of the middle of a string

SUBSTITUTE ( ) look it up

FIND ( ) use to search text or pass an input to the formula.

INDIRECT ( ) look it up.

EXACT ( ) compare cell content

CHAR ( ) Call a particular keyboard key or shift key

SUMIFS ( ) quite a different structure than SUMIF ( )

LEN ( ) can be used to count to help out with numeric functions

TRIM ( ) removes empty space around text.

These few will power up your on sheet formula constructions.

If you have invisible non-breaking spaces from web data I can give you a formula to strip those hidden characters away. Email me.

Databases usually download as text into Excel, but your reporting tool downloads as numeric, so your vlookup's fail.

Use TEXT to convert numeric keys (right justified) to TEXT keys for successful vlookup.

Use VALUE to convert TEXT numeric to VALUE keys for successful vlookups.

Get away from subtotaling, and use PIVOT TABLES instead.

The only good use of the subtotal function is where you have a static format that does not change. A PIVOT can be used in much more powerful ways and relieve a lot of the tedious updates you deal with when using Subtotals.

If you are not using power pivot you should - pivot tables on steroids and self service BI for the masses

Also use a vlookup "true" and a table instead of nested sum ifs to "band" data (ie group into buckets. much easier to use. And Valerie is 100% correct pivot tables over subtotals, vookups, or sumifs.

I regularly use CTL~ (usually the top left key in the number row). It changes the screen view so I can confirm my formulas/fixed values have made their way across a whole range. I can’t tell you how many times it has saved my bacon!

Awesome way to convert text to numbers - Put a 1 in a cell you aren't using and format it the way you want (number, number of digits, etc.), then copy this cell and paste special, multiply for all cells that you want converted to numbers. Quick and easy!

A function I use from time to time with formulas is:

Press F2 (puts the cell in edit mode), then press F9 (shows the formula as a value).

If you want to convert that formula into a value, press Enter.

A recent one that I discovered is not a shortcut itself but is under the What if analysis menu, it's called "Goal Seek" and basically gets to a desired result in a formula by changing the value of one cell contained in the formula. The process is: set this cell , to this value, by changing this other cell...

I recently started using “conditional formatting” (home tab, under style group) and “subtotal” (data tab, under outline group). Very helpful tools.

Ctrl+ inserts =sum() into the cell

Ctrl~ shows the actual formula in the cell

A new tab “Pens” can be added. Using this tab we can directly write and highlight the excel sheet using the mouse (just like we can in Microsoft paint). This can be very helpful while doing excel presentations.

Split windows (view tab, under window group), can be used to view two discrete parts of the same excel sheet

Control + Y Repeat last action, my absolute favorite Excel hack and I agree with Mark Matheny, Concatenate is too cool.

I'm surprised how few people use embedded formula's. Granted you can only have 9 "if" statements, but it allows you to perform multiple functions on the data set at the same time. I strongly disagree that Pivot tables are better than subtotals; pivot tables depend on a static data set, any additions are pushed to the bottom, making changed data presented in an often illogical series. They are great for quick and dirty evaluations. After you pivot, it is difficult to perform further analysis on the data set, because the cells outside your pivot table, do not stay attached to the data, but to the original row. Subtotals can easily be redone and can include embedded subtotals, based on several different criteria, and recalculate subsequent analysis after the data has been updated. Both have their uses and limitations, it depends on what you are trying to accomplish. My rule of thumb is if my data is static: pivot, if my data is variable due to calculations, connections or datasets outside of excel, subtotals work better.

F9 - check formula results within multiple formulas....highlight the formula and select F9 to see formula result. Don't forget to undo before exiting the formula.

Very helpfull, thanks you all

In pivot tables, you can group data in colomns or rows, very helpfull if you have daily set of data that you want to present by years and/or months

To add a second line of text within a cell use ALT+enter

wow... love this one... thanks

My favorite shortcuts are;

Vlook up

Pivot table

Left

Right

Mid

Offset

Ifferror

LEN

TRIM

F2, edit

Ctrl+D, repeat

CONCATENATE /&

SUMIFS, COUNTIFS - these allow more than one condition to be entered to then produce a result (i.e. Sales in the North-West (sales region) of oranges (product).

Index(array,match(lookup,array,type),match(lookup,array,type)) (mentionned by Jim Boswell above.

This is a formula that combines the Index formula and the match formula.

The Index formula returns a value from an array (a list of rows and columns), a certain number of rows down and columns across.

=INDEX(A1:C5,3,2)

i.e.returns the value that is in the cell 3 rows down and 2 columns across in the range A1:C5.

The Match formula looksup the row or column number from an array for a value that you are looking for.

So in the long formula above the two match formulas are being used to lookup the row number and column numbers based on conditions as opposed to entering the fixed row and column numbers.

=INDEX(A1:C5,MATCH("North-West",A1:A5),MATCH("Orange",C1:C5)

Once this is mastered, then you can even produce SUM, INDEX and MATCH formulas.

However, a pivot table can achieve the same in a slightly more clumsy way.

Whilst there are MAX and MIN functions there are no conditional equivalents i.e. MAXIF. However this can be achieved using an array. Search on Google if interested.

TRIM is a useful function as it removes any spaces at the beginning of a value. This is useful if you are pulling in values from somewhere else.

EDATE can move a date on by a full calendar month

=EDATE(15/01/16,1)+ = 15/02/2016 (15th Feb 2016)

=EDATE (15/01/2016,-2) = 15/11/2015 (15th Nov 2016)

EOMONTH moves a date onto the end of the month

=EOMONTH(15/01/2016,0) = 31/01/2016 (31st Jan 2106)

=EOMONTH (15/01/2016,-2) = 30/11/2015 (30th Nov 2015)

I used to work intensively with spreadsheets earlier in my

One other point to mention is that if you are routinely looking at huge amounts of data then possibly use SQL to analyse it, or there is a free Microsoft download called Powerpivot that is essentially a pivot table that has the capability to pivot much bigger sets of data that Excel can handle.