Advanced Excel Tips and Tricks

Advanced Excel Tips and Tricks

Presented by Ivan L. Hemmans, III

OM13 4/3/2017 1:45 PM - 3:15 PM

The handouts and presentations attached are copyright and trademark protected and provided for individual use only.

Advanced Excel Tips & Tricks

Advanced Excel Tips & Tricks .......................................................................... 1 Accessing all the functions in Excel......................................................... 1 Removing extra spaces from cells .......................................................... 2 Paste the resulting values, not the formula ............................................. 3 Distributing data across columns ........................................................... 3 Combining data from multiple cells ........................................................ 4 Use the Lookup feature to find values in lists ........................................... 5 Set up reliable data validation ............................................................... 6 Group, filter and total large lists of data.................................................. 8 Inserting charts or graphs ................................................................... 10 Working with PivotTables .................................................................... 11 General Excel Tips.............................................................................. 17

Accessing all the functions in Excel

Excel contains a vast array of functions you can use to perform various calculations. You can use Insert Function to become familiar with the functions available in Excel and to become familiar with what each of the functions does.

Use Insert Function to insert a function you choose into an empty cell. You can search for a function by keyword or by category.

To launch the Insert Function dialog box

1. Select the empty cell where you want the function to be stored.

2. Click Formulas > Insert Function.

Figure 1

The Insert Function dialog box appears. [Figure at right]

3. In the Search for a function box, type a description of the function you want.

Note:

Alternatively, you can use the category dropdown list to see functions that all relate to a certain type of calculation like "Lookup & Reference."

Page 1

Advanced Excel Tips & Tricks

4. In the Select a function area, click to select a function.

N o t e : Excel displays a description of the selected function below the Select a function area.

5. Click OK. 6. In the Function Arguments dialog box, click in an argument box.

[Figure 2]

Figure 2

Note:

Excel displays a description for the argument that corresponds to selected box.

If you have chosen a function that requires more than one argument, you can see descriptions for all required arguments by clicking in each of the boxes.

Removing extra spaces from cells

If you have data that contains additional spaces, there is a function you can use to trim them out.

To remove extra spaces from data: 1. Select a destination cell for your formula. 2. Type =trim([cell name]), then press Enter.

Note:

According to Excel's Insert Function dialog box, Trim removes all spaces from a text string except for a single space between words.

Page 2

Advanced Excel Tips & Tricks

Paste the resulting values, not the formula

Occasionally, you may need to use a formula to change the way data appears. And, it is often handy to be able to extract the resulting value of a formula, but not the formula itself.

For example, you can use a formula to remove extra spaces from data. If you then wanted to split the resulting values across columns, you would need to extract the values because you can only split text across columns, not formulas.

To paste values

1. Select the cell(s) containing the formula(s).

2. Press CTRL+C to copy.

3. With the cell still selected, on the Home tab, click the arrow beneath Paste, and then the first icon in the Paste Values area - Paste Values.

Note:

You can paste the resulting values over the original data to "change" it, or you can select different destination cells to contain the plain text.

The steps above describe how to replace the formulas with corresponding values.

Distributing data across columns

In Excel, you can take a cell and split its text across columns in a number of ways. The easiest of these is to use the Text to Columns command on the Data tab. For example, if you have a cell that contain both first and last names, you can use the Text to Columns command to split the data in each cell across multiple columns using a delimiter, a special character that indicates at what point data should be split. In this example, we will use a space as the delimiter.

To split text in a cell across columns: 1. Select the cells you wish to affect. [Figure 3] 2. On the Data tab, in the Data Tools group, click Text to Columns. 3. In the Wizard, select Delimiter, then click Next. 4. In the Delimiters area, check Space, then click Finish. [Figure 4]

Page 3

Advanced Excel Tips & Tricks

Figure 3

Figure 4

Note:

In this example, a new Column C was inserted prior to beginning the steps to split the cell data. The result of using the Text to Columns command is that the split data is saved into cells to the right of the original cell. If any preexisting data is in danger of being overwritten Excel warns you and gives you an opportunity to cancel.

