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.

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

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:

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

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

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.

Subtotals
Notice there is a new row under each change in region that gives the 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

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

Finding Records
Find all the male dogs. Find the dogs that are between 2 and 3 years old Clear the filter when you are done.
 * Go to cell C9
 * Click on the drop down arrow
 * Check only the box for M
 * Click on OK
 * 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

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

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