Managing Data Lists - Microstar Publishing
Contents
1. 9 Kettle 10 Kettle 14 Toaster 15 Toaster KE 17 Af TA090 CJ9 39 95 55 00 39 95 47 95 Sunbeam Moulinex Sunbeam Moulinex B C D E Good Byer Stores Australia Ltd Inventory Whitegoods department Name Code 5 Breadmaker BM 200 T tnller BT2500 iron HI540 11 Microwave M965 12 Microwave 13 Microwave 17 Supplier_ Cost No on Hat Value Retail Prij Remington Sunbeam Phillips 5 Samsung EMX470 Sanyo 6AVM350 Whirlpool 500 150 2f5 900 300 600 5 169 00 75 00 99 00 239 00 279 00 479 00 4 Display All records again by clicking on the Filter button on the ribbon 12 Microstar Publishing Pty Ltd 2010 Using Wildcards in Filtering Wildcards can be utilised if not all of the information is Known or a range of data is required There are two wildcards the and the The is used in Managing Lists of Data place of just one character while the is used for a string of unknown characters For example if we wanted a list of all phone numbers beginning with 03 then we would use the following Custom Filter setup 1 Click on the Sales staff worksheet 2 Click anywhere in the list 3 Click on the tab Data gt Filter to turn on the filter if necessary 4 Click on the Filter button for Phone 5 Click on Text Filters gt Custom Filter A B First Name Last Name Phone S SortAtoZ SotZtoA Sort by Co2. Code plier Cost No on Hai Value Breadmaker BM200 Remington 100 g 5 Breadmaker BM4600 Sunbeam Advanced Filter Griller BT2500 Sunbeam s Iron HI540 ps ds Action kettle Af Moulinex Filter the list in place Kettle KE 1 Sunbeam O Copy to another location 11 Microwave M965 samsung 12 Microwave EMX470 Sanyo _ Set range 13 Microwave 6AVM350 Whirlpool Criteria range yigagiegagz0 4 20 14 Toaster J9 Moulinex C 15 Toaster 9206 Russell Hc 16 Toaster TA090 Sunbeam Unique records only LL un m LT EEE BE Ee 6 Click in the Criteria Range box and then highlight the Filter cells A18 A20 7 Click on OK Microstar Publishing Pty Ltd 2010 15 BSBITU402A Complex Excel 2010 The list will be reduced to display only those records for the Suppliers Sunbeam and Sanyo as shown below B G D E F Good Byer Stores Australia Ltd Inventory Whitegoods department Row labels Code Supplier Cost No on Hai Value Retail price are now BM4600 Sunbeam 4 110 5 660 179 00 blue to riller BT2500 Sunbeam 3 50 3 150 3 75 00 indicate Kettle KE217 Sunbeam 28 9 262 39 95 they 12 Microwave EMxX470 Sanyo gS 200 6800 279 00 have 16 Toaster TAO90 Sunbeam 4 30 450 39 95 been filtered 18 Supplier 19 Sunbeam 20 Sanyo The Advanced Filter uses the criteria list to select records that match the criteria In this case the Field in the criteria was S
3. Criteria from more than One Column The criteria for the filter can be based on more than column or field For example with a list of houses at a Real Estate agency finding houses in a particular price range quickly is easy with the advanced filter feature 1 Click on the Home worksheet in the Lists file The worksheet should appear as shown below ofe List Area k k k 2 Click on any cell in the list area of the worksheet 3 Click on the Data tab gt Advanced The Advanced Filter box will appear and the list area will be selected as shown below Action Filter the list in place Copy to another location Lisk range Criteria range Copy to Unique records only x Cine FG 18 Microstar Publishing Pty Ltd 2010 Managing Lists of Data The list range will automatically appear in the box We must enter the cells for the Criteria Range In this sheet the criteria are already entered Remember the criteria range includes the headings 4 Click in the Criteria range box 5 Highlight the Criteria cell range A1 to B3 so it appears in the Criteria Range box 6 Click on OK Criteria Cell Range Advanced Filter Action Filter the list in place Copy to another location List range Pago C 18 Microstar Publishing Pty Ltd 2010 19 BSBITU402A Complex Excel 2010 7 Click on the tab Data
4. BSBITU402A Complex Excel 2010 34 Microstar Publishing Pty Ltd 2010
5. gt Clear to show a complete list Layout Formulas Review View Et la b Connections Ni BR T k Clear 3 2r Properties i Reapply Existing Refresh _ Z Sort Filter T Textto Remove Data Connections Aly Edit Links i ww Advanced Columns Duplicates Validation TT D E Nata Tools Practice Exercise 1 8 Advanced Filter You would now like to look at Units with a price less than 160 000 and Houses greater than 150 000 1 Click into cell B2 and type lt 160000 2 Click into cell B3 and type gt 150000 3 Click on the Data tab gt Advanced 4 Check the Criteria Range box and click on OK 5 Click on the tab Data gt Clear to show a complete list 20 Microstar Publishing Pty Ltd 2010 Managing Lists of Data Practice Exercise 1 9 Using Criteria in an Advanced Filter We will try the whole procedure again on the Sales staff worksheet Click on the Sales staff worksheet Enter the new data shown in column F Enter the following criteria as shown in cells A11 to B13 Click on the tab Data gt Advanced Repeat the procedure outlined on the previous pages to filter records according to criteria oF OQO N First Name Last Name Jim Brown 02 9941 4141 2105 2015 9561 Part time Brown 05 8956 7415 30 04 2013 3568 Part time Brown Of 7450 1696 26 11 2015 10254 Full time Flinstone 03 5328 7659 29 1 0 2014 12659 Full time James Of 5696 1254 1 07 2013 7656 Part time Rubb
6. Data 6 Click on the Add Level button 7 Click on the Then by drop list arrow 8 Click on First Name 9 Check A to Z is selected in Order Sort en Ada Leve i Syada revel XK pelete tevel a cony evel a Column os Sortby Last Name Atoz Then by Fisting atz This selection will sort the records by their Last Name and then their First Name where the last name is identical 10 Click on OK to view the sorted list A D La D First Name Last Name Phone Date Jim Brown 02 9941 4141 2105 2015 i Sally Brown 06 8956 7415 30 04 2013 4 Tom Brown 07 7450 1698 i 26 11 2015 Fred Flinstone 035326 7659 29 10 2014 Sarah James Of 5896 1254 1 07 2013 Barney Rubble 03 5226 3589 29 06 2015 Sally Slicksale Of 7450 1222 1 12 2015 Fred Whelan 02 9856 2147 18 04 2016 Microstar Publishing Pty Ltd 2010 5 BSBITU402A Complex Excel 2010 Practice Exercise 1 1 Sort a List In this exercise you will practice using the Sort buttons to organise the data in the Inventory list 1 Click on the Inventory worksheet in the Lists file 2 Sort alphabetically by Supplier 3 Sort by Retail Price from lowest price to highest price 4 Sort by product Name then by Retail Price your inventory list should appear as shown below B C D E F Good Byer Stores Australia Ltd Inventory Whitegoods department Supplier Cost No on Hand Value Retail Price Breadmaker BM200 Remington 500 3 169 0 6 Breadmaker BM4
7. Total 31 Grand Total 649290 32 Microstar Publishing Pty Ltd 2010 E Month 23 Click on the Level 3 button to display subtotals of Sales and Units for each Product for each Salesperson I 24 Click on the Plus Expand button for row 12 to display the details for the 10 12 15 18 19 25 28 29 30 31 32 Owen Total Phillips Total Richards Total Grand Total Product Photocopiers Total Printers Total Typewriters Total l Fhotocopiers Total Printers Total Typewriters Total Fhotocopiers Total Printers Total Typewriters Total Grand Total photocopiers subtotal for Phillips 25 Repeat for row 15 and 18 to display all the details for Phillips Olelals Collapse ain ir 9 Richards Total 1 Grand Total Owen Total Phillips 13 Phillips 14 Phillips 16 Phillips lf Phillips 19 Phillips Total rr i Product Photocopiers Total Printers Total Typewriters Total l Fhotocopiers Photocopiers Total Printers Printers Printers Total Typewriters Typewriters Typewriters Total Photocopiers Total Printers Total Typewriters Total Grand Total Units aa T 23 50 a EG Sales 129555 71170 10580 211305 79650 Tron 31460 166610 147755 76580 24840 249175 649290 Sales 129555 71170 10580 211305 79650 79650 23500 54200 T7700 6950 22510 31460 188810 147755 76580 24840 249175 6492
8. Total Salesperson 5 Photocopiers l 86 131 79 296 Product 6 Printers 89 131 111 331 Units 7 Typewriters 9 84 69 222 Bsa 8 Grand Total 346 259 849 Month g 10 11 Drag fields between areas below 42 W Report Filter Column Labels 13 14 15 Tt 2 w oao Hj Subtotal Sheeti Sheet2 Sheet3 s Pivot o RM i Ga Ready E Practice Exercise 1 10 Creating a Pivot Table Now to create another pivot table which summarises the monthly dollar value of sales by each salesperson for each product Select the Pivot worksheet to move back to the list Click on any cell in the list Click on tab Insert gt PivotTable in the ribbon Check that the whole list is selected and the correct cell range is in the Table Range box Aa GD N 5 Check that New Worksheet is selected so the new Pivot Table is put into a separate worksheet 6 Click on OK 32 Microstar Publishing Pty Ltd 2010 Managing Lists of Data 7 Click on the fields in this order Salesperson Month Product they will automatically be put in the Row Labels box 8 Click on the field Sales it will automatically be put into Sum Values 9 Drag the Product field over to the Column Labels box Your table should appear as below PivotTable Field List x a Choose fields to add to report 3 Sum of Sales Column Lak Row Labels Photocopiers Printers Typewriters Grand Total Owen 138675 80120 108
9. column labels in the first row to act as field names Organise the data so that each column contains the same type of data Format the column labels so they are easily identified Use a border instead of a blank row to separate the labels from the data Have only one list per worksheet Leave a blank column and row around the perimeter of the list so that any other data on the worksheet is not confused with and included in the list Do not have any blank rows or columns within the list so data is not left out Do not put any data to the right or left of the list B C D E Good Byer Stores Australia Ltd Inventory Whitegoods department lier Cost No on Hand Value Breadmaker BM200 Remington 5 169 00 Breadmaker BM4600 Sunbeam i b bh 179 00 Griller BT2500 Sunbeam 3 75 00 Iron H1540 Phillips 99 00 Kettle Af Moulinex 55 00 Kettle KE217 Sunbeam 39 95 Microwave EMA4 0 Sanyo 279 00 Microwave 6AVM350 Whirlpool 479 00 Microwave M965 samsung 239 00 Toaster 9206 Russell Hobbs 69 95 Toaster TA090 Sunbeam 39 95 Toaster CJS Moulinex 47 95 oA Microstar Publishing Pty Ltd 2010 Managing Lists of Data Sorting The ability to find and manipulate information easily and efficiently is one of the major purposes of a database Excel provides you with a number of tools that you can use to sort lists and find data Data can be quickly sorted into simple Ascending or Descending order using buttons on the ribbon Ali SotAtoz a1 So
10. data into a summary Microstar Publishing Pty Ltd 2010 29 BSBITU402A Complex Excel 2010 Create a Pivot Table from a List Now we will create our own pivot table 1 Click on the Pivot worksheet in the Lists file 2 Select any cell in the list 3 Click on the tab Insert gt Pivot Table in the ribbon Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Pt ee E Ye kbi if Fecal pO yp Gis N em s FivotTable Table Picture Clip Shapes SmartArt Screenshot Column Line Fie Bar Area Scatter Other Art 7 r r Cha rts r Tables Charts Illustrations 4 Check that the whole list is selected and the correct cell range is in the Table Range box 5 Check that New Worksheet is selected so the new Pivot Table is put into a separate worksheet 6 Click on OK Create PivotTable Choose the data that you want to analyze Select a table or range Table Range i Use an external data source Connection name Choose where you want the PivotTable report to be placed New Worksheet 0 Existing Worksheet New Worksheet Location 30 Microstar Publishing Pty Ltd 2010 Managing Lists of Data 1 The new worksheet will be displayed and the PivotTable task pane is opened as shown below Notice that the Task pane lists the fields from the original table The lower part of the task pane is used to organise the structure of the PivotTa
11. 230 327025 V Product May 95655 24650 10580 130885 Units June 33900 46520 80420 W Sales July 9120 8950 97650 115720 Month Phillips 90304 169200 31460 290964 May 23500 8950 32450 June 79650 54200 22510 156360 ra a July 10654 91500 102154 ee E ee 13 SRichards 147755 112449 71360 331564 14 May 56255 34900 8950 100105 15 June 91500 44680 15890 4149070 16 Jul 35869 46520 82389 17 Grand Ea 376734 361769 211050 949553 mE Bees 18 Salesperson SumofSales 19 20 71 wa bl Subtotal Sheeti Sheet Sheet3 Pivot RE Ready BS 10 Drag the button for Month to appear after Product 11 Drag the button for Product to appear before Salesperson Now the data will be summarised by product then by salesperson for each month Your PivotTable should appear as follows PivotTable Field List a Choose fields to add to report 3 Sum of Sales Row Labels May Jul Grand Total amp Photocopiers 151910 ITM Ei Prechuct Owen 95655 33900 138675 Units Phillips 79650 90304 v Sales Richards 56255 91500 5E Month 3 Printers Owen Phillips 235 5420 91500 Drag fields between areas below Richards 3450 1168 35869 1244 y Report Filter Column Labels 13 S Typewriters 46 144170 Month Owen 05 97650 Phillips Richards G 5A 46520 Grand Total 300263 Ready B 12 Save the file and close Microstar Publishing Pty Ltd 2010 33
12. 600 Sunbeam 660 3 179 0 7 Griller BT2500 Sunbeam 5 150 5 75 0 l a iron HI540 Phillips h g 99 0 9 Kettle KE 17 sunbeam 39 95 10 Kettle A Moulinex 55 0 5 lt ae t 11 Microwave 965 Samsung 239 0 12 Microwave Sanyo 279 0 13 Microwave 6AVM350 Whirlpool 479 0 14 Toaster TA090 Sunbeam 39 95 15 Toaster C19 Moulinex 47 95 16 Toaster 9206 Russell Hobbs 69 95 17 ho in Practice Exercise 1 2 Create a New Database List and Sort In this exercise you will create your own list You can use your own items e g family member details product details sporting club memberships etc Click on a blank worksheet Create a list of your own that contains at least five fields columns Enter five records Aa GQ N Double click on the sheet tab and type Custom List to give the worksheet a name Click back onto the worksheet O Ol save the file when finished 7 Sort the records by each of the columns 6 Microstar Publishing Pty Ltd 2010 Managing Lists of Data Filter a Data List The Filter feature enables you to view only those records that match your criteria This is especially handy when you have a large number of records Filter a List Suppose you want to display only those records with the last name Brown 1 Click on the Sales staff worksheet and click on any cell within the list area 2 Click on the tab Data gt Filter vout Formul
13. 90 Managing Lists of Data Month As a plus or expand button is clicked it becomes a minus or collapse button Microstar Publishing Pty Ltd 2010 BSBITU402A Complex Excel 2010 26 Click on the collapse button at row 10 for Owen and row 29 for Richards to close each of the details for the other salespeople Using the collapse and expand button you can display any combination of subtotals 1 Salesperson Product sales Month Owen Total 211305 Phillips Photocopiers 79650 Photocopiers Total 79650 Phillips Printers 26 235500 Phillips Printers 13 54200 Printers Total FFFOO Phillips Typewriters 0950 Phillips Typewriters 22510 Typewriters Total 31460 Phillips Total 1665910 Richards Total 249175 Grand Total 31 Grand Total 649290 1 1 1 3 1 1 1 1 k Remove all Subtotals 1 Click on any cell within the list 2 Click on tab Data gt Subtotals in the ribbon and click on Remove All The list will have the subtotals removed 28 Microstar Publishing Pty Ltd 2010 Pivot Tables Managing Lists of Data Just wait until you can see what you can do with a Pivot Table No more tedious summarising of endless worksheets and data the Pivot Table does it all for you You can go from this list of data to this organised pivot table in seconds Product Photocopiers Photocopiers Printers Printers Printers Typewriters Typewriters Typewriters Photocopiers Photocopiers Photocop
14. Managing Data Lists Chapter Excel has some excellent features that make working with lists easy Lists of records can be sorted searched and filtered to produce useful information Good Byer Stores Australia Ltd Inventory Whitegoods department Name Code Supplier Cost No on Hand Value Retail Price Kettle Toaster Toaster Toaster Breadmaker Breadmaker Kettle Griller AZ1 9206 TA090 CJ9 BM200 BM4600 KE217 BT2500 Moulinex Russell Hobbs Sunbeam Moulinex Remington Sunbeam Sunbeam Sunbeam 350 480 450 420 500 660 252 150 55 00 69 95 39 95 47 95 169 00 179 00 39 95 75 00 800 600 900 275 279 00 479 00 239 00 99 00 Microwave Microwave Microwave Iron EMX470 Sanyo 6AVM350 Whirlpool M965 Samsung HI540 Philips PHAPHAHAPAPAPAPAHAHAHAH SH Objectives In this module you will do the following using Microsoft Excel Construct a List Sort Data Filter Data Use Advanced Filters Apply Subtotals Work with Outlines e Create a Pivot Table 1 Open the Lists workbook from your student files Microstar Publishing Pty Ltd 2010 1 BSBITU402A Complex Excel 2010 Tips for Creating Data Lists Keep the following points in mind when creating a data list or rearranging data to create a data list 6 T 6 9 10 11 12 13 14 15 16 17 Use
15. alesperson 13 Richards Photocopiers Ae 14 Richards Printers Units 415 Richards Printers Typewriters Month Typewriters Replace current subtotals F Page break between groups Summary below data The Use function box contains a list of functions we can use in creating a subtotal The following functions return a value for each group in a field Sum Adds numeric values Count Counts the number of occurrences Average Calculates the average Max Displays the maximum value Min Displays the minimum value The Add subtotal to box contains a list of the fields the one selected is that which will have the function applied to it For example if we want a subtotal of sales we would select Sales here If we wanted to count the number of sales a salesperson had made we would use the Salesperson field 13 Click on the droplist button for Use function and click on Sum 14 Click on Sales in the Add Subtotal to section and clear the other boxes 15 Click OK turn the page to check the results 24 Microstar Publishing Pty Ltd 2010 Managing Lists of Data The subtotals appear within the list at each change in salesperson ee ee D E Salesperson Product Units Sales Month Owen Photocopiers 53 95655 May Owen Photocopiers 35 33900 June Owen Frinters Z 24650 May Owen Printers 4 46520 June Owen Typewriters A 10550 May Owen Total 211305 amp Fhillips Photocopiers l 79650 June Phillips Printers 23500 May Phillip
16. as The records will now be displayed as shown below with Filter buttons appearing for each field The Filter buttons display lists which contain options by which you can filter the data 3 Click on the Filter button for each field to see the options available The picture below shows the Filtering Criteria available for First Name 14T 18 04 20 141 2 05 2015 689 8 29 06 2015 sort by Calor 659 29 10 2014 254 1 07 2013 eee 1 12 2015 698 1 26 11 2015 Text Filters t 415 30 04 2013 rol Select All re Brown 1 i A B D 1 First Name Last Name Phone Date Filter button F Cancel Microstar Publishing Pty Ltd 2010 7 BSBITU402A Complex Excel 2010 Suppose we want to display only those records where the last name is Brown 4 Click the Filter button for the Last Name field which is to be filtered 5 Click on the Select All button to remove the tick 6 Click on Brown so a tick appears 7 Click on OK The Filter symbol appears next to the filtered list this is to show the user that this information is currently filtered using this field A B 1 First Name Last Name n 1 First Name Last Name Phone SortAtoZ sort Z to A Sort by Color Text Filters Search of Select All iW Barney lv Fred 2 Jim Brown g2 9941 4141 2 05 2015 3 Sally Brown 60956 7415 30 04 2013 4 Tom Brown T 7450 16968 26 11 2015 10 Your records are
17. ble The PivotTable task pane is only displayed while the active cell is within the pivot table Choose fields to add to report 4 a Salesperson 5 P ivo tTa bl amp 4 F Product 5 To build a report choose fields laa T from the PivotTable Field List ia PivotTable 5 Month Task pane Drag fields between areas below Report Filter 2 Column Labels i gt gt Subtotal Sheet2 Sheet RW aM Ready Sooo The order you add fields to the structure determines the layout of the PivotTable 7 Click on the field Product then Month they will automatically be put in the Row Labels box 8 Click on the field Units it will automatically be put into Sum Values PivotTable Field List zx Choose fields to add to report Photocopiers ay M an pil v Units July Sales 8 amp Printers V Month May June July 12 Typewriters May June July Grand Total a a bi Subtotal Sheeti Sheet2 Pivot s Sheetil fR i Ready Microstar Publishing Pty Ltd 2010 31 BSBITU402A Complex Excel 2010 9 In the Pivot Table Task Pane drag the Month field button from the Row Labels over to the Column Labels box Your PivotTable should appear as follows summarising the number of units sold for each product in each month E ne Choose fields to add to report 3 Sum of Units Column Labels __ 4 Row Labels May June July Grand
18. e Custom List worksheet you created in exercise 1 2 on page 6 2 Experiment with Filtering on your own list Microstar Publishing Pty Ltd 2010 9 BSBITU402A Complex Excel 2010 Set a Custom Filter Suppose you wanted to show a group of records e g Sales greater than 10000 you can set a Custom Filter 1 Click on Sales staff sheet and click on any cell within the list 2 Add a new field for Sales and enter the data as shown below A B C First Name Last Name Phone Date Jim Brown 02 9941 4141 2105 2015 9561 Sally Brown 08 6956 7415 30 04 2013 3568 Tom Brown 07 7450 1698 26 11 2015 10254 Fred Flinstone 03 5328 7659 29 10 2014 12859 Sarah James Of 5896 1254 17 07 2013 7556 Barney Rubble 03 5226 3589 29406 2015 9461 Sally Slicksale Of 7450 1222 1 12 2015 6500 Fred Whelan 02 9856 2147 18 04 2016 10580 al L 2 3 4 6 7 a g 3 Click on a cell within the list and click on the tab Data gt Filter 4 Click on the Filter button for Sales 5 Click on Number Filters gt Custom Filter A B C First Name Last Name Phone Jim Brown Al Sort Smallest to Largest 3 Sally Brown z Tom Brown ii sort Largest to Smallest gt Fred Flinstone sort by Color 6 Sarah James Barney Rubble sally slicksale Fred Whelan d Does Not Equal p Greater Than J Greater Than Or Equal To _ Less Than ee am Less Than Or Equal Ta 3 Between a Top 10 Above Average Below Average Custom Fil
19. el 2010 Conditional Sorting The Conditional Sorting method enables you to arrange your data ina customised manner For example if you want to sort the data by Last Name and then by First Name when more than one record has the same last name 1 Click on the Sales staff worksheet and click on any cell within the list 2 Click on the tab Data gt Sort File Home Insert Page Layout Formulas Data ie a i ms l Connections lie Sf Properties From From From From Other Existing Refresh Access Web Text Sources Connections Ally Edit Links Get External Data Connections The Sort dialog box is displayed as shown below and the entire list has been selected Notice that the header row in the table is not selected because Excel has assumed the first row is a heading row 3 Click on the droplist button of the Sort by box to display the list of headings 4 Click on Last Name to choose it from the list 5 Check that A to Z appears in Order F 1 2 Sally Brown 08 8956 7415 30 04 2013 3 Sarah James Of 5896 1254 1 07 2013 4 Fred Flinstone 03 5326 7659 29 10 2014 5 Jim Brown 02 9941 4141 2 05 2015 5 Barney Rubble 03 5226 3589 29 06 2015 i Tom Brown Of 7450 1696 26 11 2015 a Sally Slicksale 07 7450 1222 1 12 2015 9 Fred Whelan 02 9856 2147 18 04 2016 FirstName Last Name Phone Date I ort Column Sort On Sortby fastWam Values 4 Microstar Publishing Pty Ltd 2010 Managing Lists of
20. iers Printers Printers Printers Typewriters Typewriters Photocopiers Photocopiers Printers Printers Printers Typewriters Typewriters Month Units Sales 33 56255 May 53 95655 May 2a 35 34900 Mlay 26 24650 Mlay E EEEE A ON 28 23500 May C 22 8950 Mlay ee 23 10580 May 24 8350 Mlay 35 33900 June 46 41500 June QE sae d 50 79650 June Da y 44 46520 June D d 44 41680 June r 43 4200 June 42 15890 June June 42 22510 Month Salesperson Photocopiers Printers Typewriter Grand Tota May Owen 95655 Z24650 10560 130655 Phillips 23500 350 32450 5 Richards 56255 34900 A950 100105 6 May Toa 15190 e3050 26460 263440 a June Owen 33900 46520 a04 20 B Phillips 9650 54200 22510 156360 9 Richards 91500 41660 15690 149070 205050 142400 36400 41 July Ower 9120 ogsi gresi 115720 Phillips 10654 91500 102154 Richards 35064 46520 02309 14 July Total 19FF74 136319 144170 300263 15 Grand Total 376734 361769211060 A pivot table is most useful when data within some of the columns is out of a few values eg the product is one of three machines the salesperson is either Owens Phillips or Richards The pivot table can be summarised easily around these distinctive categories Pivot tables work best with lists of data where there is a clear list of records with obvious headings as in the list above The headings of each column are used to organise the
21. le 03 5276 3569 29 06 2015 9461 Full time Slicksale Of T450 1222 1 12 2015 6500 Part time 02 9656 2147 16 04 2016 10580 Full time Position Advanced Filter erm Part time gt 5000 13 Full time gt 10000 ix Filter the list in place Copy to another location The list should appear as shown If it doesn t check all your data entry very carefully i B C D E F First Name Last Name Phone Date Sales Position Jim Brown 02 9941 4141 2 05 2015 95671 Part time Tom Brown Of 7450 1696 26 11 2015 10254 Full time Fred Flinstone 03 5326 7659 29 10 2014 12059 Full time Sarah James 07 5896 1254 1 07 2013 7056 Part time Sally Slicksale Of 7450 1222 1712 2015 6500 Part time Fred Whelan 02 9656 2147 18 04 2016 10560 Full time 6 Click on tab Data gt Clear in the ribbon show a complete list Microstar Publishing Pty Ltd 2010 21 BSBITU402A Complex Excel 2010 Subtotaling Excel provides us with an automatic subtotalling feature for use with lists 1 Click on the Subtotal worksheet in the Lists file The Subtotal feature will put subtotals for each particular group of items in a list For example as shown below if we had a list of sales by salespeople we could sort the list so that each record for a particular salesperson was grouped together Then the list could be subtotalled so the total sales for each salesperson are displayed Salesperson Owen Owen Owen Owen Owen T Owen Total Phillips Phillips Phillip
22. lor n Select All M102 9856 2147 W102 9941 4141 ar 03 5226 3589 W03 5328 7659 w 07 5896 1254 ocd OF 7450 1222 W107 7450 1698 W08 8956 7415 D Date 2 05 20 30 04 2013 26 11 2015 10254 29 10 2014 12059 1 07 2013 7856 29 06 2015 9461 V1 P 0145 nO Equals Does Not Equal Begins With Ends With Contains Does Not Contain Custom Filter Microstar Publishing Pty Ltd 2010 Custom Filter BSBITU402A Complex Excel 2010 The Custom AutoFilter window will appear 6 Click in the second box and type 03 7 Click on OK Show rows where Phone equals 7 03 and Oo Use to represent any single character Use to represent any series of characters This will bring up a list of all records with phone numbers that begin with 03 B D E A C 41 First Name Last Name Date Sales 5 Fred Flinstone 035326 7659 29 10 2014 12659 Barney Rubble 03 5226 3589 29 06 2015 9461 if 8 Click on the Filter button in the ribbon to remove the filter and display all records Practice Exercise 1 6 Use a Wildcard in Filtering 1 Click on the Inventory worksheet 2 Use the Custom filter to display products from supplier Mol something can t remember the spelling so use Equals and Mo E Good Byer Stores Australia Ltd Inventory Whitegoods department No on Hai Value Retail Kettle A Moulinex 5 35 10 350 55 00 16 T
23. now filtered as shown above Show all Data To remove a filter that has been applied is simple 1 Click on the Last Name filter button and choose Select All to display all the records again 2 Click on OK 3 Repeat this process of filtering on some of the other fields finishing with all records displayed A 1 First Name Last Name Phone j SortAtoz Z SontZtoA Sort by Color Text Filters sM Select All Select All W Brown _ Flinstone ond James Rubble 2 Slicksale i7 Whelan cane 8 Microstar Publishing Pty Ltd 2010 Managing Lists of Data Turn the Filter Off When finished the filter buttons can be turned off 1 Click the tab Data gt Filter r Filter This will turn the filter off Ji E Connections Eile Properties Refresh Alr Edit Links Practice Exercise 1 3 Filtering Lists 1 Click on the Inventory worksheet 2 Use the Filter button to display only the products supplied by Sunbeam ai A B C D E F Good Byer Stores Australia Ltd Inventory Whitegoods department 3 4 Name p ost 6 Breadmaker BM4600 Sunbeam 11 6 gt 660 179 00 T Griller BT2500 Sunbeam 5 3 6 150 75 00 10 Kettle KE 17 Sunbeam 20 g 252 39 95 16 Toaster TAOSO Sunbeam 3 15 gt 450 f 39 95 3 Display All records by clicking on the Filter button to turn the filter off Practice Exercise 1 4 Filter Your Custom List 1 Click on th
24. oaster cjg Moulinex 35 12 420 AT 95 3 Click on the Filter button in the ribbon to remove the filter and display all records 4 Leave the file open when finished it will be used in the next section 14 Microstar Publishing Pty Ltd 2010 Managing Lists of Data Advanced Filters Advanced Filters can be used when there is more than one criteria For example looking for products from two different suppliers or a particular product from a specific supplier Several Conditions in One Column Criteria Range More than one condition can be applied to a column To do this you must set up the criteria on the spreadsheet below the list 1 Click on the Inventory worksheet in the Lists file 2 In cell A18 type the field name Supplier 3 Type the suppliers you are looking for Sunbeam in cell A19 and Sanyo in cell A20 4 Click on any cell in the main list 5 Click on the tab Data gt Advanced in the Sort amp Filter group Home Insert Page Layout Formulas Data Review View fy FES FED 5 gt Ea Connections 4 BR F jal i fu F A EF g eee 2y 21 ll From From From From Other Existing Refresh Z Sort Filter Access Web Text Sources Connections Al Can Links _ iet External Data iy Advanced _ Sort amp Filter Connections The Advanced Filter dialog box will appear with the range of the list already in it Inventory Whitegoods department Su
25. plist button for Order for Month and click on Zto A 0 Click on OK when ready Photocopiers Photocopiers Printers Printers Printers Typewriters Typewriters Typewriters Column sala Printers Then b z Frinters iui Product w Printers M Typewriters Typewriters ty Advanced columns Once the list is sorted a subtotal can be calculated for each group Microstar Publishing Pty Ltd 2010 23 BSBITU402A Complex Excel 2010 11 Click on any cell within the list 12 Click on tab Data gt Subtotal in the Outline group on the ribbon roy POE SHEL P ee yg What lf Group Ungroup Subtotal Analysis i i The Subtotal dialog box will appear and the whole list will be highlighted as shown below The At each change in box indicates to Excel how we want the data grouped Excel assumes we wish to create subtotals for each salesperson as it is in the first column If we click on the droplist button at the end of the box the other fields will be available to choose Product Units Sales Month Photocopiers 53 95655 May Photocopiers 35 33900 June 26 24650 Frinters La Frinters Subtotal Typewriters illips Photocopiers t each change in At each Phillips Printers illi Frinters Use function change In Typewriters sur ses Add subtotal to 12 Richards gd subtotal to a OO S
26. s Phillips Product Photocopiers Photocopiers Printers Printers Typewriters Photoc o piers l Printers Printers Typewriters Typewriters 3 Phillips Total Richards Richards 6 Richards Richards 5 Richards Richards Richards Total 21 Grand Total Photocopiers Photocopiers Printers Printers Typewriters Typewriters 22 Microstar Publishing Pty Ltd 2010 Units 53 35 26 44 23 50 26 4a 24 A 33 46 35 44 22 42 o D Sales 95655 33900 24650 46520 10550 211305 9650 23500 54200 0950 22510 166910 56255 91500 34900 41630 J50 15590 249175 649290 E Month May June May June May June May June May June May June May June May June Managing Lists of Data Before using the subtotal function the list should be sorted according to the item for which you want to create subtotals 2 Click on any cell in the Salesperson field 3 Click on the tab Data gt Sort Page Layout Formulas Review View a Lb Connections piaz Pu Clear LI Fas FA T rf Properties E Reapply ae Existing Refresh Z Sort Filter Text ta Connections Alls Edit Links 4 Click on the first Sort by box droplist button and click on Salesperson 5 Click on Add Level 6 Click on the droplist button for the Then by box and click on Product 7 Click on Add Level 8 Click on the droplist button for the Then by box and click on Month 9 Click on the dro
27. s Printers 1 54200 June Phillips Typewriters 2i 0950 May 2 Fhillips Typewriters 4 22510 June 3 Phillips Total 165510 Richards Photocopiers 56255 May Richards Photocopiers 91500 June Richards Printers X 34900 May r Richards Printers 4 41650 June Richards Typewriters oe 0950 May Richards Typewriters A 15890 June Richards Total 249175 Grand Total 649290 We can add more subtotals Suppose we wanted to calculate the number of units of each product type a salesperson had sold We can still keep the other subtotal information and add this to it 16 17 18 19 20 Click on the tab Data gt Subtotals Click on At each change in and click on Product Click on the droplist button for Use function and click on Sum Click on Units in the Add Subtotal to section Clear the Replace current subtotals box this keeps the original subtotal of Sales on the sheet Subtotal t each change in Product Product Use Function Sum Add subtotal to Salesperson Product Sales Month Replace current subtotals Page break between groups Microstar Publishing Pty Ltd 2010 25 BSBITU402A Complex Excel 2010 Your sheet will now show both Sales subtotals and Units and Sales subtotals of the number of units sold for each Product Outline symbols Notice that the B worksheet now has 2a __ et A 2 Owen Photocopiers the Outline symbols to 3 Owen Photocopiers the left Photocopiers Total 5 O
28. tZtoA Each atfected row is moved and sorted retaining the whole record as a complete row When sorting a list DO NOT select a whole column only select one cell otherwise the data will be scrambled 2 Open the Lists workbook from your student files 3 Check the Sales staff worksheet is displayed 4 Click on cell A2 within the list 5 Click the tab Data gt Sort A to Z the list will be sorted in alphabetical order by first name 6 Click on cell D2 7 Click on Data gt Sort Z to A button the list will sorted in descending order by date 8 viene the tab Data gt Sort A to Z the list will be sorted in ascending order y agate Home Insert Page Layout Formulas Data Review View From From From From Other Existing Refresh Access Web Text Sources Connections Alle Car tin P EA Connections Rif a Ohm la ie Get External Data Co nnections D2 fe 30 04 2013 A B i C Note Some Sort amp Filter 1 FirstName LastName Phone buttons are also available 2 Sally Brown 08 8956 7415 30 04 2013 o the Home tab We will Sarah James OF 5696 1254 1 07 2013 use the Data tab because Fred Flinstone 03 5328 7659 29 10 2014 it includes the Advanced Jim Brown 02 9941 4141 2 05 2015 button which is not on the Barney Rubble 03 5226 3589 29 06 2015 Home tab Tom Brown OF 7450 1698 26 11 2015 Sally Slicksale OF 450 1222 1 12 2015 Fred Whelan 02 9856 2147 16 04 2076 Microstar Publishing Pty Ltd 2010 3 BSBITU402A Complex Exc
29. ter 5 f 10 Microstar Publishing Pty Ltd 2010 Managing Lists of Data The Custom Filter window will appear 6 Click on the droplist button for the first box The possible operators will be displayed 7 Click on is greater than 8 Click in the next box and type 10000 Custom AutoFilter Custom AutoFilter Show rows where Sales is is greater than Y isgreaterthan w 000 Show rows where Sales Use to represent any single character Use to represent any series of characters 9 Click on the OK button Notice that only the records for sales greater than 10000 are displayed Notice also that the Filter button for Sales has a small filter symbol to indicate that the filter is applied to this field P A B L D 1 First Name Last Name hi Phone Date Sales A A Tom Brown Of 7450 1698 26 11 2015 10254 5 Fred Flinstone 03 5328 7 659 29 10 2014 12659 9 Fred Whelan 02 9856 2147 18 04 2016 10580 10 Repeat for Sales less than 10000 Custom AutoFilter Show rows where Sales Use to represent any single character Use to represent any series of characters 11 Display All records Microstar Publishing Pty Ltd 2010 11 BSBITU402A Complex Excel 2010 Practice Exercise 1 5 Set a Custom Filter 1 Click on the Inventory sheet 2 Use the Custom Filter to display only those products with a retail price less than 60 D Good Byer Stores Australia Ltd Inventory Whitegoods department 1
30. upplier and the criteria were Sunbeam and Sanyo So the Advanced Filter selects records that have either Sunbeam or Sanyo in the Supplier field Remove an Advanced Filter 1 Click on the tab Data gt Clear in the Sort amp Filter group Layout Formulas ita view _ Connections rm k Clear ma a r Properties i gt Reapply Existing Refresh re Sort Filter y T Tetto Remove Data Connections All Edit Links ww Advanced Columns Duplicates Validation 16 Microstar Publishing Pty Ltd 2010 Managing Lists of Data Practice Exercise 1 7 Use an Advanced Filter In this exercise you will enter another set of criteria this time to display all toasters and breadmakers 2 Click into to cell A18 and type Name 3 Click into cell A19 and type Toaster then cell A20 and type Breadmaker 4 Click on a cell in the list 5 Click on the tab Data gt Advanced in the Sort amp Filter group 6 Click on OK B C D E F Good Byer Stores Australia Ltd Inventory Whitegoods department Code Supplier Cost No on Hai Value Retail pric 5 Breadmaker BM200 Remington 100 5 4 169 00 Breadmaker BM4600 Sunbeam 110 E 4 179 00 Toaster clo Moulinex 35 1 47 95 15 Toaster 9706 Russell He AQ g 69 95 Toaster TAOSO Sunbeam 30 4 39 95 Name 5 Toaster Breadmaker 7 Click on the tab Data gt Clear to clear the filter Microstar Publishing Pty Ltd 2010 17 BSBITU402A Complex Excel 2010
31. wen Printers The Outline feature 6 Owen Printers Printers Total allows us to collapse eee oc Sa the worksheet to J various levels 10 Owen Total Phillips Photocopiers This worksheet has Fhotocopiers Total four levels Phillips Printers Phillips Printers Level 4 shows the Printers Total complete list 46 Phillips Typewriters 17 Phillips Typewriters Level 3 shows the 16 Typewriters Total Phillips Total subtotals for Units 20 Richards Fhotocopiers for each person 21 Richards Photocopiers 22 Photocopiers Total Level 2 shows the 23 Richards Printers subtotals for Sales 24 Richards Printers 25 Printers Total for each 26 Richards Typewriters salesperson 2f Richards Typewriters 6 Typewriters Total Level 1 displays 29 Richards Total only the Grand it Grand Total Totals 31 Grand Total D Sales 95655 33900 179555 24650 46520 71170 10560 10550 211305 79650 79650 23500 54200 T7700 a950 22510 31460 188810 56255 91500 147755 34900 41660 76560 a950 15890 24840 249175 649290 21 Click on the Level 1 button to display only the Grand Total for Sales d A E C D 1 Salesperson Product Units Sales 30 Grand Total 590 31 Grand Total 649290 32 E Month 22 Click on the Level 2 button to display only the Sales totals for each 26 salesperson Salesperson Product Sales 10 Owen Total 211305 19 Phillips Total 188810 29 Richards Total 249175 30 Grand
Download Pdf Manuals
Related Search