August 21st, 2017 by adminWhoever said “there are no shortcuts to any place worth going,” never spent an entire day inside of an Excel worksheet. If you are a regular Microsoft Excel user, one of the following shortcuts will take you 15 seconds to learn and will save you hours of time in the long run. (Trust us – it’s worth going where these shortcuts will take you.) Here are the top 10 shortcuts that we feel every beginning Excel user should know.
1. Select to the Edge of a Data Region
- This one comes in handy when you are working with large sets of data because no matter how fast your finger can scroll…you are never going to beat this shortcut.
- To Select cells to the Right: Control + Shift + Right Arrow
- To Select cells to the Left: Control + Shift + Left Arrow
- To Select cells Above: Control + Shift + Up Arrow
- To Select cells Below: Control + Shift + Down Arrow
2. Enter the Current Date
- If you are anything like me and struggle to remember the current date, this one is particularly useful.
- To Enter Current Date: Control + :
3. Auto-Fill a Range Using the Handle
- If you are wanting to fill a large range of cells with a specific value or pattern, meet your new best friend: the handle.
- How To: Start your pattern in the first couple of cells. Select those cells and note the little green square in the bottom right corner of your selection. Hover over that square, until your mouse becomes a black plus sign (the handle). Click and drag however far down or over you would like that pattern to extend. Selected cells will automatically fill.
- Note: This is particularly useful for number sequences, dates, and even text patterns.
4. The Essential Formatting Tools
- Bold: Control + B
- Italic: Control + I
- Underline: Control + U
5. Toggle Absolute and Relative References
- If you use a lot of formulas this shortcut will save you (what feels like) years of punching in $’s.
- In your formula, click the cell you would like to make absolute and hit F4. Every time you hit F4 Excel will automatically rotate between: absolute ($A$1), row locked (A$1), column locked ($A1), and relative (A1).
6. Auto Sum
- Click below or next to the cells you want to sum and hit Alt + =
- Excel will automatically guess the range you want to sum, or you can preselect it.
7. Get to Know Your Paste Options
- After pasting your data, click on the (Ctrl) box that appears (pictured below). A Paste Options box will also appear. Remember that you can paste formulas as values, choose to keep column widths the same, and other number formatting options.
- The handiest paste option is probably the “Transpose” feature. If you ever suddenly realize that your columns should be rows and your rows should be columns “Transpose” is an easy way to fix that. You can use it by clicking the Transpose icon in the paste options box, or by using the alternate method below.
- Copy your data like normal (Control + C), select where you would like that data pasted and press Control + Alt + V. This will bring up a dialogue box. Select “Transpose” and click “OK”. This will paste all of your columns as rows and your rows as columns.
8. You have this thing called a Status Bar (and it’s customizable)
- Looking to do some quick sums? You don’t even have to remember any keystrokes for this one. Just select your data and look at the bottom right corner of your screen. (You’d be AMAZED how many people don’t know that this status bar exists.)
- To customize the status bar you can right click on top of it and select what you would like shown.
9. Add a Sequence Column
- If you are sorting or filtering anything, this tip is a NECESSITY.
- Before you start filtering/sorting, insert a column to the left of all your data. Using Step #3 insert a number sequence. That way if anything funky happens in your filtering you can easily sort back to your original data.
10. Start a New Line in the Same Cell (Soft Return)
- This can be one of the most frustrating features in Excel if you don’t know this simple shortcut.
- After typing in your first line of text hit Alt + Enter and it will take you to a new line in the SAME cell to continuing typing, instead of automatically taking you to the cell beneath it.
Added Bonus: Remove Duplicates
- This may not be a keyboard shortcut but it makes me want to cry when people don’t know about this feature in Excel. If you are working with a large list of data, and especially when trying to merge or compare two lists, the “Remove Duplicates” feature is a lifesaver.
- Select the information you would like cleaned.
- Click the “DATA” tab along your top toolbar.
- Click “Remove Duplicates.” A prompt will appear asking which column you would like it to remove duplicates from. Select which column you prefer (like phone numbers or emails).
- Click “OK”
- All exact duplicates will be automatically deleted.