Top 10 Excel Tricks for Increased Efficiency

excel tricks

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.

1. Adopt some useful keyboard shortcuts

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.

2. Use the “New Window” button on the “View” tab to see different sheets from the same file in separate windows

You’ll minimize the need to click back and forth between sheets, and instead see all the data you need easily.


excel1

3. Add commonly-used features to the Quick Access Toolbar (QAT)

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.”


excel2

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).

4. Use Conditional Formatting to view duplicate cells

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).
excel3
Pro Tip: You can filter table columns by cell color. These two features together allow you to filter a table to show only duplicates.

5. Leverage Named Ranges

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.

excel4

6. Create drop-down lists with Data Validation

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.

excel5

7. Employ Tables and Structured References

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.

excel6

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.

8. Use INDEXMATCH formulas

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.

9. Use the Group feature to quickly show and hide columns or rows

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.

excel7

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.

10. Take your skills to the next level with PowerQuery and PowerPivot

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

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.