# 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 | 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