21 Essential MS Excel Hotkeys When Working With Lots of Data



When you're working on a large data set in Excel, it can get pretty time consuming to navigate the numerous rows and columns of data in order to identify what's important. That's why we've put together this list of 21 hotkeys you need to know when working with large data sets based on our experience over the years in Finance, consulting and IT. Guaranteed to make your life a lot easier!



Navigation
CTRL+ARROW KEYMoves to the edge of the current data region in a worksheet.
Useful if you want to reach the last row or column of a worksheet.

SHIFT+ARROW KEYExtends the selection of cells by one cell.
Use it to select multiple cells in a small region at a time.

CTRL+SHIFT+ARROW KEYExtends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.
Use it to select a row or column of cells within a longer row/column.

CTRL+PgUpSwitches between worksheet tabs, from left-to-right.

CTRL+PgDnSwitches between worksheet tabs, from right-to-left.


Editing Sheets
ALT+I+RInserts a row above the active cell.
To add multiple rows, insert one row and then press Ctrl + Y as many times as needed.

ALT+I+CInserts a column to the left of the active cell.
To add multiple columns, insert one column and then press Ctrl + Y as many times as needed.

ALT+H+HHighlights the select cells.
Useful if you want to filter the highlighted cells later on.

ALT+H+FCChange the font color of the selected cells


Copy Paste Wizadry
CTRL+CCopies the selected cells.

CTRL+XCuts the selected cells.

CTRL+VPastes the copied/cut cells including formatting.

ALT + E + S + VPaste values only.

ALT + E + S + TPaste formats only.

One fast way to get rid of formulas is to use a ALT + E + S + V followed by an ALT + E + S + T combo.

Formatting
CTRL+SHIFT+~ Applies the General number format

CTRL+SHIFT+$ Applies the currency format to 2 d.p.

CTRL+SHIFT+% Applies the Percentage format with no decimal places


Miscellaneous
ALT+A+T Applies the filters to the top row of the selection

ALT+N+V+T Creates a pivot table based on the selected data

ALT+W+F+R Freeze the top row.
If you have many rows of data, freeze the top row so you know what the cell values are supposed to represent

ALT+W+F+C Freeze the top column.

Keep the first column fixed while scrolling through the rest of the worksheet.


These time savers really add up so get started on using them right away and you'll notice the difference immediately! Did we miss out any useful shortcuts? Let us know and we'll add it on to the list.

Title Image Credit: thomasivarssonmalmo
Next PostNewer Post Previous PostOlder Post Home

1 comment: