Microsoft Office/Create a spreadsheet with lists

From Wikibooks, open books for an open world
Jump to: navigation, search

Objectives[edit]

  • 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[edit]

  • 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 colapse 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[edit]

Create a List/Table[edit]

Titles[edit]

  • 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[edit]

  • Highlight the titles
  • Go to the Insert Tab
  • Go to the Tables Group
  • Go to TABLE

Set Data Validation[edit]

  • 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[edit]

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[edit]

Computational Fields[edit]

  • 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[edit]

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[edit]

  • 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[edit]

Turn on the Total row[edit]

  • 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[edit]

  • 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[edit]

  • Age(years)
    • Click on cell E21
    • Click on the drop down arrow
    • Choose Average

Create Count for number of items in a column[edit]

  • 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[edit]

Convert to Normal Spreadsheet[edit]

  • Click anywhere in the table
  • Go to the TABLE TOOLS - Design tab
  • Go to the Tools group
  • Click on Convert to Range

Sorting[edit]

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[edit]

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[edit]

Subtotals[edit]

  • 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[edit]

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[edit]

  • 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[edit]

Finding Records[edit]

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[edit]

Create a Form[edit]

  • 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[edit]

Titles[edit]

  • Copy A8:J9 The headings
  • Paste in cell A1
  • Change the title to Criteria Area

Create Criteria[edit]

In row three place criteria to filter the table by.

  • Age >3
  • Gender = M
  • Grade >C

Advanced filter Command[edit]

  • 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[edit]

  • 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[edit]

Rubric[edit]