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.

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

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 [removed]Figure C).

Figure B

Use the Paste Name dialog.

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

Double-click the fill handle.

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

Copy a value to selected cells.

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

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.

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.

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

Expose dependent cells.

http://www.techrepublic.com/article/10-timesaving-excel-tricks-you-might-not-know-about/