DOC

Adv Excel Sorting and Filtering Data, Advanced Formulas, Linking

By Ida Bailey,2014-06-22 10:40
12 views 0
Adv Excel Sorting and Filtering Data, Advanced Formulas, Linking

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 Scott Jacobson 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 Scott Jacobson 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 Scott Jacobson 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 Scott Jacobson 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 Scott Jacobson 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 Scott Jacobson 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 Scott Jacobson 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 scores. thThe 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 Grade refers to the 6

    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 Scott Jacobson 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 Scott Jacobson 10 of 13

Report this document

For any questions or suggestions please email
cust-service@docsford.com