Download Excel Functions and Features: A Comprehensive Q&A Guide and more Exams Advanced Education in PDF only on Docsity!
BUSN1410 Final Exam Study
Guide100% Pass
Can you create your own conditional formatting rules? If so, how? - ANSWER Yes; use the New Formatting Rule dialog box. QuiExcel can add text such as GX- before all the values typed in cells if the cells have been formatted using the _____ category. - ANSWER Custom _____ formatting converts numbers to exponential notation (E + n). - ANSWER Scientific How can you format a cell based on the value in a different cell? - ANSWER Use a formula. How can you tell that a filter has been defined for a column? - ANSWER A filter arrow appears in the column heading. How will numbers display when the format code is [Red];[Blue]? - ANSWER Positive numbers will display in red; negative numbers in blue. Icon sets allow you to - ANSWER classify data into three to five categories. If a custom number format is deleted, the custom formatting will be removed from which of the following? - ANSWER all the cells to which it was applied in that workbook If the custom number format code is ###.##, which value will display if the value 23.959 is entered? - ANSWER 23. When applying conditional formatting using predefined rules, the first step is to - ANSWER select a range of cells. When filtering a range using an advanced filter, start by - ANSWER adding five or six rows at the top of the worksheet. When you delete a custom rule at the Conditional Formatting Rules Manager dialog box, what happens to cells already formatted by the rule? - ANSWER The formatting is removed Which of the following numbers is represented by 2.60E + 05? - ANSWER 260,000. Which of the following would you use to set up a filter to display the names of all US states
beginning with the letter M? - ANSWER M* Which of these custom number format codes aligns numbers vertically on the decimal point? - ANSWER ????.?? Which of these custom number format codes will round values to fit the number of digits after the decimal point and fill in leading zeros? - ANSWER 000. Which of these questions would best be answered using conditional formatting? - ANSWER Which salespeople exceeded their goals last month? You can edit or delete conditional formatting rules in the _____ dialog box. - ANSWER Conditional Formatting Rules Manager You can filter values by more than one criterion at a time by using - ANSWER a comparison operator. Which of the following is not an example of a conditional formatting rule? - ANSWER Format only values in exponential notation A function includes two parts: the name of the function and the - ANSWER argument Excel stops evaluating an IF formula - ANSWER once the logical_test has been answered as true. If a range name used in a formula is deleted, cells that used the name will display this error message. - ANSWER #NAME? In a nested IF function, how does Excel help you keep track of the parentheses that belong to each IF function? - ANSWER It color-codes them In a PPMT function, the argument fv refers to the - ANSWER balance at the end of the loan. In the VLOOKUP function, what does the col_index_num argument indicate? - ANSWER the column number from the lookup table that contains the data to be placed in the formula cell Range names can be created, edited, and deleted at the Name Manager dialog box, which is opened by clicking the - ANSWER Name Manager button on the Formulas tab The ABS, RAND, SQRT, and SUMIF functions are in the _____ category. - ANSWER math and trigonometry The argument (range,criteria,average_range) is required by the _____ function. - ANSWER
(range1,criteria1,range2,criteria2)? - ANSWER COUNTIFS At the Data Validation dialog box, choose the type of data to be validated in the _____ option box on the Settings tab. - ANSWER Allow Before creating a subtotal for a range of related data, you must first - ANSWER sort the data by the fields in which the records are to be grouped Before creating subtotals for a range of data, be sure to remove any _____ within the range to be grouped and subtotaled. - ANSWER blank rows Before removing duplicate records from a table, how should you first check to see what records will be deleted? - ANSWER Click Duplicate Values in the Highlight Cells Rules at the Conditional Formatting drop-down list. By default, where does Excel display filter arrows in a table? - ANSWER next to each label in the header row By inserting a check mark in the First Column or Last Column check box in the Table Style Options group, you can - ANSWER format that column differently. If you type a formula in the first record of a new table column, Excel automatically creates a(n) - ANSWER calculated column. Insert a check mark in the Header Row check box in the Table Styles Options group to - ANSWER show or hide the column headings in the table
_____ is not one of the data validation criteria available in the Allow option box at the Data Validation dialog box. - ANSWER Fraction On the Table Tools Design tab, which of these boxes contains a check mark by default? - ANSWER Banded Rows Suppose that you have received a table containing first and last names in one column. What is the easiest way to split the names into separate columns? - ANSWER Use the Text to Columns feature The first row of an Excel table contains - ANSWER field names To select only specific subtotals and/or grand totals in a collapsed outline, click the Find & Select button, click the Go to Special option, click the _____ radio button to insert a bullet, and then click OK. - ANSWER Visible cells only Use the keyboard shortcut Shift + Alt + Right Arrow key to _____ data. - ANSWER group What happens if you type new data in the row immediately below the last row of a table? - ANSWER The table automatically adds a new row. What two options are presented at the Group dialog box? - ANSWER Rows and Columns When data in a worksheet is entered as a table, each row is a _____ and each column is a _____. - ANSWER record; field When groups and subtotals are added to a range, Excel automatically adds a _____ at the
The point-and-click method of creating a link to another workbook creates an _____ reference to the source cell. - ANSWER absolute To create a PivotChart in a worksheet without a PivotTable, - ANSWER select the data range, click the PivotChart button arrow, and then click PivotChart. To filter a PivotTable, - ANSWER click the filter arrow next to the field to be filtered Using buttons in the Group group on the Sparkline Tools Design tab, you can do each of the following except _____ Sparklines. - ANSWER copy What are Sparklines? - ANSWER miniature charts embedded in the background of cells What does a Slicer pane contain? - ANSWER all the unique values for the specified field What happens after you click the Insert Timeline button? - ANSWER The Insert Timelines dialog box opens. What is the first step in consolidating data? - ANSWER Make the starting cell active. What will happen to the link to an external reference if you move the source workbook? - ANSWER The link will not work. When using a 3-D reference, it is a good idea to set up the data in each worksheet in - ANSWER identical cells.
When writing a formula, how should you enter the workbook name reference? - ANSWER enclosed in square brackets: [ ] Question text When you create a PivotTable, you can select to place it in the existing worksheet or in - ANSWER a new worksheet. Which of these examples shows the correct syntax for a formula that links to a cell in the budget.xlsx workbook? - ANSWER =[budget.xlsx]January!A A range of cells in a copied source range can be multiplied by the cells in the destination range by - ANSWER using Paste Special and clicking Multiply. At the Scenario Summary dialog box, enter in the Result cells text box - ANSWER the formula cell or cells that change when the data is applied in various scenarios. Cells that provide data to formula cells are called _____ cells. - ANSWER precedent Convert data in columns to rows and vice versa using the _____ button in the Paste button drop-down gallery or using the _____ option at the Paste Special dialog box. - ANSWER Transpose; Transpose How is the Scenario Manager different from the Goal Seek feature? - ANSWER It allows you to examine several different answers to your question. How many models can you create using the Scenario Manager dialog box? - ANSWER as
Using data tables provides a means of analyzing various outcomes in a calculation without
- ANSWER creating multiple formulas. What can you do to see a descriptive reference next to the input text box, rather than the cell address when adding a scenario? - ANSWER Create a range name for each changing cell. When tracing relationships between cells and formulas, Excel draws _____ if an error is detected - ANSWER red tracer arrows Which of the following is not one of the options in the Operation section of the Paste Special dialog box? - ANSWER Count With a large worksheet, you can keep track of dependent cells that may not be visible while other cells are being changed by - ANSWER opening a Watch Window. Before transferring data from Excel to an existing table datasheet in Access, make sure that - ANSWER the column structures in the two programs match. Flash fill can be used to - ANSWER join parts of the contents of cells How many Access tables can be imported into Excel at a time? - ANSWER one If cell A3 holds the text JD Enterprises, the formula =LEN(A3) will return which of the following? - ANSWER 14
If the data being copied from Excel to Word is not likely to need editing or updating, copy it to the Clipboard in Excel, open Word, place the insertion point in the desired location, - ANSWER and then click the Paste button. If you have linked an Excel object to a Word document and decide to keep the data but not maintain the link, - ANSWER open the Word document, right-click the linked object, point to Linked Worksheet Object, click Links, and then click the Break Link button. Import data into Excel using buttons in the _____ group on the _____ tab. - ANSWER Get & Transform Data; Data Text file formats usually use _____ to separate data fields. - ANSWER commas or tabs The formula =CONCAT("-",519,555,1234) will return which of the following? - ANSWER - 5195551234 The text function PROPER - ANSWER capitalizes the first letter of each word. The TEXTJOIN function uses a ____ delimiter. - ANSWER constant To exchange Excel data with someone who does not have Excel, save the workbook as a - ANSWER text file UPPER and RIGHT are both _____ functions. - ANSWER text Use the _____ function to join the content of two or more cells, including text, numbers, and cell references - ANSWER CONCAT
A workbook can be saved as a template by changing Save as type to Excel Template or - ANSWER Excel Macro-Enabled Template. By default, AutoRecover automatically saves information every - ANSWER 10 minutes. By default, Excel saves the macro in - ANSWER the current workbook. By default, the Macro dialog box displays all the macros in - ANSWER all the open workbooks. Macros are created using Excel's - ANSWER macro recorder. To have your macros available when creating additional workbooks, you should consider - ANSWER creating a macros workbook To run a macro, view the list of macros by clicking the Macros button in the Macros group on the _____ tab. - ANSWER View What is the first step in inserting a form control? - ANSWER Click the Developer tab. What is the keyboard shortcut to minimize the ribbon? - ANSWER Ctrl + F When a workbook that contains a macro is opened, the default macro security setting is - ANSWER Disable all macros with notification. When displayed in the Visual Basic for Applications window, the first few lines of a macro
are preceded by apostrophes ('). These lines are - ANSWER comments When you create a new tab in the ribbon, where is the new tab placed? - ANSWER after the tab name you have clicked in the Main Tabs list When you make changes to workbook display options, the changes - ANSWER are saved with the workbook. Where is the Customize Quick Access Toolbar button? - ANSWER at the right side of the Quick Access Toolbar Where is the Ribbon Display Options button? - ANSWER in the upper right corner of the screen Which type of form control includes a drop-down list? - ANSWER combo box You can create a macro from scratch in Excel or using which of the following? - ANSWER VBA Editor A password should contain four types of characters. Which of the following is not one of them? - ANSWER spaces For a workbook that has been marked as final, all the following statements are true except which one? - ANSWER The workbook is encrypted. How can you prevent people from making additions, deletions, or changes to a workbook you plan to distribute? - ANSWER Click the File tab, click Info, click the Protect Workbook
What does PDF stand for? - ANSWER Portable Document Format What does XML stand for? - ANSWER Extensible Markup Language What is the name for the scrambled text Excel uses to encrypt passwords? - ANSWER ciphertext When the schema file is added to the workbook, each in the XML code appears as - ANSWER an XML element in the XML maps in the workbook list box When you publish a worksheet as an HTML web page, - ANSWER Excel creates files for supplemental data When you send a worksheet as an XPS document, the recipient must have _____ on his or computer to use the file. - ANSWER XPS viewer Which of the following is one of the workbook properties that Excel adds automatically? - ANSWER date the workbook was created Workbook properties can be viewed and edited at the - ANSWER Info backstage area.
