more-arw search

Q&A Forum

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

excel shortcuts


Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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?

Topic Expert
Moshe Kravitz
Title: Director of Finance
Company: IDT Telecom
LinkedIn Profile
(Director of Finance, IDT Telecom) |

I just posted in Proformative Resources my spreadsheet containing my favorite Excel keyboard shortcuts. It's copied right below, but some of the formatting seems to be lost.

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+% %
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)

Aaron Reibin, CPA, CMA
Title: Financial Analyst
Company: BCLC
(Financial Analyst, BCLC) |

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

Moshe's workbook can be found here -

Nodir Khashimov
Title: Assistant Financial Controller
Company: Radisson Blu Residence, Dubai Marina
(Assistant Financial Controller, Radisson Blu Residence, Dubai Marina) |

Dear Moshe,
Can you please share the spreadsheet via email?
my email address: hnodiratyahoo [dot] com
Thanks for sharing.

Patrick J. Garvey
Title: President
Company: Emerald Strategic Advisors, Inc.
(President, Emerald Strategic Advisors, Inc.) |

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!

Topic Expert
Doug Thompson
Title: Director of Revenue
Company: Castlight Health
(Director of Revenue, Castlight Health) |

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!

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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

Topic Expert
Christie Jahn
Title: CFO
Company: Prime Investments & Development
(CFO, Prime Investments & Development) |

=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!

Rommel Hernandez, MBA
Title: CFO
Company: Musik trends
LinkedIn Profile
(CFO, Musik trends) |

I use concatenate often too I love it.

Rommel Hernandez, MBA
Title: CFO
Company: Musik trends
LinkedIn Profile
(CFO, Musik trends) |

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

Larry Conrad
Title: Accounting Manager
Company: State Auto Insurance
(Accounting Manager, State Auto Insurance) |

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

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

Widalys Garces
Title: Senior Financial Analyst
Company: Oriental
(Senior Financial Analyst, Oriental) |

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

Jim Boswell
Title: Dir. FP&A
Company: Marco's Franchising, LLC
LinkedIn Profile
(Dir. FP&A, Marco's Franchising, LLC) |


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.

Wayne Lumsden
Title: Senior Accountant
Company: Sharp USA
(Senior Accountant, Sharp USA) |

Jim, would you care to give an example?

(project accountant) |

Tell me more....!

Bob Low
Title: Principal
Company: Perron & Low
(Principal, Perron & Low) |

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.

John Argo
Title: Consultant
Company: Independent Advisory Services
(Consultant, Independent Advisory Services) |

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

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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

Carolyn Santiago
Title: Corporate Finance Manager
Company: Oriental Bank
(Corporate Finance Manager, Oriental Bank) |

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?

Laura Lehman-Hawes
Title: Sr. Analyst
Company: Littler
(Sr. Analyst, Littler) |

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.

Stephen Glenn
Title: Controller
Company: Pierre Frey, Inc.
(Controller, Pierre Frey, Inc.) |

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.

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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

Randall Bolten
Title: CEO
Company: Lucidity
LinkedIn Profile
(CEO, Lucidity) |

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.).

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

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...
...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...
...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.


Michael Filiatrault
Title: Associate Director, Financial Planning a..
Company: Merck
(Associate Director, Financial Planning and Analysis - US Market, Merck) |

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)

Chris Holtzer
Title: Senior Manager - Strategic Analysis
Company: Sargento
(Senior Manager - Strategic Analysis, Sargento) |

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.

Chris Grimm
Title: Accounting Informations Systems Manager
Company: Mortenson Family Dental
LinkedIn Profile
(Accounting Informations Systems Manager, Mortenson Family Dental) |

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.

Simon Selkrig
Title: Director
Company: Strategize Financial Modelling Inc
LinkedIn Profile
(Director, Strategize Financial Modelling Inc) |

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

Topic Expert
Henry Schumann
Title: Manager FP&A
Company: Allscripts
(Manager FP&A, Allscripts) |

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.

Ron Lathouwers
Title: CFO
Company: WEM Corp
(CFO, WEM Corp) |

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.

Willio Elmore
Title: Benefits Admin
Company: Conversion Partners
(Benefits Admin, Conversion Partners) |

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.

Topic Expert
Doug Thompson
Title: Director of Revenue
Company: Castlight Health
(Director of Revenue, Castlight Health) |

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.

Susan Hollen
Title: Senior Business Analyst
Company: OpenLink
(Senior Business Analyst, OpenLink) |

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.

Mark Matheny
Title: VP - FInancial Planning and Analysis
Company: Novolex (formerly Hilex Poly)
(VP - FInancial Planning and Analysis, Novolex (formerly Hilex Poly)) |


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

Topic Expert
Keith Perry
Title: Director of Global Accounting
Company: Agrinos, Inc.
(Director of Global Accounting, Agrinos, Inc.) |

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.

(Agent, JKS Solutions, Inc.) |

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.

Greg Anderson
Title: CFO
Company: iGPS
LinkedIn Profile
(CFO, iGPS) |

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

Greg Anderson
Title: CFO
Company: iGPS
LinkedIn Profile
(CFO, iGPS) |

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.

Topic Expert
Mike Caruana
Title: Director of Financial Services
Company: Diamond Resorts International
(Director of Financial Services, Diamond Resorts International) |

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!

Elisa Moore
Title: Regional Controller/SEC Reporting
Company: Nexstar Broadcasting
(Regional Controller/SEC Reporting, Nexstar Broadcasting) |

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!

James Brandon
Title: Chief Financial Officer
Company: Hytrol Conveyor Company, Inc.
(Chief Financial Officer, Hytrol Conveyor Company, Inc.) |

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.

Necker Javier
Title: Mexico Controller
Company: Reiter Affiliated Companies
(Mexico Controller, Reiter Affiliated Companies) |

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...

Syed Bukhari
Title: Financial Analyst
Company: None
(Financial Analyst, None) |

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

Sharon Desser
Title: Director of Finance
Company: sharondesser
(Director of Finance, sharondesser) |

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

Linda StClaire
Title: VP Finance
Company: Extron Inc.
(VP Finance, Extron Inc.) |

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.

Denis J Collins
Title: Branch Controller
Company: Simpson Strong-Tie
LinkedIn Profile
(Branch Controller, Simpson Strong-Tie) |

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.

Marie-Andree Giroux FCPA, FCMA
Title: CFO
Company: AFI Expertise
(CFO, AFI Expertise) |

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

marcus Abernethie
Title: Lean Consultant
Company: Archmill House Inc.
(Lean Consultant, Archmill House Inc.) |

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

(Financial Analyst) |

wow... love this one... thanks

Yirga Alebachew
Title: Financial Analyst
Company: ethio telecom
(Financial Analyst, ethio telecom) |

My favorite shortcuts are;
Vlook up
Pivot table
F2, edit
Ctrl+D, repeat


Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email [email protected] to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.

Excel @ Excel - 30 Courses Available