Adv Excel: Sorting and Filtering Data, Advanced Formulas, Linking

Document Sample
Adv Excel: Sorting and Filtering Data, Advanced Formulas, Linking Powered By Docstoc
					Advanced Excel
                                                     LCSD – Advanced Excel – May 2005




Table of Contents
TABLE OF CONTENTS ............................................................................................................................. 2
SORTING AND FILTERING DATA ........................................................................................................ 3
   SORTING ...................................................................................................................................................... 3
     Quick Sorting .......................................................................................................................................... 3
     The Data Sort Command ........................................................................................................................ 3
   FILTERING ................................................................................................................................................... 4
     Custom Filtering ..................................................................................................................................... 4
CHARTS ....................................................................................................................................................... 5
   QUICK CHART KEY – F11............................................................................................................................ 5
   THE CHART WIZARD ................................................................................................................................... 6
LINKING CELLS ........................................................................................................................................ 9
   LINKING ONE CELL TO ANOTHER ................................................................................................................. 9
ADVANCED FORMULAS ......................................................................................................................... 9
   IF STATEMENTS .......................................................................................................................................... 9
MACROS .....................................................................................................................................................10
       Macro Tips ............................................................................................................................................11
TEMPLATES ..............................................................................................................................................12




Written by Darren Wilkins                                                                                                                               2 of 13
                                LCSD – Advanced Excel – May 2005



Sorting and Filtering Data
Sorting
Sorting is a simple concept and a simple process as far as Excel is concerned. You may need to
sort a list of employees alphabetically by last name or perhaps by hire date. Excel makes it easy
on you.


Quick Sorting
                      Sort Ascending                          Sort Descending



                                  Sort Ascending       A-Z, 1-100

                                  Sort Descending      Z-A, 1-100

    1. Click on an item in your list that contains the type of data you wish to sort by (i.e. a last
       name, an ID number, etc.)
    2. Click on one of the two quick sort buttons (see above). That’s it! Your list should now be
       sorted in the order you specified. Amazingly simple, isn’t it?
     Note:       As long as you have NO BLANK ROWS, sorting will work just fine. Blank cells
    are fine, but blank rows are not.

The Data Sort Command
For more detailed and advanced sorting, you can use the DATA menu and select the SORT
command from there. This will give you more options as far as sorting goes.
    1. Click on any piece of data inside your list (on any last name, any employee number or
       other type of data)
    2. Go to the DATA menu and select SORT.
    3. Select the first sort method and then the optional second and third sort methods. You
       can also choose to sort each column (or type of data) in ascending or descending order.




Written by Darren Wilkins                                                                     3 of 13
                                 LCSD – Advanced Excel – May 2005



Filtering
Say you have a large list of employees from all over the world. Wouldn’t it be great to easily be
able to display the employees from your Texas office without losing the data from the rest of your
list? Well, filtering allows you to do this in a rather simple way.
The simplest way to filter is to use AUTOFILTER.
    1. Click on the DATA menu and select FILTER and then AUTOFILTER. This will turn “on”
       auto filtering.




    2. You will then notice that each of your column headings now contain a small filter arrow
       next to them.



             Filter
             arrow




    3. Click on the filter arrow for the column you wish to filter. Then select the item you wish
       from the drop-down list. The screen will then display only items that match your criteria.
       Amazing, isn’t it?

Custom Filtering
Sometimes, you may have the need to perform a filter that cannot be accomplished by using the
single selection method of Autofilter. When you click on a filter arrow, you will notice the selection
of “custom”. This opens up many new avenues of creating custom criteria to filter your results.
Here are a few examples of what you could perform a custom filter for on an employee list:
       All employees that make more than $10.00 per hour (see graphic below).
       All employees whose last name begins with “G”
       All employees who work in the SALES or DEVELOPMENT departments.




Written by Darren Wilkins                                                                       4 of 13
                                  LCSD – Advanced Excel – May 2005



To custom filter, simply select “custom” from the filter drop-down arrow for a given column. You
will then see the following dialog box.




Be wary of the AND and OR. AND means that both criteria sets must be met. OR means that
just one of the 2 sets of criteria must be met. This is important to remember when performing
custom filters.

Charts
Charts are a graphical representation of data. They come in all shapes and sizes and can be
used to represent all types of data.
The key to charts lies in the proper selection of data and then in the proper selection of the
correct type of chart. There are many options.

Quick Chart Key – F11
    1. The first step is to select your data. The selection of data (the right amount, the right
       kind) directly affects the outcome of your chart.




                    The selection of data here DID NOT include the larger totals. The larger
                  numbers can throw a chart “off balance”. However, you may need those totals
                       in the chart or wish to reflect those in a separate chart altogether.
    2. Press the F11 key. This will create a chart on a separate worksheet. The type of chart it
       creates is a COLUMN chart. This is one of the most common types of charts.




Written by Darren Wilkins                                                                        5 of 13
                                LCSD – Advanced Excel – May 2005




                               A column chart created with the F11 key


The Chart Wizard
If you would like more options than the F11 key gives you, then the chart wizard is for you.
    1. Select the data on your spreadsheet that you wish to reflect in the form of a chart.
    2. Go to the INSERT menu and select CHART. This will bring you into the Chart Wizard.
       You will notice that the Chart Wizard dialog box states that you are on Step 1 of 4. So,
       keep in mind, you have to spend a few minutes making some decisions about your chart.




    3. Step 1 of 4 is the selection of the chart type. There are many kinds of charts for many
       purposes. Which chart is best for you? Well, that takes practice and testing. The four
       most common types of charts are the first four listed: Column, Bar, Line and Pie. You will
       probably find yourself using these more times than not, unless you are a physicist or
       statistician. You can see a preview of what your data will look like in that particular chart
       type by using the “Press and Hold to Preview” button. You may find that your results are
       not to your liking, so be prepared to do some hunting.




