Skip to main content

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
Labels:

Comments

Post a Comment

Popular posts from this blog

How to View YouTube Shorts in the Regular YouTube Player

YouTube's "Shorts" is YouTube’s answer to TikTok and Instagram reels but let’s be honest, the YouTube Shorts player lacks some functionality, such as the ability to fast forward and go back in the video. However, you might be surprised to know that every video that loads in the "Shorts" player is actually a regular video, and by changing the URL slightly, you can load the same video in the regular player along with all the functionality of a traditional YouTube video. Changing the URL Changing the URL is the easiest way to switch the player from a technical standpoint. It’s so easy even your grandma can do it, as long as she knows how to type an address into the address bar of a browser.   Let’s use this super popular short video about AI taking over ping pong as an example. Look in the URL bar of a video playing in the "Shorts" player, where you should see the text Shorts/. Replace that text with watch?v= and load the new URL. So in this...
Post a Comment
Read more

Create QR Codes in Excel With VBA

Need to automate the creation of QR Codes? Excel and VBA can help with that! Here's how to do it in less than 15 lines of code. What we're looking to do is have a URL in a cell and then we're going to use a function that we create in VBA call Google APIs to create the code. Here's a video that explains the whole process of creating a user defined function in VBA that will create QR Codes: Code to create QR Codes in Excel with VBA Open up your Visual Basic editor and insert the following code and then save. To call the function, type =GETQRCODES(Cell Address) where Cell Address is the address of the cell which contains a URL that you want to send your user to. Function GETQRCODES(QRCode As String)     Dim URL As String     URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & QRCode     ActiveSheet.Pictures.Insert(URL).Select     With Selection.ShapeRange(1)         .Name = "QR_CODE_" & QRCode...
Post a Comment
Read more

How To Turn Bluetooth On Windows 11

  Need to connect your Bluetooth device to a Windows 11 computer? You need to turn on Bluetooth on Windows 11 first. Here are all the ways to do so in Windows 11, including the Bluetooth Network Connections and Device Manager. 1. Quick Settings Menu Quick Settings menu usually found on the bottom right of the Windows Taskbar The easiest way to turn on Bluetooth on Windows 11 is to use the Quick Settings menu. The Quick Settings menu is the group of indicators that can be found on the bottom right of the screen, next to your date and time on the taskbar. Expanded Windows 11 Quick Settings menu that shows if Bluetooth is enabled Check whether the Bluetooth icon is colored. If it is, then Bluetooth is already turned on. If it is grey, click the Bluetooth icon to turn it on. Once you have clicked the Bluetooth icon, it will be colored, to indicate that Bluetooth has been enabled on Windows 11. By default, Bluetooth should already be listed in the Quick Settings list. If you...
Post a Comment
Read more