Excel: The stomping ground for tracking metrics, building equations and navigating through the moving parts of a business and seeing what’s working and what’s not working. It’s a cornerstone tool for business.
Here’s a compilation of 6 easy Excel shortcuts and overlooked features to make your life easier.
From Charts To Formatting Cells Here’s Our List Of Easy Excel Shortcuts.
AutoFill, one of the most loved and under-rated Excel Shortcuts, can save you serious amounts of time during your work day. If your sheet has clear patterns, such as a series of dates or numbers, this feature can be precious to you.
Say you’re about to embark on a long day of typing hundreds of thousands of dates into your Exel sheet. You could type of series of dates like (3/31/18, 4/1/18, 4/2/18) or you could use Excel’s built-in feature, AutoFill.
To use AutoFill, begin the series and move the cursor to the lower right part of the last cell (the fill handle). When it turns into a plus sign (+), click and drag down to select all the cells that you need to fill. Exel will work its magic and fill in the rest of the pattern.
AutoFill works on both columns and rows which is a massive bonus for those of us who are anti- Carpal Tunnel and want to get home before the Harry Potter Marathon ends. What if your data doesn’t have much of a pattern? No worries. Pick a cell or cells, move to the fill handle, click, and drag. A menu of options should pull up. The more data you input, the better the Fill Series option will do creating your AutoFill options.
Multiple Cells, Same Data
If you have multiple entries that you need to write over and over again, make use of Excel Shortcuts, such as multiple cells, same data feature. Just click the entire set of cells, either by dragging your cursor or by holding Ctrl as you click each one. Type it on the last cell, then hit Ctrl+Enter — which then fills the rest of the cells with the content you typed in the first cell.
How To Save Charts As Templates
Excel makes it extremely easy to customize all graphs using their own plethora of templates. But what if none of these suit your individual business’s needs? Luckily, Excel makes it easy to save your original chart as a template. Here’s how:
Once you have your chart set up, right click it. Select “Save as Template.” Save the file with a CRTX extension in your default Microsoft Excel Templates folder. Then, when you wish to apply the template, select the data you want to chart, go to the Insert tab, click Recommended Charts, and then the All Charts tab, and the Templates folder. In the My Templates box, pick the one to apply, then click OK. There you have it; it’s that easy!
Hide In Plain Sight
Hiding rows and column is a super useful tool in Excel, but when you hide these elements, you can no longer manipulate or perform calculations. So, here’s a great Excel Shortcut to hide these elements. It’s easy to hide a row or column— select the whole thing by clicking the letter or number header, right-click, and select “Hide.”
But what if you have just a little section of inconveniently placed data you want to hide, but you still want to be able to work with? Easy. Highlight the cells, right-click, and choose Format Cells. On the Number tab at the top, go to Category and select “Custom.” Type three semicolons (;;;) in the Type: field. Click OK. Now the numbers aren’t visible, but you can still use them in formulas.
Use $ To Prevent Shift
When you write a formula, you reference cells by their position, such as A1. If you copy a formula and paste it in the next cell down, Excel will shift that referenced cell, so it would say A2 instead, which as you can imagine can cause chaos fast. To prevent this shifting, use the $. So in that case, saying $A1 would prevent a shift in the column (A); A$1 prevents the shift in the row (1), and $A$1 prevents the shift change in any direction when copying a formula.
This is particularly important when you have a single cell to use in a lot of formulas. For example, if you wish to multiply everything by 2, you could do a formula like =(A1*2), but that means you can’t change the 100 easily across the board. Put the 2 in the B1 and use =(A1*B1) — then when you cut and paste it down the $B$1 reference never changes.
Easy Excel Shortcut Keys
10 Excel Keyboard Shortcuts
|F2||Edit the Active Cell. If you motor around your Excel data using the arrow keys it can quickly become aggravating every time you need to grab the mouse and click in the formula bar to edit the cell contents. Just move to the cell you want to edit, hit F2 and you can start typing/editing the cell.|
|Ctrl-O||Opens a file|
|Alt-Enter||Force a line break inside of a cell. I'm a stickler for details and organized formatting. If you have multiple words in a cell, such as a column header like "Quarterly Sales Totals" and you have Word Wrap turn on for the cell, Excel may break the line in a place you don't like. Just go into the Cell where YOU want the line break and hit Alt-Enter.|
|Ctrl-PgDn/PgUp||Flip through the tabs in a spreadsheet. If you work in spreadsheets with multiple worksheets you can quickly navigate through the tabs for each Worksheet using ctrl-pgdn/ctrl-pgup.|
|Ctrl-Home||Moves you back to cell A1|
|Ctrl-UpArrow/DownArrow||Move to the start, end, left and right of a data table. If you have long (or wide) lists of data and you need to get to the bottom row (or top, left, or right) just click anywhere in the table and use Ctrl-UpArrow to go to the top row or Ctrl-DownArrow to go to the last row. Hint: This is very handy when you are working with un-formatted data and need to find the next blank row in a long table. Double Hint: Use SHIFT with this key combination to select the range while navigating.|
|Ctrl-;||Inserts the current date in a cell.|
|F4||Repeats the last action. Say you are deleting specific rows from a long list of data. Delete the first row then move to the next row you want to delete and hit F4. This works with just about any action, ex: formatting.|
|Ctrl-F||Find Dialog Box. I'm always searching for data in long lists, finding the search dialog is a pain, a quick Ctrl-F will pop it up. Hint: Ctrl-H will bring up search and replace.|
|Alt-F then X||Closes Excel|
If you found this article helpful, you might be interested in visiting these links:
- Print Like A Pro: 3 Printing Tips For Excel
- Autosave To Save The Day: Never Lose Work Again With This 1 Simple Trick
- 12 Cool Ways To Save Time And Boost Productivity With Microsoft’s Virtual Assistant Cortana
- Save Time By Reusing Text In Microsoft Outlook
- How To Quickly Find Deleted Emails In Outlook