Reference

Document Sample
Reference Powered By Docstoc
					                                                                              Excel Workshop, Susanna Dorr, 7/29/09, page 1

                           Workshop: Excel 2007 Tips and Tricks

                                                     Contents
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
                                   on.




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

                                                    Sorting Data

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 Data

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
filtered results).

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
   window.
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
   interested in).
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
   again.




          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):

       John                    Doe
       Marybeth         K      Smith
       Horace                  Jones
       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
   remove them.)




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




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
                any cell.
             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
                                                                                parentheses):

                                                                                 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

                                                  Upper/Lower Case
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
like this:




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:

                        =CONCATENATE(UPPER(LEFT(A2,1)), LOWER(MID(A2,2,100))

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



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
missing data.)



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

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
task.
                                                                         Excel Workshop, Susanna Dorr, 7/29/09, page 12

Technique:

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:

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:37
posted:8/7/2012
language:
pages:14