Computer Excel Term 1 2nd 1/2

Computer Excel Term 2

​Steps for Sorting Data in Excel

1. ​Click any cell in the table you want to sort.

2. ​Click on the data tab.

3. ​Click on the sort option.

4. ​A box will appear and click the little box that says "my data has headers".

5. Sort however you were told.​

​1. Click any cell in the table you want to sort.

​​2. Click on the data tab.

3. ​Click on the sort option.

​A box will appear and click the little box that says "my data has headers".

​5. Sort however you were told.​

​5. Sort however you were told.​

​This was sorted by ascending order under duration. Check previous slides to see the change.

Multiple Choice

What is the first thing you do when sorting in excel?

• click on the data tab

• click on sort

• click on a cell in the table

• All of the above

VLOOKUP​ FUNCTION

Excel's definition:

• ​=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

​

​Simple definition:

• ​=VLOOKUP(value to look for, range to look in, column number of the value to return, approximate to exact match [TRUE/FALSE])

​

​Sidenote: despite the syntax saying "value" it doesn't have to be a number. It can be both a label and a value. More than likely, it'll be a label.

​Sidenote: "true" means approximate match and "false" means exact match. When using VLOOKUP always return the exact match unless the question tells you not to.

​This question wants you to find the price of a Caffe Mocha, size Grande using the VLOOKUP function.

​Cell A15 was selected for the lookup_value because we're LOOKING for Caffe Mocha. We could've also selected A7 since the word "Coffe Mocha" is there too.

​The table_array is basically our range, you'd highlight the table except the headings.

​Grande is the third column from the right so you'd put the number 3.

​Then you'd type FALSE since you want to return the exact value.

​Check the previous slide to see the price of Caffe Mocha size grande and you'll see it matches this price.

​If you're lazy and wanna do the whole autofill (is that what it's called?) thing where you drag it down or whatever, first, freeze the cell. So put the dollar sign before both numbers and before both letters in the cell range.

​Sidenote: If there's an error sign like "#N/A" you should check if there's a space in the cell you selected. And just backspace to get rid of that space.

​Steps When Using AutoFilter

1. ​Click any cell in the table you want to filter.

2. ​Go to the data tab and click on filter.

3. ​Click on the drop-down arrow on the heading in the table you want to filter.

​​1. Click any cell in the table you want to filter.

2. ​Go to the data tab and click on filter.

​3. Click on the drop-down arrow on the heading in the table you want to filter.

If you wanna filter by camera, you'd unselect everything and then select camera.​

​You can also filter the numbers down ID, after you select on an option, depending on the option another tab like thing will come up.

​Sidenote: to deactivate the AutoFilter, once again click on the data option on the menu bar, click Filter, and select AutoFilter from the submenu.

​​Steps When Using Advanced Filter

1. ​Copy and paste the headers.

2. ​Put in the criteria under the specific heading/headings.

3. ​Click on data tab and then advanced.

4. ​List range: select all cells in the original table.

5. ​Criteria range: select all cells in the new table created.

6. ​Copy to: select a cell two places down the first heading of the new table.

7. ​Press "Ok".

​1. Copy and paste the headers.

​1. Copy and paste the headers.

​2. Put in the criteria under the specific heading/headings.

​Sidenote: there can be more than one criteria at the same time.

​3. Click on data tab and then advanced.

​4. List range: select all cells in the original table including headings.

Sidenote: Remember to click on copy to another location.

​Sidenote: Because this was previously used with other tables, you delete the info there and put in new info.

​5. Criteria range: select all cells in the new table created.

​6. ​Copy to: select a cell two places down the first heading of the new table.

​7. Press "Ok".

​Results

Multiple Choice

Counts the amount of numbers that meet a given criteria.

• countif

• count

• counta

• if

Multiple Choice

Select the syntax of of countif.

• =COUNTIF(C6>=70,"Pass","Fail")

• =COUNTIF(D5:D12)

• =COUNTIF(D5:D12,">100")

Fill in the Blanks

Type the function.

Multiplies all the numbers given as arguments and returns the product.

Fill in the Blanks

The function =PRODUCT(A1:A3) is the same as the formula =A1*A2*A3.

True or False?