Written by Darren Wilkins                                                                      6 of 13
                               LCSD – Advanced Excel – May 2005




    4. Once you have selected the type of chart you wish to create, press NEXT.
    5. Step 2 of 4 is basically unnecessary if you have already “pre-selected’ your data. Simply
       click NEXT to continue.




    6. Step 3 of 4 is where you can add, delete, or edit some “chart options”. These include
       titles, a legend and data labels. All can be useful and necessary at times. Data labels
       are especially helpful when using pie charts.




Written by Darren Wilkins                                                                  7 of 13
                               LCSD – Advanced Excel – May 2005




    7. Step 4 of 4 is simply letting Excel know where you wish to place this chart. Your options
       include adding it as separate, new worksheet to your workbook or as an object in your
       current spreadsheet (or any other within the workbook for that matter). Placing a chart as
       an “object in” is also called embedding a chart. Make your choice and select FINISH to
       complete the chart wizard.




                                 An example of an embedded chart



Written by Darren Wilkins                                                                  8 of 13
                                 LCSD – Advanced Excel – May 2005




Linking Cells
You can create links from cell to cell, from sheet to sheet, or even from workbook to workbook.

Linking one cell to another
Scenario: You have multiple spreadsheets in a workbook that track students FCAT Writes
                                                        th
scores. The first sheet contains the scores for all of 6 grade and the subsequent sheets contain
just the names and scores and students on specific teams.
    1. Determine which spreadsheet will be the “source” and which will be the “dependent”.
    2. Select the dependent spreadsheet. Select the cell that you wish to create a link for.
    3. Type in an equals (=) sign.
    4. Click on the “source” worksheet tab to activate that sheet.
    5. Click on the cell that contains the data you wish to link to (it can be text or numeric).
    6. Press [Enter]. You have just created the link. Whenever you update the “source” cell,
       the “dependent” cell will automatically update.




                                                               This linking formula
                                                                              th
                                                               refers to the 6 Grade
                                                               worksheet, cell C3.




Advanced Formulas
There are an abundance of formulas available in Excel. However, most people don’t even scratch
the surface as far as formulas go.

IF Statements
IF(logical_test,value_if_true,value_if_false)
Checks whether a condition is met and then returns a value if true and a value if false. The value
can be a numeric value, a formula, or text. When using a text value in a formula, you must
enclose the desired text in quotes.


The above IF statement states that if the value in F4 is greater than or equal to 200,000, then
multiply F4 times 5% and then add that value to F4. If the value is less than 200,000 then return
the text “No Bonus”.
Here is the final result on the spreadsheet.




Written by Darren Wilkins                                                                      9 of 13
                                LCSD – Advanced Excel – May 2005




Canada, Germany
and Great Britain did
not attain the bonus.




Macros
You can create macros to automate common tasks. You should not create a macro for
something that you may only do once or twice. It’s not worth it. However, if you have a need to
perform a task multiple times with a given spreadsheet, then a macro may be an efficient solution
for you.
To create a new macro, follow these steps:
     1. Click on the TOOLS menu and select MACROS, then RECORD NEW MACRO.




     2. You will then be asked to name your macro. Macro names may NEVER have spaces in
        them.




Written by Darren Wilkins                                                                 10 of 13
                                LCSD – Advanced Excel – May 2005




    3. Once you click OK after naming the macro, you are ready to start “recording” your macro.
       You will see a small macro toolbar. There is a blue stop button on there. That is to be
       pressed when you are finished recording the macro.
    4. Now you basically begin performing the task that you wish to record. It’s a good idea to
       “rehearse” prior to recording the macro, as to make sure you get all the steps correct.
    5. When you are finished recording the macro, press the stop button on the macro toolbar.
    6. To run the macro later on, go to the TOOLS menu and select MACROS and then
       MACROS. You can then select and play your macro.

Macro Tips
       Never use spaces in a macro name.
       Editing a macro is very difficult. If there is a problem with yours, you may want to
        consider simply re-recording the macro.
       Macros can potentially be unsafe. Some PCs are set to not allow macros to run. Be
        aware of this when sending files containing macros to others. You can always check
        your security setting in Excel by going to the TOOLS menu and selecting MACROS and
        then SECURITY (see graphic below).




Written by Darren Wilkins                                                                      11 of 13
                                  LCSD – Advanced Excel – May 2005




Templates
A template is a file that you create that you wish to use over and over again. In a template, you
generally leave most of the data empty and you only fill in common items like headers, titles,
formulas, and more.
The process for creating a template is simple and totally up to your specifications. All you need to
do is create a spreadsheet that you think you will use more than once. Fill in all the common
information and complete as much as you can. Then, simply save it as a template.
    1. Create the basic structure of your spreadsheet.
    2. When you are finished, click on the FILE menu and select SAVE AS.
    3. On the SAVE AS dialog box, click on the SAVE AS TYPE drop down menu and select
       Template (*.xlt). You’ll notice that the file will be given the extension .xlt instead of .xls.
       This indicates that it is a template and not simply a spreadsheet. It is important to note
       this difference when you are searching for the file later on for editing.




    4. It is important to save the file in the templates folder. If you do not save it in that location
       (see graphic below)




    5. To use your template, go to the FILE menu and select NEW. Click on the link for general
       templates (or templates from file) on the right hand window pane. This should bring up a
       box that allows you to select from multiple templates.




Written by Darren Wilkins                                                                        12 of 13
                            LCSD – Advanced Excel – May 2005




Written by Darren Wilkins                                      13 of 13

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:7/4/2012
language:
pages:13