Mastering Advanced MS Excel

Henry is analyzing sales data for his small business and wants to understand the performance of different products over the past year. What tool in Excel can he use to summarize and analyze this data effectively?

• A Pivot Table is a feature for formatting text in Excel.

• A Pivot Table is a type of chart in Excel.

• A Pivot Table is used to create macros in Excel.

• A Pivot Table is a tool in Excel used to summarize and analyze data.

Luna is working on a project and needs to analyze her data set. How does she create a Pivot Table from her data set?

• Choose data, navigate to 'View' > 'Freeze Panes', and adjust columns.

• Highlight data, click 'Home' > 'Format as Table', and save.

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

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

Abigail is trying to find the price of a specific item in a store's inventory. She has a table where the items are listed vertically in the first column and their prices in the second column. She wonders how she can retrieve the price of an item using a function. Meanwhile, Grace is looking at a different table where the items are listed horizontally in the first row and their prices in the second row. She is curious about how to find the price of an item in her table. Can you explain the difference between VLOOKUP and HLOOKUP to them?

• VLOOKUP can only be used in Excel, whereas HLOOKUP can be used in any spreadsheet software.

• VLOOKUP retrieves data from the left side of a table, while HLOOKUP retrieves data from the bottom.

• VLOOKUP is used for sorting data, while HLOOKUP is for filtering data.

• VLOOKUP searches vertically in columns, while HLOOKUP searches horizontally in rows.

Noah is trying to find the price of a specific item in a store's inventory list. What is the syntax for the VLOOKUP function that he should use?

• VLOOKUP(lookup_value, array_table, column_index)

• VLOOKUP(value, range, index)

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

• VLOOKUP(lookup_value, table, col_index)

Ava is trying to find a student's score from another sheet in her Excel workbook. How can she use VLOOKUP to find data in another sheet?

• Use the formula =VLOOKUP(lookup_value, 'Sheet2'!range, column_index, FALSE) to find data in another sheet.

• Use the formula =VLOOKUP(lookup_value, 'Sheet2'!range, column_index, TRUE) to find exact matches.

• Use the formula =VLOOKUP(lookup_value, 'Sheet1'!range, column_index, TRUE) to find data in another sheet.

• Use the formula =VLOOKUP(lookup_value, range, column_index) without specifying the sheet.

Aiden is working on a project in Excel and needs to ensure that the data entered by his team members is accurate. What are data validation techniques in Excel that he can use?

• Creating charts for data visualization

• Applying conditional formatting to highlight cells

• Data validation techniques in Excel include setting rules for data types, creating drop-down lists, using custom formulas, and restricting entries based on conditions.

• Using pivot tables for data analysis

Aiden is trying to create a drop-down list in his spreadsheet for a project. How does he set up a drop-down list using data validation?

• Select a cell, go to Data > Sort, and choose 'List'.

• Select a cell, go to Data > Data Validation, choose 'List', and specify the source.

• Right-click a cell, select 'Format Cells', and enter a list.

• Use the 'Insert' menu to create a drop-down from a chart.

Lily is analyzing her sales data for the past year and wants to identify which products performed exceptionally well or poorly. What is conditional formatting and how can it enhance data visualization?

• Conditional formatting is used to create pivot tables and summaries.

• Conditional formatting is a method for creating charts and graphs.

• Conditional formatting only applies to text data and not numerical data.

• Conditional formatting enhances data visualization by highlighting important data points based on specific criteria, making trends and anomalies easier to identify.

Abigail wants to highlight her grades in a spreadsheet based on whether they are above or below a certain threshold. How does she apply conditional formatting based on a formula?

• Select cells, go to Data Validation, set criteria, and apply.

• Highlight cells, go to Home, select Fill Color, and apply.

• Choose cells, click on Format, select Borders, and apply.

• Select cells, go to Conditional Formatting, choose New Rule, use a formula, set format, and apply.

Benjamin wants to create a combination chart in Excel to visualize his sales data. What are the steps he should follow?

• Select chart type, insert pie chart, adjust colors, and save.

• Highlight data, insert bar chart, set gridlines, and share.

• Choose data range, create line chart, format labels, and print.

• Select data, insert combo chart, customize types, adjust axes, and finalize.