MS Office Excel 2007/2010 Advance - Pre Test

Consolidating data from separate worksheets into a master worksheet allows for the summarization and reporting of results. By combining the data from multiple worksheets, it becomes easier to analyze and present the information in a cohesive manner. This can be particularly useful when working with large amounts of data or when needing to compare and contrast information from different sources. Overall, consolidating data into a master worksheet streamlines the reporting process and enhances the ability to draw insights from the data.

The statement is stating that a Pivot table can be created either within the same data source worksheet or in a new worksheet. This means that the user has the flexibility to choose where they want to create the Pivot table based on their preference or requirements. Therefore, the correct answer is True.

Data Validation is a process of ensuring that data entered into a system meets certain criteria and is accurate, complete, and consistent. By implementing data validation, we can standardize and uniform data, ensuring that it is in the desired format and meets specific requirements. Additionally, data validation can help in controlling human errors by creating warning messages or alerts when incorrect or inconsistent data is entered. Therefore, the statement that data validation can help us to uniform data and control human error 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 and returns a value from the same position in a different row or column. The H Lookup function searches for a value in the top row of a table or array and returns a value from the same column in a different row. The V Lookup function searches for a value in the leftmost column of a table or array and returns a value from the same row in a different column. Therefore, the statement "Excel provides some LOOKUP functions which is Vector Lookup, H Lookup, V Lookup" is true.

The statement is true because the view feature in a worksheet allows users to customize the display and print settings according to their preferences. These settings can be saved and applied whenever needed, making it convenient and efficient for users to switch between different views without manually adjusting the settings every time.

The explanation for the given correct answer is that in Excel, the Name Manager dialog box can be found under the Formula tab. This dialog box allows users to create, edit, and delete named ranges in their workbook. By using named ranges, users can easily refer to specific cells or ranges in formulas, making their formulas more understandable and easier to manage. Therefore, the statement "We can find a Name Manager Dialog Box in Excel at Formula Tab" is true.

The statement is true because cell names and range names in Excel can only be created using alphanumeric characters and the symbol "_". They cannot contain spaces or any other symbols. This is a rule set by Excel to ensure that cell and range names are valid and can be easily referenced in formulas and functions.

The statement is incorrect. In Excel, you can create a hyperlink within a spreadsheet or workbook by right-clicking on a cell, selecting "Hyperlink" from the menu, and then entering the desired link address. Pressing Ctrl+H in Excel brings up the Find and Replace dialog box, not the hyperlink creation feature.

The given formula "=LOOKUP(B13,D3:D11,E11)" is valid for Vector Lookup. The LOOKUP function searches for a value in the first column of a range and returns a value from the same row in a specified column. In this case, it searches for the value in cell B13 within the range D3:D11 and returns the corresponding value from the same row in column E. Therefore, the answer is True.

The statement is false because "What If Analysis" does not contain methods such as Solver, Data Tables, and Conditional Formatting to help make decisions. These methods are actually tools within Microsoft Excel that can be used for data analysis and problem-solving, but they are not part of the "What If Analysis" feature itself.