Excel Workshop, Susanna Dorr, 7/29/09, page 1
Workshop: Excel 2007 Tips and Tricks
Basic Tasks You May Not Know – For Reference .................................................... 2
Insert a blank column between two columns
Create a new worksheet in a workbook ("workbook" = Excel file)
[Re-] name a worksheet
Move or copy a worksheet into a different workbook (Excel file)
Sorting Data ............................................................................................................ 3
Filtering Data ........................................................................................................ 4-5
Splitting Column Data – Example 1 (Splitting Names) ............................................ 6
Splitting Column Data – Example 2 (Class Section Codes) ..................................... 7
Combining Column Data – “Concatenating” .......................................................... 8
Upper/Lower Case .................................................................................................. 9
Replacing Formulas in Cells with the Formula Results ......................................... 10
VLookups – Combining Data from Separate Worksheets ............................... 11-13
Consolidating Data: Eliminating Duplicate Rows .................................................. 14
Excel Workshop, Susanna Dorr, 7/29/09, page 2
Basic Tasks You May Not Know – For Reference
1. Insert a blank column between two columns
Right-click on the letter at the head of the column and choose Insert from the
menu that appears. This will insert a blank column to the left of the column clicked
2. Create a new worksheet in a workbook
Left-click on the new-worksheet tab at the bottom.
3. [Re-]Name a worksheet
Right-click on the tab at the bottom of the workbook and choose Rename from the menu that appears. Type in the
new name, then click somewhere else.
4. Move or copy a worksheet into a different workbook
Right-click the tab at the bottom of the worksheet you want to copy (e.g. “CRSELIST in the example).
Choose Move or Copy… from the menu that appears.
A Move or Copy window opens, letting you choose an existing open workbook or a new one, and letting you
choose wherein that other workbook you want the page to be inserted.
To copy the worksheet (instead of move it), be sure to click the Create a copy box.
Excel Workshop, Susanna Dorr, 7/29/09, page 3
You can put a worksheet in alphabetical or numerical order, according to the data in one column or many columns. An
example of a many-column sort might be a name-address list, where you want the data first sorted by city, then by last
name, then by first name, so that all the Elko residents are together with their names in order, followed by all the Ely
residents, and so on. This is the example used below.
1. Click the Data tab at the top of the page.
2. Select the data you want to sort. Here, we’re clicking the upper left
corner of the worksheet to select everything on the worksheet (or you
could press Ctrl-A instead to select all).
3. Click the Sort icon in the tool bar.
4. The Sort window below appears.
Tip: You can undo a sort by pressing Ctrl-Z.
5. Note that the “My data has headers” box has been checked in the upper left corner of the Sort window shown
below. If your worksheet does contain a header row, this makes the “Sort by” selection a bit easier. If it does not,
you can sort by column letter. (Note: if your data has a header row and if you do not click the box in the Sort
window telling Excel that fact, your header row will be sorted with the data).
6. Here we’ve selected the Campus column as the first sort criterion and added levels for Last and First names.
7. Click the OK button to perform the sort.
8. After sorting, all Elko people will be together, followed by all the Ely people, and so on. Within each campus
location, names will be listed in alphabetical order.
You can also sort just part of a worksheet, but you must make sure you include all the data that belongs together within
the sort selection. For example, in a name-address worksheet, sorting only the name columns (without including the
address columns) would result in names and addresses being mismatched.
Excel Workshop, Susanna Dorr, 7/29/09, page 4
Filtering is one of Excel’s most powerful tools. It lets you quickly and easily extract data you’re interested in from a
worksheet, without altering the data (unless you overtly and purposefully wish to alter it once you’re viewing the
You can filter for data matching nearly any criteria – to show only those rows wherein, for example:
A specific column contains a specific value or set of values.
A specific column contains a range of numeric values.
A specific column contains text that includes specific word or other string of characters.
The data in a column matches a specific color.
Filtering criteria may be in one column or many columns.
Filtering always assumes that a worksheet has a header row at the top.
Example #1: In this example, we filter for all faculty and staff on the Elko Campus first. Then we filter for only those
people with “Instructor” or “Professor” in their job titles. This results in the display of only full-time faculty members at
the Elko Campus.
1. Click the Data tab at the top of the Excel
2. Drag your mouse across the heading letters of all
the columns containing data you're interested in
(you can include even columns you're not
3. Click the Filter tool in the toolbar.
4. Drop down arrows appear in the header row.
5. Click the drop-down arrow in the Campus
column. By default, the Select All Box is checked. Uncheck it, and check only the
campus[es] of interest. Then click OK. Poof, now you see data for only the
campus[es] of interest! (All the data remains on the worksheet and will reappear as it
was when you click the Filter tool in the toolbar again to turn filtering off, but don't
turn it off yet!)
Excel Workshop, Susanna Dorr, 7/29/09, page 5
6. Now click the drop-down menu in
the Title column. Click the word
Text Filters in the drop-down menu.
7. Click the word Contains.
8. A window appears that will let us precisely
determine what we're looking for. In this
example, we're looking only for GBC employees
with the word "Instructor" or "Professor" in their job
titles. We create the custom filter accordingly.
9. Now our worksheet shows
us only full time faculty on
the Elko campus. If desired,
copy the filtered information
and paste it in a separate
worksheet. Or, simply click
the Filter tool in the toolbar
again to turn filtering off and
reveal all your data once
Filtering is amazingly versatile. Just play with it. You can filter by most any criteria that are relevent in
the data . You can filter for data you just want to look at, or data you want to copy-paste elsewhere, or
data you want to eliminate (to eliminate the filtered results from your data, just drag your mouse down
the matching row numbers at left, then right-click and choose Delete Rows from the drop-down menu).
Excel Workshop, Susanna Dorr, 7/29/09, page 6
Splitting Column Data – Example 1 (Splitting Names)
Example 1: Split a column containing whole names (e.g., “John A. Doe” or “Doe, John A. ”) into first, last, and middle
name name columns.
1. Click the Data toolbar.
2. Click the heading of the column
containing the names.
3. Click the Text to Columns icon.
4. A window opens. In our example, the first and last
names are “delimited” (separated) by a comma and a
space. So we make sure Delimited is selected.
If the names were listed like “John A. Doe,” they would also
be delimited, but by only a space, without a comma
separating name parts.
“Fixed width” data looks like this (each data part is a fixed
number of characters wide):
Marybeth K Smith
James P Brown
5. Then click Next. The window changes.
6. Now we tell Excel what the delimters are (what
characters separate the data we want to split). In our
example, we want to use commas and spaces.
7. Click Finish. Our names will be split into three columns.
Note: Splitting delimited columns will
overwrite any data to the right of the
columns being split. It's best then, to cut-
paste the column to be split so that it the
right-most column of the worksheet.
Excel Workshop, Susanna Dorr, 7/29/09, page 7
Splitting Column Data – Example 2 (Class Section Codes)
Example 2: Split a column containing class section designations (e.g., EV1) so that the location codes (e.g., E), the type
codes (e.g., V) and the section numeral s (e.g., 1) each has its own column. This can be very useful for subsequent
filtering or sorting.
Method 1: Use the same technique as on page 6, steps 1-3, then…
4. Sected “Fixed width” in the Convert Text to
Columns Wizard window and click Next.
5. Click where you want the fields separated in
the Data Preview area of the window. Lines
appear to show where data is separated. (If
you make a mistake, drag unwanted
separation lines outside the selection area to
Method 2: This method is more versatile, for it allows you to pull characters or strings of characters out of variably sized
fields. This method involves the use of formulas, which you can then copy-paste down the columns of an entire
For the data we’re using here, the formulas shown in row 1 will have exactly the same effect as the formulas in row 2.
LEFT(cell, number of characters to extract) extracts the designated number of characters from the left of
MID (cell, start-at character #, number of characters to extract) extracts the specified number of
characters from anywhere in a cell, beginning at the start-at character. To extract everything following
the start-at character, regardless of how long the data string is, simply type in a large number (larger
than the length of the characters in any cell of your data).
RIGHT(cell, number of characters to extract) extracts the designated number of characters from the
right of a cell.
Here are the results:
Excel Workshop, Susanna Dorr, 7/29/09, page 8
Combining Column Data – “Concatenating”
Example 1: Combining first, middle, and last name columns
into a single column. (Begin with the data in columns A, B,
and C at right with the result being the names in column D.)
The “concatenation” technique can be used to combine data
from two or more fields into a single field. The data need
not be names – it can be most anything.
Hint: Use this technique after the technique
on page 6 to convert names from a
“last, first middle”
format to a “first middle last” format.
To accomplish this, use the concatenate formula.
Arguments are separated by commas
for the concatenate formula (in
First, cell B2, the first name
Then a space (must be bounded
by quote marks)
Then cell C2, the middle name
Then another space (“ “)
Then cell A2, the last name
Then we copy-paste our formula all the way down column D. Voila!
If instead we wanted to format our names like “Doe, John A.” we’d use the following formula:
=CONCATENATE(A1, ", " ,B2 , " " ,C2)
Notice that here we put comma space in quotes (", ") to separate the last name and the first name.
You can put whatever characters/words you wish inside quotes to add to the concatenation items.
Excel Workshop, Susanna Dorr, 7/29/09, page 9
Converting data to all upper or all lower case is easy. We simply need to use the UPPER() and LOWER() formulas, which
we can copy-paste all the way down a page.
The results of the above would look
It's trickier to begin with all-upper or all-lower case data and convert it to a combination of upper and lower case, but it
can be done.
The above uses the LEFT() and MID() formulas described on page 7 to isolate parts of the text, capitalizing each
differently. NOTE: We've used 100 as the number of characters in the MID formula simply because we can be confident
that no name is going to be longer than that – it's an arbitrary number to grab the rest of the name.
These formulas, like all formulas, can be copy-pasted all the way down a column.
The result looks like this:
We could make a longer formula to combine the separate operations into one:
Or an even longer forumula to create an outcome like "Lisa Abbott" in a single column:
=CONCATENATE (UPPER(LEFT(B2,1)), LOWER(MID(B2,2,100))," ",UPPER(LEFT(A2,1)), LOWER(MID(A2,2,100)))
Excel Workshop, Susanna Dorr, 7/29/09, page 10
Replacing Formulas in Cells with the Formula Results
One of the problems with formulas in worksheet is that they only work so long as the cells being referred to are intact.
In our capitalization example from the previous
page, we wind up with column C and D containing
"junk" data that we really don’t want. We'd
probably prefer just having the data in column E.
In fact, we'd probably like to replace column A with
column E and eliminate columns C, D, and E
However if we try just pasting column E over
column A, we end up with a terrible result! Excel is
totally confused now, because our formulas no
longer work. We've replaced the original data on
which the formulas depend with formulas that
make no sense without the raw data.
Instead, we'll copy the whole column
by right clicking on the column letter
E and choosing Copy from the menu.
Then we'll right click on Column A, and choose Paste Special,
selecting Values from the window that appears.
After this operation, our last names are the way we want them. Cell
A2 contains "Abbott" – not a formula. And we can delete columns
C,D, and E entirely.
Excel Workshop, Susanna Dorr, 7/29/09, page 11
VLookups – Combining Data from Separate Worksheets
Example: Let's say we have one worksheet
containing hundreds of classes scheduled for
a semester but that we have a missing data
piece – say, the class department. (We've
inserted an empty column to contain the
Let's also say that we have a second data sheet that lists all classes
ever offered at GBC along side department names.
Otherwise, the content of the two worksheets differs so that
neither one, by itself, contains all the information we need.
This is an ideal circumstance for a VLookup operation ('V' stands for
Vertical). We're going to make Excel do the work of extracting the
department names for the first worksheet from the second
To prepare for a Vlookup, we need to do some prior preparation:
Put the two worksheets in the same Excel file. The lookup operation will be simpler and remain viable over
time if the two worksheets coexist in the same Excel file (if the two are in separate files, the lookups will fail if
one of the files is moved, deleted, or renamed). See Page 2 for instructions on copying a worksheet to a
different workbook file.
Make sure the actual data we want to match is identical in both worksheets. In our example, we can use the
values in Column A of both worksheets to match the data records (e.g. ANSC 209 in the first worksheet will
exactly match ANSC 209 in the second worksheet).
Make sure the key column is somewhere to the LEFT of the data to be retrieved. In the worksheet containing
department names, the lookup column (with ANSC 209) is to the left of the department name column.
Sort the data in the worksheet containing the data you want to retrieve by the key field. For best results, in
our example, we'll sort the worksheet containing the department names by column A.
Now we can use a VLOOKUP formula to insert the missing department name for each class into the first worksheet to
achieve the result shown at left.
The VLOOKUP function seems complicated until you get the hang of it,
but the time saving can make a trial-and-error effort worth the result.
The more times you slog through the process, the easier it is to do the
next time, till it becomes something that comes easily.
On the next page, we'll use Excel's formula helper to accomplish the
Excel Workshop, Susanna Dorr, 7/29/09, page 12
1. Click the Formulas tab.
2. Click the Lookup and Reference icon.
3. Choose VLOOKUP from the list.
4. A Formula Arguments window opens.
At any time during the steps that follow, you
can grab the Function Arguments window
by the colored bar at the top to move the
window around your screen, so that you can
access what's under it
a. First, place your cursor in the Lookup_value field. This is
where we inform Excel about which value we're going to
be looking up elsewhere (if we were looking up a word in
a dictionary to find a definition, the word itself is would
be the "lookup value.")
b. Click Cell A2 (ANSC 209) as the Lookup_value
c. Click in the Table_array field. This is where we tell Excel where to look for the data we're seeking (essentially,
where the reference dictionary resides). We click on the tab at the bottom of the workbook to open the
worksheet containing the department names. Then we highlight the span of columns that interest us – they
must include both the lookup value (ANSC 209) and the data we're wanting to extract (the department name),
and all the columns falling between them. So we'll drag the mouse across columns A, B, and C.
Excel Workshop, Susanna Dorr, 7/29/09, page 13
d. Now, in the Col_index_num field, we let Excel know which column inside our Table_array selection contains the
data we want to extract. In this case, we want to extract the data in the third column of the Table_array
selection. We type in 3. (If we wanted the division number instead of the department name, we'd type in a 2,
since the division number appears in the second column of the table array.)
e. Finally, we tell Excel whether or not the lookup value has to match exactly or only approximately. We want only
exact matches. In the Range_lookup field we type FALSE for an exact match (instructions in the window when
we click in the Range_lookup field tell us that FALSE will restrict the lookup operation to exact matches).
f. Click OK on the Function Arguments window.
g. Excel finds the department for ANSC 209. Now all that remains is to copy-paste cell C2 all the way down the
worksheet to make Excel look up the department names for all the classes in our spreadsheet.
Excel Workshop, Susanna Dorr, 7/29/09, page 14
Consolidating Data: Eliminating Duplicate Rows
This technique is amazingly useful when a worksheet contains more detail than you need. It is a new technique for Excel
2007, not found in previous versions.
Example: We have a worksheet containing all the classes offered during a semester, with a separate row for each day
the class is taught and each subsection of the class. We're not interested in the sections or the days, just in the number
off different classes we're offering.
A piece of the original worksheet
looks like this:
1. Click Data in the menu bar of the workbook.
2. On the Data toolbar click the Remove Duplicates icon.
3. Uncheck all columns except column A
and click OK. This will instantly give us a
list of all the different courses offered.
It will also delete all redundant records,
altering our spreadsheet (and rendering
sections, days, times, etc., meaningless
since they only show one of many
possible instances of the class).
Press Ctrl-Z to undo this operation if it
does not behave as you wished. Also, it's
a good idea to do this operation with a
copy of your worksheet, so that your
original data is preserved for other
The results look like this:
operations involving more detail.
The result looks like this: