Software

10 timesaving Excel tricks you might not know about

Mastering these simple shortcuts will help speed your work in Excel.

hero

Image: iStockphoto.com/Nastco


Excel is powerful and offers some complex solutions, but that doesn't mean it has to be hard to use. On the contrary, Excel is packed full of small timesaving click behaviors that make tasks quick and easy. In this article, I'll show you 10 tips that simplify everyday Excel tasks. I can't promise you won't know some of these tips, but chances are, you'll learn something new!

I'm using Excel 2016 on a Windows 10 64-bit system, but most of these tips have been around for a long time. If something doesn't work for you in a previous version, it might not be available to you. Feel free to use any Excel sheet or download the demonstration .xlsx or .xls file.

SEE: Master sorting basics in Excel

1: Quick range names

Most of us rely on range names, and remembering them can be troublesome if you have a lot of them. If you can't remember the name and you can't find your documentation, just zoom out. That's right. A sheet will display range names, as shown in Figure A, when the zoom percentage is less than 40. You can use the Zoom option in the bottom-right corner or the options in the Zoom group on the View tab. It's a helpful behavior, but unfortunately, the names are difficult to read. Still, it might be just the hint you need.

Figure A

Figure A
Zoom in to see range names.

2: Quick list

The first tip shows you ranges by name and location. If you need a quick list or quick access, you can press [F3]. Doing so will display a list of the range names, but there's more. With the list available, you can insert it into an expression. To illustrate, let's enter an expression in the Commission column that returns 2% of the values in column C:

  1. Select F4 and enter =.02*. (If you select a range of cells, Excel will enter the expression in all of the cells.)
  2. Press [F3].
  3. In the Paste Name dialog, select Value (Figure B) and then click OK.
  4. Press Enter to commit the expression (Figure C).

Figure B

Figure B
Use the Paste Name dialog.

Figure C

Figure C
Quickly create and add a range name to an expression without typing the name.

3: Quick copy 1

You probably know how to copy expressions and functions from one cell to an extended range using [Ctrl]+C and [Ctrl]+V or the Clipboard options (on the Home tab). You might also know how to drag the fill handle down to do the same thing. This last trick can be a bit awkward if the paste range is large. Fortunately, there's an even quicker way.

If you have a column of values adjacent to the new expression or function, you can copy it down accordingly with a double click. To illustrate, copy the Commission expression that you entered during the last tip. To do so, simply double-click cell F4's fill handle (Figure D). Doing so will copy the expression to the rest or the rows in the data set, as shown in Figure E. Excel will stop when it reaches a blank cell.

Figure D

Figure D
Double-click the fill handle.

Figure E

Figure E
Double-clicking a cell's fill handle copies its contents.

4: Quick copy 2

While we're discussing copy tips, do you know about [Ctrl]+D? If more than one cell in a column is selected, [Ctrl]+D will copy the contents from the top cell to the remaining cells. This behavior will overwrite existing data, though—so be careful.

We can quickly illustrate this behavior by copying the first value in the Region column as follows:

  1. Select E4:E9.
  2. Press [Ctrl]+D. As you can see in Figure F, with just a few quick clicks you replaced the existing region values in E5:E9 with the value in E4 (Southeast).

Figure F

Figure F
Copy a value to selected cells.

This behavior works with rows as well as columns. Selecting B4: E9 and pressing [Ctrl]+D would copy the record in row 4 to rows 5 through 9. (Press [Ctrl]+Z to undo the paste.)

5: Quick range select

The Name box is in the top-left corner, to the left of the Formula bar. It displays the address of the selected cell or range. This control also provides one of the quickest ways to select a cell or range. To illustrate using our example sheet, enter B3:F9, as shown in Figure G, and then press Enter. Doing so will select the dataset. Similarly, you can enter a range name. If you downloaded the demonstration file, enter Commission to select that column. This behavior is helpful when you want to select a large range or when the range is offscreen.

Figure G

Figure G
Use the Name control to select a cell or range.

6: Quick column move

This tip allows you to quickly move a column. Simply select the column (or columns) by clicking its header cell. Then, hover the mouse over the selection's right or left border (not the header cell) until the four-arrow pointer appears. Then, simply drag the column or (contiguous) columns anywhere you want—left or right. You can also use this behavior with selected rows.

SEE: Create a quick and effective dashboard using Excel's PivotChart and Slicer objects

7: Quick row insert

Similar to moving columns, you can insert multiple rows (and columns) with a quick selection and click trick. First, select the number of rows (click or drag down the row header cells) you want to insert. For instance, if you want to insert three rows, select three rows. Then, click Insert in the Cells group on the Home tab to insert three rows above the selection (or to the left if you're inserting columns).

8: Quick sums

There's more than one way to enter a SUM() function. You probably know how to enter functions manually or by highlighting or typing the range. Both methods are tedious and come with the potential to introduce an error from the keyboard. Let's look at two simpler methods:

  • Select a cell and press [Alt]+=. Doing so will enter a SUM() function that evaluates the contiguous cells above. This trick works with multiple cells, contiguous and noncontiguous.
  • Select a cell and click AutoSum in the Editing group on the Home tab. This option works identically to pressing [Alt]+=.

Both are great timesavers when the range you're summing is large or extends beyond the screen.

9: Quick selection

Most users know how to select an entire sheet by clicking the Selector cell at the top-left of the sheet (the gray cell that intersects the row and column headers). But there are also shortcuts for selecting a column or row:

  • To select the current column, press [Ctrl]+[Spacebar].
  • To select the current row, press [Shift]+[Spacebar].

This behavior also works with contiguous selections.

SEE: How to use Excel's Data Table analysis tool

10: Quick dependency view

Formulas can reference literal cell address or range names, and with this quick tip, you can highlight a formula's dependent cells. Simply double-click the cell. If the cell is already selected, press [F2]. Either will display a blue line around the dependent cells, as shown in Figure H.

Figure H

Figure H
Expose dependent cells.

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

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox