Shortcut #1
Add/Remove Filters
When you wish to display the filter/sort dropdown controls on the header row of your table, click inside the data set and press
CTRL-Shift-L
This is also a VERY fast way to clear ALL your filters so you can formulate a fresh filter strategy. Simply press
CTRL-SHIFT-L-LThis will quickly turn the filters OFF (and display all records) and then back ON again, essentially resetting the filters.
Shortcut #2
Create a Table
When you wish to “upgrade” your plain table into a proper Excel Data Table, click anywhere in the data and press
CTRL-TAssuming you have no blank rows or columns, and you have a header row, you can press ENTER and you have your newly upgraded table.
You now have access to all the bells, whistles, and toys for manipulating Data Tables, like
- Adding a total row
- Adjusting row/column banding
- Summarizing with a PivotTable
- Inserting Slicers for advanced filtering
If you want all the features of a proper Data Table, but none of the art styling, press the lower of the three buttons to the right of the Table Styles group and select Clear.
This will remove all the artistry from the table while retaining all other Data Table functionality.
If you wish to remove ALL Data Table functionality and return to a plain table, click Convert to Range in the Tools group of controls.
Shortcut #3
Move to the Edge of the Data Region
To instantly move to the last row in your data (assuming you have no blank rows in the data), press
CTRL-⇓ (down arrow key)To instantly move to the last column in your data (assuming you have no blank column in the data), press
CTRL-⇒ (right arrow key)To instantly move to the first row in your data, press
CTRL-⇑ (up arrow key)To instantly move to the first column in your data, press
CTRL-⇐ (left arrow key)This technique is much, much faster than using the traditional scrollbar.
Shortcut #4
Increase Selection to the Edge of the Data Region
If you want to select (highlight) all the cells from your current cell to the last row, press
CTRL-Shift-⇓If you want to select (highlight) all the cells from your current cell to the last column, press
CTRL-⇒Assuming you are in the “first” cell of your table (“first” being the upper-left corner), you can highlight all your data by pressing
CTRL-Shift-⇓-⇒Shortcut #5
Format Cells Dialog Box
After you have selected a range of cells, and you want to apply some exotic formatting that can’t be accomplished by the Ribbon alone, press
CTRL-1
This will allow you to control borders, shades, number formatting, alignment, cell protection, and many more features not readily available on the ribbons.
Shortcut #6
Instant AutoSum
Because the AutoSum function is the most used function in the function library, it gets its own dedicated launch button in the upper-right corner of the Home ribbon.
You can create a formula using the SUM function by clicking below a column (or to the right of a row) of data and pressing
ALT-= (equals sign)If you have a single cell selected, the SUM function will give you a change to proof the selected range for accuracy.
If you have several columns (or rows) of data that require SUM operations, select the cells where you wish the SUM functions to exist and press
ALT-=
This will create the formulas without user intervention. It’s still a good idea to proof the results for accuracy.
Shortcut #7
Paste as Values
If you wish to take a cell (or array of cells) containing formulas and copy the formula results to a new location, but you only want the formula results, not the formulas themselves, select the cells and press
CTRL-Cor COPY from the Home ribbon.
Next, select the location you wish the copied cells to reside and press
ALT-E-S-VThis will bring up the Paste Special dialog box; now press ENTER.
This can be difficult to remember, so create a pneumonic like “East Some Vitamins”, or something that sticks in your brain to help you remember the key sequence.
Shortcut #8
Create In-cell Carriage Return
If you wish to “stack” text in a cell to keep from using multiple cells in a column, press
ALT-ENTERThis produces the effect of an “in-cell carriage return”.
Shortcut #9
Select Data in Current Range
To select all the data in a selected region, click in the data range and press
CTRL-A
Shortcut #10
Insert Chart on the Same Sheet
To insert a default chart on the current sheet, click in the data range and press
ALT-F1
If you wish to create a default chart on a new, dedicated “chart sheet”, click in the data range and press
F11Shortcut #11
Flash Fill
Flash Fill is one of the most impressive tools in Excel for cleaning and fixing data with simple issues.
Suppose we wish to create email addresses for all the sales representatives in this list.
Click in an empty cell on the first sales reps row and type their email address and press ENTER.
After you press the ENTER key, immediately press
CTRL-E
Flash Fill looks for patterns in your example and repeats those patterns for the remainder of the list.
Shortcut #12
Select Entire Row
To select/highlight an entire row, press
Shift-Space
Shortcut #13
Select Entire Column
To select/highlight an entire column, press
CTRL-Space
Shortcut #14
Insert a New Cell/Row/Column
To insert a new row or column, click where you wish to perform the insertion and press
CTRL-+ (plus sign)This will bring up the Insert dialog box.
NOTE: This MUST be the “plus sign” on the numeric keypad. If you use the “plus sign” key located along the top row, you will need to add the “Shift” key to the key sequence (i.e. CTRL-Shift-+).
To streamline this process, let pair this with Shortcuts #12 and #13.
To insert a new column, click in the desired cell and press
CTRL-Space
CTRL-+To insert a new row, click in the desired cell and press
Shift-Space
CTRL-+Cool Tip: If you select multiple rows or columns and execute a CTRL-+ action, you can insert multiple rows or columns in a single keystroke. Give it a try; it’s REALLY cool!!!
Shortcut #15
Delete a Cell/Row/Column
Using the same techniques noted in Shortcut #14, we will substitute the “plus sigh” with the “minus sign”.
CTRL- – (CTRL and the minus key)This will give us the ability to delete a column or row.
Don’t forget the previous tip where you select multiple rows or columns and then press CTRL- -.
Shortcut #16
Drag a Cell/Row and Paste in Between Rows
If you wish to drag an entire row of data and place it between two other existing rows of data, select the row to be moved and press
Shift-Space…to select the row; then click-and-hold the border of the highlighted row.
The trick is to hold down the SHIFT key while you drag the row to its new position in the sheet.
Shortcut #17
Hide Columns
To hide an entire column, select a cell in the desired column and press
CTRL-0 (zero)Shortcut #18
Hide Rows
To hide an entire row, select a cell on the desired row and press
CTRL-9 (nine)Shortcut #19
Select Visible Cells
If you are curious as to whether rows or columns have been hidden in your sheet, press
ALT-; (semi-colon)This will select the visible cells. The byproduct of this action is that hidden rows and columns will have a visible line identifying their position.
If there is no visible break indicator, then no rows or columns are hidden.
Shortcut #20
Add Date/Time Stamp
If you wish to add a date stamp of the current date, select a cell and press
CTRL-; (semi-colon)If you wish to add a time stamp of the current time, select a cell and press
CTRL-Shift-: (colon)You can add a date/time stamp by selecting a cell and pressing
CTRL-; (add a space or two) CTRL-Shift-:Bonus Shortcut
Select Cells with Values
If you have a file and you suspect that some of the cells containing formulas have been replaced with statically typed values, or you simply wish to identify any and all cells containing typed values, press
F5 then ALT-S-O-XPick 3 and Practice
It will take time to commit all these shortcuts to memory, so for the time being, pick three of the shortcuts that you think will save you the most time. Practice those three until they become automatic in nature.
Once you have those three mastered, pick three more and repeat the process.
Before long, you will use these shortcuts without even the slightest thought.
20 Excel Shortcuts PDF Download Link
Feel free to Download the Shortcuts PDF HERE.
Or the Excel version HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
