Microsoft Office/Create a spreadsheet with lists

Objectives Create lists in a spreadsheet Validate Data Add Computational Fields to a list Create a VLOOKUP function Examine Data with the Toggle Total Row Print a list Sort a list Organize a spreadsheet with groups and outlines Query a list Create a COUNTIF function Save to different file formats Vocabulary List - a database or collection of organized data Computational Field - displays results based on other fields VLOOKUP - determine the value of one cell based on a table of data, you are looking up the value in a different table Data Validation - the data you enter into a cell is within the limits you set. Example: gender can only be M or F and not male or female. Table Array - A smaller table used in a spreadsheet to store data you will look up later - like a grade to percentage look up table. Table Arguments - the left most column in the array that lists the limiting values - 0%, 60%, 70%, 80%, 90% Table Value - the value that is returned so if you look up 63% it would return a value of D. major sort key - the first item that a table is sorted by - Sorted By Intermediate sort key - The second item that a table is sorted by - Then By Minor sort key - The last item that is sorted by - Then By Outlining features - Gives the user the ability to hide and then unhide detail rows Row Level Symbols - Numbers at the top that signify what level of detail should be displayed Show detail symbol - a plus sign is used to expand the list and show the details Hide detail symbol - a minus sign is used to collapse the list and hide the details Row Level bar - a vertical bar is displayed to connect items of the same level Wildcards - characters that allow you to find records that contain certain characters in a field. Asterisk - used to represent any number of characters - A* means any set of characters that starts with A Question mark - used to represent one character - A? means any set of two characters where the first one is an A AND Operator - used to mean that both parts of the criteria are true OR Operator - used to mean that one or both parts of the criteria are true Extract Range - When you use a set of criteria to only look at one set of the data, the location where those records are sent is called the extract range. Lesson Create a List/Table Titles A7 = Dogs R Us - Breeding Service Center from A7:J7 A9 = Owner B9 = Breed C9 = Gender D9 = Welp Date E9 = State F9 = Region G9 = Prize Money won H9 = Potential Money Earned I9 = % Potential J9 = Grade Create a List/Table Highlight the titles Go to the Insert Tab Go to the Tables Group Go to TABLE Set Data Validation Go to cell C10 Go to the Data Tab Go to the Data Tools Group Click on Data Validation Go to the Settings Tab Go to the ALLOW field Choose LIST The only check box checked should be IGNORE BLANK Go to the SOURCE field Type F, M Go to the ERROR ALERT tab Check the box - SHOW ERROR ALERT AFTER INVALID DATA IS ENTERED Go to the STYLE field Choose STOP Go to the TITLE field Type GENDER INVALID Go to the ERROR MESSAGE field Type: GENDER CODE MUST BE AN F OR M. Click on OK Type in the data Type in the data from the following table + Dogs R Us - Breeding ServiceABCDEFGHIJ9OwnerBreedGenderWelp DateStateRegionPrize MoneyPotential% of PotentialGrade10Bull, SGolden RetrieverF11/14/2007CONW$10,000$12,00011Welsh, TManchester TerrierF5/22/2006PANE$2,000$4,00012Highland, WMiniature SchnauzerM5/17/2007FLSE$6,000$8,00013Clark, MSamoyedF5/1/2006MDNE$54,000$50,00014Westminster, KStandard PoodleM11/1/2006NYNE$20,000$40,00015Hampton, HTibetian MastiffF11/15/2005CONW$7,000$15,00016Ford, LScottish TerrierM5/4/2006CASW$250,000$200,00017Pedier, SWhippetM11/30/2005WYNW$500$2,00018Bistle, VToy PoodleM5/27/2007MTNW$11,500$20,00019Miller, BBrittany SpanielF4/30/2003PANE$65,000$70,00020Baker, NPuliM11/8/2007NMSW$1,000$3,000 Create special Fields Computational Fields Create an Age column Click on the E column Header Go to the HOME Tab Go to the CELLS group click on Insert click on cell E9 Type: Age (years) Click on cell P2 Use the NOW function for today's date =now() Click on cell E10 Do a subtraction from today's date and the welp date to find out how old the dog is =($P$2-D10)/365 notice the absolute reference we divide by 365 to get number of years Notice the column fills in for you format as a general number with two decimal places Fill in the % of Potential column Go to cell J10 divide the money earned by the estimated amount =H10/I10 Notice the column fills in for you Format as a percent with no decimal places Create a Lookup table type in the table below: + Newsletter RubricMN1Grade Table2% of QuoteGrade30%F460%D570%C680%B790%A Create a VLOOKUP field Go to cell K10 enter the function for a VLOOKUP =vlookup(I10,$M$3:N$7,2) This uses the %of Potential from column J, then goes to the table M3:N7, and takes the second column value to fill into the field. Notice you do not need to fill down Creating a Total Row Turn on the Total row Click in the table anywhere Go to the TABLE TOOLS - Design tab Go to the Table Style Options group Click the check box for TOTAL ROW Create totals for Columns Prize Money Go to cell H21 Click the drop down arrow Choose SUM Potential Go to cell I21 Click on the drop down arrow Choose SUM Create Averages for columns Age(years) Click on cell E21 Click on the drop down arrow Choose Average Create Count for number of items in a column Grade Go to cell K21 Notice that the number 11 is there Click on the drop down arrow Notice that the COUNT is choosen Viewing Records in the Table Convert to Normal Spreadsheet Click anywhere in the table Go to the TABLE TOOLS - Design tab Go to the Tools group Click on Convert to Range Sorting Sorting means putting the records in order. This can be done from the drop down arrows at the top of each column. Sort Alphabetically by Owner Go to cell A9 click the drop down arrow Choose SORT A TO Z Sort Numerically by Age Go to cell E9 click the drop down arrow Choose SORT LARGEST TO SMALLEST This will put the dogs in order from oldest to youngest. Sort by multiple items Click anywhere in the table Go to the DATA tab Go to the SORT & FILTER group Click on the SORT button Fill in the first row Click on the Add level to add more items to filter on Limiting Limiting is a feature that lets you look at only a small set of the data. Limit to all dogs from the NW Go to cell G9 click on the drop down arrow Click the Select All check box to deselect all click the check box for NW Click on OK Undo that when you are done Limit by prize money won at or over $20,000 Go to cell H9 click the drop down arrow Click on Number Filters - Greater than or equal to In the blank box in the top right of the window click the drop down and choose $20,000 click OK Clear filters - click the drop down and choose clear filters. Creating Subtotals Subtotals Sort by Region Click on cell G9 Click the drop down Click on SORT A TO Z Take the data out of a table click in the table anywhere Go to the DESIGN tab Go to the TOOLS group Click on Convert to Range Click on YES Select the range for the data cells A9:K20 Add Subtotals Go to the DATA tab Go to the OUTLINE group click on Subtotal At each change in: choose REGION Use function: SUM Add subtotal to: PRIZE MONEY and POTENTIAL check REPLACE CURRENT SUBTOTALS check SUMMARY BELOW DATA Click on OK Notice there is a new row under each change in region that gives the subtotals Outline View Look at the far left of the spreadsheet and you should see the outline groups. Row level Symbols Go to the upper left of the window below the cell name and function bar See the boxes with the numbers 1, 2, 3 click on the 1 and notice you only get the grand total Click on the 2 and notice you get each region and the grand total Click on the 3 and notice you get all the detail Row level bars See the brackets [ on the far left side of the window These show items that are at the same level the set to the right are for each region the set to the left is for the grand total show detail symbol When the details are hidden there should be a + on the far left. click on that to expand that section. Hide detail symbol When the details are being shown you can click on the - sign to hide the detail for that section. Remove subtotals Go to the Data tab Go to the Outline group Click on the Subtotal Button Go to the bottom of the popup window Click on the REMOVE ALL button Querying Records Finding Records Find all the male dogs. Go to cell C9 Click on the drop down arrow Check only the box for M Click on OK Find the dogs that are between 2 and 3 years old Clear the filter for gender Go to cell C9 Click the drop down arrow Click CLEAR FILTER FROM 'GENDER' Go to cell E9 Click on the drop down arrow Click on the Number Filters Click on BETWEEN Fill in 2 in the top field Change the bottom Criteria to IS LESS THAN Fill in 4 in the bottom field Make sure the radio button is set for AND Click OK Clear the filter when you are done. Forms Create a Form Add the form button to the quick access toolbar Click the more button on the quick access toolbar click on MORE COMMANDS Under Choose Commands From - choose ALL COMMANDS Click on Form Click on ADD Click on OK Setting a Criteria Range Titles Copy A8:J9 The headings Paste in cell A1 Change the title to Criteria Area Create Criteria In row three place criteria to filter the table by. Age >3 Gender = M Grade >C Advanced filter Command Click in the table to activate it - Cell A10 Go to the DATA tab Go to the Sort & filter group Click on Advanced make sure the Action radio button is FILTER THE LIST, IN PLACE Make sure the list range is the whole table Click in the field for CRITERA RANGE Click and drag from A2:J3 Click OK Extracting Records Copy A8:J9 The headings Paste in cell A26 Change the title to Extract Area Click cell A10 to activate the table Go to the DATA tab Go to the Sort & filter group Click on Advanced Make sure the Action radio button is COPY TO ANOTHER LOCATION Make sure the list range is the whole table Make sure the Criteria Range is from A2:J3 Click in the COPY TO field Click and drag A27:J27 Click OK Project Rubric