Excel 2021

You named your table "Customer Information," but when you hit Enter to finish creating the table name, you receive the following message: "you must enter a valid reference you want to go to, or type a valid name for the selection." What went wrong? a) Neither table or range names can contain spaces. Your reference is incorrect. b) The table name was too long. It can only contain five characters. c) The table name must begin with shift + underscore ( _ ). d) You misspelled the table name when you entered it.

a) Neither table or range names can contain spaces. Your reference is incorrect. Table and range names cannot contain spaces, special characters, or begin with numbers. You will receive an error message if the name contains any of these.

What's the best way to repeat row and column headings when printing a document? a) Use Print Titles on the Page Layout tab to select the row(s) and/or column(s) you want to repeat on each printed page. b) You can't repeat the row and columns heading on each page. c) Copy and paste the row and column headings after each page break. d) Use freeze panes.

a) Use Print Titles on the Page Layout tab to select the row(s) and/or column(s) you want to repeat on each printed page. When you print a multi-page document, it is important to make sure your column headings and sometimes your row headings repeat on each printed page so that readers can better understand the data they're viewing.

Which function is NOT available in the Total Row of a table? a) AVERAGE( ) b) Conditional Formatting c) COUNT( ) d) MAX( )

b) Conditional Formatting While conditional formatting can be used to visually summarize the data in the Total Row cells, it is not a function under the Total Row Summary Functions menu.

You can only sue AutoFill to fill in a list of dates. a) True b) False

b) False AutoFill can be used to fill cells with data that follows a pattern or are based on data in other cells, such as dates, months, and quarters.

Headers and footers can be viewed in both Page Break Preview and Page Layout view. a) True b) False

b) False Headers and footers can only be viewed in Page Layout view and Print Preview.

The Quick Access Toolbar is accessed by selecting a range of cells and clicking on the Quick Analysis Tool popup menu and selecting the desired command. a) True b) False

b) False The Quick Access Toolbar contains commands that are not available on the Ribbons, and is located above the Ribbons. The Quick Analysis Tool menu is accessed by selecting a range of cells, and selecting the desired data analysis tool.

Monthly Sales are in A2:A13, and the Sales Tax Amount is in C1. Based on this information, =$A$2*C1 will correctly calculate the sales tax for all months if the formula is copied to A3:A13. a) True b) False

b) False The correct formula would be =A2*$C$1, making the reference to the Sales Tax in C1 an Absolute Reference, which will correctly calculate the sales tax for the other months if the formula is copied to A3:A13.

You CANNOT insert more than one new row at a time in Excel 2019. a) True b) False

b) False To insert more than one new row at a time, select the number of rows you want to insert just above where the rows will be added, go to Home > Cells > Insert > Rows.

To link an object such as a logo graphic to a Named Range, go to Insert > Link > Existing File or Webpage. a) True b) False

b) False To link an object to a Named Range, go to Insert > Link > Place in This Document and select the Named Range listed under Defined Names.

To modify the font used in a Style, select a cell where the Style has been applied, right-click Format Cells, select the desired Font, and click OK. a) True b) False

b) False To modify the font used in a Style, go to Home > Styles > Cell Styles, right-click on the Style > Modify > Format > Font, selected desired Font and click OK.

Which keyboard shortcut should you use to convert a selection of cells into a table format? a) Ctrl + t b) Shift + Ctrl + t c) Alt + t d) Ctrl + c, Ctrl + p

a) Ctrl + t The quickest and easiest way to reformat selected cells as a Table is the keyboard stroke Ctrl + t.

A structured reference in a formula occurs when a table name or cell name is used in the formula. a) True b) False

a) True

The RIGHT( ) function displays the specified number of characters from end of a text string. a) True b) False

a) True

Data from an Access database table can be imported into Excel. a) True b) False

a) True Data from Access database tables is just one of the data types that can be imported into Excel. Others including text files, CSV, other Excel workbooks, and XML files.

Which choice is NOT a Sparkline option? a) pie chart b) line chart c) win/loss chart d) column chart

