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

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?

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.

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.

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 learning the Lookup & Reference functions (INDEX(), MATCH(), VLOOKUP(), etc.).

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.

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

My favorite shortcuts are;
Vlook up
Pivot table
Left
Right
Mid
Offset
Ifferror
LEN
TRIM
F2, edit
Ctrl+D, repeat
CONCATENATE /&