Combining data from multiple cells

As you might imagine, Excel also gives you the ability to combine text from multiple cells into a single destination cell.

For example, if you have a cell that contains a first name and another cell that contains a last name, you can combine those cells together and also include arbitrary strings of characters such as a spaces (" "), or comma space (", "), or even whole words or phrases.

To combine text from multiple cells:

1. Select the empty cell where you want the function to be stored.

2. Click the Formula button (Fx) on the Formula bar.

3. In the Category dropdown list, select Text.

4. In the Function area, select Concatenate, then click Enter. [Figure 5]

5. Select Text1 in the Concatenate dialog box, then either click a cell on the sheet or type some text.

6. Select Text2 in the Concatenate dialog box, then either click a cell on the sheet or type some text. [Figure 6]

7. Repeat steps 5 and 6 as necessary, then click OK.

Page 4

Advanced Excel Tips & Tricks

Figure 5

Figure 6

Note:

In this example, the text elements for a last name, a comma followed by a space, and a first name are combined to make the text string "Beverly, Martha."

You can use the "&" symbol to make your own concatenate string. This alternate formula would read =C2 & ", " & B2.

Use the Lookup feature to find values in lists

You can use VLOOKUP or HLOOKUP to find corresponding information in a list of data, either vertically (VLOOKUP) or horizontally (HLOOKUP).

The VLOOKUP function scans vertically down the leftmost column of data, looking for a match to the input you provide. Upon finding a match, VLOOKUP returns a value from the given row, corresponding to a column you specify.

Note:

HLOOKUP functions similarly, but it scans horizontally across the first row of data and returns a value from a given column, with respect to a corresponding row number.

For example, if your data looks like the data in Figure 7 below, then this formula, =VLOOKUP("associate",A1:B4,2), would return 135:

Figure 7

The three arguments required by VLOOKUP are Lookup Value, Table Array, and Column index. There is an optional fourth argument, Range Lookup.

Lookup Value: The data you want to find. This can be text (enclosed in quotes) or it can be a reference to another cell.

Page 5

Advanced Excel Tips & Tricks

 Table Array: A reference to a range of cells of at least 1 column of data. By default, the first column of data is used as the index to find the corresponding data for each row. Your data should be sorted in ascending order by the first column.

Column Index: The corresponding column that contains the data you want to return.

Range Lookup [optional]: A logical value, TRUE or FALSE. If this argument is omitted or TRUE the lookup returns the first closest match. If the argument is set to FALSE, lookup searches for an exact match.

Note:

If you plan to copy your formula to use across more than one cell, you may wish to use absolute cell references for the table array so the addresses do not automatically adjust to a new range as the formula is copied. In our above example, the Table Array reference would become $A$1:$B$4.

Set up reliable data validation

You can use data validation to restrict the type of information allowed in a given cell (or cells). You can also specify a range of valid data for numerical, time or date values, and even text length. In addition, you can use data validation to limit data to a predefined list of acceptable items.

Page 6

Advanced Excel Tips & Tricks

To set up data validation:

1. Select the cells you wish to affect. [Figure 8]

2. Click Data, Validation.

The Data Validation dialog box appears.

3. On the Settings tab, in the Allow box, click List.

4. In the Source box, type the values, separated by commas, you want to allow.

For example, try typing: blue, green, orange. [Figure 9]

5. Click OK.

When you click on a cell, the drop-down arrow is available with the values you specified. [Figure 10]

Figure 8 Figure 9

Note:

Figure 10

In Outlook 2007, click the Data tab, then click Data Validation.

Note:

In the Source box, you can use a formula or reference to a range of cells that contain your list values rather than explicitly typing those values in.

However, if you want to refer to a range of cells on a different worksheet in the same workbook, you need to define a named range, then refer to the named range in the Source box, =named_range.

Tip:

You can use the Input Message and Error Alert tabs to set up user-friendly feedback to explain the nature of the data to be entered and to prompt the editor of the workbook to enter correct data.

Page 7

Advanced Excel Tips & Tricks

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download