Excel 2003 Sorting And Filtering Manual
Contents
1. Check this box to Page break between groups have a Summary below the data Summary below data g Remove All To remove any existing Seay ee subtotals press this button 5 Click OK 6 Data will be subtotaled and collapsible groups will be compiled 4dd subtotal to Cuantity 4 7 2005 Academic Computing Box of Cleaning Wipes Polk Library he 5 10 2005 Academic Computing Labels Polk Library Me Academic Computing Total A 2A 4005 Applications amp Prog Batteries AA Central Stores Applications amp Prog Total 12 7 2004 Athletics long phone cord Boise 4 12 2005 Athletics Kraft Envelopes 12 5 6 5 Polk Library Me Sv 10 2005 Athletics Rubberbands Folk Library Mli Athletics Total 5102005 Information Technology Dry erase markers green Polk Library Mli S252005 Information Technology 9 volt Battery Central Stores Information Technology Total 25 4005 Polk Library pink bond Central Stores o2o 2005 Polk Library Canary Bond Central stores of2o 2005 Polk Library Green Bond Central Stores f25 4005 Folk Library Cream Bond Central Stores Grouped Totals l sealing tape Polk Library Mle SSeS A White copy paper Central Stores black ink for stamp pads Central Stores Reeve Union Total i Grand Total Grand Total2. or Descending for the first criterion 72 PY 5 Click the drop down in the Then by box to select the Last Name v Ascending second criterion Descending 6 Select Ascending or Descending for the second criterion rst Mame 7 If necessary repeat steps 6 and 7 for a third criterion 8 If your data contains labels at the top of the column Aico 2 select the radio button next to header row v Ascending 9 If your data does not contain labels select the radio Descending button next to No header row 10 More detailed tools are available by pressing the Header row No header row Options button on the sort window a First key sort order this should always be Options Cancel set to normal unless you are sorting days of the week or months of the year b Case sensitive Check this box to make your sort case sensitive If the data is sorted in descending order this will put all the words starting with capital letters first in a sort then all the words starting with lower a Pai Case sensitive Cancel case letters A B C a b c the opposite is Orientation Sort top to bottom Sort left to right D Then by Ascending Descending My data range has First key sort order W armal true if using ascending order c Orientation Top to bottom will sort columns Left to Right will sort Rows d Click OK 11 When finished designing sort click OK Filter Sorting and Filt
3. Excel 2003 Sorting and Filtering Data User s Manual University of Wisconsin Oshkosh Division of Information Technology Sarah Bradway August 2006 Sorting and Filtering Data October 2006 Table of Contents SOR FDA BATES US a a casas ease aaa 1 BUTTER DA TACT IS US T EE E EENE E E 2 CREATE AND APPLY ADVANCED FILTERS ccccccscssssssesseccsccccscscssccscscsssscccccscsccecsesscsscess 3 CALCULATE WITH DATABASE FUNCT IONG cccccocsscsscscssssscsssscscsssssssssscccsssssscsscsssssscess 5 ADD SUBTOTALS TO A WORKSHEET eeeeossossoessessessessesssossossoesoesoesoessesssosssesoessessessesseoo 7 Sorting and Filtering Data October 2006 Sort Data Lists Sorting Data Excel may be one of the easiest programs available to sort large amounts of data quickly and easily Data can be sorted in ascending order A to Z or descending order Z to A Sorting can be as simple or as complex as you need to be options are available to sort data by numeric or alphabetic information and by single or multiple criterion Single level Sort 1 Click on a cell located in the column you would like to sort 2 On the standard toolbar click the Sort Ascending button 4 or the sort Descending Z button 3 Data will be sorted in the appropriate order Multiple level Sort 1 Data Menu gt Sort 2 The Sort Text Dialog box will appear 3 Click the drop down in the Sort by box to select the 2 x first criterion Select Ascending
4. Filters Sometimes the AutoFilter feature may not allow you to filter all the criteria you would like An Advanced Filter gives you many more options when it comes to the type and amounts of information being filtered l Zi Insert a minimum of three blank rows above your set of Data Copy your original Header into the top row so that there are several blank rows to hold our criteria see below Cee Pe ee oO 2 ae eee ee a 1 Last Fin Aid Award Major Minor Year Comp Exper S Last Fin Aid Award 6 Alderson 0 Accounting Mone Freshman Some gu 7 Hitz WIS 2000 Education Mone Freshman Lots amp Smith DA T Phy Ed Health Sophomore Some f Ley DA O Criminal Justice Mone Freshman 10 Luljak D 0 Undecided Mone Sophomore 11 MacDonald 0 Fine Arts Photography Freshman data to be filtered Sorting and Filtering Data October 2006 3 Enter desired criteria into blank rows under appropriate columns text Find exactly the text specified within the quotation marks copie Ae AO TS 4 Click Data Menu gt Filter gt Advanced Filter ew Equal to lt Less than gt Greater than lt Less than or equal to gt Greater than or equal to lt gt Not equal to Accounting Education Enter desired criteria into appropriate columns Phy Ed Criminal Justice Undecided Mone Mone Health Mone Mone Replaces any single char
5. acter in the same position as the question mark Replaces multiple characters in the same position as the asterisk Comp Exper Lots Year Freshman Comp Exper Some Lots Some Some Lots Year Freshman Freshman sophomore Freshman sophomore 5 The Advanced Filter dialog box will appear select the appropriate options Filter the list in place clicking this radial a L 6 Click button will hide rows that do not fit the criteria similar to AutoFilter Copy to another location will take those entries that fit the criteria and will copy them to another location on the sheet List Range select the cells of original data to be filtered include header row Criteria Range select the cells that hold criteria include header row Copy to select the cell you would like to have filtered data copied to this option is only available if the Copy to another location is checked Advanced Filter x Ackion Filter the list in place Lisk range tata 1657 z Criteria range 5heet1 f4 1 1 2 Copy to patag Unique records only Cancel Unique records only will only allow a record to appear once in the filtered list OK 7 Data that fits the desired criteria will appear Sorting and Filtering Data October 2006 Calculate with Database Functions Database functions can be very useful when working with large amounts of data It takes the benefits of an Advanced Filter a
6. ering Data October 2006 Filter Data Lists The ability to filter data is most helpful when you want to see only a small subset of your original data that fits a given criteria For example you have a list of 500 of the University s most charitable donors you want to see how many of those donors live in Oshkosh All you need to do is set up a filter to find this out Filter a Data List with AutoFilter 1 Data Menu gt Filter gt AutoFilter 2 Notice that small dropdown arrows appeared next to each column heading 3 To apply a filter click the drop down and select the criteria you would like to view a b C d Sort Ascending will sort by that column from A to Z Sort Descending will sort by that column from Z to A All after a filter has been applied click all to see all entries Top 10 will deliver the top ten entries under a heading 1 The number 10 is adjustable you can make it 5 or 20 or 17 1i You can also change items to percent to show the top or bottom ten percent Custom see below instructions for Filter a Data List with a Custom AutoFilter Individual Item select the item you would like to see for example select 10 00 to see only the people who donated 10 00 4 Notice that once a filter has been applied the dropdown arrow turns blue 5 You can filter out multiple criteria by clicking multiple drop down arrows a Example filter by city to get all those people living in Oshkosh then filte
7. ls that hold your specific criteria 11 Click OK Function Arguments SR a iB OO Database a4 6179 s Date Department Field Total Price CS Total Price Criteria ars OO feag PEE Adds the numbers in the Field columnt of records in the database that match the conditions WOU specify Criteria is the range of cells that contains the conditions you specify The range includes a coluron label and one cell below the label For a condition Formula result 75 02 Help on this Function Cancel Sorting and Filtering Data October 2006 Add Subtotals to a Worksheet Subtotals can be very useful if you want to total up the same piece of data for several different types of entries 1 Sort data using the column you would like to subtotal 2 Choose Data gt Subtotals 3 The Subtotal Dialog Box will appear 4 Use Dropdown menus and check boxes to choose Subtotal appropriate options fap eck chee IP a At each change in Select the field you Department SSC Department ar want totals for b Use Function Select the function you Use Function would like to use Sum Average Count etc Sum c Add Subtotal to Click the boxes for the fields you would like to total d Replace Current subtotals Checking this T unit price box will replace any existing totals bal Tobal Price e Page break between groups Check this box to put each grouping on a separate page V Replace current subtotals f Summary below data
8. nd mixes it with a basic function to give you the sum count etc of your data Take for example a spreadsheet that tracks office supplies ordered by different departments on campus you want to know how much each specific area spent a database function will give you this result 1 Insert a minimum of three blank rows above your set of Data 2 Copy your original Header into the top row so that there are several blank rows to hold our criteria see below A B B O S E FE Gy Date Department ltem Description Vendor Quantity unit price Total Price Date Department ltem Description Vendor Quantity unit price Total Price AF2004 Reeve Union Clear Packing tape A 24004 Reeve Union Kraft Envelopes 7 5 x 10 5 1A 4004 Reeve Union Fine point Sharpie A i2004 Reeve Union black stick pen 12272004 Reeve Union File Folders 1 3 cut assorteg 12 7 20 Data to be summed ong phone cord counted etc 10 00 10 58 2 63 blank rows to hold criteria 3 Enter desired criteria into blank rows under appropriate columns text Find exactly the text specified within the quotation marks Replaces any single character in the same position as the question mark Replaces multiple characters in the same position as the asterisk Equal to Less than gt Greater than lt Less than or equal to gt Greater than or equal to lt gt Not equal to roma mo
9. noses A Reeve Union Deparment 1A722004 Reeve Union 1A722004 Reeve Union ri VA 2004 Reeve Union Fine point Str 1AF 2004 Reeve Union black stick pen afer 4004 Reeve Union File Fold 12 7 2004 Athletics 12 p0 56 p 72 1 bls 1 23 1 P10 06 10 58 2 63 Enter desired criteria into appropriate columns Sorting and Filtering Data October 2006 4 Click the cell where you would like your function to be 5 Click the function button located next to formula bar 6 The Insert function box will appear 7 Select the function you would like to use a Select Database from the category dropdown to see available database functions b As each function is highlighted an explanation of that function will be displayed at the bottom of the window 8 Click OK Insert Function aE Search For a Function pa Go Cr select a category Database Select a Function SLIM DSUMfdatabase field criteria Adds the numbers in the Field column of records in the database that match the conditions you specify Help on this Function Cancel 9 Once a function has been selected a dialog box specific to that function will appear a We will use the DSUM as an example 10 Fill in boxes with appropriate entries a Database select the entire section of cells to be figured include header row b Field Which do you want to add or count Enter the label in quotation marks c Criteria Select the cel
10. r by zip code to get those people living near campus Phote Home Address _ Madeline 1847 1235 Any Street Oshkosh 54904 695 1253 Sort Ascending Brittany 2136 22695 Maes Ave Oshkosh 54902 215 6964 Sort Descending Larry 3649 266 drd Street Appleton 54915 965 1547 cheri 9521 6905 Parkland Cr Appleton 54911 264 1575 Kristi 6593 8696 Elm Street Menasha 54952 125 6997 Ryan 2146 25 Woodland Appleton 54915 265 9642 Matthew 44d 56 Sugar Hollow Or Oshkosh 54904 215 2609 Melissa 2504 3235 Blackbird Ln Oshkosh 54901 675 5944 Shana 2694 6269 Oak Crest Ln Oshkosh 54904 500 00 Jason 5964 Westbridge Ave Appleton 54915 05 4457 1 000 00 Joel Appleton Sorting and Filtering Data October 2006 Filter a Data List with a Custom AutoFilter l Aa a Data Menu gt Filter gt AutoFilter Click the dropdown in the column you would like to filter Drag down to Custom The Custom AutoFilter Dialog box will appear Use the dropdown in the first box to select the parameter you would like to use for your filter Examples greater than less than or equal to Use the dropdown or type a limit into the second box Example 5 00 If necessary use the radio buttons for AND or IF and the second set of boxes to add another criteria to the filter Click OK Show rows where Donation equals z Use to represent any single character Use to represent any series of characters cna Create and Apply Advanced
Download Pdf Manuals
Related Search