ISBC 117 Microsoft Excel Exam
Cell B2 contains the formula =SUME(A1:A100) with the name of the SUM function misspelled as SUME. What error value will appear in the cell?
#NAME!
What is the 3-D reference to cell E6 in the adjacent Summary 1, Summary 2, and Summary 3 worksheets?
'Summary 1:Summary 3'!E6
A list of college students includes a code to indicate the student's gender (male or female) and a field to identify the student's major. Would you use a filter or a PivotTable to (a) create a list of all females majoring in history and (b) count the number of males and females in each major?
(a) filter, (b) PivotTable
If 4/30/2017 and 5/31/2017 are the initial values, what are the next two values AutoFill will insert?
6/30/2017, 7/31/2017
If cell R10 contains the formula =$R1+R$2, which is then copied to cell S20, what formula is entered in cell S20?
=$S11+S$12
What formula would you enter in the Summary worksheet to reference cell C8 in the Quarter 2 worksheet?
='Quarter 2'!C8
What formula would you enter to add the values in cells C1, C2, and C3? What function would you enter to achieve the same result?
=C1+C2+C3 =SUM(C1:C3)
What formula would you enter to count how many numeric values are in the range D21:D72?
=COUNT(D21:D72)
Convert the following equation into an Excel formula where the radius value is stored in cell E31 and the value of π is stored in cell D12: Area= pi x radius^2
=D12*E31^2
If cell Q3 is greater than cell Q4, you want to display the text "OK"; otherwise, display the text "RETRY". Write the formula that accomplishes this.
=IF(Q3>Q4,"OK","RETRY")
Write a formula to return the middle value from the values in the range Y1:Y100.
=MEDIAN(Y1:Y100)
If you insert a new worksheet named Sheet5 after Sheet4, how would you change the formula =MIN(Sheet1:Sheet4!B1) to include Sheet5 in the calculation?
=MIN(Sheet1:Sheet5!B1)
In Excel, the PI() function returns the decimal value of π. Rewrite your answer for the previous formula using this function.
=PI()*E31^2
You want to apply for a $225,000 mortgage. The annual interest on the loan is 4.8 percent with monthly payments. You plan to repay the loan in 20 years. Write the formula to calculate the monthly payment required to completely repay the loan under those conditions.
=PMT(4.8%/12, 20*12, 225,000)
Write a formula to round the value in cell A5 to the fourth decimal place.
=ROUND(A5, 4)
The range of a set of values is defined as the maximum value minus the minimum value. Write a formula to calculate the range of values in the range Y1:Y100 and then to round that value to the nearest integer.
=ROUND(MAX(Y1:Y100) - MIN(Y1:Y100),0)
If cell R10 contains the formula =R1+R2, which is then copied to cell S20, what formula is entered in cell S20?
=S11+S12
Cell E11 contains the formula =SUM(D1:D20). How does this formula change if a new row is inserted above row 5?
=SUM(D1:D21)
If you insert a new worksheet named Sheet5 before Sheet4, how would you change the formula =SUM(Sheet1:Sheet4!B1) to include Sheet5 in the calculation?
=SUM(Sheet1:Sheet4!B1)
Write the formula to display the current date in the worksheet.
=TODAY()
Write the formula to perform an exact match lookup with the lookup value from cell G5 using a vertical lookup table located in the range A1:F50. Return the value from the third column of the table.
=VLOOKUP(G5,A1:F50,3,FALSE)
Write the formula to display a date that is four workdays after the date in cell A5. Do not assume any holidays in your calculation.
=WORKDAY(A5, 4)
What is a PivotChart?
A PivotChart is a graphical representation of the data in a PivotTable.
What is a PivotTable?
A PivotTable is an interactive table used to group and summarize either a range of data or an Excel table into a concise, tabular format for reporting and analysis.
What is text data?
A combination of alphanumerical characters that form words and sentences (called a text string)
What is a conditional format?
A conditional format is a format that changes based on the value entered in the cell.
What are data bars? How do data bars differ from sparklines?
A data bar is a conditional format in which a horizontal bar is added to a cell background with the length of the bar proportional to the cell's value.
Explain the difference between a field and a record.
A field is a single piece of data that is entered in a column. A record is a group of related fields that appears in a row.
What is a histogram?
A histogram is a chart used to display the distribution of data values grouped within bins.
What is the difference between a line chart and a scatter chart?
A line chart plots a data series against a set of category values which are assumed to be arranged in sequential order. A scatter chart plots one data series against another.
What are major tick marks, minor tick marks, and chart gridlines?
A major tick mark matches each entry on the chart axis. Minor tick marks further divide the space between the major tick marks. Chart gridlines extend the tick marks into the plot area.
What is a slicer, and how does it work?
A slicer is an object used to filter data in an Excel table. It includes a button for each unique value in the field. You click a button to filter the table to show only records that contain that value.
What is a worksheet group?
A worksheet group is a collection of two or more worksheets that have been selected.
How do you add fields to a PivotTable?
After you create the blank PivotTable, you use the PivotTable Fields pane to drag fields into different areas to build the PivotTable.
The range B3:B13 is merged into a single cell. What is this cell reference?
B3
What is the cell reference for the cell located in the second column and fifth row of a worksheet?
B5
What is the reference for the nonadjacent block of cells B5 through C10 and cells B15 through D20?
B5:C10,B15:D20
Cell B2 contains the entry May 3, 2017. Why doesn't Excel consider this a text entry?
Because it's a date; all dates are numbers formatted to appear in standard date formats.
Why does the PMT function return a negative value when calculating the monthly payment due on a loan or mortgage?
Because the PMT value represents an expense to the borrower (a negative cash flow).
If the research firm is interested in comparing the numeric sizes of different ethnic groups over time, which chart should it use? Explain why.
Because the chart does not need to display the total population but only the comparison of the population values of different ethnic groups, a clustered column chart will work best.
If the research firm wants to display the changing ethnic profile of the county over time as a percentage of the county population, which chart type should it use? Explain why.
Because the chart needs to display the percentage of each ethnic to the whole, a 100% Stacked Column chart will work best.
A research firm wants to create a chart that displays the total population growth of a county over a 10-year period broken down by five ethnicities. Which chart type best displays this information? Explain why.
Because the chart needs to display the total population of the county, a stacked column chart will work best to show how that total is divided into different ethnic groups.
A researcher wants to plot weight versus blood pressure. Should the researcher use a line chart or a scatter chart? Explain why.
Because there are two data series involved in the chart, the researcher should use a scatter chart and not a line chart
What is the range reference for the block of cells C2 through D10?
C2:D10
If you have a list of employees that includes fields for gender and salary, among others, how can you determine the average salary for females using the Total row feature?
Click the Gender filter button and check only the Female check box. Insert the Total row, click the arrow that appears to the right of the total for the Salary column, and then click Average in the list of functions.
How can you display a list of economics majors with a GPA less than 2.5 from an Excel table with records for 1000 students?
Click the Major filter button, and then check only the Economics check box. Click the GPA filter button, point to Number Filters, click Less Than, and then enter the value 2.5 to specify the condition for a GPA less than 2.5.
How do you format text so that it is set vertically within the cell?
Click the Orientation button from the Alignment group on the HOME tab and select Vertical Text.
Describe how to add the workbook filename to the center section of the footer on every page of the printout.
Click the Page Layout tab, and then click the Dialog Box Launcher in the Page Setup group. Click the Header/Footer tab in the Page Setup dialog box. Click the Custom Footer button, and then click in the Center section box. Click the Insert File Name button. Click the OK button in each dialog box.
What are print titles?
Content from worksheet rows or columns that are repeated on every page of the printed sheet
What keyboard shortcut makes the active cell to cell A1?
Ctrl+Home
How do you autofit a column to match the longest cell entry?
Double-click its right border or select the column, go to the Home tab, click the Format button in the Cells group, and then click AutoFit Column Width.
How do you change the scale of a chart axis?
Double-click the axis scale to select it and open the Format pane. Enter the new axis scale into the minimum and maximum value boxes of the new scale and the specify the space between the minor and major tick marks.
Stephen is entering hundreds of temperature values into an Excel worksheet for a climate research project, and he wants to speed up data entry by leaving freezing point values as blanks rather than typing zeroes. Explain why this will cause complications if he later tries to calculate the average temperature from those data values.
Excel does not treat blanks as zeroes and will skip the blank values in calculating an average from a range of cell
An Excel table of professional baseball player data consists of team name, player name, position, and salary. What area of a PivotTable report would be used for the Team name field if you wanted to display the average salaries by position for all teams or an individual team?
FILTERS area
An Excel table includes records for 500 employees. What can you use to calculate the average salary of employees in the finance department?
Filter the table to show only the finance department, and then use the AVERAGE function in the Total row.
Explain filtering.
Filtering temporarily hides any records that do not meet the specified criteria.
If you insert cells into the range C1:D10, shifting the cells to the right, what is the new location of the data that was previously in cell F4?
H4
If the research firm wants to display the ethnic profile of the county only for the current year, which chart should it use? Explain why.
If the number of different ethnic groups is not too large, a pie chart might work the best; otherwise, a column or bar chart
In Excel, what is the difference between a range of data and a structured range of data?
In Excel, a range of data is any block of cells, whereas a structured range of data has records and fields organized in rows and columns.
What is the difference between a what-if analysis by trial and error and by Goal Seek?
In a what-if analysis the input value is changed and its effect on a calculated value is observed; in Goal Seek the value for the calculated value is specified and Excel determines the input value needed to achieve it.
A data series contains values grouped into 10 categories. Would this data be better displayed as a pie chart or a column chart? Explain why.
It would be better displayed as a column chart because as the number of slices in the pie chart increases, the harder it is to distinguish different slice values. Generally, you should not have more than 5 or 6 slices in the pie chart for readability
What function do you use to determine how many payment periods are required to repay a loan?
NPER
Describe four ways of viewing the content of a workbook in Excel.
Normal view shows the columns and rows of the worksheet. Page Layout view shows the layout of the worksheet as it appears on a page. Page Break Preview shows the page breaks within the worksheet. Formula view shows formulas rather than the values returned by the formulas.
How do you display the formulas used in a worksheet instead of the formula results?
Press the Ctrl+' keys to switch to formula view
After you update data in an Excel table, what must you do to a PivotTable that is based on that Excel table?
Refresh the PivotTable.
You need to reference cell Q57 in a formula. What is its relative reference? What is its absolute reference? What are the two mixed references?
Relative: Q57 Absolute: $Q$57 Mixed: $Q57 and Q$57
How do you apply the same page layout to all of the worksheets in a workbook at one time?
Select a worksheet group that consists of all the sheets in the workbook, click the Page Layout tab, and then select the page layout specification that you want to apply to all worksheets in the group.
Explain how to use the Quick Analysis tool to calculate a running total of the values in the range D1:D10.
Select the D1:D10 range and then click the Quick Analysis button, select Totals from the Quick Analysis menu and then scroll to and select Running Total to insert the running total values in E1:E10.
How do you insert a manual page break in a worksheet?
Select the first cell below the row at which you want to insert the page break, click the Breaks button in the Page Setup group on the Page Layout tab, and then click Insert Break.
How would you highlight the top 10 percent values of the range A1:C20?
Select the range A1:C20. On the Home tab, in the Styles group, click the Conditional Formatting button. Point to Top/Bottom Rules, and then click Top 10%. In the dialog box, verify that 10 is entered for the percent of the items to show, and then click the OK button
What is the difference between a serif font and a sans serif font?
Serif fonts have extra decorative strokes at the end of each character. Sans serif fonts do not include these decorative strokes.
An Excel table of college students tracks each student's first name, last name, major, and year of graduation. How can you order the table so that students graduating in the same year appear together in alphabetical order by the students' last names?
Sort by year of graduation and then by student last name.
What are sparklines? Describe the three types of sparklines.
Sparklines are compact graphs containing very few chart element and displayed within a worksheet cell. The line sparkline displays data values in a line chart. The column sparkline displays values using a column chart. The win/loss spark displays positive and negative values
Explain the difference between the COUNT function and the COUNTA function.
The COUNT function counts the number of cells containing numeric values and excludes blanks and text cells. The COUNTA function counts the number of non-blank cells and includes both numeric and text cells
Describe the differences between Currency format and Accounting format.
The Currency style floats the currency symbol to the left of the first digit in the monetary value, while the Accounting style fixes the currency symbol on the left edge of the cell, encloses negative values in parenthesis, and displays zero values using dashes
What is the purpose of the Freeze Panes button in the Window group on the View tab? Why is this feature helpful?
The Freeze Panes button enables you to keep, or freeze, rows and columns so that they don't scroll out of view as you move around the worksheet. By freezing the rows and columns that contain headings, it's easier to understand the data in each record as you scroll.
After you display subtotals, how can you use the Outline buttons?
The Outline buttons show or hide details from the subtotal report. Each level adds or removes another level of detail. Level 1 displays the least amount of detail
What is the difference between the chart area and the plot area?
The chart area contains the entire chart, including the plot area. The plot area contains the region of the chart in which the data values are plotted.
Red is a standard color. What happens to red text when you change the workbook's theme?
The color is not changed under the new theme, and it remains red.
What is the General format?
The default Excel number format that displays numbers just as they are entered
A cell containing a number displays #######. Why does this occur, and what can you do to fix it?
The number of digits could not fit within the width of the cell. To correct this problem you can either increase the width of the column or reduce the number of digits to the right of the decimal point.
What is the difference between a theme color and a standard color?
Theme colors are the colors that belong to a workbook's basic design, giving the elements in the workbook a uniform appearance. A standard color is always available to every workbook regardless of which themes might be in use.
How do you select an adjacent worksheet group? How do you select a nonadjacent worksheet group? How do you deselect a worksheet group?
To select an adjacent group of worksheets, click the first sheet tab, press and hold the Shift key, and then click the sheet tab of the last worksheet in the range. To select a nonadjacent group of worksheets, click the sheet tab of one of the worksheets in the group, press and hold the Ctrl key, and then click the sheet tabs of the remaining worksheets in the group. Deselect a worksheet group by either clicking the sheet tab of a worksheet that is not in the group, or right-clicking one of the sheet tabs in the group and clicking Ungroup Sheets on the shortcut menu.
How do you enter a number so that excel sees it as a text?
Type an apostrophe (') directly before the number
An Excel table of sales data includes the Month field with the values Jan, Feb, Mar, ... Dec. How can you sort the data so the sales data is sorted by Month in chronological order (Jan, Feb, Mar, ... Dec)?
Use a predefined custom list.
How can you quickly find and delete duplicate records from an Excel table?
Use the Remove Duplicates dialog box to locate and delete duplicate records from an Excel table
Where can you access all the formatting options for worksheet cells?
Within the Format Cell dialog box
Describe two methods of applying the same format to different ranges.
You can use the Format Painter to copy and paste the format from one range into another, or you can apply the same cell style to the ranges.
Explain the relationship between the Sort and Subtotal commands.
You must first sort the data for which you want to calculate subtotals because subtotals are inserted whenever the value in the specified field changes.
When would you use a waterfall chart?
You would use a waterfall chart to display the contribution of positive and negative values to an overall total, such as with a profit-loss statement involving revenue and expense values
What is a print area?
a range or ranges in a worksheet that you specify to be printed
How are page breaks indicated in Page Break Preview?
as dotted blue lines
What are the two types of sheets used in a workbook?
chart sheets and worksheets
If you sort table data from the most recent purchase date to the oldest purchase date, in what order have you sorted the data?
descending order (Newest to Oldest)
What three elements indicate that a range of data is an Excel table?
filter buttons appear in the column headers, a table style format is applied to the table, and the Table Tools Design tab appears on the ribbon.
What orientation would you use to make the printed page wider than it is tall?
landscape
How are fields such as region, state, and country most likely to appear in a PivotTable?
row labels, column labels, or filter
Explain what the formula =AVERAGE(Sheet1:Sheet4!B1) calculates.
the average value found in cell B1 in all of the worksheets from Sheet1 to Sheet4
What three chart elements are included in a pie chart?
the chart title, the chart legend, and data labels
How are fields such as revenue, costs, and profits most likely to appear in a PivotTable?
values
