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 Service

	A	B	C	D	E	F	G	H	I	J
9	Owner	Breed	Gender	Welp Date	State	Region	Prize Money	Potential	% of Potential	Grade
10	Bull, S	Golden Retriever	F	11/14/2007	CO	NW	$10,000	$12,000
11	Welsh, T	Manchester Terrier	F	5/22/2006	PA	NE	$2,000	$4,000
12	Highland, W	Miniature Schnauzer	M	5/17/2007	FL	SE	$6,000	$8,000
13	Clark, M	Samoyed	F	5/1/2006	MD	NE	$54,000	$50,000
14	Westminster, K	Standard Poodle	M	11/1/2006	NY	NE	$20,000	$40,000
15	Hampton, H	Tibetian Mastiff	F	11/15/2005	CO	NW	$7,000	$15,000
16	Ford, L	Scottish Terrier	M	5/4/2006	CA	SW	$250,000	$200,000
17	Pedier, S	Whippet	M	11/30/2005	WY	NW	$500	$2,000
18	Bistle, V	Toy Poodle	M	5/27/2007	MT	NW	$11,500	$20,000
19	Miller, B	Brittany Spaniel	F	4/30/2003	PA	NE	$65,000	$70,000
20	Baker, N	Puli	M	11/8/2007	NM	SW	$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 Rubric

	M	N
1	Grade Table
2	% of Quote	Grade
3	0%	F
4	60%	D
5	70%	C
6	80%	B
7	90%	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