Excel 2013 Expert Free Sample Questions

This is the correct format string. The structure for custom formats is Positive;Negative;Zero;Text. #.0,," M" correctly formats positive numbers to millions with one decimal place. The two commas are crucial for dividing by one million. [Red](#.0,," M") handles negative numbers by setting the color to red and enclosing them in parentheses. The hyphen - correctly specifies the format for zero values. The final semicolon is optional but good practice.

The WORKDAY.INTL function allows for a custom weekend string as the third argument. This string consists of seven characters, starting with Monday. A '1' indicates a non-working day (weekend), and a '0' indicates a working day. To specify that only Friday is a day off, the string must be "0000100" (Mon, Tue, Wed, Thu are workdays; Fri is a non-workday; Sat, Sun are workdays).

The 'Accept/Reject Changes' functionality is specifically part of the legacy 'Shared Workbook' feature. This command is only active when the workbook has been explicitly shared via 'Review' > 'Share Workbook'. If the workbook is not in this mode, even if 'Track Changes' is on, you cannot use the 'Accept/Reject' dialog. The feature requires the workbook to be formally shared to manage changes from multiple users.

A 'Calculated Field' is a custom formula that is part of the PivotTable itself, not the source data. It allows you to perform calculations on the sum of other PivotTable fields. In this case, you would create a calculated field with a formula like ='Sales Amount' * 0.05. This new field will appear in the PivotTable and update automatically with any changes to the underlying data or filters.

The WEEKDAY function, by default, returns a number from 1 (Sunday) to 7 (Saturday). The condition > 5 correctly identifies both Saturday (7) and Sunday (1 is not > 5, this is wrong). A better formula would be OR(WEEKDAY(B2)=1, WEEKDAY(B2)=7). However, if we use the second argument return_type as 2, WEEKDAY(B2, 2) returns 1 for Monday through 7 for Sunday. In that case, =WEEKDAY(B2, 2) > 5 would correctly identify Saturday (6) and Sunday (7). Assuming the default context of the exam, the most common approach is the one given. Let's re-evaluate. The default WEEKDAY(date) returns 1 for Sunday and 7 for Saturday. So >5 would only get Saturday. The correct logic should encompass both. Let me correct the answer. The most robust formula among plausible choices would be one that checks for both conditions. Let's rephrase the options.

Corrected Explanation: The WEEKDAY(B2, 2) function returns a number from 1 (Monday) to 7 (Sunday). Therefore, a result greater than 5 indicates that the day is either Saturday (6) or Sunday (7). Using this formula in a conditional formatting rule applied to the range B2:B50 will correctly highlight all weekend dates. The reference to B2 is relative and will adjust for each cell in the range.

The 'Compare and Merge Workbooks' command is designed for this exact purpose. As long as each copy originated from the same shared workbook and has change tracking enabled, Excel can merge the changes back into the original file, regardless of the file names of the copies. This allows a manager to distribute a file, have multiple people work on their own copies, and then consolidate all the changes.

Sparklines are miniature charts that reside in a single cell, perfect for providing a quick visual representation of data trends without taking up much space on a dashboard.

Within the Sparkline Tools Design tab, you can enable Markers for 'High Point' and 'Low Point'. This will automatically highlight the highest and lowest values in the sparkline's data range, fulfilling the requirement.

The Data Validation feature is designed for this exact scenario. On the 'Settings' tab, you can 'Allow' a 'List' and set the 'Source' to =DeptList. On the 'Input Message' tab, you can enter the required instructional text. This restricts input to the list values and provides user guidance.

While other auditing tools are useful, this is the most direct navigation method. Editing the cell (or using the formula bar) and highlighting a cell reference within the formula allows you to use the 'Go To' command (F5) to jump directly to that specific precedent cell, even if it's on another worksheet.

This scenario is the primary use case for a combination chart with a secondary axis. By creating a combo chart (e.g., column for revenue, line for units sold) and plotting the 'units sold' series on a secondary vertical axis, each series gets its own scale. This allows both trends to be clearly visible and comparable on the same chart.