Excel Intermediate 2013 Quiz

The term used to refer to the column name data that is stored within the topmost row of an Excel table is the "Header Row." This row typically contains labels or names for each column in the table, allowing users to easily identify and understand the data in each column.

Ascending is the correct answer because it is a method of sorting field data within an Excel table. When data is sorted in ascending order, it is arranged from smallest to largest or from A to Z, depending on the type of data. This allows for easier analysis and organization of the data within the table.

 The character that is NOT allowed in range names in Excel is the period (.) You can use underscores, hyphens, and spaces, but periods are not allowed in range names. Excel treats periods as a special character reserved for other purposes, such as separating worksheet names from cell references.

The function within Excel that allows you to look up values in a table structured with a "header row" at the top of the table is HLOOKUP. "H" stands for horizontal, indicating that the function searches for values in the horizontal direction (rows) of the table, making it suitable for tables with this structure.

The correct answer is "=SUM(Sheet1:Sheet2!A1)". This formula calculates the sum of the values in cell A1 from Sheet1 to Sheet2 in Excel. The use of the colon (:) between "Sheet1" and "Sheet2" indicates a range of sheets, making it a 3D formula.

Excel's MONTH function extracts the month as a number from a date. However, it requires a valid date serial number as input. Since your dates are in a text format ("dd-mmm-yyyy"), you need to convert them to date values first. The DATEVALUE function does this.

Here's why the other options won't work:

=MONTH(A1): This will return an error because A1 is not a recognized date value.

=TEXT(A1, "mm"): This will extract the month as text ("03"), not a number.

=VALUE(MID(A1, 4, 3)): This extracts the "Mar" text and tries to convert it to a number, resulting in an error.

Only option d) correctly converts the text to a date value and then extracts the month as a number. This question tests your understanding of date formats, text functions, and date/time functions in Excel, making it suitable for an intermediate level.

Named ranges in Excel can simplify the process of referencing cell ranges in formulas. By assigning a name to a specific range of cells, users can easily refer to that range in formulas instead of manually entering the cell references. This not only makes the formulas more readable and easier to understand, but also allows for easier editing and maintenance of the formulas. Additionally, named ranges can be used across multiple worksheets and workbooks, providing consistency and efficiency in formula usage.

The "Cell Styles" button in Excel allows users to apply preset styles to selected cells. This means that users can easily format their cells by choosing from a variety of predefined styles, such as headings, titles, or data formats. This feature saves time and effort as users do not have to manually format each cell individually.

The correct answer is Cell Ranges because it is not one of the choices shown in the drop-down menu that appears when you click the "Conditional Formatting" button within the Ribbon. The other options - Top/Bottom Rules, Highlight Cell Rules, and Data Bars - are all choices that are available in the drop-down menu for conditional formatting.

The checkbox option that you can check or uncheck within the "Share Workbook" dialog box to share or remove sharing from a workbook is "Share the workbook and allow changes by other users." Checking this option enables sharing, and unchecking it removes sharing permissions for other users in the workbook.

 The function within Excel that allows you to look up values in a table structured with a "header column" at the left end of the table is VLOOKUP. "V" stands for vertical, indicating that the function searches for values in the vertical direction (columns) of the table, making it suitable for tables with the structure you described.

The purpose of auditing workbooks in Excel is to trace formula references between cells. This means that auditing helps in identifying and tracking the formulas used in different cells, allowing users to understand how the data is calculated and ensuring accuracy in calculations. By auditing, users can easily identify any errors or discrepancies in the formulas, helping to maintain the integrity of financial data and ensuring accurate analysis.

The purpose of consolidating data in Excel is to perform a mathematical calculation on data gathered from multiple worksheets and/or workbooks. This means that you can combine and analyze data from different sources in order to perform calculations such as summing, averaging, or finding the maximum or minimum values. This feature is useful for organizing and analyzing large amounts of data in a single location.

When you define a table in Excel, a drop-down arrow is added to the top of each column within the table. This drop-down arrow allows you to filter and sort the data in that column, making it easier to analyze and manipulate the information in the table.

