Transcription
Microsoft Excel 2013ExcelSorting, Filtering& AdvancedFiltering of DataIT TrainingSt. George’s, University ofLondonInformation ServicesConditional Formatting & Data Validation
Microsoft Excel 2013ContentsUnderstanding Lists . 1Performing An Alphabetical Sort . 2Performing A Numerical Sort . 3Sorting On More Than One Column. 4Understanding Filtering . 5Applying And Using A Filter . 6Clearing A Filter . 7Creating Compound Filters . 8Multiple Value Filters . 9Creating Custom Filters . 10Using Wildcards . 11Understanding Advanced Filtering . 12Using An Advanced Filter . 13Extracting Records With Advanced Filter . 14Using Formulas In Criteria . 15If you have a St. George’s username and password you can access all the files that goeswith this manual.Files can be found in a folder on the N drive in the IT Training folder named:Excel - Sorting, Filtering & Advanced Filtering of DataN:\IT Training\ Excel - Sorting, Filtering & Advanced Filtering of DataInformation ServicesConditional Formatting & Data Validation
Microsoft Excel 2013UNDERSTANDING LISTSMicrosoft Excel is quite often used to create lists– such as lists of customer contacts, lists of itemsin an inventory, lists of employees, lists ofupcoming events, and the like. To cater for thesekinds of lists, Microsoft Excel allows you to performsort operations so that all of the data in the list canbe rearranged in a more desirable and logicalfashion.Lists – The Key to Understanding SortingTo understand how Microsoft Excel performs a sorting operation you first need to grasp the concept of alist in Microsoft Excel.When you make a cell active Excel analyses all of the adjacent cells – up, down, left and right. Itconsiders all of the cells around the active cell to be part of a list range as long as the cells contain data.So, in Excel, a list is automatically defined as the area around the active cell that contains data. Theboundary of the list range is defined when an empty cell is encountered. In the example below, theactive cell is C4. Excel therefore deems the list to be the one bounded by empty cells – in other words,the list is made up of all of the non-empty cells.The list extends to the left as far as column A, and to the right as far as column G. It doesn’t go up anymore rows because C3 is an empty cell. It goes down as far as row 23, the last non-empty cell in thecolumn. So the list range is automatically defined as A4 to G23.You can make any cell in this list the active cell and the list range will be the same. You don’t have toselect or highlight the range.Sorting a ListOnce a list is available to Excel, the data in it can be sorted. The data is usually sorted down a column,known in database jargon as a field. Data is sorted alpha-numerically, meaning that alphabeticalcharacters are sorted first, and then numbers. If there are only alphabetical characters in it then the listwill be sorted alphabetically from left to right. If there are only numbers the list will be sorted numerically.If there is mixed data the list will be sorted by alphabetical characters and then by numbers.Lists can be sorted in ascending order (from lowest to highest) and in descending order (from highestto lowest).Information ServicesPage 1ITtraining@sgul.ac.uk
Microsoft Excel 2013PERFORMING AN ALPHABETICAL SORTThe most common use for sorting is to rearrangethe data in a list in a specific order. A list issimply a grouping of data without any emptycolumns or rows. In a list, a single column canbe sorted by placing the cell pointer anywhere inthe column that you wish to sort and choosing theSort & Filter command in the Editing group.OpenFileTry This Yourself: Before starting thisexercise you MUSTopen the file E1327Sorting 1.xlsx Click on the SortingText worksheet tab andspend a few momentsstudying the data – it isa list of employeesClick in cell C4 to selectthe cell – this is the LastName column of thelisting2On the HOME tab, clickon Sort & Filter in theEditing group andselect Sort A to ZThe data in the list willbe sorted alphabeticallyin ascending order bylast name Click on Sort & Filter inthe Editing group againand select Sort Z to Ato sort the data indescending orderRepeat the above stepsand sort the list byPosition, by Office, andfinally by E-Mail3For Your Reference Handy to Know To alphabetically sort data in a list:1. Click in the column to sort, then click on theHOME tab2. Click on Sort & Filter in the Editing groupand select either Sort A to Z to sort inascending order, or Sort Z to A to sort indescending order When you choose to sort, Excel searches inall directions from the active cell. The end ofthe list is deemed to be the first blank cellencountered in all directions: up, down, leftand right.Information ServicesPage 2 Excel assumes that the first row of the listcontains the column heading or field.ITtraining@sgul.ac.uk
Microsoft Excel 2013PERFORMING A NUMERICAL SORTMicrosoft Excel allows you to sort all kinds ofdata – alphabetic, numeric, dates and mixed.When you place the cell pointer in a cell, Exceldetermines the data type in that cell and amendsthe sort commands accordingly. For example,when sorting alphabetical data, the command willbe Sort A to Z, but for numeric data it changes toSort Smallest to Largest.Same FileTry This Yourself: Continue using theprevious file with thisexercise, or open thefile E1327Sorting 2.xlsx.Click in cell A4 whichrepresents the start ofthe employee NocolumnOn the HOME tab,click on Sort & Filterin the Editing groupand select Sort A to Zto sort the data inascending order2Notice how the list issorted first by letters,then numbers Click in cell I5 which isthe start of the Agecolumn – these cellsstore numbersClick on Sort & Filterin the Editing groupagain and select SortSmallest to Largestto sort the data fromyoungest to oldestRepeat the abovesteps and sort the listby Telephone, bySalary Level and byService4For Your Reference Handy to Know To sort a list numerically:1. Click in the column to sort2. On the HOME tab, click on Sort & Filter inthe Editing group and select either SortSmallest to Largest to sort in ascendingorder or Sort Largest to Smallest to sort indescending order If a numeric column contains a formula whichdisplays a calculated value, the sortoperation will be performed on the calculatedvalue rather than the formula.Information ServicesPage 3ITtraining@sgul.ac.uk
Microsoft Excel 2013SORTING ON MORE THAN ONE COLUMNExcel allows you to select multiple columns tosort by, thereby enabling you to analyse dataaccording to different categories. Each column issorted in order one at a time. The listing is sortedSameFileTry This Yourself: by the first column, then by the second column, andso on. For example, a staff listing can be sortedfirst by Position, then by Last Name so that eachposition contains an alphabetical sub-listing.2Continue using theprevious file with thisexercise, or open the fileE1327 Sorting 3.xlsx.Click in cell A4 toposition the active cellwithin the listOn the HOME tab, clickon Sort & Filter in theEditing group andselect Custom Sort todisplay the Sort dialogbox5Click on the drop arrowfor Sort by to display alist of the field (column)names, then click onPositionClick on [Add Level] toadd another level in thedialog boxClick on the drop arrowfor Then by and click onLast NameClick on [OK] to displaythe list sorted byPosition then by LastName6For Your Reference Handy to Know To sort on more than one column:1. Click on the HOME tab, then click on Sort &Filter in the Editing group2. Select Custom Sort3. Specify the columns to sort the list on Be careful when sorting large lists that gobeyond the boundaries of the screen. Youshould ensure that there are no blank rowsor columns that can result in you omittingsome of the data.Information ServicesPage 4ITtraining@sgul.ac.uk
Microsoft Excel 2013UNDERSTANDING FILTERINGFiltering refers to comparing a list of recordsagainst specific criteria and then hiding therecords that don’t match the criteria. It can beused simply to help find a record, or to create asubset of data that you can then edit, format, copy,move, chart or otherwise manipulate withoutaffecting the other records. Here’s a brief exampleof how simple filtering works.An Example of FilteringHere is a list of 65 records in a table. The field names appear at the top and are No, First Name, LastName and so on. After filtering using the criterion of Type Gold, the list is reduced to the 16 recordsthat have the word Gold in the Type column and the other records are hidden. Notice the row numberson the left – these confirm that some of the rows are not visibleInformation ServicesPage 5ITtraining@sgul.ac.uk
Microsoft Excel 2013APPLYING AND USING A FILTERThe Filter command applies (or removes) droparrows to the right of the column labels in the list.When you click on a Filter arrow, it displays a listof the unique items in the column, includingblanks and non-blanks. By selecting an item from alist for a specific column, you can instantly hide allrows that don't contain the selected value, anddisplay only those that do.OpenFileTry This Yourself: Before starting this exerciseyou MUST open the file E1328Filtering Data 1.xlsx.Click anywhere in the listThe values in Years in yourworksheet may vary fromthose shown here becauseYears updates automatically toshow the current duration ofmembership 1Click on the DATA tab, thenclick on Filter in the Sort &Filter groupClick on the filter arrow to theright of Type to display a list ofoptionsClick on (Select All) to removeall of the ticks, then click onGold and click on [OK]All records for Goldmemberships will be shownand the rest of the recordstemporarily hidden.2Notice that the drop arrow nextto Type has changedindicating that a filter is activeon this column4For Your Reference Handy to Know To turn the filter on or off:1. Click in the data, click on the DATA tab, thenclick on Filter in the Sort & Filter groupTo apply a simple filter:1. Click on a filter arrow, click on (Select All),then click on an option and click on [OK] If the column that you want to filter includesblank cells, you will also have the option(Blanks) to choose from. This can be usedto help you locate missing data.Information ServicesPage 6ITtraining@sgul.ac.uk
Microsoft Excel 2013CLEARING A FILTEROnce a filter has been applied, a subset of data isshown in the list. Before you can apply analternative filter, the first one must be cleared sothat all of the records become available again.Filters can be cleared either by clicking on (SelectAll) in the filter options list or by selecting ClearFilter From “fieldname” from the menu. You canalso remove the filter arrows altogether.SameFileTry This Yourself: Continue using the previousfile with this exercise, oropen the file E1328 FilteringData 2.xlsx.Examine the list of recordsin the spreadsheetYou’ll notice that it iscurrently filtered on Goldunder Type. 1Click on the filter arrow forType and select ClearFilter From “Type”All of the records will againbe listed. You can alsoremove the filteraltogether. Ensure the DATA tab isactive, then click on Filter inthe Sort & Filter group toremove the filter arrows23For Your Reference Handy to Know To clear the filter:1. Click on the filter arrow2. Select Clear Filter From “fieldname” You can remove the filter altogether in onestep by clicking on Filter . Use the ClearFilter From command when you want toperform subsequent filters.To remove the filter arrows:1. Click on the DATA tab, then click on Filter inthe Sort & Filter groupInformation ServicesPage 7ITtraining@sgul.ac.uk
Microsoft Excel 2013CREATING COMPOUND FILTERSThe Filter tool allows you to select a filter on onecolumn or field at a time. When the filter isapplied, the records that match that filter will bedisplayed. As you create successive filters onother fields, the filters are applied to only therecords that are currently on display. In otherwords, the filters build up on each other, orcompound, refining the list as required.SameFileTry This Yourself: Continue using the previousfile with this exercise, or openthe file E1328 FilteringData 3.xlsx.Click anywhere in the list,ensure the DATA tab is active,then click on Filter in the Sort& Filter group to display thefilter arrows2Click on the filter arrow forType and click on (Select All),then click on Theatre and clickon [OK]Only the records for Theatreare displayed Click on the filter arrow forSuburb, click on (Select All),click on Heidelberg Heights,then click on [OK]Only the Theatre members inHeidelberg Heights are nowshown. Let’s see if there areother members who live inHeidelberg Heights. 3Click on the filter arrow forType and select Clear FilterFrom “Type”Now that the list is filtered toshow all Types in HeidelbergHeights, a Junior member isalso listed. Click on the filter arrow forSuburb and select ClearFilter From “Suburb”5For Your Reference Handy to Know To create a compound filter:1. Apply the first filter to the list to display asubset of the records2. Apply a second filter to the list to show asubset of the subset of records When you print a filtered list, Excel will printthe list as shown in the worksheet, with all ofthe unwanted records hidden.Information ServicesPage 8ITtraining@sgul.ac.uk
Microsoft Excel 2013MULTIPLE VALUE FILTERSYou may want to list records by creating twocriteria for one field so that you can selectrecords with one of two possible values. Forexample, you may want to see all the records fortwo particular suburbs, or two membership types.The filter options list all of the unique values foundin that field in the list, so you can click on any of thevalues that you want to display.Same FileTry This Yourself: Continue using theprevious file with thisexercise, or open the fileE1328 FilteringData 4.xlsx.Click on the filter arrow forSuburb and click on(Select All) to remove theticksClick on Ascot Vale andBentleigh so that ticksappear next to both itemsClick on [OK] to display thefiltered listOnly those records withAscot Vale or Bentleigh inthe Suburb are listed. 2Click on the filter arrow forSuburb and select ClearFilter From “Suburb” tolist all of the records3For Your Reference Handy to Know To filter on multiple values:1. Click on the filter arrow for the required field2. Click on (Select All)3. Click on the checkboxes for each of thevalues that you want to filter by4. Click on [OK] Using multiple values in criteria is the sameas saying, for example, if Suburb AscotVale OR Suburb Bentleigh.Information ServicesPage 9 If the field is a date field, you can filter onspecific years, specific months or evenspecific dates. These are all provided foreasy access in the filter menu.ITtraining@sgul.ac.uk
Microsoft Excel 2013CREATING CUSTOM FILTERSThe Filter feature enables you to createindividual conditions for multiple fields using thedrop-down lists. To set more than one conditionper field you can use the Custom Filter option.Same FileTry This Yourself: This is ideal if you want to select records with oneof several possible values, or where you want arecord that falls within a range of values rather thanmatching an exact value.1Continue using theprevious file with thisexercise, or open the fileE1328 FilteringData 5.xlsx.Click on the filter arrow forLast Name, point to TextFilters, then selectBegins WithThe Custom AutoFilterdialog box will display Type W, as shownClick on [OK] to filter thelist so that all memberswith last names beginningwith W are listed2Repeat steps 1 to 3 tocreate a list of memberswhose surnames startwith SClick on the filter arrow forLast Name and selectClear Filter from “LastName”3For Your Reference Handy to Know To create a custom filter:1. Click on the filter arrow for the field2. Select FieldType Filters [option]3. Type the filter criteria4. Click on [OK] If you are not sure how to spell a word, butknow that it includes particular letters, youcan search using the criteria contains.Information Services Each of the equals, contains, begins with,and ends with criteria have an oppositechoice e.g., does not equal, does notcontain etc.Page 10ITtraining@sgul.ac.uk
Microsoft Excel 2013USING WILDCARDSIf you need to filter for specific values in a list,you can select them individually from the filtermenu or use wildcards to create a morepowerful filter. Wildcards are characters that canSameFileTry This Yourself: 2Continue using the previousfile with this exercise, or openthe file E1328 FilteringData 6.xlsx.Click on the filter arrow forLast Name, point to TextFilters, then select CustomFilter to display the CustomAutoFilter dialog box4Type S*Click on [OK] to see themembers whose names beginwith the letter SRepeat step 1 to display theCustom AutoFilter dialog boxNotice that Excel changedyour criteria so that it nowreads “begins with S” be substituted for any character (?) or series ofcharacters (*). For example, B*N would find allwords starting with B and ending with N, while B?Nwould find the same, but look for three letters.5Click on the drop arrow belowLast Name, scroll up andselect equals, then click in thefield to the right of S andtype *nClick on [OK] to see thenames that begin with S andend with nClick on the filter arrow forLast Name and select ClearFilter From “Last Name” todisplay all of the records6For Your Reference Handy to Know To use wildcards in custom criteria:1. Click on a filter arrow, then point to TextFilters2. Select Custom Filter3. Enter a criteria with either an * or a ?depending on what you are searching for You can filter for the question mark orasterisk character as the actual characteritself, and not the wildcard character, bypreceding the character with the tilde . Forexample, if you use the criteria Year ?,Excel will search for the character stringYear?.Information ServicesPage 11ITtraining@sgul.ac.uk
Microsoft Excel 2013UNDERSTANDING ADVANCED FILTERINGTo work with Advanced Filters in Excel, youneed to understand a few of the concepts thatare used. A list in Excel is a series of rows ofinformation. Each row is effectively one unit ofinformation. This structure is very similar to asimple database and therefore Excel uses similarterminology to describe the parts of the list. Thefollowing illustrates a list and its parts.Fields, Field Names and RecordsA field is a column in the list of data. In the example below, the column of data for the Last Name is anexample of a field.The field name is the heading at the top of the column. The field names within one list must be unique.In the example below, the text Annual Fee is an example of a field name.A record is a row of data in the list. Each record is one item of data in the list. In the example below, therow of information for Fred Jackson is one record. Note that advanced filters do not work correctly ifthere are blank rows in the list.FieldField NameRecordCriteria, Criteria Range, AND and ORCriteria are tests against the data in specific fields, for instance Gold. When Gold is tested against thefield Type, the filter would display only the people with Gold memberships. All other records are filteredout (hidden).The criteria range is the area where you specify the criteria. The first row contains the field names thatmirror those in the list. The second and subsequent rows are used to type the criteria or examples ofwhat you are looking for in the list.If you want the conditions between fields joined with an AND, you write the conditions on the same row.If you want them joined with an OR, you write the conditions on separate rows. In the next example, ourcriteria specifies greater than 15 years membership AND Gold membership.CriterionInformation ServicesCriterion RangePage 12ITtraining@sgul.ac.uk
Microsoft Excel 2013USING AN ADVANCED FILTERTo use an Advanced Filter, you need to create acriteria area, enter your criteria, specify the list tobe filtered and then run the filter. The AdvancedFilter tool has one distinct advantage over otherfiltering techniques (such as AutoFilter), you typeyour criteria directly into the worksheet. The criteriaare always visible in the worksheet above therecords that you have filtered.OpenFileTry This Yourself: Before starting this exerciseyou MUST open the fileE1357 AdvancedFilters 1.xlsx.Study the list of records tofamiliarise yourself with thedataFirst we need to add criteria.2Type the labels and values inthe range D2:E3 as shown45The criteria reads “whereYears are greater than orequal to 20 AND the Type isSilver”. Now to apply thefilter. Click in cell E6 so that Excelcan locate the listClick on the DATA tab, thenclick on Advanced in the Sort& Filter group to display theAdvanced Filter dialog boxThe List range is automaticallyselected. Click in Criteria range, thentype D2:E3Click on [OK]Only the records matching thecriteria will be displayed.On the DATA tab, click onClear in the Sort & Filtergroup to restore the list6For Your Reference Handy to Know To use the Advanced Filter:1. Create the criteria range2. Click in the list to be filtered3. Click on the DATA tab, then click onAdvanced in the Sort & Filter group4. Type the Criteria range, then click on [OK] When using the Advanced Filter dialog box,if you can’t remember the range of cells thathold certain values (e.g. Criteria range), youcan click on Collapse Dialog which movesthe focus to the workbook, enabling you toselect the actual cells on the relevantworksheet.Information ServicesPage 13ITtraining@sgul.ac.uk
Microsoft Excel 2013EXTRACTING RECORDS WITH ADVANCED FILTERThe Advanced Filter can be used to filter a list inplace (hiding unwanted records) or to extractrequired records and paste them in anotherlocation. By creating a subset of the list, you canfurther analyse the data without risking accidentalmodifications of the original list. You must specifythe fields that you want and the location where therecords are to be copied.Same FileTry This Yourself: Continue using theprevious file with thisexercise, or open the fileE1357 AdvancedFilters 2.xlsx.Scroll down to and clickin cell C75This is a blank cell whichappears below the list Type the field headingsas shownThese are the fields thatwe want to extractaccording to the criterialisted at the top of theworksheet. 265Click anywhere in theoriginal listClick on the DATA tabthen click on Advancedin the Sort & FiltergroupUnder Action click onCopy to anotherlocationClick in Copy to andtype C75:F75Click on [OK], then scrolldown and examine theextracted data7For Your Reference Handy to Know To extract records using the Advanced Filter:1. Create the criteria and extract ranges2. Click on the DATA tab, then click onAdvanced in the Sort & Filter group3. Click on Copy to another location4. Type the Criteria range and Copy to range,then click on [OK] You can use the extract feature of theAdvanced Filter to create a list of uniquecodes that are used in a list. For example, tocreate a list of Type codes, extract the Typefield with no criteria and select the checkboxUnique records only in the AdvancedFilter dialog box.Information ServicesPage 14ITtraining@sgul.ac.uk
Microsoft Excel 2013USING FORMULAS IN CRITERIABy making slight adjustments to the criteria rangeand using a formula for the criteria, you cancreate more sophisticated filters. The first row ofthe criteria range must contain a label that is notthe same as a field heading. The criteria examplein the second row must be expressed as aformula. The formula often makes a comparisonusing the first record as a model for all others.SameFileTry This Yourself: Continue using the previousfile with this exercise, or openthe file E1357 AdvancedFilters 3.xlsx.Delete the contents of cellsD2:E3Type the headings AveMem incell D2 and AverageMembership in cell F2 asshown, then enter theseformulas:in F3 AVERAGE(E7:E70)in D3 E7 F 3254The formula in F3 calculatesthe average Years (20.4), thenthe formula in D3 tests theYears of the first recordagainst the average. If theYears are higher than theaverage, the answer is TRUE.Let’s now extract the matchingrecords. Click in the list, then click onthe DATA tabClick on Advanced in the Sort& Filter group, thendouble-click on E 3 inCriteria range and type D3Click on Copy to anotherlocation in Action – we’ll usethe same Copy to cell rangeClick on [OK], then scroll downto examine the extracted data6For Your Reference Handy to Know To use formulas in criteria:1. Type a criteria heading that does not matcha field name2. Create a formula (starting with ) below theheading that performs a comparison with thevalue of a field in the first record When you perform the copy operation duringfiltering, Excel names the header row of thecopied records as Extract. It names the cellscontaining the criteria range as Criteria. Youcan use these names to navigate quickly tothe extract or criteria ranges of the worksheetvia the Name box.Information ServicesPage 15ITtraining@sgul.ac.uk
of how simple filtering works. An Example of Filtering Here is a list of 65 records in a table. The field names appear at the top and are No, First Name, Last Name and so on. After filtering using the criterion of Type Gold, the list is reduced to the 16 records that have the word Gold in the Type column and the other records are hidden .
