All of us want to work more efficiently. Fortunately, there are dozens of ways to save time when working in Excel. In this article, I’ll share 10 time-saving Excel tips that share two traits: They work with common tasks you preform all the time and they’re 10 of my favorites.

I’m using Excel 2016 on a Windows 10 64-bit system, but most of these are valid in older versions.

1: Use Table objects

Table objects treat data as a unit and consequently, take care of a lot of things automatically—reducing the amount of time you need to interact with the data. When you convert a data set to a Table, you get automatic filters and formatting. Excel knows you’re working in a Table, so pressing Tab cycles through the Table for effortless data entry. With a quick click, you can add a totaling row to the bottom of the Table. Headers are always visible, regardless of how many records you have. When you extend the Table by adding a column or a row, the Table extends formulas and formatting. Charts based on Table objects update automatically when you change the source data. You can accomplish all this manually without a Table, but a Table eliminates these tasks.

To convert a data set into a Table, click anywhere inside the data. Then, click the Insert tab and click Table in the Tables group. In the resulting dialog, specify whether your data has a header row, then click OK.

Note: Table objects aren’t available in Excel’s menu versions.

For a more detailed look at Excel’s Table objects, read 10 reasons to use Excel’s table object and How to vamp Excel Table objects for better readability and function.

2: Open several files at the same time

If you work with lots of workbooks open, you can save a bit of time by opening them simultaneously. Open Windows Explorer and use the Ctrl or Shift key to create a multi-file selection. When you’ve selected all the files you need, press Enter. That’s it! The only limitation is that your workbooks need to be in the same folder. This isn’t strictly an Excel tip; you can open all kinds of files this way.

3: Add multiple rows and columns at once

You probably know how to add a row or a column, but do you know you can use the same technique to enter multiple rows and columns? Simply select the same number of rows or columns you want to insert. For instance, to insert three columns between columns C and D, you’d select columns D, E, and F—three columns to the right of column C. Then, right-click the selection and choose Insert. (Or choose Insert Sheet Columns from the Insert dropdown in the Cells group on the Home tab.)

This trick works similarly for inserting multiple rows. For example, to insert three rows between rows 10 and 11, you’d select rows 11, 12, and 13—three rows below row 10—before right-clicking the selection.

SEE: Microsoft Excel 2016 for Beginners: Master the Essentials (TechRepublic Academy)

4: Quickly move or copy rows and columns

You can move a row or multiple rows by selecting the row(s) and then hovering over the top or bottom border until Excel displays the four-arrow cursor. At this point, you can drag and drop the selection—it couldn’t be easier. To move column(s), use the left or right borders. If you want to copy the selection instead of moving it, hold down the Ctrl key while you drag and drop.

5: Generate a unique set of values

It’s common to see values repeated in the same column of a data set. If you need a unique set of those values, you can use Excel’s Advanced Filter feature as follows:

1. Make sure the column has a header cell—it’s required. Add header text if necessary.
2. Click any cell in the column.
3. Click the Data tab.
4. Click Advanced in the Sort & Filter group.
5. In the resulting dialog, click the Copy To Another Location option.
6. Make sure the List range is correct; it doesn’t always represent your clicked column.
7. Click inside the Copy To control and enter the first cell of the copy range or click a cell in the sheet.
8. Check the Unique Records Only option (Figure A).
9. Click OK to create a unique list of values in the Copy To range (Figure B).

Figure A

Check the Unique Records Only option.

Figure B

Now you have a list of unique values.

You can use this feature to copy a unique set of records by specifying the data set (instead of a single column) in the List range. This feature works equally well with a Table. However, I have found the feature a bit unpredictable when working with a normal range you’ve converted from a Table.

SEE: Build your Excel skills with these 10 power tips (free PDF)

6: Create a custom style from an existing format

Using cell styles is a great way to alert users to specific types of values and actions. You could use a built-in style, but you might not find any of them adequate. Once you’ve manually applied the appropriate formats, you can use those formatted cells to create a custom style you can use in the future.

To illustrate, Figure C shows three input cells with a yellow fill and green font. To quickly create a custom cell style, select any of those cells. Then, click the Styles gallery’s More dropdown (circled in Figure C) and choose New Cell Style (near the bottom of the list). In the resulting dialog, enter a meaningful name, such as Input Cell, and click OK. The next time you want to format an input cell, simply click Input Cell in the Styles gallery, as shown in Figure D.

Figure C

Create a custom style using existing cell formats.

Figure D

Choose the custom style from the Styles gallery.

7: Parse values without using functions

You might think you need string functions to parse values, but you don’t. Always try the Text To Columns feature first—it will usually save you a lot of time. To demonstrate, let’s use this feature to parse the names from a few email addresses (Figure E) as follows:

1. Select the data (B4:B5).
2. Click the Data tab.
3. Click Text To Columns in the Data Tools group.
4. In the resulting dialog, click Delimited (if necessary) and click Next.
5. Check Other and enter @ in the control to the right (Figure E). If necessary, deselect any other checked options and then click Next. The @ character is the common character that identifies where you want to parse the values.
6. Check the results and reset the data type if you need to (usually this won’t be necessary). Click Finish to see the parsed results, shown in Figure F.

Figure E

Identify the delimiter.

Figure F

You quickly parsed the data into two columns.

This feature is quick and easy, but it has a few limitations. First, Excel will prompt you to replace adjacent data if those cells aren’t blank. If you don’t want to replace existing adjacent data, insert a column before you parse. In addition, this feature parses the original data. If you don’t want the source data changed, parse a copy, not the original set. You’ll also notice that the delimiter—the @ character—didn’t make it to the results. You’ll want to consider these limitations when using this feature. But when it’s adequate, it’s quick and easy!

WATCH: How to create a pivot table in Microsoft Excel (TechRepublic)

8: Make a speedy return

It’s easy to browse data and get lost. To quickly return to the active (selected) cell from anywhere in your sheet, click Ctrl+Backspace. That fast, you’re back where you started. If this shortcut doesn’t work it’s because you changed the active cell while browsing. This is handy for those times when you’re using the scroll bars to browse large sheets that extend beyond a single screen.

9: Document your named ranges

To quickly see a list of your workbook’s range names, press F3. That’s it; this dialog will display a complete list. If you’d like to document that list, simply click Paste List. Excel will paste a complete list of range names and their references at the active cell. You can then copy that list to another sheet, another workbook, or even a Word document. If you have no defined ranges, Excel won’t display the dialog.

10: Create expressions with this paste trick

You can quickly create expressions using the F3 trick above to paste range names for you. Of course, you must have defined ranges for this to work. To illustrate, let’s enter a commission’s expression as follows:

1. Select F4:F14, the range where you want to enter the new expression. in this case, we’re adding a commission value to the simple sheet (Figure G). Notice that you’re selecting several cells, not a single cell—you’ll see why soon.
2. Enter = to begin the expression and then press F3 to display the range names.
3. Select Value, as shown in Figure G, and click OK.
4. Type *.2 (Figure H) to complete the expression, then press Ctrl+Enter to enter the commission expression for the entire data set.

Figure G

Select the range name you want to include in the expression.

Figure H

Complete the expression.

Using this quick trick not only saves you a few keystrokes, but it helps when you can’t remember the exact range name. It also enables you to avoid potential typos.

Did you catch that bonus tip? If you select a range instead of a single cell and then press Ctrl+Enter instead of only Enter, Excel copies the expression from the anchor cell to the selected range. This technique is more efficient than the traditional route of entering an expression and then copying it after the fact.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also see…