COM S Exam 1
Describe the purpose of each of these chart types: (a) column (b) bar (c) line (d) pie (e) combo.
(a) A column chart displays values in vertical columns where the height represents the value. Categories display along the horizontal (category) axis. (b) A bar chart displays values in horizontal bars where the length represents the value. Categories display along the vertical (category) axis, which helps when the category labels are too long. (c) A line chart shows trends over time, such as months, years, or decades. (d) A pie chart shows the proportion of individual data point to the whole. (e) A combo chart combines two chart types, such as column and line, to represent different data types (such as individual values and percentages).
Which of the following characters are wildcards in Excel? (Check all that apply.)
* ?
-This helps make a chart clearer. -Decides what is hidden or displayed in a chart -Contains no spreadsheet data -Sets the formatting of the chart -Describes the entire chart
-Chart element -Chart filter -Chart sheet -Chart style -Chart title
-Columns are set side by side -Compares individual points to the whole category -Compares values across categories -Changes individual data points to percentage -Adds an additional dimension to your chart
-Clustered column chart -Stacked column chart -Column chart -100% stacked column chart -3-D chart
-Colors are based on the relative values in the cell -Symbols or signs that classify data -Using conditions to limit the visible data -Arranging records -Various widths represent the cell's value
-Color scale -Icon set -Filtering -Sorting -Data bars
-Combines two chart types -One variable is plotted on the horizontal axis and the other on the vertical axis -Subtypes include: High-Low-Close, Open-High-Low-Close, Volume-High-Low-Close, and Volume-Open-High-Low-Close -Colors are used between the lines -Uses two dimensions and a continuous curve
-Combo chart -X Y (scatter) chart -Stock chart -Area chart -Surface chart
-A conditional formatting that displays a horizontal gradient or solid fill indicating the cell's relative value compared to other selected cells. -The process of listing records or text in a specific sequence, such as alphabetically by last name. -The process of specifying conditions to display only those records that meet those conditions. -A set of rules that applies specific formatting to highlight or emphasize cells that meet specifications.
-Data Bar -Sorting -Filtering -Conditional Formatting
-Shows the precise value of a data point -A precise value in a chart -A group of data points -A grid used to plot data in a chart -Helps to distinguish between various data series
-Data Label -Data Point -Data Series -Data Tabel -Legend
-Easier to read than a legend -Helps you use predefined layouts -Applying features to make it easier on disabled users -Examples include: line, column, win/loss
-Data labels -Quick Layout -Accessibility Compliance -Sparklines
-Smallest piece of data collected -Complete set of fields for one entry -Structured range -Includes complimentary fill colors -Appears below the last row in a table
-Field -Record -Table -Table Style -Table Row
-The smallest data element contained in a table, such as first name, last name, address, and phone number. -A structure that organizes data in a series of records (rows), with each record made up of a number of fields (columns). -The process of keeping rows and/or columns visible onscreen at all times even when you scroll through a large dataset.
-Field -Table -Freezing
-Displays the field names when checked -The formatting of this row/these rows take/takes priority over column formatting -Makes sure that the row headings stand out -Often used for aggregated data -Alternate fill colors distinguish one row from another row
-Header Row -Total Row -First Column -Last Column -Banded Rows
-Removes field names when not checked -Displays a total row when selected -Makes sure that the row headings stand out -Used to make aggregated data stand out -Displays on the right side of each heading in the header row
-Header Row -Total Row -First Column -Last Column -Filter Button
-Formats cells if a date falls in a certain range -Formats cells if they fall in the lowest % (where % determined by the user) -Formats cells with colors whose width is proportional to the other values in the range -Formats cells with gradients of color depending on the cell's values compared to the other values in the range -Uses symbols to represent the comparison of values
-Highlight Cells Rules -Top/Bottom Rules -Data bars -Color Scales -Icon Sets
=???(B7>9, "Bigger", "Smaller") =???(12%/12, 360, -120000) =???(A4, B4:F9, 2) =???(B9:E21) =???()
-IF -PMT -VLOOKUP -SUM -NOW
-Symbol or signs that classify data into three, four, or five categories, based on the values in a range. -The range of cells within a worksheet that will print. -A predefined formula that calculates an aggregate value, such as totals, for values in a range, a table, or a database.
-Icon set -Print area -SUBTOTAL Function
-Sort Z to A -Sort Oldest to Newest -Sort Largest to Smallest -Sort A to Z -Sort Newest to Oldest
-Karen; Doug; Barb -1/16/1957; 12/26/1959; 1/7/87 -58; 29; 27 -Number; Othniel; Psychology -11/20/2015; 8/20/2015; 5/25/91
-Line connect data points -Uses slices to display data points -Uses horizontal bars -Uses vertical bars -Chart resides in one cell
-Line chart -Pie chart -Bar chart -Column chart -Sparkline
-Has the same starting and ending point -One or more pieces are separated from the others -Helps you to focus on particular data -Seen as 8 circles
-Parallel range -Exploded pie chart -Chart filter -Sizing handles
-The range of cells that will be printed -Sequence of printed pages -Where data will print on the next page -Keeps certain rows or columns on the screen at all times -Rules used to emphasize cells
-Print area -Print order -Page break -Freezing -Conditional formatting
-Spokes radiate from the center to the outer edges -Similar to a column chart -Similar to a line chart -Often used in educational, scientific, and medical experiments -Used for two different but related data types
-Radar -Histogram -Area -X Y (scatter) chart -Combo chart
-A group of related fields representing one entity, such as data for one person, place, event, or concept. -The rules that control the fill color of the header row, columns, and records in a table. -An indication of where data will start on another printed page.
-Record -Table Style -Page Break
-1, 2, 3, 4, 5 -Winona Lake, Winchester, Perrysburg, Clear Spring -Return 52 from the list: 29, 47, 21, 52, 1 -Return 30 from the list: 29, 47, 21, 52, 1 -Return 29 from the list: 29, 47, 21, 52, 1
-Sort Ascending -Sort Descending -MAX -AVERAGE -MEDIAN
-A set of rules that governs the structure and components for properly entering a function. -Displays the current date. -Indicates a cell's specific location; the cell reference does not change when you copy the formula. -An input, such as a cell reference or value, needed to complete a function.
-Syntax -TODAY Function -Absolute Cell Reference -Argument
-Formats any cell that has content that matches the first content in the first selected cell -Formats cells that have exactly the same value -Formats cells that have no matching values -Formats cells that have the value -Removes any conditional formatting
-Text Contains -Duplicate Values -Unique Values -Equal To -Clear Format
-A table row that appears below the last row of records in an Excel table and displays summary or aggregate statistics, such as a sum or an average. -A conditional format that displays a particular color based on the relative value of the cell contents to the other selected cells. -The sequence in which the pages are printed. -A tag or use of a table element, such as a field label, as a reference in a formula.
-Total Row -Color Scale -Print Order -Structured Reference
T/F -You can freeze as many columns and rows at the same time as you desire. -In a data table calculated columns will copy down the table automatically. -Locking is the process of making sure that certain rows or columns are visible at all times. -This could be a valid formula in Excel: =[@Cost]-[@Pre_Paid_Taxes] -After a table has been named, that name can be used in functions instead of cell references. -You can manually adjust a page break by pointing to the page break line and waiting for the four-headed arrow to appear, then drag the page break to your desired location. -Selecting a range automatically makes it a data table. -In Normal view or Page Layout view the data to be printed has a dark blue border. -You can select multiple print areas. -To print row labels on the left side of each page you would select the row(s) that contain the labels in the rows to repeat at left box.
-True -True -False -True -True -False -False -False -True -False
-Field headings without row references -Contains the table name -Uses a field name as the reference in a table -Calculates an aggregate value -Displays below the last row in a table
-Unqualified reference -Fully qualified structured reference -Structured reference -SUBTOTAL function -Total row
-Horizontal border -Vertical border -Holds the entire chart -Both axes form a border for this -Uses color or patterns to identify each data series
-X-axis -Y-axis -Chart area -Plot area -Legend
-The last possible column in a worksheet. -A group of cells selected at the same time. -A measurable amount. -Also called pointing. -A combination of letters not used in calculations.
-XFD -Range -Value -Semi- Selection -Text
T/F 1. Pie charts can only display one data series. 2. A parallel range is one that consists of the same starting point of another range, but can have a different end point. 3. You cannot enter data or formulas on a chart sheet. 4. It is a good practice to delete any blank rows or columns before creating a chart. 5. Excel normally uses all the selected data in creating a chart. 6. Effective charts never overwhelm an audience. 7. Once a data source is selected for a chart, the only to change the data source is to start over creating the chart. 8. A stacked column chart displays multiple columns for each category. 9. As long as you pick the correct chart you can mix numeric value systems, such as dollars and number of units sold.
1. True 2. False 3. True 4. True 5. True 6. True 7. False 8. False 9. False
The ROUND functions requires how many arguments?
2
If you use the Color Scale conditional formatting, you are limited to how many colors (and their gradations)?
2 or 3
By how many levels will Excel allow you to sort?
64
Which of the following is an unqualified structured reference? =[Purchase_Price] - [Down_Payment] =Sales[Purchase_Price] - Sales[Down_Payment] =Purchase_Price - Down_Payment =[Sales]Purchase_Price - [Sales] Down_Payment
=[Purchase_Price] - [Down_Payment]
How does Excel indicate that a column has been selected in a structured reference?
A colored border appears around that column
What is a sparkline, and why would you insert one?
A sparkline is a miniature chart that is embedded within a cell. You use it to create a quick visual within worksheet cells when you don't need to chart an entire data set.
How can you use Quick Analysis to create a chart?
After you select a range, the Quick Analysis button appears so that you can click the button for quick available options. Click Charts to display recommended chart types based on the data you selected in the Quick Analysis gallery.
Including what, distorts the effectiveness of charting data points?
Aggregates
In the task pane, what does a diagonal black triangle next to a chart category indicate?
All the category's options are displayed
What chart type is preferable when the category names are long?
Bar
Which date filter option enables you to restrict the view to only dates that occur in March of 2018?
Between
Using the data bars conditional formatting, how is the highest value represented?
By the longest bar
You have a large dataset that will print on several pages. You want to ensure that related records print on the same page with column and row labels visible and that confidential information is not printed. You should apply all of the following page setup options except which one to accomplish this task? Set a print area Print titles Adjust page breaks Change the print page order
Change the print page order
List at least four types of appropriate labels that describe chart elements. What types of things can you do to customize these labels?
Chart title, horizontal axis title, vertical axis label, data label, legend, etc. You can change the font, font size, font color, etc.
How can you change a chart so that the data in the legend are on the X-axis and the data on the X-axis are in the legend?
Click Switch Row/Column in the Data group on the Design tab.
What are two ways to change the color scheme of a chart?
Click the Chart Style icon, click COLOR, and then change the color scheme. Click Change Colors in the Chart Styles group on the Chart Tools Design tab.
Why would you convert a range of data into an Excel table?
Column headings remain onscreen without having to use Freeze Panes. Table styles easily format table rows and columns with complementary fill colors. Calculated columns where the formulas copy down the columns automatically are available to create and edit. Calculated total row enables the user to implement a variety of summary functions. Structured references can be used instead of cell references in formulas. Table data can export to a SharePoint list.
How is conditional formatting is similar to an IF function?
Conditional formatting is similar to the IF function in that they both perform a task conditionally—based on the truth of the condition. If a condition is true, an IF function provides a particular result. If a condition is met, the conditional formatting rule applies formatting.
How is data bar conditional formatting helpful when reviewing a column of data?
Data bar conditional formatting displays a horizontal gradient or solid fill indicating the relative value of each cell's value to those in the applied range. The higher the value compared to other cells, the wider the data bar.
List at least five ways you can filter numbers.
Exact number(s) by clicking check box(es), Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Less Than, Less Than or Equal To, Between, Top 10, Above Average, Below Average
What is the purpose of exploding a slice on a pie chart?
Exploding a slice moves that slice slightly away from the rest of the pie chart so that the exploded slice is emphasized.
What is the keyboard shortcut to create an absolute reference?
F4
You are working with a large worksheet. Your row headings are in column A. Which command(s) should be used to see the row headings and the distant information in columns X, Y, and Z?
Freeze Panes command
Why should you not include aggregates, such as totals or average, along with individual data points in a chart?
Including aggregates diminishes the effectiveness of comparing individual data points. Including aggregate data is an invalid data point. Totals should not be included with non-aggregate data points.
List at least four guidelines for planning a table in Excel.
Keep field names on the top row. Keep field names relatively short, descriptive, and unique. Format the field names so that they stand out from the data. Enter data for each record on a row below the field names. Do not leave blank rows between records or between the field names and the first record. Delete any blank columns between fields in the dataset.
Which statement is not a recommended guideline for designing and creating an Excel table? Avoid naming two fields with the same name. Ensure that no blank columns separate data columns within the table. Leave one blank row between records in the table. Include field names on the first row of the table.
Leave one blank row between records in the table.
What is the default data table style?
Medium 2
What chart type represents data as spokes going from the center to the outer edge of the chart?
Radar
What are some of the fill options you can apply to a chart area or a plot area?
Solid, gradient, picture, and texture
What are two ways to arrange (sort) dates?
Sort Oldest to Newest Sort Newest to Oldest
What is the purpose of sorting data in a table?
Sorting helps you arrange data in different sequences so that you can display the data in different perspectives for analysis.
What is the purpose of freezing panes in a worksheet?
TData bar conditional formatting displays a horizontal gradient or solid fill indicating the relative value of each cell's value to those in the applied range.
What happens when you copy a formula containing a relative cell reference one column to the right?
The cell references in the copied formula adjust one column to the right. For example, if the original formula is =B1+C1, the copied formula would be =C1+D1. The row numbers remain the same while the column letters change relative to the direction (to the right) that you copy the formula.
What data does Excel print by default?
The entire dataset on the active sheet
What are the first three arguments of a PMT function? Why would you divide by or multiply an argument by 12?
The first argument is the rate of the loan. If the rate is expressed on an annual basis and you want to calculate monthly payments, you must divide the rate by 12. The second argument is nper, the number of periods for the loan. If the term is expressed in years, you must multiply the number of years by 12 to get the total number of monthly payments. The third argument is pv, or the present value of the loan.
When you have frozen rows and columns, what happens when you press the Ctrl+Home combination?
The first unfrozen cell becomes the active cell
What is the order of operations? Provide and explain two examples that use four different operators: one with parentheses and one without.
The order of operations is a set of rules that specify the sequence in which arithmetic calculations are performed from left to right in the following order of importance: Parenthesis, exponentials, multiplication and division, and addition and subtraction. =A1+A2*A3/(A4-A5) In this above example, the A4-A5 in parentheses is calculated first. Then A2*A3 is calculated. That result is divided by the result from A4-A5. Finally, that value is added to the value in A1. =A1+A2*A3/A4-A5 In this second example without parentheses, the order of operations is this: A2*A3 is calculated first. That result is then divided by A4. That result is added to A1, and A5 is finally subtracted from that sum.
What are six options you can control after selecting a table style?
The six options include header row, total row, first column, last column, banded rows, and banded columns.
What does pressing the Enter key while in a cell, do?
The value is set, and the cell below becomes the active cel
Which Conditional Formatting rule is best suited to apply formatting to the top five values in a range of values?
Top 10 Items
What conditional formatting would be helpful to identify the three movies with the highest revenue playing at theaters?
Top/Bottom Rules
If you would like to set a conditional formatting rule based on the function =AND(G6="Finance",H7<7000), which of formatting rule type is needed?
Use a formula to determine which cells to format
What should you do to ensure that records in a table are unique?
Use the Remove Duplicates command.
Which of the following is not an aggregate function that can be applied in a total row? MAX AVERAGE COUNT VLOOKUP
VLOOKUP
What is the benefit of using a mixed reference?
When applicable, mixed references can be used in place of absolute references as a method of shortening the length of the formula.
What steps should you take to ensure that column labels display on each printed page of a large dataset?
You can select the Rows to repeat at top option in the Print titles section on the Sheet tab in the Page Setup dialog box.
Why would you want to insert page breaks instead using the automatic page breaks?
You might want to adjust page breaks if the automatic page breaks occur in undesirable locations. For example, a worksheet might contain data organized by groups in which the automatic page break occurs after the first row of a particular group. You can insert a page break before that row so that the data will be printed on the same page as the rest of its related data.
How do you decide whether to move a chart within the worksheet where you created it or move it to a chart sheet?
You might want to keep it on the sheet with the data source so that you can print the data and chart on one sheet of paper. However, if you are creating several charts, you can organize them by moving them to individual chart sheets.
Assume you are filtering a list and want to display records for people who live in Boston or New York. What settings do you enter in the Custom AutoFilter dialog box for that field?
equals Boston Or equals New York
A range is specified by its ...
top left and bottom right cells
-Relative reference -Mixed reference -Absolute reference -Function
- =A54 - =$A54 - =$A$54 - =SUM(A54:B97)
Match the result with the function using the following numbers 21, 42, 97, 1, 14, 199 [to two decimal places]. -MEDIAN -AVERAGE -MIN -COUNT -MAX
-31.50 -62.33 -1 -6 -199
-Does not change when you copy and paste a formula. -Changes when you copy and paste a formula. -Part of it changes when you copy and paste a formula. -Required item in a function. -Simplifies complex calculations.
-Absolute cell references -Relative cell references -Mixed cell references -Argument -Function
-The position of data between the cell margins. -Formatting that enables a label to appear on multiple lines within the current cell. -The background color appearing behind data in a cell. -A combination of cell references, operators, values, and/or functions used to perform a calculation.
-Alignment -Wrap text -Fill color -Formula
-Matches the letters you type to another value in the column if the letters match -Helps complete a logical sequence -The letter in the cell reference "F4" -The number in the cell reference "F4" -Looks like a check mark next to the formula bar
-Autocomplete -Autofill -Column heading -Row heading -Enter icon
-Label that describes either the category axis or the value axis. -Key that identifies the color, gradient, picture, texture, or pattern fill assigned to each data series in a chart. -Chart type that compares categories of data horizontally. -Chart that shows each data point in proportion to the whole data series.
-Axis title -Legend -Bar chart -Pie chart
-Only uses calculable values -Includes text, but not blank cells. -Only includes empty cells -Evaluates true and false statements
-COUNT -COUNTA -COUNTBLANK -IF
-Chart that groups columns side by side to compare data points among categories. -Miniature chart contained in a single cell. -Chart type that shows trends over time in which the value axis indicates quantities and the horizontal axis indicates time. -Label that describes the entire chart.
-Clustered column chart -Sparkline -Line chart -Chart title
-Numeric value that describes a single value on a chart. -Chart that contains two chart types, such as column and line, to depict two types of data, such as individual data points and percentages. -A circle that enables you to adjust the height or width of a selected chart. -Horizontal or vertical line that extends from the horizontal or vertical axis through the plot area.
-Data Point -Combo Chart -Sizing handle -Gridline
-Accepts the data -Reverts back to previous data -Displays information about a command or operation as it is being performed -Allows you to see more or less on the screen at one time -Shows the contents of the active cell
-Enter -Cancel -Status bar -Zoom Control -Formula Bar
-A small green square at the bottom-right corner of a cell. -The horizontal measurement of a column. -The vertical measurement of a row. -A rectangular group of cells.
-Fill handle -Column Width -Row height -Range
-Matches letters as you type -Can be used to apply formatting and insert basic functions -Pop-up description -Evaluates to True or False -A set of rules
-Formula AutoComplete -Quick Analysis -Function ScreenTip -Logical Test -Syntax
-Evaluates a condition and returns one value if the condition is true and a different value if the condition is false. -Calculates the total of values contained in one or more cells. -Calculates the periodic payment for a loan with a fixed interest rate and fixed term.
-IF Function -SUM Function -PMT Function
-A range that contains data for the basis of the lookup and data to be retrieved. -Calculates the arithmetic mean, or average, of values in a range. -Identifies the midpoint value in a set of values. -Displays the current date and time.
-Lookup table -AVERAGE Function -MEDIAN Function -NOW Function
-Identifies the highest value in a range. -Tallies the number of cells in a range that contain values. -Looks up a value in a vertical lookup table and returns a related result from the lookup table.
-MAX Function -COUNT Function -VLOOKUP Function
-Midpoint value -Average -Sum -Table array -Breakpoint
-Median -Arithmetic Mean -Total -Range used for looking up -Lowest value in a series
-Returns the system date and time -Returns the system date -Helps figure out how much a loan will cost each month
-NOW () -TODAY () -PMT
-Displays the address of the active cell -Always begins with an equals sign (=) -Small green square used to perform various actions -Also known as semi-selection
-Name box -Formula -Fill Handle -Pointing
-Identifies the address of the current cell. -Displays the content (text, value, date, or formula) in the active cell. -Displays the name of a worksheet within a workbook. -The intersection of a column and a row.
-Name box -Formula bar -Sheet tab -Cell
-Does not display margins or page breaks. -Displays data and margins -Displays data and page breaks -Icons that let you change views -Allows you to go to the first, previous, next, or last sheet in a workbook
-Normal view -Page Layout view -Page Break Preview -View Controls -Sheet tab navigation
-Section of a chart that contains graphical representation of the values in a data series. -A container for the entire chart and all of its elements. -An identifier that shows the exact value of a data point in a chart. -Displays incremental numbers to identify approximate values, such as dollars or units, of data points in a chart.
-Plot area -Chart area -Data label -Value axis
-Indicates a cell's location from the cell containing the formula; the cell reference changes when the formula is copied. -Contains both an absolute and a relative cell reference in a formula; the absolute part does not change but the relative part does when you copy the formula. -An expression that evaluates to true or false. -Displays the lowest value in a range.
-Relative Cell Reference -Mixed Cell Reference -Logical Test -MIN Function
-Moves right one cell in the same row -Moves active cell down one screen -Moves active cell to column A in that row -This does nothing by itself -Moves active cell to column A, row 1
-Right arrow -Page down -Home -End -Ctrl + Home
-Includes letters, numbers, symbols, and spaces. -A number that represents a quantity or an amount. -Rules that control the sequence in which Excel performs arithmetic operations. -Enables you to copy the contents of a cell or cell range or to continue a sequence by dragging the fill handle over an adjacent cell or range of cells.
-Text -Value -Order of Operations -Autofill
-Data is organized in columns -Data is organized in rows -Has the data used with HLOOKUP or VLOOKUP -Cell that contains the value to be looked up -Column that contains the return values
-VLOOKUP -HLOOKUP -Lookup Table -Lookup Value -Column Index Number
-A spreadsheet that contains formulas, functions, values, text, and visual aids. -A file containing related worksheets. -A range of cells containing values for variables used in formulas. -A range of cells containing results based on manipulating the variables.
-Worksheet -Workbook -Input area -Output area
-Chart type that shows the relationship between two variables. -Group of related data points that display in row(s) or column(s) in a worksheet. -Window of options to format and customize chart elements. -Provide descriptive labels for the data points plotted in a chart.
-X Y (scatter) chart -Data series -Task pane -Category axis
T/F 1. The contents of a cell can be seen in the naming box. 2. In Excel, columns are identified by numbers and rows by letters. 3. The fill handle allows you to copy and paste formulas to adjacent cells. 4. You can hide or delete multiple rows or columns by using the Ctrl or Shift key and then selecting the rows or columns you want to hide or delete. 5. You cannot hide column A or row 1 because they cannot be unhidden. 6. The cell at row 4 and column B, would be identified as 4B. 7. Using Alt + Enter is a text wrapping option.
1. False 2. False 3. True 4. True 5. False 6. False 7. True
T/F 1. A lookup table should contain at least two rows and two columns, not counting headings. 2. You could use both A3>b9 or B9<a3 as the test in an IF function and get the same results. You would just switch the second and third arguments. 3. If you are looking up exact values in a lookup table then it does not matter how the table is sorted. 4. It is very important to sort the first column in a table lookup array in descending order. 5. This is not a valid way to use the MAX function: =MAX(a4:d11, f1:f2) 6. The default calculation using the PMT function will produce a negative number. 7. The COUNTA function only counts text entries. It does not count calculable values or blank cells. 8. Excel assigns date number 1 to January 1, 1900. 9. When entering functions, the square brackets, [], indicate required values.
1. True 2. True 3. True 4. False 5. False 6. True 7. False 8. True 9. False
You want to create a single chart that shows the proportion of yearly sales for five divisions for each year for five years. Which type of chart can accommodate your needs?
100% stacked column chart
A formula containing the entry =$B3 is copied to a cell one column to the right and two rows down. How will the entry appear in its new location?
=$B5
Which of the following would not be considered text? -Phone number (111.111.1111) -Social Security number (111-11-1111) -Part number (PT111) - = 2*2
=2*2
What function would most efficiently accomplish the same as =(B5+C5+D5+E5+F5)/5?
=AVERAGE(B5:F5)
Which of the following functions should be used to insert the current date and time in a cell?
=NOW()
Which of the following is not an appropriate use of the SUM function? =SUM(B3:B45) =SUM(F1:G10) =SUM(A8:A15, D8:D15) -=SUM(D15-C15)
=SUM(D15-C15)
What is a nested function, and why would you create one?
A nested function is where one function is used as an argument within another function. You use nested functions to create more complex functions that provide sophisticated calculations that otherwise would be very difficult.
Which of the following is not a valid keystroke for navigating around a worksheet? Ctrl + Home Ctrl + End Alt + Up arrow Page Down
Alt + Up arrow
What is a fast way to apply several formats at one time?
Apply a cell style
Given the formula =B1*B2+B3/B4^2, what operation is calculated first?
B4^2
Which of the following is not a type of sparkline? Line Bar Column Win-Loss
Bar
Look at the stacked bar chart in Figure 3.35 (below). Which of the following is a category on the category axis? -Thousands -Job Titles -CIS Managers -700
CIS Managers
What do you click to remove a data series from a chart so that you can focus on other data series?
Chart Filters
Currently, a column chart shows values on the value axis, years on the category axis, and state names in the legend. What should you do if you want to organize data with the states on the category axis and the years shown in the legend?
Click Switch Row/Column in the Data group on the Design tab.
You selected and copied worksheet data containing formulas. However, you want the pasted copy to contain the current formula results rather than formulas. What do you do?
Click the Paste arrow in the Clipboard group and select Values & Source Formatting.
If you want to show exact values for a data series in a bar chart, which chart element should you display?
Data Labels
Which of the following does not display automatically when you create a clustered column chart? Data labels Chart title placeholder Gridlines Legend
Data Labels
When would it be useful to display formulas instead of formula results in a worksheet?
Displaying formulas is helpful to check formulas. You can see more formulas at one time instead of clicking each cell containing a formula and then looking at the Formula Bar. You can also print cell formulas if you find it is easier to proofread formulas on a printout.
Which of the following characteristics is not applicable to the Accounting Number Format? -Dollar sign immediately on the left side of the value -Commas to separate thousands -Two decimal places -Zero values displayed as hyphens
Dollar sign immediately on the left side of the value
Which step is not part of planning a worksheet design? -Decide what input values are needed. -State the purpose of the worksheet. -Decide what outputs are needed to achieve the pur. pose. -Enter labels, values, and formulas.
Enter labels, values, and formulas.
What keyboard shortcut will automatically update any formulas using =TODAY() or =NOW()?
F9
To look up an exact match, what should you enter in the range_lookup argument?
FALSE
Which of the following is not an alignment option? -Increase Indent - Merge & Center -Fill Color -Wrap Text
Fill Color
What group is not found on the Sparkline Tools Design tab? -Format -Type -Show -Style
Format
When you start to type =AV, what feature displays a list of functions and defined names?
Formula AutoComplete
What visual features help guide you through typing a function directly in a cell?
Formula AutoComplete provides a list of function names as you type. Function ScreenTips provide the syntax of a function as you type it, where the name of the argument you are currently typing is bold.
The best way to apply Page Setup options to multiple worksheets is to .... -group worksheets -use the Format Painter -copy and Paste the formatting codes from one sheet to another -use the Apply to Selected Sheets formatting command
Group worksheets
What should you do if you see pound signs (###) instead of values or results of formulas?
Increase the column width.
Assume that the data on a worksheet consume a whole printed page and a couple of columns on a second page. You can do all of the following except what to force the data to print all on one page? -Decrease the Scale value. -Increase the left and right margins. -Decrease column widths if possible. -Select a smaller range as the print area.
Increase the left and right margins.
Functions can be selected by using the ...............
Insert Function dialog box
Besides the Page Setup dialog box, where else can you create headers or footers?
Insert tab, Text group, Header & Footer
What is not true about an effective chart? -It always contains more than one series. -It is clear. -It is easy to understand. -It is useful.
It always contains more than one series.
Which of the following is not a valid lookup function? HLOOKUP VLOOKUP LLOOKUP LOOKUP
LLOOKUP
What is not one of the tabs in Excel? -Formulas -Page Layout -File -Mailings
Mailings
After you create a line type sparkline, what option should you select to display dots for each data point?
Markers
If cell E15 contains the formula =$C$5*J$15, what type of cell reference is the J$15 in the formula?
Mixed Reference
List at least five elements you can insert in a header or footer.
Page number, number of pages, current date code, current time code, file path code, file name code, sheet name code, and a picture
The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5?
Periodic interest rate
A 100% stacked column chart is similar to what other chart in that it converts values to percentages?
Pie
Which type of chart is the least appropriate for depicting yearly rainfall totals for five cities for four years?
Pie Chart
How can you display formulas within the cells instead of the cell results?
Press Ctrl + `
What actually changes the value Excel stores to a user determined number of decimal places?
ROUND Function
The value axis currently shows increments such as 50,000 and 100,000. What option would you select to display the values in increments of 50 and 100?
Show Axis in Thousands
Where does Excel automatically display statistics like count, average, and sum when a range of values is selected?
Status Bar
What three types of content can you enter into a cell? Give an example (different from those in the book) for each type.
Text: Microsoft Excel Value: 1234.56 Date: January 31, 2018
What are the main differences between Accounting Number Format and Currency format? Which format has its own command on the Ribbon?
The $ is at the left side of the cell for Accounting Number Format and immediately to the left of the value in Currency format. Negative values display in parentheses in Accounting Number Format and are preceded by a minus sign or a parentheses or in red in Currency. The right side of the value is at the right cell margin for Currency format, and is offset a little from the right cell margin for Accounting Number Format. The Accounting Number Format has its own command on the Ribbon.
What is the difference between the AVERAGE and MEDIAN functions?
The AVERAGE function provides a mathematical average of values in a range, whereas the MEDIAN function provides the exact midpoint value in a range in which one-half of the numbers are above the median and one-half are below.
Describe the three arguments for an IF function.
The logical_test is an expression that evaluates to true or false. The value_if_true argument contains the result if the logical_test is true. The value_if_false argument contains the result if the logical_test is false.
Which of the following is not true about the VLOOKUP function? -The lookup table must be in ascending order. -The lookup table must be in descending order. -The default match type is approximate. -The match type must be false when completing an exact match.
The lookup table must be in descending order.
How should you structure a vertical lookup table if you need to look up values in a range?
The lookup values need to be in the first column of the lookup table, and if the last optional argument for range_lookup is TRUE (or omitted), then the first column of the lookup table should be sorted in ascending order.
Why should you use a cell references instead of typing values in formulas?
The values could change. If you enter values in a formula, you would have to edit the formulas, which could introduce other errors. You should enter values in other cells and then use those cell references in the formulas. That way, when you change individual cell values, the results of the formulas will automatically update.
What are two major advantages of using an electronic spreadsheet instead of paper based ledger?
Using a calculator to perform calculations and entering results in a paper-based ledger can lead to greater inaccuracies than building formulas in a spreadsheet, especially when a value changes and new results are needed. In addition, it is easier to make data-entry changes in a spreadsheet over making changes in a paper ledger.
You just copied a range of data containing formulas. However, you want to preserve the formula results and the original number and text formatting in the pasted range. Which paste option would you select?
Values & Source Formatting
What type of data do you enter in a Function Arguments dialog box, and what are four things the dialog box tells you?
You enter the required and optional arguments for a particular function. The dialog box tells you the values stored in the respective arguments, the function results, a description of the active argument, a link to get help on the function, and a description of the function itself.
Why would you use the Paste Special options in Excel?
You might want the pasted data to be in a different format than the default paste. For example, you might want to paste formulas or values only.
Why would you insert several worksheets of data in one workbook instead of creating a separate workbook for each worksheet?
You would insert multiple worksheets in a workbook when you want to store related data together. For example, you might want to create a weekly worksheet of sales for each week in a month so that you can save the entire month's data in one workbook.
Why would you use an absolute reference in a formula?
You would use an absolute reference when you want a permanent reference to a particular cell and don't want that reference to change when you copy the formula to another location.
Which of the following is not an argument of the IF function? value_if_true value_if_false logical_test lookup_value
lookup_value
