Advanced Microsoft Excel Test Quiz!

You can consolidate data from each separate worksheet into a master worksheet to summarize and report results. This means that you can gather data from multiple worksheets and combine it into one worksheet to analyze and present the data in a more organized and concise manner.

The given statement suggests that it is possible to perform automatic calculations on filtered data from a table. This implies that the software or tool being used has the capability to calculate figures automatically based on the filtered data. Therefore, the correct answer is True.

The 3-D surface and XY Scatter are valid types of charts in Microsoft Excel. These types of charts allow users to visualize and analyze data in a three-dimensional format, making it easier to identify patterns and trends. The 3-D surface chart displays data points as a continuous surface, while the XY Scatter chart plots data points on a two-dimensional plane. Both types of charts are commonly used for scientific and engineering data analysis.

The given statement is true. Microsoft Excel allows users to import data from external sources such as txt, csv, and prn files. This feature enables users to easily bring in data from different file formats into their Excel spreadsheets for analysis and manipulation.

A pivot table is a powerful tool in data analysis that allows users to summarize and analyze large amounts of data. It uses specified calculations and formats to provide a concise and organized summary of the data. This helps users to gain insights and identify patterns or trends in the data. Therefore, the statement that pivot tables summarize large amounts of data using specified calculations and formats is true.

The given statement is true. In Excel, there are three main categories of templates that you can work with. The first category is Installed Templates, which are pre-designed templates that come with Excel. The second category is Templates Online, which are templates that you can download from Microsoft's online template library. The third category is Your Created Templates, which are templates that you create yourself by customizing an existing template or starting from scratch. These categories provide users with a variety of options for creating and using templates in Excel.

Data validation is a feature in Excel that allows you to set restrictions on the type and range of data that can be entered into a cell. By using data validation, you can create a drop-down list of predefined items in a list, and restrict the input to only those items. This ensures that the data entered is accurate and consistent. Therefore, the statement "You can use data validation to restrict data to predefined items in a list" is true.

Excel does provide some LOOKUP functions such as Vector Lookup, H Lookup, and V Lookup. These functions allow users to search for specific values in a range of cells and return corresponding values from another range. The Vector Lookup function searches for a value in a single row or column, while the H Lookup function searches for a value in the first row of a table. The V Lookup function searches for a value in the first column of a table. Therefore, the statement "Excel provides some LOOKUP functions which is Vector Lookup, H Lookup, V Lookup" is true.

The given functions, including LEFT, MID, FIND, PROPER, TRIM, DAY, EDATE, MONTH, and WORKDAY, are valid for use in Microsoft Excel. These functions are commonly used for manipulating and extracting data in Excel spreadsheets.

The given answer is true because What If Analysis can indeed be used to support decision making. Solver is a tool in Excel that can be used to find the optimal solution for a problem by adjusting certain variables. Goal Seek is another tool in Excel that can be used to find the input value needed to achieve a desired output. Scenario Manager is a feature in Excel that allows users to create and compare different scenarios by changing input values. Therefore, all three tools mentioned (Solver, Goal Seek, and Scenario Manager) are examples of What If Analysis techniques that can assist in decision making.

The explanation for the given correct answer is that Ms Excel 2007 does not have the slicer function, but in Ms Excel 2010/2013, slicers are available. Slicers are easy-to-use filtering components that contain a set of buttons. These buttons allow you to quickly filter the data in a Pivot Table report without the need to open drop-down lists to find the items that you want to filter. Therefore, the statement "Ms Excel 2007 don't have a slicer but for Ms Excel 2010/2013, Slicers are easy-to-use filtering components that contain a set of buttons that enables you to quickly filter the data in a Pivot Table report without the need to open drop-down lists to find the items that you want to filter" is true.

In Microsoft Excel, the Watch Dialog Box can be found in the Formula Tab. This feature allows users to monitor and keep track of specific cells or formulas while working on a spreadsheet. By adding cells or formulas to the watch list, users can easily observe the values and results as they change. Therefore, the statement "We can find a Watch Dialog Box in Microsoft Excel at Formula Tab" is true.

The formula =IF(E8=C8,"Equal","Not Equal") checks if the value in cell E8 is equal to the value in cell C8. In this case, since E8 contains the value 34 and C8 contains the value 43, the two values are not equal. Therefore, the answer is "Not Equal".

In Excel, the subtotal function can be found in the Data tab within the Outline group. This function allows users to calculate subtotals for a specific range of data in a worksheet. By clicking on the Data tab and locating the Outline group, users can access the subtotal function and apply it to their data as needed.

The formula "=LOOKUP(B13,D3:D11,E3:E11)" is valid for Vector Lookup. It uses the LOOKUP function to search for a value in the range D3:D11 and returns the corresponding value from the range E3:E11. This formula is commonly used to find an approximate match for a given value in a range and retrieve the corresponding value from another range.

The explanation for the given correct answer is that the TODAY() function in Excel is used to calculate the current date and does not have the capability to calculate past or future dates based on a starting date and a specified number of days. Therefore, the statement that the TODAY() function can be used to calculate a past or future date is false.

The statement is false because in Excel, cell or range names cannot include symbols, numbers, or spaces. They can only consist of letters and underscores. Therefore, the given statement is incorrect.

Using only a password cannot prevent a user from accidentally changing, moving, or deleting important data from a worksheet. While a password can provide some level of protection, it is not foolproof. Other measures such as setting specific user permissions or using data validation can also be necessary to ensure the integrity of the data.

The correct answer is Trendline, Sparkline, What If Analysis, Solver. These are all data analysis tools available in Microsoft Excel. Trendline is used to show the trend or pattern in data, Sparkline is used to display small charts within a cell to show trends, What If Analysis is used to analyze different scenarios by changing input values, and Solver is used to find the optimal solution for complex problems by adjusting variables.