Add/Remove Filters

To show the Filter/Sort dropdown controls on your table’s header row, click inside your 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 > L

This will quickly turn the filters OFF (and display all records) and then back ON again, essentially resetting the filters.

Create a Table

To turn your basic table into an official Excel Data Table, click anywhere in the data and then press

CTRL + T

If your table has no empty rows or columns and includes a header row, pressing ENTER will transform it into your upgraded table.

With your table upgraded, you can now use all the advanced features for Data Tables, such as:

  • Adding a total row
  • Adjusting row/column banding
  • Creating summaries with a PivotTable
  • Using Slicers for more detailed filtering

If you want all the features of a Data Table without the decorative styles, click the lowest button to the right of the Table Styles group and choose Clear.

This action takes away the decorative elements from the table but keeps all its functions.

💡 To get rid of all Data Table features and go back to a basic table, click Convert to Range in the Tools section.

Quickly Jump to the End of Your Dataset

To quickly go to the last row of your data (if there are no blank rows), use the Excel shortcut keys

CTRL + ⇓ (down arrow key)

To immediately jump to the last column in your data (provided there are no blank columns), press

CTRL + ⇒ (right arrow key)

To instantly go 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.

Increase Selection to the Edge of the Dataset

If you wish to select (highlight) all cells from your current position to the last cell with content, press the Excel shortcut

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 + ⇓ ⇒

Open Format Cells Dialog Box

After selecting a cell range, if you need to apply unique formatting not available in the Ribbon, press

CTRL + 1

This action gives you control over borders, shading, number formatting, alignment, cell protection, and many other options not directly accessible from the Ribbon.

Instant AutoSum

The AutoSum function is so popular that it has its own special button in the upper-right corner of the Home Ribbon.

To create a SUM formula for a column (or a row) of data, click below the column (or to the right of the row) and press

ALT + Equal sign (=)

If you select just one cell and use the SUM function, it gives you a chance to check the selected range to make sure it’s correct.

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 + Equal sign (=)

This will create the formulas without user intervention.  It’s still a good idea to proof the results for accuracy.

Paste as Values

If you want to copy the results of formulas from a cell or group of cells to a new place, but only need the results, not the formulas, select the cells and press

CTRL + C

or COPY from the Home ribbon.

Next, select the location you wish the copied cells to reside and press

ALT + E S V

This will bring up the Paste Special dialog box; now press ENTER.

Remembering this can be tough, so try making a memory aid like “Eat Some Vitamins” or any catchy phrase to help you remember the key sequence easily.

Start a New Line of Text Inside a Cell

If you want to creating a line break inside a cell, press

ALT + ENTER

This produces the effect of an “in-cell carriage return”.

Select Data in Current Range

To select all the data in a chosen area, click within the data range and press

CTRL + A

Insert Chart on the Same Sheet

To add a default chart to the current sheet, click inside 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

F11

Apply Flash Fill

Flash Fill is a powerful Excel tool for quickly fixing and cleaning data with minor issues.

If you want to make email addresses for all sales representatives on a list, click on an empty cell next to the first sales rep’s name. Type their email address and hit ENTER.

After you press the ENTER key, immediately press the Excel shortcut

CTRL + E

Flash Fill looks for patterns in your example and repeats those patterns for the remainder of the list.

Select Entire Row

To select/highlight an entire row, press

Shift + Spacebar

Select Entire Column

To select/highlight an entire column, press

CTRL + Spacebar

Insert a New Cell/Row/Column

To add a new row or column, click where you want to insert it and press the Excel shortcut

CTRL + Plus sign (+)

This will bring up the Insert dialog box.

❗You need to use the “plus sign” on the numeric keypad. If you use the “plus sign” key from the top row of keys, you must also press the “Shift” key with the sequence (i.e., CTRL + Shift + Plus sign).

To insert a new column, click in the cell where you want the column and press

CTRL + Spacebar
CTRL + Plus sign (+)

To insert a new row, click in the cell where you want the row and press the Excel shortcuts

Shift + Spacebar
CTRL + Plus sign (+)

💡If you select several rows or columns and use the CTRL + Plus sign shortcut, you can add multiple rows or columns with just one keystroke. Give it a try; it’s very useful!

Delete a Cell/Row/Column

Using the same methods mentioned before, replace the “plus sign” with the “minus sign”.

CTRL + Minus sign (-)

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

Move a Row and Put it Between Rows

If you want to move an entire row of data and put it between two other rows, select the row you’re moving and press

Shift + Spacebar

After that, click and hold the border of the highlighted row.

❗The key is to keep the SHIFT key pressed while you drag the row to its new spot in the sheet.

Hide Columns

To hide a whole column, select a cell within the column you want to hide and press

CTRL + 0 (Zero)

Hide Rows

To hide an entire row, select a cell on the row you want to hide and press

CTRL + 9  (Nine)

Select Visible Cells

If you want to check if there are any hidden rows or columns in your sheet, press

ALT + Semicolon (;)

This action selects the cells you can see. As a result, hidden rows and columns will be marked with a visible line to show where they are.

If you don’t see any lines indicating a break, then there are no hidden rows or columns.

Add Current Date and Time

If you want to insert the current date into a cell, select the cell and press

CTRL + Semicolon (;)

If you want to insert the current time into a cell, select the cell and press

CTRL + Shift + Colon (:)

You can add a date/time stamp by selecting a cell and pressing

CTRL + Semicolon (add a space or two) CTRL + Shift + Colon

Bonus Shortcut

Select Cells with Values

If you suspect some formula cells in a file have been replaced with static values, or if you want to find all cells with typed values, press

F5 then ALT + S O X

💡Pick 3 and Practice

Learning all these shortcuts by heart takes time, so start with choosing three Excel shortcuts that you believe will save you the most time. Practice them until they feel automatic.

After mastering those, pick another three and do the same.

Soon, using these keyboard shortcuts will become second nature to you.

Download your free Excel shortcuts Cheat Sheet

Grab your free Excel Shortcut Cheat Sheet PDF 👉 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.