Microsoft Office/Create a spreadsheet with lists

< Microsoft Office

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

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

This article is issued from Wikibooks. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.