a) pie chart Pie chart is not an available Sparkline option.

How many results can an IF statement have? a) 3: Yes, No, or Maybe b) 2: True or False c) 1: True d) 2: Yes or No

b) 2: True or False An IF statement is a comparison statement and will have only one of two results: either True or False. Based on the result, a specific action (such as inserting text or performing a calculation) will occur.

You CAN NOT simultaneously apply a title to the Horizontal Axis and the Vertical Axis. a) True b) False

b) False You can apply a title to both the Horizontal Axis and the Vertical Axis at the same time.

Which of the following properties are system-generated properties? a) File Size, Last Modified, Last Printed, Created and Tags b) File Size, Category, Last Printed, Created and Author c) File Size, Last Modified, Created, Last Printed, and Author d) File Size, Title, Last Printed, Created, and Author

c) File Size, Last Modified, Created, Last Printed, and Author

To find the total of the values in a range of cells which function would you use? a) COUNT b) IF c) SUM d) AVERAGE

c) SUM

How can you keep certain cells visible, no matter where you scroll in the document? a) use Split Window to keep the cells at the top of the worksheet. b) Use a Named Range to keep the cells at the top of the worksheet. c) Us the Freeze Panes option under the View tab. d) Us copy and paste to move the cells to the top of the worksheet.

c) Us the Freeze Panes option under the View tab. Use Freeze Panes on the View tab to select whether to freeze the top row, first column, or both so that your Row and Column headings are always visible as you scroll through the worksheet.

You use the CONCAT( ) function to combine the first and last names found in two separate cells, but the first and last names are combined without any space? What happened? a) You should have used TEXTJOIN( ) to join the fields and add a space in between. CONCAT( ) does not allow for any delimiters or spaces to be included. b) You forgot to use "&" (amersand) between the first and last names. c) You forgot to add an extra space after the first name in each cell. d) You forgot to add an extra space before the last name in each cell.

a) You should have used TEXTJOIN( ) to join the fields and add a space in between. CONCAT( ) does not allow for any delimiters or spaces to be included.

When importing data from a text file, the data ___. a) can be imported into either a new worksheet or a specified cell in an existing worksheet. b) cannot have headers. c) cannot be separated with commas. d) must be formatted correctly before the import begins.

a) can be imported into either a new worksheet or a specified cell in an existing worksheet. In the last step of the Wizard, you can choose where the data will be imported by selecting either "Existing worksheet" and selecting the beginning cell or "New Worksheet."

You have been given a worksheet that contains a table with Employee data such as Last Name, First Name, Hire Date, Department, and Manager. You need to create a report that lists Employees in alphabetical order, by Department, and only lists those who began working for the Company after May 1st of 2019. How would you complete this task? a) First, sort the Employees by First Name, and then by Last Name. The sort by Department, and then filter by Hire Date >= 5/1/2019. b) First, sort the Employees by First Name, and then by Last Name. Then sort by Department, and then filter by Hire Date > 5/1/2019. c) Sort by Hire Date and then by Last Name and First Name. Then apply a filter to Department to show Employees hired after 5/1/2019. d) First, sort the Employees by Department, then by Last Name and First Name. Then filter employees by Hire Date < 5/1/2019.

b) First, sort the Employees by First Name, and then by Last Name. Then sort by Department, and then filter by Hire Date > 5/1/2019. By first sorting by First Name, then by Last Name, and finally by Department, you will see the Employee data in Alphabetical order by Department. Once the sorts have been applied, you can then filter the Hire Date to only show Employees hired after 5/1/2019.

How do you move a chart from one worksheet to another? a) You can't move a chart from one worksheet to another. b) Go to Chart Tools > Design > Move Chart and select the worksheet on which you want to display the Chart. c) Go to Chart Tools > Quick Layout > Selected Worksheet. d) Recreate the chart on the desired worksheet.

b) Go to Chart Tools > Design > Move Chart and select the worksheet on which you want to display the Chart.

When merging cells, what is NOT an option under Merge & Center? a) Merge Cells b) Merge Horizontally c) Merge & Center d) Merge Across