In Excel, the maximum number of cells that can be changed within a single scenario is 32. This means that when creating a scenario or making changes to existing data, you can modify up to 32 cells at once. It is important to note that this limit is specific to scenarios and does not apply to other operations or functions in Excel.

"Comments" is not an available option within the "Paste" section of the "Paste Special" dialog box in Excel. The other options, "All," "Formats," and "Characters," are typically available for pasting specific elements of the copied data. "Comments" is not a standard option in the Paste Special dialog for the "Paste" section.

Data tables in Excel allow users to change variables in a formula to view different possible outcomes. By inputting different values into the data table, users can see how the formula's result changes accordingly. This is useful for conducting sensitivity analysis or exploring different scenarios. Data tables do not involve changing filters in a table or scenarios in a worksheet, nor do they allow users to change functions in a formula.

The purpose of using the Paste Special feature in Excel is to copy cells and then paste only selected elements into a new location. This feature allows users to choose specific formatting options, such as values, formulas, formats, or comments, to paste into the desired location. It provides more control and flexibility when copying and pasting data in Excel.

The correct answer is the "Sparkline Tools" contextual tab. This tab appears in the Ribbon when a sparkline is selected and allows the user to modify the attributes of the selected sparkline. It provides various options and tools specifically designed for working with sparklines, such as changing the type, style, and color of the sparkline, as well as adjusting its axis and data source.

Charts in Excel are not one of the items that become locked and unable to be altered after sharing an Excel workbook. When you share a workbook in Excel, items like macros, pictures, and named ranges can become locked or protected, but charts can still be edited and modified by other users who have access to the shared workbook.

The correct answer is Plot Area. In Excel 2010/2007, the Plot Area is an object that can be selected from the "ChartElements" drop-down within the Ribbon. It specifically refers to the area within the chart where the actual data is being plotted. By selecting the Plot Area, you can make changes or apply formatting specifically to the data area of the chart.

The correct answer is "WordArt Styles" because in Excel 2010/2007, the "Chart Tools" contextual tab includes a button group called "WordArt Styles" that contains premade formats specifically designed for text objects within a chart. This button group allows users to easily apply different styles to their text objects, enhancing the visual appearance of the chart.

Outlining in Excel allows users to create collapsible/expandable cell groupings in a worksheet. This feature helps in organizing and managing large amounts of data by grouping related cells together. It allows users to collapse or expand these groups, making it easier to navigate and analyze data. This feature is particularly useful when working with complex workbooks that contain multiple sheets and extensive data.

The button that allows you to change the placement of a selected chart within a workbook in Excel 2010/2007 is "Move Chart".

A header row is necessary for the "AutoFilter" feature to function properly because it provides the column names or labels that the feature uses to filter and sort the data in the table. Without a header row, the feature would not know which columns to apply the filters to, making it impossible to use the "AutoFilter" feature effectively.

The "Chart Styles" button group appears within the "Chart Tools" contextual tab within the Ribbon in Excel 2013. It contains a listing of the various preset chart formats that you can apply to a selected chart within a worksheet.

Cell validation in Excel allows you to restrict the range of values that can be entered into selected cells in a worksheet. This means that you can set specific criteria or conditions for the data that can be entered into a cell, such as only allowing numbers between a certain range, or only allowing text of a certain length. By using cell validation, you can ensure that the data entered into your worksheet is accurate and meets your specified requirements.

The button group in the "Chart Tools" contextual tab within the Ribbon that contains the "Plot Area" button in Excel 2010 and 2007 is called "Walls." This button group allows you to format and customize the walls, such as side walls, of 3D charts.

The drop-down within the "Current Selection" button group on the "Format" tab of the "Chart Tools" contextual tab in the Ribbon that allows you to select objects within a chart is called "Chart Elements".

The "Format" tab is one of the tabs that appear within the "Chart Tools" contextual tab within the Ribbon when a chart is selected in Excel 2013. This tab allows the user to format various aspects of the chart such as the chart style, chart elements, chart layout, and chart styles. By selecting this tab, the user can easily customize the appearance of the chart to meet their specific needs and preferences.