Review Questions Answer Key
Microsoft Excel 2013 Expert
Answers to Review Questions
Lesson 1
1. List each of the components of a function, and explain why it is important to ensure that the syntax of the function is correct.
The structure or syntax of a function is =FUNCTION(Arguments) where you must start the formula with the = symbol, followed by the function name, and then the list of arguments (if any) inside parentheses. It is important to ensure the syntax is correct because Excel needs to read it so that it knows what to do for you. The = symbol tells it that you are entering a formula of some kind. The function name – if it is correctly spelled – tells it which function calculation to perform. The list of arguments must match what that function needs or else it will not be able to perform the function calculation properly. If any of these components are missing or incorrectly entered, Excel will display an error symbol or an incorrect result.
2. The vector version of the LOOKUP function will generate the same results as either the VLOOKUP or HLOOKUP function.
True. This version of the LOOKUP function uses the lookup vector and the result vector to perform its task, which are similar to the VLOOKUP and HLOOKUP functions.
3. What is the formula for adding 10 days to the current date? Will this formula still be accurate even if today is December 29?
The formula is:
=TODAY()+ 10
Yes, the formula remains accurate when changing from one year to the next.
4. Explain why you might create a PivotTable.
A PivotTable allows you to summarize or cross-tabulate large amounts of data by selecting fields for rows and columns, and by performing a summary function on the intersections of the row and column fields. When the data is flipped around like this, you will often discover new data relationships or trends that were not obvious without the PivotTable.
5. What are the similarities and differences between a data slicer and the AutoFilter tool in the PivotTable?
The data slicers work directly with the AutoFilter options in the PivotTable. When you select or deselect options in the slicer boxes, the same filter options are activated or de-activated in the AutoFilter. In this manner, the two are the same. However, the data slicer puts the data options in an easy-to-use list box on the worksheet, allowing you to quickly and visibly select your options. With the AutoFilter options, you have to click on several buttons to accomplish the same task.
6. The formula =SUM(B5:B10) can be used in the calculated field for a pivot table.
False. Calculated fields in a pivot table cannot refer to worksheet cells. They can only reference field names.
7. Under what circumstance would a user use PowerPivot instead of pivot tables?
A user will use PowerPivot when they need to access data residing in large complex databases that are run on centralized corporate servers. Once connected to these data sources, users can use the regular features of pivot tables.
8. Under what circumstance would a user have to manage table relationships in PowerPivot?
A user will need to use this feature in PowerPivot when they are extracting data from two tables and the relationship between them is missing. This may occur when the data is obtained from different data sources (such as two different databases) or from different object types in the same database.
Lesson 2
1. Which of the following customizations is not possible with an Excel chart:
f. – All of the above customizations can be performed on a chart.
2. A line chart can have both a primary and secondary Y-axis, even though it is not a combo chart.
True. A chart with only one line type can have two Y-axes.
3. Which of the following changes to the data will cause the chart to change immediately:
e. – All of the above.
4. Once you have created a chart template, can you apply it to almost any worksheet containing similar data?
Generally yes, as long as the data is similar. For example, it would not make sense to apply a chart template that was designed using numeric data to another worksheet that contained only dates or text.
5. Why would you want to include a trendline in a chart?
It’s a common method of analyzing data in the charts, but also to help predict future values.
6. Which of the following is not a valid conditional format?
b. – the conditional formatting cannot be applied to a chart.
7. Suppose you create two conditional format rules on a range of cells:
a. Display an orange background color if the cell has a value > 7.5.
b. Display a green background color if the cell has a value >= 7.4.
What background color will display if every value had a minimum value of 8?
The conditional format rule that is displayed above the other in the Rules Manager will take precedent.
8. Which of the following is not a valid conditional format:
b. – the conditional formatting cannot be applied to a chart.
9. Which of the following are valid formulas to use in a conditional formatting rule (circle all that apply):
b, c, and d.
10. What are some of the suspicious conditions that the Excel Error Checking tool will flag for you?
This feature can identify formulas that are inconsistent with similar formulas in adjacent cells, formulas that refer to a range but omit a cell that is adjacent to the range, formulas that evaluate to an error, and numbers that are stored as text.
11. Why should you check the formulas in your worksheet manually?
You should check worksheet formulas manually because the error checking tool will not identify every incorrect formula. Ultimately, you are responsible for every formula in the spreadsheet, and sometimes significant decisions are made based on the data displayed.
12. How can you display all the formulas in a worksheet at once in order to identify all errors in the formulas, or do you need to check each cell individually?
On the Formulas tab, in the Formula Auditing group, click Show Formulas to display all formulas in the worksheet. This is much faster than checking individual cells, although that is another option.
Lesson 3
1. When filtering, why should you use the copy and paste tools to copy the column titles to the criteria range?
The criteria range requires column titles that exactly match the column titles for the data. Even a blank space at the end of the column title will cause Excel to ignore the criteria for that column. If any one of the column titles has a non-visible character, you will have a lot of difficulty trying to find this problem. Therefore, the easiest way of preventing the problem is to copy and paste the column titles into the criteria range.
2. When would you create a shared workbook?
You would create a shared workbook when you need to allow more than one person to update the same workbook at the same time.
3. What kind of conflict will Excel identify when you are working with a shared workbook?
Excel will notify you that a conflict has occurred when you and at least one other person had the same workbook open at the same time and then saved it with different formulas or values in the same cells.
4. Shared workbooks automatically track all changes made to a workbook. These changes may be kept for a maximum of 30 days.
False. By default, Excel saves tracked changes for 30 days, but this can be changed at any time in the Share Workbook dialog box. You can store up to 32,767 days of changes.
5. When would you want to track the changes made to a worksheet?
You would want to track changes whenever you are concerned about unrestricted and uncontrolled changes to the data. By turning on this feature, all changes to the workbook are recorded in a history log.
6. What can you use comments for in a worksheet?
Use comments as notes to yourself or others regarding the data in this cell such as reminders or details of formulas.
7. The only way to change the currency format for numbers in your worksheet is to select the correct currency symbol and country in the Format Cells dialog box.
False. You can set the default format for all currency values in your workbook by using the Control Panel Regional Settings window.
8. Which of the following date formats are not valid?
e. – the year must have either 2 or 4 digits.
9. Which of the following is not one of the guidelines for improving accessibility for a workbook?
b. – fonts do not need to be a minimum size of 24. However, they should not be too small either.
10. A workbook containing VBA code to force a specific tab order for cells must be saved as a macro-enabled workbook.
True.
Lesson 4
1. Describe how a custom number format is constructed, and the purpose of each component.
Custom number formats are specified as:
positive # format; negative # format; zero format; text format.
This means you can use a different format if a number is positive, negative, or zero. A custom format does not require all four sections — it can have as little as one section. If you only specify the first section, Excel will use this same format for all numbers (positive, negative, and zero). If you specify only the first two sections, Excel will use the positive number format for zero values as well as positive values, and the format in the second section for negative numbers.
A format is specified by using a sequence of symbols to indicate the position and type for each of the numeric digits to be displayed, such as # 0 ? , . _ color.
2. How will the number 1234567890 appear if the cell was formatted as $#,##0,,?
c. – $1,235
3. Which of the following date formats are not valid?
e. – the year must have either 2 or 4 digits.
4. When a cell style is applied to various cells in four different worksheets, any changes to the style will
True.
5. What’s the difference between setting up a manual what-if worksheet and using the Goal Seek tool?
With a manual worksheet, you have to keep guessing at what values will work to get the end result. With the Goal Seek tool, you can set the parameters for the end result and it will calculate the correct input value for you.
6. The scenario manager is useful when you need to compare different sets of input numbers and the results together as a single report.
True.
7. What is a cell watch?
b. – An Excel feature that allows you to always see the current value of one or more worksheet cells in a small pop-up window no matter where you are in the workbook.
8. The Fill Series feature can be used to calculate the date of the third Friday of every month over the next 6 months.
False. In a fill series, the same step value is used to calculate the next cell value. However, the third Friday of the next month will be either 28 or 35 days in the future.
Lesson 5
1. You can create a pivot chart directly from the source data without creating a pivot table first.
True.
2. Which of the following design or formatting changes can be made to a pivot chart:
f. – all of the above.
3. Which of the following statements is incorrect about the PMT function:
d. – the function does not adjust for inflation.
4. If cell C5 contained the value 33, then the formula
=IF(C5<10,"Red",IF(C5<20,"Green",IF(C5<30,"Blue",IF(C5<40,"Orange","Brown")))) will result as:
d. – Orange.
5. Suppose you created a worksheet in which cell D2 can contain any value from 1 to 50. But if that cell contains a number greater than 40, then the value in cell E2 must not exceed 20 because that is an error situation. Which of the following formulas would you use to check for this error situation:
b. =IF(AND(D2>40,E2>20),"Error"," ")
6. How do conditional summary functions such as SUMIFS or AVERAGEIFS differ from SUM or AVERAGE?
The conditional summary functions SUMIFS and AVERAGEIFS allow you to selectively calculate the sum total or average for a range of cells. That is, they allow you to use selection criteria as a filter and only apply the summary function on those cells that meet the criteria. The SUM and AVERAGE function will calculate the sum total or average using every value in the range of cells.
7. Which of the following is the correct workbook link to obtain the value in cell B12 in Sheet1 of the January Sales workbook:
d. ='[January Sales.xlsx]Sheet1'!B12
8. Describe what consolidating data means.
Consolidating data means to take data from multiple areas and combine them using a consolidation function such as sum and placing the results in another area. The source and destination areas do not have to be in the same worksheet or even the same workbook.
Lesson 6
1. Give an example of why you might want to create a template of your own.
You may want to create a template of your own to save time when you are creating a new workbook. You might use a template provided by Microsoft as a starting point and then customize it to include your company logo or terminology.
2. In order for you to copy a cell style from another workbook, that other workbook must be open at the same time.
True.
3. What’s the difference between protecting a worksheet and protecting a workbook?
A protected worksheet prevents users from entering data or formulas into locked cells in that worksheet. A protected workbook prevents users from adding, deleting, or moving worksheets within the workbook, and prevents worksheet windows from being moved or resized.
4. When you protect a worksheet with the Select locked cells option turned off, how will Excel behave differently than when it is turned on?