b) Merge Horizontally While you can merge cells both horizontally and vertically, Merge Horizontally is not one of the options in the Merge & Center menu.

When copying data between worksheets, in order to keep both the values and the formatting, you select Paste Special, the select ___. a) Paste > Formulas & Number Formatting b) Paste Values > Values & Number Formatting c) Paste > Paste Values and select Values d) Paste > Paste Special and select Values

b) Paste Values > Values & Number Formatting Under Paste Special, you have options that will combine options for how the data is pasted. If you're not sure which one correct, mouse-over each one until you see the correct option, which is Past Values, Paste Values and Formatting.

To quickly change all of the text in a cell to upper case use the ___. a) PROPER function b) UPPER function c) Copy > Paste Special > Uppercase Function d) CAPS LOCK key and retype the text in upper case.

b) UPPER function

You used COUNT( ) to see how many cells in the Last Name field were null, but Excel returned "0" and you know there are at least a few. Why do you think this happened? a) You used COUNT( ) which returns the number of cells in a range that contain numbers. You should have used COUNTA( ) which returns the number of empty cells in a range. b) You used COUNT( ) which returns the number of cells in a range that contain numbers. You should have used COUNTBLANK( ) which returns the number of empty cells in a range. c) You used COUNT( ) which returns the number of cells in a range that contain numbers. You should have used COUNTALL( ) which returns the number of empty cells in a range. d) You used COUNT( ) which returns the number of cells in a range that contain numbers. You should have used DCOUNT( ) which returns the number of empty cells in a range.

b) You used COUNT( ) which returns the number of cells in a range that contain numbers. You should have used COUNTBLANK( ) which returns the number of empty cells in a range.

Why would you want to switch the row and column data in a Chart? a) You would switch the row and column data in a Chart to change the Chart type. b) You would switch the row and column data in Chart to "swap" the data displayed on the Horizontal Axis and the Legend. c) You would switch the row and column data in a Chart to re-select the data displayed in the Chart. d) You would switch the row and column data in a Chart in order to Transpose the data.

b) You would switch the row and column data in Chart to "swap" the data displayed on the Horizontal Axis and the Legend.

Which method for changing worksheet order is incorrect? a) After selecting the worksheet tab, go to the Home tab, select Format/Move or Copy and select the desired location. b) After selecting the worksheet tab, click and hold down the mouse button, and then drag and drop the worksheet to the new location. c) After selecting the worksheet tab, use the right-click menu and select Move and Copy and select the correct location. d) After selecting the worksheet tab, use the right-click menu select Cut and then Paste the Worksheet in the correct location.

d) After selecting the worksheet tab, use the right-click menu select Cut and then Paste the Worksheet in the correct location. On the right-click menu, you will not find any Cut and Past options.

To display the top 10 items in a selection, which Conditional Formatting rule should you use? a) Highlight Cells Rules > Greater than... b) Data Bars > Gradient Fill c) Icon Sets > 3 Arrows (Colored) d) Top/Bottom Rules > Top 10 Items...

d) Top/Bottom Rules > Top 10 Items... You can easily apply Conditional Formatting to the Top 10 items by using the preset options under Top/Bottom Rules, Top 10 Items....

To help users with vision impairment understand what the object represents you can ___. a) use a text box to describe the object b) insert a Comment in the cell to describe the object c) use specific colors and font sizes d) add a title and description of the object using Alt Text

d) add a title and description of the object using Alt Text

You can create a hyperlink to a place in the current workbook and ___. a) an existing file b) an email address c) a web page d) all of the above

d) all of the above Hyperlinks can be created to point to a place in the current workbook, an email address, an existing file, or web page.

Which of the following issues will Excel reveal when the Workbook is Inspected? a) hidden properties; personal information; passwords b) hidden properties or personal information; missing Alt Text; misspelled words c) personal information; hidden rows and columns; named ranges d) hidden properties or personal information; hidden columns and rows; comments

d) hidden properties or personal information; hidden columns and rows; comments