Mastering Excel Functions

What does VLOOKUP do in Excel?

• VLOOKUP calculates the sum of a range of cells.

• VLOOKUP formats cells based on their values.

• VLOOKUP searches for a value in the first column of a table and returns a corresponding value from a specified column.

• VLOOKUP creates a new worksheet in Excel.

How do you calculate the sum of a range of cells?

• Use the SUM function with the cell range, e.g., =SUM(A1:A10).

• Use the COUNT function to sum the cells, e.g., =COUNT(A1:A10).

• Add the values manually without using a function.

• Use the AVERAGE function with the cell range, e.g., =AVERAGE(A1:A10).

What formula would you use to find the average of a set of numbers?

• Average = (Sum of all numbers) / (Count of numbers)

• Average = (Sum of all numbers) + (Count of numbers)

• Average = (Count of numbers) x (Sum of all numbers)

• Average = (Largest number) - (Smallest number)

How can you count the number of cells that contain numbers?

• Use the AVERAGE function to find the mean of the numbers.

• Use the SUM function to add the numbers.

• Use the COUNT function in your spreadsheet.

• Count the cells manually one by one.

What is the purpose of the COUNTIF function?

• To sort a list of values in ascending order.

• To count cells that meet a specific condition in a range.

• To calculate the average of a range of cells.

• To find the maximum value in a dataset.

How do you create a pivot table in Excel?

• Select data, go to 'Data' > 'Sort', and apply filters.

• Highlight data, click on 'Home' > 'Format', and choose a style.

• Select data, go to 'View' > 'Freeze Panes', and adjust the layout.

• Select data, go to 'Insert' > 'PivotTable', choose location, and arrange fields.

What is the syntax for the SUM function?

• SUM(number1 + number2)

• SUM(number1; number2)

• SUM(number1, number2, ...)

• SUM(number1, number2)

How can you use VLOOKUP to find data in another table?

• Use the SUM function to add values from another table.

• Apply the CONCATENATE function to merge data from two tables.

• Utilize the INDEX function to retrieve data without a lookup value.

• Use the VLOOKUP function with the syntax VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) to find data in another table.

What is the difference between COUNT and COUNTA?

• COUNT counts unique values; COUNTA counts duplicates.

• COUNT counts empty cells; COUNTA counts only text cells.

• COUNT counts numeric cells; COUNTA counts all non-empty cells.

• COUNT counts all cells; COUNTA counts only numeric cells.

How do you filter data in a pivot table?

• Right-click on the pivot table and select 'Sort' options.

• Use the 'Data' tab to apply filters directly.

• Select the entire table and press 'Delete' to remove data.

• Click the drop-down arrow in row/column labels and select filter options.