Reorder and summarize data In this chapter = Sort worksheet data = Sort data by using custom lists = Organize data into levels = Look up information in a worksheet One of the most important uses of business information is to record when something happens. Whether you ship a package to a client or pay a supplier, tracking when you took those actions, and in what order, helps you analyze your performance. Sorting your information based on the values in one or more columns helps you discover useful trends, such as whether your sales are generally increasing or decreasing, whether you do more business on specific days of the week, or whether you sell products to lots of customers from certain regions of the world. Microsoft Excel has capabilities you might expect to find only in a database program—the ability to organize your data into levels of detail you can show or hide, and formulas that let you look up values in a list of data. Organizing your data by detail level lets you focus on the values you need to make a decision, and looking up values in a work-sheet helps you find specific data. If a customer calls to ask about an order, you can use the order number or customer number to discover the information that customer needs. This chapter guides you through procedures related to sorting your data by using one or more criteria, calculating subtotals, organizing your data into levels, and looking up information in a worksheet.Sort worksheet data Although Excel makes it easy to enter your business data and to manage it after you've saved it in a worksheet, unsorted data will rarely answer every question you want to ask it. For example, you might want to discover which of your services generates the most profits, or which service costs the most for you to provide. You can discover that information by sorting your data. When you sort data in a worksheet, you rearrange the worksheet rows based on the contents of cells in a particular column or set of columns. For instance, you can sort a worksheet to find your highest-revenue services. You can sort a group of rows in a worksheet in a number of ways, but the first step is to identify the column that will provide the values by which the rows should be sorted. In the revenue example, you could find the highest revenue totals by sorting on the cells in the Revenue column. You can do this by using the commands available from the Sort & Filter button on the Home tab of the ribbon. Gre The exact set of values that appears in the Sort & Filter list changes to reflect the data in your column. If your column contains numerical values, you'll get the options Sort Largest To Smallest, Sort Smallest To Largest, and Custom List. If your column contains text values, the options will be Sort A To Z (ascending order), Sort Z To A (descending order), and Custom List. And if your column contains dates, you'll get Sort Newest To Oldest, Sort Oldest To Newest, and Custom List.69 Conditional Format ae Cell °° Formatting ~ Table ~ 4 Syles H 1 Service Ground 2Day Overnight 3Day Priority Overnight | Styles ~ Revenue 1,467,588 1,292,720 974,723 940,737 Revenue sorted in descending order = a ep » . SAMA Find & lee Fir Solect - 21, Sort Smallast to Largest Sat Larget to Sales tom Sort A mh Y The Sort Smallest To Largest and Sort Largest To Smallest options let you sort rows in a worksheet quickly, but you can use them only to sort the worksheet based on the contents of one column, even though you might want to sort by two columns. For example, you might want to order the worksheet rows by service category and then by total so that you can tell which service categories are used most frequently.A B c 1 2 Customer Service Revenue 3 |contoso [20ay |s 246811 4 Fabrikam 2Day $1,152,558 5 Tailspin Toys 2Day $ 851,922 6 Contoso 3Day s 318,710 7 Fabrikam 3Day $658,371 8 Tailspin Toys 3Day $ 1,026,163 9 Contoso Ground $941,717 10 Fabrikam Ground $ 964,280 11 Tailspin Toys Ground $ 1,147,078 12 |Contoso Overnight s 675,122 13 Fabrikam Overnight $801,656 14 Tailspin Toys Overnight $ 35,456 15 Contoso Priority Overnight $ 955,755 16 Fabrikam Priority Overnight $ 175,699 47 Tailspin Toys Priority Overnight $ 161,061 Sort a list of data by more than one column You can sort rows in a worksheet by the contents of more than one column by using the Sort dialog box, in which you can pick any number of columns to use as sort criteria and choose whether to sort the rows in ascending or descending order. If you want to create two similar rules, perhaps changing just the field to which the rules are applied, you can create a rule for one field, copy it within the Sort dialog box, and change the field name. If your data cells have fill colors applied to them, perhaps representing cells with values you want your colleagues to notice, you can sort your list of data by using those colors. In addition, you can create more detailed sorting rules, change the order in which rules are applied, and edit and delete rules by using the controls in the Sort dialog box.2 x *Pladdtevel | XDelete Level || 2 copy Level Options. My datahas headers Column Sort On Orde Sorby evenie [=] [cetvales | | Smatiestto Largest z ox Caneel Use the Sort dialog box to create detailed sorting rules To sort worksheet data based on values in a single column 1. Select a cell in the column that contains the data by which you want to sort. 2. On the Home tab of the ribbon, in the Editing group, select the Sort & Filter button to display a menu of sorting and filtering choices. 3. Select Sort A to Z to sort the data in ascending order. Or Select Sort Z to A to sort the data in descending order. To sort worksheet data based on values in multiple columns 1. Select a cell in the list of data you want to sort. 2. On the Sort & Filter menu, select Custom Sort. 3. If necessary, select the My data has headers check box. 4. In the Sort by list, select the first field 5. In the Sort On list, select the option by which you want to sort the data (Cell Values, Cell Color, Font Color, or Conditional Formatting Icon). 6. In the Order list, select an order for the sort operation.Sort Ladd vee || PX Dectetee || 55 comy vow! ||| = || > || Onions. Column orton onder omtby [customer Z) cellvalues J) Atoz ¥ Thenby cell Values © | Smalestto Largest x OK Canes Create sorting rules in the Sort dialog box 7. Select the Add Level button. 8. In the Then by list, create another rule by using the techniques described in steps 4 through 6. 9, When you are finished creating sort levels, select OK to sort the values. A B e D E 1 2 Customer Season Revenue 3 iContoso Summer $ 114,452.00 4 Contoso Fall $ 118,299.00 5 Contoso Winter $183,651.00 6 Contoso Spring $201,438.00 a Fabrikam Winter $100,508.00 8 Fabrikam Spring $139,170.00 9 Fabrikam Summer $ 183,632.00 10 Fabrikam Fall $ 255,599.00 n Northwind Traders Spring $120,666.00 2 Northwind Traders Summer $ 129,732.00 B Northwind Traders Winter $174,336.00 14 Northwind Traders Fall $ 188,851.00 Allist of data that has had sorting rules applied to it To sort by cell color1. Select a cell in the list of data. 2. On the Sort & Filter menu, select Custom Sort. 3. If necessary, select the My data has headers check box. 4. Inthe Sort by list, select the field by which you want to sort. 5. In the Sort On list, select Cell Color. 6. In the Order list, select the cell color on which you want to sort. 7. Inthe last list box, choose On Top to position the color you identified ‘on top, Or Choose On Bottom to position the color you identified on the bottom. ee oe |Tailspin Toys Priority Overnight | 3s sl a [10 iI "| Add Level X Delete Level 75 Copy Level ‘Options. [4 by data has headers: fe le i ‘Column ‘Sono: Order |) c — fe ie FA fl rn s 2 or cane Sort lists of data using cell fil color as a criterion When you are done creating sorting rules, select OK to sort the values, = To copy a sorting level 1. Select a cell in the list of data. 2. On the Sort & Filter menu, select Custom Sort.s Select the sorting level you want to copy. Select the Copy Level button, and edit the rule as needed. Select OK. oP To move a sorting rule up or down in pi . On the Sort & Filter menu, select Custom Sort. Select the sorting rule you want to move. Select the Move Up button to move the rule up in the order. Or Select the Move Down button to move the rule down in the order. 4, Select OK. oN To delete a sorting rule 1, On the Sort & Filter menu, select Custom Sort. 2. Select the sorting level you want to delete. 3. Select the Delete Level button. 4. Select OK. Sort data by using custom lists The default setting for Excel is to sort numbers according to their values and to sort words in alphabetical order, but that pattern doesn't work for some sets of values. One example in which sorting a list of values in alphabetical order would yield incorrect results is the months of the year. In an “alphabetical” calendar, April is the first month and September is the last! Fortunately, Excel recognizes a number of special lists, such as days of the week and months of the year. You can have Excel sort the contents of a worksheet based on values in a known list. And, if needed, you can create your own list of values. For example, the default lists of weekdays in Excel start with Sunday. If you keep your Chapter 8: Reorder and summarize data business records based on a Monday-Sunday week, you can create a new list with Monday as the first day and Sunday as the last.You can create a new custom list by using the Custom Lists dialog box, which you access through the Excel Options dialog box. The Custom Lists dialog box gives you the choice of entering the values yourself or importing them from a cell range in your workbook. Custom Lists ? x Custom Liss | Custom fists: Uist entries: i Add ‘Sun, Mon, Tue, Wed, Thu, Fri, Sat Sunday, Monday, Tuesday, Wedne Jan, Feb, Mar, Apr, May, Jun, Jul, At January, February, March, April, M Press Enter to separate list entries. Import list from cell: > OK Manage your lists by using the Custom Lists dialog box Import Cancel Gre Another benefit of creating a custom list is that dragging the fill handle of a list cell that contains a value causes Excel to extend the series for you. For example, if you create the list Spring, Summer, Fall, Winter, and then enter Summer in a cell and drag the cell's fill handle, Excel extends the series as Fall, Winter, Spring, Summer, Fall, and so on. To define a custom list by entering its values 1. On the File tab, select Options.ta In the Excel Options dialog box, select the Advanced category. Scroll down to the General area, and then select the Edit Custom Lists button. 4. Inthe Custom Lists dialog box, enter a list of items in the List entries area. » Press Enter after each item to move to the next line. Select Add. Select OK, and then select OK again to close the Excel Options dialog box. om To define a custom list by copying values from a worksheet 1. Select the cells that contain the values for your custom list. 2. Open the Custom Lists dialog box. 3. Inthe Custom Lists dialog box, select the Import button. 4. Select OK, and then select OK again to close the Excel Options dialog box. To sort worksheet data by using a custom list 1. Select a cell in the list of data you want to sort. 2. On the Home tab, select the Sort & Filter button, and then select Custom Sort. . If necessary, select the My data has headers check box. . In the Sort by list, select the field that contains the data by which you want to sort. . If necessary, in the Sort On list, select Values. . Inthe Order list, select Custom List. . Inthe Custom Lists dialog box, select the list you want to use. . Select OK. Re PnNag Organize data into levelsAfter you have sorted the rows in an Excel worksheet or entered the data so that it doesn’t need to be sorted, you can have Excel calculate subtotals (totals for a portion of the data). In a worksheet with sales data for three different product categories, for example, you can sort the products by category, select all the cells that contain data, and then open the Subtotal dialog box. Subtotal 2 x reach cmnge Urs fon Ad subtle reper cron sbtetas Bagebreakermees groups [Zi sermary blo eta sero oe coe Apply subtotals to data by using the Subtotal dialog box In the Subtotal dialog box, you can choose the column on which to base your subtotals (such as every change of value in the Week column), the summary calculation you want to perform, and the column or columns with values to be summarized. After you define your subtotals, they appear in your worksheet.' A EB c D 1 Year ‘Quarter Month Package Volume 2[__2017]t arwary 5,213,292 a 2017 1 Febraary 21028516 4 2017 1 March 2,489,501 5 2017 2 April 9,051,231 5 20172 May 5,225,156 7 2017 Zune 3,266,64a 8 2017 3 July 2,078,794 9 20173 August 1591,434 10 2017 2 September 8.518.985 4 2017 October 11973,050 12 2017 4 November 7,599,195, 3 2017-4 December 9,757,876 14 2017 Total 58,803,774 15. 2018 A January 5,304,039 16 2018 1 February 5,465,096 7 20181 March 1,007,799 pot A list of data with Subtotal outiining applied When you add subtotals to a worksheet, Excel also defines groups based on the rows used to calculate a subtotal. The groupings form an outline of your worksheet based on the criteria you used to create the subtotals. For example, all the rows representing months in the year 2017 could be in one group, rows representing months in 2018 in another, and so on. The outline area at the left of your worksheet holds controls you can use to hide or display groups of rows in your worksheet.a A 8 € 6 1__Year___ Quarter Month —_—Package Volume | 14 2017 Total 58,803,774 6 2018 January 5,204,039, 16 2018 1 February 5,465,096 a 2018 1 March 1,007,799 18 2018 2 April 4,010,287 19 2018 2 May 4,817,070 20 2018 2 June 8,155,717 nu 2018 3 July 6,882,370 2 2018 3 August 2,295,635. 23 2018 3 September 7,115,883 24 2018 4 October 1,362,767 28 2018 4. November 8,935,488 26 2018 4 December 9,537,077 27 2018 Total 64,559,228 - 28 Grand Total 123,363,002 A list of data with details for the year 2017 hidden When you hide a group of rows, the button displayed next to the group changes to a Show Detail button (the button with the plus sign). Selecting a group's Show Detail button restores the rows in the group to the worksheet. The level buttons are the other buttons in the outline area of a worksheet with subtotals. Each button represents a level of organization in a worksheet. Selecting a level button hides all levels of detail below that of the button you selected. The following table describes the data contained at each level of a worksheet with three levels of organization. Level Description 1 Grand total 2 Subtotals for each group 3 Individual rows in the worksheet7 i 8 c 5 1 Yea Quarter Month Package Volume 14 2017 Total 58,803,774 27 2018 Total 64,559,228 : 28 Grand Total 123,363,002 29 A list of data with details hidden at level 2 You can add levels of detail to the outline that Excel creates. For example, you might want to be able to hide revenues from January and February, which you know are traditionally strong months. You can also delete any groupings you no longer need or remove subtotals and outlining entirely. Gr If you want to remove all subtotals from a worksheet, open the Subtotal dialog box and select the Remove All button. To organize data into levels 1. Select a cell in the group of data you want to organize. 2. On the Data tab of the ribbon, in the Outline group, select the Subtotal button. 3. In the Subtotal dialog box, in the At each change in list, select the field that controls when subtotals appear. 4. Inthe Use funetion list, select the summary function you want to use for each subtotal. 5. Inthe Add subtotal to group, select the check box next to any field you want to summarize. 6. Select OK. To show or hide detail in a list with a subtotal summary = Do either of the following © Select a Hide Detail control to hide a level of detail ° Select a Show Detail control to show a level of detail.To create a custom group in a list that has a subtotal summary 1. Select the rows you want to include in the group. | A B Ee D 1 Year _Quarter___Month _ Package Volume 2 2017 1 January 3 2017 1 February 4 2017 1 March 2,489,601 5 2017 2 April 9,051,231 A data list with rows selected to create a custom group 2. Select the Group button. To remove a custom group in a list that has a subtotal summary 1. Select the rows you want to remove from the group. 2. Select the Ungroup button. To remove subtotals from a data list 1. Select any cell in the list 2. Select the Subtotal button. 3. Inthe Subtotal dialog box, select Remove All Look up information in a worksheet Whenever you create a worksheet that holds information about a list of distinct items, such as products offered for sale by a company, you should ensure that at least one column in the list contains a unique value that distinguishes that row (and the item the row represents) from every other row in the list. Assigning each row a column that contains a unique value means that you can associate data in one list with data in another list. For example, if you assign every customer a unique identification number, you can store a customer’s contact information in one worksheet and all orders for that customer in another worksheet. You can then associate thecustomer’s orders and contact information without writing the contact information in a work-sheet every time the customer places an order. In technical terms, the column that contains a unique value for each row is known as the primary key column. When you look up information in an Excel worksheet, it is very useful to position the primary key column as the first column in your list of data. If you know an item's primary key value, it's no trouble to look through a list, of 20 or 30 items to find it. If, however, you have a list of many thousands of items, looking through the list to find one would take quite a bit of time. Instead, you can use the VLOOKUP function to find the value you want. A a ° E F 1 2 ShipmentiO. _ Destination 3 4 5 ShipmentibD_- CustomeriD Dat ationPostalCode = [DestinationPostalCode |- a ‘sH2I0 cisea71 5/21/2015 59685 77408 7 shad 495231 5/22/2015 24348, 91936 8 sH212 0429120 5/23/2015 70216 "53501 9 sH213 caisi2s 5/24/2015 81196 21660 10 shai cc7ezs90 5/25/2015 13193 92518, 1 sH215 102300 5/26/2015 27910 "76842 2 shais iss0742_ 5/27/2015 73820 21303 B sH217 c4esz69 9/28/2015 34245, 33375, 14 sH218 ci7s2i79____ 5/29/2015 87569 11471 An Excel table for use with VLOOKUP- The VLOOKUP function finds a value in the leftmost column of a named range, such as a table, and then returns the value from the specified cell to the right of the cell with the found value. A properly formed VLOOKUP function has four arguments, as shown in the following definition: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). The following table summarizes the values Excel expects for each of these arguments. Argument —_ Expected valueArgument Expected value Jookup_value The value to be found in the first column of the named range specified by the table_array argument. The lookup_value argument can be a value, a cell reference, or a text string. table_array The multicolumn range or name of the range or data table to be searched, col_index_num The number of the column in the named range that has the value to be returned. range_lookup A TRUE or FALSE value, indicating whether the function should find an approximate match (TRUE) or an exact match (FALSE) for the lookup_value. If this argument is left blank, the default value for it is TRUE. A IMPORTANT When range_lookup is left blank or set to TRUE, for VLOOKUP to work properly, the rows in the named range specified in the table_array argument must be sorted in ascending order based on the values in the leftmost column of the named range. The VLOOKUP function works a bit differently depending on whether the range_ lookup argument is set to TRUE or FALSE. The following list summarizes how the function works based on the value of range_lookup: = If the range_lookup argument is left blank or set to TRUE, and VLOOKUP doesn't find an exact match for /ookup_value, the function returns the largest value that is less than lookup_value. = If the range_lookup argument is left blank or set to TRUE, and Jookup_value is smaller than the smallest value in the named range, an #N/A error is returned. «= If the range_lookup argument is left blank or set to TRUE, and Jookup_value is larger than all values in the named range, the largest value in the named range is returned.= If the range_lookup argument is set to FALSE, and VLOOKUP doesn't find an exact match for Jookup_value, the function returns an #N/A error. As an example of a VLOOKUP function, consider the following data, which shows an Excel table with its headers in row 2 and the first column in column B of the worksheet. CustomerID Customer cuo1 Fabrikam Cuo2 Northwind Traders Cu03 Tailspin Toys Cu04 Contoso If the =VLOOKUP (E3, B3:C6, 2, FALSE) formula is used, when you enter CU02 in cell E3 and press Enter, the VLOOKUP function searches the first column of the table, finds an exact match, and returns the value Northwind Traders to cell F3. Fa fe | =VLOOKUP(E3,B3:C6,2,FALSE) A 8 © D E F 1 2 CustomerID Customer LookupID Customer 3 cuon Fabrikam cuo2 Northwind Traders 4 cuo2 Northwind Traders 5 cuo3 Tailspin Toys 6 cu04 Contoso A VLOOKUP formula that looks up a customer name given a customer IDGre The related HLOOKUP function matches a value in a column of the first row of a table and returns the value in the specified row number of the same column. The letter H in the HLOOKUP function name refers to the horizontal layout of the data, just as the Vin VLOOKUP refers to the data’s vertical layout. For more information on using the HLOOKUP function, select the Excel Help button, enter HLOOKUP in the search terms box, and then select Search. A IMPORTANT Be sure to give the cell in which you type the VLOOKUP formula the same format as the data you want the formula to display. For example, if you create a VLOOKUP formula in cell G14 that finds a date, you must apply a date cell format to cell G14 for the result of the formula to display properly. To look up worksheet values by using VLOOKUP 1. Ensure that the data list includes a unique value in each cell of the leftmost column and that the values are sorted in ascending order. 2. In the cell where you want to enter the VLOOKUP formula, enter a formula of the form =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). Enter TRUE for the range_lookup argument to allow an approximate match. Or » Enter FALSE for the range_lookup argument to require an exact match. 4, Enter a lookup value in the cell named in the VLOOKUP formula's first argument, and press Enter. Key pointsIn this chapter, you learned how to: = Sort worksheet data = Sort data by using custom lists = Organize data into levels = Look up information in a worksheet @ Practice tasks Before you can complete these tasks, you need to copy the book's practice files to your computer. The practice files for these tasks are located in the Oifice2019SBS\Ch08 folder. You can save the results of the tasks in the same folder. The introduction includes a complete list of practice files and download instructions. Sort worksheet data Open the SortData workbook in Excel, and then complete the following tasks: 1. Sort the data in the list in ascending order based on the values in the Revenue column. 2. Sort the data in the list in descending order based on the values in the Revenue column, 3. Sort the data in the list in ascending order based on a two-level sort where the first sorting level is the Customer column and the second is the Season column. 4. Change the order of the fields in the previous sort so that the first criterion is the Season column and the second is the Customer column. 5. Sort the data so that the cells in the Revenue column that have a red fill color are at the top of the list. Sort data by using custom listsOpen the SortCustomData workbook in Excel, and then complete the following tasks 1. Create a custom list by using the values in cells G4:G7. 2. Sort the data in the cell range B3:D14 by the values in the Season column based on the custom list you just created 3, Create a two-level sort by using the values in the Customer column, in ascending order, as the first criterion, and the custom list-based sort for the Season column as the second criterion. Organize data into levels Open the OrganizeData workbook in Excel, and then complete the following tasks: 1. Outline the data list in cells A1:D25 to find the subtotal for each year. 2. Hide the details of rows for the year 2018. 3. Create a new group consisting of the rows showing data for June and July 2017. 4. Hide the details of the group you just created 5. Show the details of all months for the year 2018. 6, Remove the subtotal outline from the entire data list. Look up information in a worksheet Open the LookupData workbook in Excel, and then complete the following tasks: 1. Sort the values in the first table column in ascending order. 2. In cell C3, create a formula that finds the CustomeriD value for a shipment ID entered into cell B3. 3. Edit the formula so that it finds the DestinationPostalCode value for the same package