Review Questions Answer Key
Microsoft Office Excel 2013
Answers to Review Questions
Lesson 1
1. List some benefits of using an electronic spreadsheet.
Spreadsheets are extremely useful for solving financial and statistical problems, performing “what-if” analyses, and displaying charts and graphs.
2. Explain the difference between a workbook and a worksheet.
A worksheet document is similar to an extremely large sheet of paper divided into rows and columns, whereas a workbook is a single Excel file containing one or more worksheets, for example, Sheet1, Sheet2, Sheet3.
3. Explain how commands are grouped on the Ribbon and how you access them.
The Ribbon consists of command buttons that are grouped by the type of task and can be accessed by clicking the appropriate tab, navigating to the group that contains the type of task you want and then clicking the command button. In some cases, the command may have a drop-down arrow that provides other options. If so, click the drop-down to select another command. Many groups also have a small icon (called a dialog box launcher) at the lower right of the group, which activates a dialog box or task pane with more options for this feature.
4. Explain how you can access the Ribbon using the keyboard and then how to access the commands.
You can also access the Ribbon using the keyboard by pressing the Alt or F10 key. Excel then displays icons that reference the keys on the keyboard. Pressing these keys will activate the buttons on the Ribbon or open the tab to show the buttons in that Ribbon tab.
5. Explain when it is better to create a blank workbook and when it is better to create a new workbook using a template.
Create a new workbook when you want to enter information using your own layout. Create a workbook using a template if you want to use a design and layout previously created, either to enter data or to use as a base for your own reports.
6. Explain the difference between using the Save and Save As commands.
The first time you save a workbook, you always see the Save As option to enter a name for the new file. Once the file has been saved, the Save command will save any changes made to the existing file, overwriting the one previously saved. If you want to save the existing workbook with a new name, such as in the case of a new department budget for the next year, use the Save As command.
7. Provide examples of different types of data.
The different types are text values, numeric values and formulas. Formulas generally use cell references containing numeric values or functions to perform a calculation.
8. List different formats you can use to enter a date so that Excel will recognize it as a date value.
Dates can be entered using the following formats:
September 15, 2013 (you must include the comma followed by a space)
Sep 15, 13
15-Sep-13
09/15/13 (assuming your region setting uses month/day/year sequence)
9-15-13
Sep 2013
Sep 15
9. Explain why you would save a workbook using a previous Excel version format.
Excel allows you to save your workbook in the Excel 97-2003 or the Excel 5.0/95 format because you may need to share it with someone who is using an older version of Excel.
10. Explain how you can select different areas of a worksheet at the same time and why you might want to use this feature.
To select different areas of a worksheet, use the Ctrl key as you click to select other cells. This can be handy when you want to perform a similar task for multiple cells that are not adjacent to each other.
Lesson 2
1. Suppose you used the Copy and then the Paste command to copy the contents of cells A1 to cell B1, but you do not want to copy the cell formatting to cell B1. What can you do to fix it?
You can use the Paste Special command instead, and select the Values option.
2. Provide examples of when using the AutoFit command would be the best option to change the width of a column.
AutoFit can be handy when you want the column to be a specific width based on the longest entry in that column. It can also save time instead of guessing the width for a particular column.
3. Explain how pressing Delete to delete the contents of a cell differs from using the Delete Cells command.
Deleting the contents of a cell leaves the structure of the worksheet intact. If you use the Delete Cells command, you have the option of shifting the cells after the delete action is complete.
4. Provide examples of when you may want to hide rows or columns.
You may want to hide rows or columns when you want to prevent other users from seeing the data in these hidden rows or columns. In other situations, you may be improving the readability of your worksheet by reducing the number of rows or columns of data to print together on a single page, or to hide cells that only contain intermediate calculations for otherwise complex formulas.
5. Explain the difference between using the Copy and AutoFill features.
Copy Cells: You copy text, values or formulas with their formatting. This does not create a series.
Fill Series: You create a simple linear series and copy the formatting of the source cell. A series is a sequentially incrementing or decrementing set of values, such as 1 2 3 4, Sun Mon Tue, -5 -10 -15 -20, Jun Jul Aug Sep.
6. How would you set up the values if the AutoFill series is not a common series, such as weekdays, months or years?
You would enter the pattern for the series so that AutoFill would be able to detect how to complete the series. For example, Apr 5 Apr 12 tells Excel that the pattern is 7 days between dates.
7. Provide examples of when you might want to duplicate a worksheet.
Examples could include changing the data in a worksheet to create a what-if analysis, or entering data into a new version of that worksheet instead of in the “original” one, something like using a template.
8. What is the maximum length of a worksheet tab name?
The maximum length of a worksheet tab is 31 characters.
9. How many new worksheets can you add to an Excel workbook?
You can add as many worksheets as the available memory of your system will permit.
10. When you insert a new worksheet using the Insert Worksheet tab, the new worksheet tab appears:
a. To the left of the current worksheet tab
b. To the far left of all worksheet tabs
c. To the far right of all worksheet tabs
d. In a random position, depending on which worksheet is active
e. To the right of the current worksheet tab
Lesson 3
1. List which standard math operators Excel uses and what is their order of precedence.
* Multiplication
/ Division
+ Addition
- Subtraction
2. Give examples of how using formulas to perform a what-if analysis is beneficial to you.
By using formulas in what-if analyses, you will save time, eliminate calculation errors, and reduce personal frustration whenever you change the numbers for the many scenarios you may create.
3. Which of the following are invalid?
a) =MAX(B5:B15)
b) =MAX(B5,B6,B7,B8,B9,B10,B11,B12,B13,B14)
c) =MAX(B5:B7,B8:B10,B11:B15)
d) =MAX(B1,B5:B8,B9:B15)
e) All of the above are invalid formulas
f) None – all are valid formulas
g) c
h) b, c, and d
4. Define the different parts of a reference to other worksheets using the reference Tours!B4 as an example.
A cell reference to another worksheet has two components using the format <worksheet name>!<cell reference>, In the example, ”Tours” refers to the name of the worksheet where you can find the data and ”B4” is the address of this cell in the referenced worksheet.
5. What is the main difference between an IF function and other functions discussed in this lesson such as SUM, AVERAGE, and UPPER?
Most of the functions discussed in this lesson will perform their operation on all of the cells specified in their argument list. For example, the function =SUM(A1:D1) will calculate the sum total for every cell from A1 to D1. However, a conditional function will perform the logical test first, and will then perform the operation or an alternative. An IF conditional function will perform one of two possible calculations, depending on the results of the logical test. The conditional summary functions will only perform the operation as long as the cell meets the logical test. For example, =SUMIF(A1:D1,”>10”) will look at each cell from A1 to D1 and calculate the sum total for all cells that contain a value of greater than 10.
6. Are the LEFT and MID functions fully interchangeable? In other words, can you always use the LEFT function instead of MID, as well as using MID instead of LEFT?
The MID function can always be used instead of LEFT, simply by entering a “1” as the starting position. The LEFT function can only be used to replace a MID function if you are extracting text starting from the far left (position 1) of the text string.
7. Explain the difference between absolute and relative cell references.
A relative cell reference is one that, when copied, will be adjusted in the new location based on the relative position of the original formula’s input cells. An absolute cell reference tells Excel that this formula will always reference the same cell, even when you copy the formula to another cell.
8. Provide examples of when or why it may be beneficial to print the formulas in the worksheet.
You may want to print the formulas in a worksheet to see all of them at the same time, such as when checking their accuracy.
Lesson 4
1. What is Live Preview?
Live preview is an Excel feature that temporarily changes the appearance of the selected cell(s) to the format that your mouse is currently pointing to on the Ribbon. If you move your mouse away, the selected cell(s) revert to their current format.
2. How do data appear in a cell if the alignment is set to General?
With the alignment set to General, numeric, date and time values align to the right, whereas text values align to the left.
3. How can applying colors or patterns be useful in a worksheet?
Applying colors or patterns can help to identify or emphasize areas in the worksheet such as totals, trends or patterns in the values, and column titles.
4. How does applying themes differ from applying formatting features individually?
Applying themes can be an easier way to format a worksheet because a pre-built theme includes a specific set of colors and fonts which Excel can apply to all data on the worksheet. If you apply the formatting individually then you have to apply the settings to each cell or range of cells, which may be more time consuming than using a theme.
5. Give an example of when you might want to change a theme in a workbook.
Themes are very useful for creating a consistent look for all of your documents, whether they are letters, spreadsheets or slide presentations. By maintaining a consistent theme for these documents, you can create an identity that external customers, suppliers, and others can quickly recognize.
6. What is the difference between clicking the Format Painter once and clicking it twice?
Click the Format Painter once to apply the formatting attributes to only one other cell or cell range. Double-click the Format Painter to apply the formatting attributes to as many cells or cell ranges as you want until you click it again to turn it off.
7. Give some examples of why you might want to use styles on cells in the worksheet.
Apply styles on cells to display a more professional look in the worksheet, possibly emphasizing specific areas or helping the viewer read or understand the data better. Another reason is to make it easier to change the formatting of multiple cells at the same time by changing the formatting settings in the style.
8. Explain what conditional formatting is and provide examples of how you can use it to find or enhance data.
Use conditional formatting to set a different format (within certain limitations) for a cell depending on the value that the cell contains. The format can, therefore, change automatically with different values without having to make the changes manually.
Lesson 5
1. Give examples of when it can be helpful to use the different types of worksheet window arrangement such as tiled, horizontal, vertical, or cascade.
Select the tiled, horizontal, or vertical options when comparing the data in two or more windows at the same time. The windows may be different areas of the same worksheet or different workbooks. The choice of tiled, horizontal, or vertical depends on how you have laid out the data within the window—each option resizes the windows differently. Cascading the worksheets enables you to keep the size of each worksheet window as large as possible instead of dividing the screen into small display areas for each workbook.
2. Explain when you would split the panes of a worksheet instead of setting up a tiled arrangement of two or four windows.
Both options will achieve the same end result. Splitting the panes is faster to set up and uses slightly less screen space than a tiled arrangement because only one worksheet window is used. However, each pane can only display different parts of the same worksheet. Using two or four windows will display the same workbook in different windows, each of which has their own set of horizontal and vertical scroll bars.
3. Give examples of when you might freeze the panes.
You might freeze the panes on a large worksheet where you need to lock certain rows or columns on the screen while viewing any part of that worksheet. For example, freeze the column titles or the labels in the first three columns to show the performance in the most recent years compared to the last five years.