# 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

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

M N
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

• 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

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

• click on MORE COMMANDS
• Under Choose Commands From - choose ALL COMMANDS
• Click on Form
• Click on OK

### Setting a Criteria Range

#### Titles

• 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

• Click in the table to activate it - Cell A10
• Go to the DATA tab
• Go to the Sort & filter group
• 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

• 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