Considering the time today’s IT professionals spend staring at Microsoft Excel spreadsheets, one might think we’d all be Excel wizards by now. The truth is that while Excel has a low barrier to entry, to become a true super user takes a lot of time, energy, and dedication.
While you may think you’re an above-average user, adopting a few quick Excel tricks can exponentially increase your efficiency — and save you tons of time in the long run.
Keyboard shortcuts are the easiest way to speed up spreadsheet administration tasks. Here are a few you should add to your arsenal today:
| Shortcut | Feature |
| CTRL + Arrow Keys | Quickly move around within your data. For example, CTRL + Down Arrow selects the last cell below the current cell that has data in it before it hits a blank cell. |
| F2 | Begin editing the selected cell (just like double clicking the cell) |
| F4 | Repeat the last action you took (e.g., applying formatting or deleting a row) |
| F12 | The same as selecting Save As… from the File tab, but much quicker. |
| When Editing Formulas | |
| F4 | F4 will toggle the leading $ which makes the column and row parts of your references static (A1, $A$1, $A1, A$1) |
| F9 | Also while editing formulas, pressing F9 will evaluate the selected part of your formula. This can be useful when error-checking your formulas. |
| TAB | Autocomplete the name of the function that matches what you’ve typed. For example, if you type “=CON” then press TAB, the formula will autocomplete to “=CONCATENATE(“. |
| Alt + Enter | Insert a line break into a cell without leaving formula editing mode. |
You’ll minimize the need to click back and forth between sheets, and instead see all the data you need easily.
Taking advantage of the QAT eliminates the need to search the ribbon for your most-used features. You can even use the QAT to launch macros. Anything in the QAT is automatically given a keyboard shortcut (ALT + #).
To add a feature to the QAT, right-click the item in the ribbon and click “Add to Quick Access Toolbar.”
Pro Tip: Add the “Paste Special” function to the QAT and you’ll be able to paste values or formats with two quick keystrokes (ALT + 1, then V).
You may know you can remove duplicate rows with the Remove Duplicates feature on the Data tab, but what if you just want to see them without removing them? Try creating a new Conditional Formatting rule to highlight duplicate cells (or, alternatively, unique cells).
Pro Tip: You can filter table columns by cell color. These two features together allow you to filter a table to show only duplicates.
Named Ranges make it easier to refer to single cells or a range of cells in a formula by giving them a name. Simply select a cell or range of cells, click the “Name” box in the Formula tab and enter a name. Now, when you need to refer to the Named Range in a formula, just start typing its name and Excel will know which cell(s) you are referring to.
This will allow you to ensure that correct data is being entered into a spreadsheet, and can also alert you to incorrect data. To create a drop-down list, select the cells in which you want to add a drop-down menu, then choose Data Validation on the Data tab. Choose List and type your list items in the Source field. Combine with Named Ranges to create dynamic drop-down lists that pull from a range in your spreadsheet.
Using Tables and Structured References together makes it easy to group like data. It also makes your data more elegant and easier to read with easy formatting templates. Managing data is simpler as well, as you can insert and delete rows and columns without interfering with other data on the same sheet.
More importantly, Tables and Structured References simplify formula writing. Similar to Named Ranges, columns of data will be named automatically by virtue of inclusion in a table. Not only does this keep your data clean and easy to understand, it’s a cinch to refer to data on other sheets, as well.
To refer to a column in the table you’re in, type a [ (open square bracket). Excel will show you a list of columns and narrow it as you start typing a column name. Complete the column name and type a ] (close square bracket). To refer to a column in a different table, first type the table name (you can set this in the Table Tools > Design tab), then type a [ and continue as described above.
By replacing VLOOKUP formulas with INDEX-MATCH, you’ll have an easier-to-read formula without the many shortcomings of VLOOKUP. Click here to see a full write-up on this item alone.
You don’t always need to see all the data in your spreadsheet. Use the Group feature to quickly show and hide columns and rows you don’t always need to see. Just select the section you want to treat as a group and then select the Group function on the Data tab.
If you’d rather see the show/hide button before the data you’re hiding (I usually do), click the small Dialog Box Launcher button in the lower right corner of the Outline group (circled in the image above). You can change the setting from there.
So you already know the tricks above and more. That’s great, and there’s still plenty of powerful features you can learn!
If you’re a more advanced user, get to know the PowerQuery and PowerPivot features. We offer a two-day course specifically on PowerQuery and PowerPivot.
PowerQuery is a powerful tool that allows you to pull data into Excel from many different sources, including SQL databases, Web APIs, SharePoint lists, other Excel files, text files, and more. It is available as an add-in for Excel 2013 and earlier and has been integrated by default into the Data tab of Excel 2016.
PowerPivot allows you to use Excel more like a relational database, linking multiple datasets into a single data model through key-based relationships. This allows you to use data from multiple disparate sources without having to manually merge them into a single, overwhelming table.
Interested in more Excel tips and tricks? Check out Learning Tree’s full list of Excel courses to find the one that’s right for you. LearningTree.com/Office
