The Best Shortcuts You Need to Know & Best Hidden Tricks in Microsoft Excel
Everyone uses Excel in some form or fashion, so check out our top 40 list of the most useful excel shortcuts to speed up your work!
Almost everyone uses Excel in some form or fashion in the modern workforce, but many of us may be wasting a little too much time trying to format those pesky spreadsheets. In order to help you work a little bit faster, or maybe more efficiently, we have assembled 40 of the best Excel shortcuts that you need to know!
F2 : Edit selected cell
By pressing F2, Excel will enter cell edit mode with the cursor at the end of the cell. A hand shortcut for making quick changes.
F4 : Repeat last edit
Pressing F4 will repeat your last action or edit. If you want to repaste something again or repeat a formula insert, this key will save time.
CTRL + ALT + F9 : Calculates all worksheets
Using this Excel shortcut force calculates all worksheets that have open workbooks.
F11 : New chart
Using F11 is a handy and quick way to create a new chart without having to navigate through Excel’s native toolbars. When you have data selected and press F11, a default chart will automatically appear with the selected data inside.
ALT : Access the ribbon for formulas
Pressing the ALT key makes small letters appear on the toolbar ribbons. These are called KeyTips which will help you navigate the ribbons using only your keyboard.
ALT + = : Automatically SUM() selected
If you have a block of data and you want to add all of the columns and the rows, using this command will Autosum the data and put all of the equations in one swoop. Make sure you include a spare column on the right and a spare row on the bottom for the summation data.
ALT + Enter : Start a new line in the same cell
This shortcut is handy for those of us who need to put a lot of data into one cell. It essentially functions as a line break inside of a cell. If you need to write a large paragraph inside a worksheet, knowing this function is essential.
ALT + H + O + I : Autosize columns
This shortcut functions as more of a sequence. First press Alt + H to naviate to the home tab. Then type O to select the format menu (while still holding Alt). Then type I to autofit the column width.
PG + UP : Go to next worksheet
This command switches worksheet tabs to the right.
PG + DOWN : Go to previous worksheet
This command switches worksheet tabs to the left.
CTRL + ` : Display Formulas
By using this Excel shortcut you can quickly switch between displaying a cell’s formula and its values in a snap.
CTRL + Backspace : Show active cell
If you have absolutely no idea where the active cell your working on is because you scrolled so far, this one is for you. Pressing CTRL + Backspace will quickly navigate your window right back to the active cell.
CTRL + Shift + # : Change Date format with day, month and year
This shortcut quickly gives you access to the default date format.
CTRL + K : To insert Hyperlink
First, you need to select where you want a hyperlink to appear. Then press these keystrokes and the insert hyperlink box appears, making the process that much easier.
CTRL+ Shift + $ : Applies the currency format to the selected cells
If you quickly want to change a cell to the currency format, you can select it and hit this shortcut. It will apply to multiple cells as well if you have them selected.
CTRL + Shift + & : Applies border to cells
Instead of navigating through endless menus, use this Excel shortcut to quickly add borders to the cells you have selected. Borders are generally a good idea to use if you want to organize your worksheet and make it more professional.
CTRL + B : Bold
Use this to quickly bold or unbold a cell or text.
CTRL + I : Italics
Use this to quickly italicize or un-italicize a cell or text.
CTRL + U : Underline
Use this to quickly underline or un-underline a cell or text.
CTRL + Shift + ~ : General style number
This command styles your cell to a general style number.
CTRL + Shift + % : Percentage style Number
This command styles your cell to a percentage style number. Make sure your original number has the right number of decimals. If you want 20 percent, you will need to have .2 in the cell before formatting.
CTRL + Shift + ^ : Scientific notation style
Working with a big number? Use this shortcut to quickly convert to scientific notation. This useful for all us engineers out there.
CTRL + Shift + @ : Time Style
This shortcut will give you quick access to the time of day style and allow you to add it into a cell.
CTRL + Shift + ! : Number Style
Using this will give you access to a cells number style.
CTRL + F12 : Open
Forgot that the data you needed was in another workbook? Hit this shortcut and quickly navigate to opening another file.
CTRL + Spacebar : Select the entire column
Instead of using your mouse to click on the header, use this shortcut to automatically select the entire column that you are working in.
CTRL + [ : Select all cells directly referenced by formulas in the selection
This shortcut is especially useful in excel worksheets that have seemingly endless formulas. If you quickly want to see what cells are referenced in a nother cell, use this Excel shortcut to see them. Of all of the shortcuts, this one I use the most as it keeps me from having to look through a formula and see what is referenced where.
CTRL + ; : Insert actual date in active cell
Can’t get a date? Hit this shortcut to get one quickly. (Only works in Excel, not real life)
CTRL + : : Insert actual time in active cell
Need to remember right when you input some data into your spreadsheet? Using this function will place the exact current time in a cell of your choosing.
CTRL + A : Select All
If you are frustrated with your worksheet and just and to start over, use this shortcut to select all and delete. When the frustration abides and you want all your work back, just hit CTRL + Z and your job will be saved.
CTRL + Shift + J : List Constants
Hitting this will list your properties/methods/constants within a worksheet.
CTRL + D : Copy equation down
This command will make an exact copy of the formulas in the cell above it. Useful for repeating cells or if you want a new cell with a slightly altered formula quickly.
CTRL + F : Find
Forgot where you put that crucial piece of data, use this shortcut to quickly find it.
CTRL + H : Find&Replace
If you misspelled a word or just want to replace something with a new something, the find and replace function is the best way to go about it.
CTRL + Right arrow : Move right
Essentially, this pages right in your Excel worksheet.
CTRL + Left arrow : Move left
This does the same thing as the page right above, except it pages left.
CTRL + Up arrow : Move up
Use this function to page up in a worksheet.
CTRL + Down arrow : Move down
Use this function to page down in a worksheet.
CTRL + Enter : Same data in multiple cells
If you need to enter the same data into various cells, this function allows you to do it incredibly quickly.
Shift + Spacebar : Select the entire row
If you are working in a cell and realize you need to select the entire row that cell is in, hitting this Excel shortcut will automatically select the row. You can then delete the entire row or make whatever changes you think are necessary.
Shift + F3 : Find Previous
If something in your data is starting to look a little repetitive, you can use this shortcut to make sure you aren’t just going crazy. Find Previous is especially useful for finding patterns in a spreadsheet or seeing when something occurred before.
Most of us are probably familiar with Excel, but not knowing the shortcuts and tricks can turn any simple spreadsheet into a long drawn-out process. We have put together some of the best hidden tricks in Excel that you may not have known about.
Best Hidden Tricks in Microsoft Excel
New Line in a Single Cell
When typing in cells, sometimes your text may need to run over to the next line. If you have ever tried to do this, when you press enter, Excel reads this as you moving on to the next cell and it inserts the following text there. However, you may not have known that you can press ALT+ENTER, and you can add a new line of text to the same cell.
Most of us have probably run into the issue of needing the sum of two different cells, but not wanting to have to go through the trouble of writing a whole new formula in another cell for it. Well, you no longer have to go through this trouble. To add two numbers, click the first cell, then hold down CTRL while clicking the next cell. The sum of these two numbers will automatically appear in the status bar down below. You can also right click and find other functions like average (AVG) and Minimum (MIN).
If you are working with a large spreadsheet of data, it can often be hard to interpret if you don’t have heading rows displaying key information. Instead of scrolling back up to the top or side each time you need information, you can use the freeze panes function to lock rows and columns. This is located in VIEW–>WINDOW–>FREEZE PANES, and from here you can select the row or columns you would like to freeze. This only works on the first rows or columns of an Excel sheet, so make sure you have the necessary data there.
Make Formulas Easier to Understand
If your spreadsheet incorporates a lot of formulas, or even just a few complex ones, seeing things like =A6*F9*(H3/S6), can get pretty confusing. To clear things up, you can assign names like ‘Reference Number’ or ‘Coefficient’ to cells or groups of cells, and use these names in formulas. To do this, select the cells you want to name and go to FORMULAS–>DEFINE NAME. Once you do this, you can input the name you want and click okay, but don’t use any numbers. If you named a cell ‘Reference Number’, to use this in a formula simply type =Reference Number*A2 or other variations. Naming cells also allows you to find them really fast in the ribbon.
Double Click Tricks
Excel has a lot of built in features that are operated through double clicking a certain area. If you double click on a column separator up top, it will autosize the column to fit the data. You can snap to the last cell in a column or row by double clicking on the cell border of the first cells in that row or column. The selection will automatically jump to the last cell in the series. Lastly, if you have a series of data that automatically increases or decreases with a pattern, you don’t have to manually input each value. Simply type out 2 cells and then select them, and double click in the bottom corner when the plus icon appears. This will autofill the data to every box that has data next to it