CBA 390 - Excel Training Certificate

CBA 390 - Excel Training Certificate

Excel will display it as 6/31/2019, but it will be left-justified and just a set of numbers and dashes, not an actual date.

As you are entering sales data into a new worksheet, you inadvertently type 6/31/2019. Which action will Excel take with this?

Excel will display it as 6/31/2019 and right-justified, but only as a set of numbers and dashes and not an actual date.

Excel will replace 6/31/2019 with 7/1/2019, the next real date after 6/30; there is no such date as June 31.

Excel will return an error message telling you there is no such date as June 31 in any year.

Excel will display it as 6/31/2019, but it will be left-justified and just a set of numbers and dashes, not an actual date.

This is a new file that has not previously been saved.

You click the Save icon at the top of an Excel workbook. The image below shows what appears on your screen. Why do you see this screen?
This is a new file that has not previously been saved.

This screen will always appear when using the Save icon.

You have not edited the file since the last save.

You made edits after saving the file.

deleting a tab in the workbook

Which action CANNOT be reversed using the Undo command?

deleting a column in the workbook

deleting a tab in the workbook

deleting more than 20 rows of data in the workbook

typing over the contents of a cell in your workbook

The date will appear as 8/1/2020 and will be right-aligned.

You are adding dates in the spreadsheet shown below. What will happen when you press Enter after entering the date as shown in cell C9?

The date will appear as 8/1/2020 and will be right-aligned.

The date will appear as Jan 8, 2020 and will be right-aligned.

The date will appear as 8/1/20 and will be right-aligned.

The date will appear as 8-1-2020 and will be right-aligned.

Type in "January," point to the lower right-hand corner of the cell, hold down the left mouse button, and drag across the next 11 rows.

You are entering Sales data for each month in 2018. How can you quickly enter all 12 months in columns?

Type in "January," point to the lower right-hand corner of the cell, hold down the left mouse button, and drag across the next 11 rows.

Type in "January," left-click for the Shortcut menu, select Copy, move your mouse over the next 11 cells, and then click Paste.

Type in "January," left-click the January cell, type "EXTEND," and the other 11 months will be filled in.

Type in "January," point to the lower right-hand corner of the cell, hold down the right mouse button, and drag across the next 11 rows.

By default, cells with alpha characters are left-aligned and while numeric characters are right-aligned.

In the image below, what is most likely the reason the work order and status columns are left-aligned while the cost column is right-aligned?

By default, cells with alpha characters are left-aligned and while numeric characters are right-aligned.

The work order and status columns have been changed to left-aligned.

The work order column was changed to left-aligned.

The cost column has been changed to right-aligned.

The formula =C2-C3 will appear in cell C4.

You are working in the worksheet shown below. What will happen in cell C4 if you use the fill handle to drag cell B4 to C4?

The formula =B2-B3 will appear in cell C4.

The formula =C2-C3 will appear in cell C4.

You will receive an error in the cell.

The result of 300 will appear in the cell.

=B2-B3

You are working in the worksheet shown below. What formula in cell B4 will calculate the profit in January?

=B2-B3

=SUBTRACT(B2-B3)

=B2:B4

=SUM(B2:B3)

=XLOOKUP(I2,B:B,A:A)

In the worksheet shown below, you want to use the XLOOKUP function to find the employee whose number appears in cell I2. Using the data in columns A and B, what formula is needed in cell J2?

• =XLOOKUP(I2,B:B,A:A)

• =XLOOKUP(B:B, A:A)

• =XLOOKUP(B:B,A:A,I2)

• =XLOOKUP(I2,A:A,B:B)

Select cells A1 to E13. Then select Formula > AutoSum > Average.

You have numerical data in A1 to D12. You want to show an average for each column and each row. What's the most efficient solution?

Create =AVERAGE functions in each target cell.

Select cells A1 to D12. Then select Formula > AutoSum > Average.

Select cells A1 to E13. Then select Formula > AutoSum > Average.

Select cells A1 to E12. Then select Formula > AutoSum > Average.

The formula =SUM(E2:E3) will be added to the cell to add the total sales and costs.

In the worksheet shown below, you click in cell E4 and then click AutoSum. What will be the result?

The formula =SUM(B4:D4) will be added to the cell to add the total profits.

The formula =SUM(B4+D4) will be added to the cell to add the total profits.

The formula =SUM(E2:E3) will be added to the cell to add the total sales and costs.

The formula =SUM(E2+E3) will be added to the cell to add the total sales and costs.

Highlight the entire worksheet then double-click the boundary between any two columns.

You want to change the columns in the worksheet shown below to the best fit for the data. What is the fastest way to accomplish this?

Highlight the entire worksheet then double-click the boundary between any two columns.

Highlight columns A-E then use the Format option in the ribbon.

Highlight the entire worksheet then right-click to set the width.

Highlight the cells with data then double-click.

Use Draw Border under Border on the ribbon under Home.

How can you quickly add a border around a set of cells covering 3 rows and 12 columns?

Highlight the cells, double-click, select Format Cells, and then Border.

Use Draw Border Grid under Border on the ribbon under Home.

Highlight the cells, right-click, and then select Border.

Use Draw Border under Border on the ribbon under Home.

The content in the cell is wider than the cell width.

One of the number cells in your worksheet shows as ###. What does this mean?

The content in the cell is wider than the cell width.

There is a division-by-zero error in the cell contents calculation.

The number you entered in the cell is larger than 1,000.

The cell is empty in a row of cells filled with content.

Double Accounting

Which feature can only be accessed from the Format Cells dialog box?

Double Accounting

Font

Font Size

Double Underline

Select the entire worksheet and double-click any column boundary.

You have a worksheet with multiple hidden columns and you need to view all of the data that is hidden. What is the best way to unhide the columns?

Double-click the column boundary for each hidden column.

Select the entire worksheet and double-click any column boundary.

Select the entire worksheet, then click Tools in the ribbon and select Unhide.

Right-click the column to the right of each hidden column and select Unhide.

Use the Match entire cell contents option under Replace.

Your worksheet includes data for your company's IT department. How can you use Find and Select to quickly capitalize the many instances of IT appearing in the worksheet?

Use the Match entire cell contents option under Replace.

Use the Match case option under Replace.

Drag the capitalized version over each uncapitalized version.

Use the Match entire cell contents option under Find.

Use the Find tool, enter the badge number, and check the box next to Match Case and Match Entire Cell Contents.

The worksheet shown below tracks badge numbers assigned to a company with 5,000 employees. How would you quickly find who is assigned a specific badge number?

Use the Find tool, enter the badge number, and check the box next to Match Case.

Use the Find tool, enter the badge number, and check the box next to Match Entire Cell Contents.

Use the Find tool, enter the badge number, and leave all boxes unchecked.

Use the Find tool, enter the badge number, and check the box next to Match Case and Match Entire Cell Contents.

Click B to highlight the column then hold the Shift key down and drag the column in front of the Employee column.

In the worksheet shown below, you want to move the ID before the employee name. What is the fastest way to move this data?

Click B to highlight the column and then left-click and hold to drag the data in front of the Employee column.

Click B to highlight the column then hold the Shift key down and drag the column in front of the Employee column.

Click B to highlight the column then hold the Ctrl key down and drag the column in front of the Employee column.

Click A and right-click to insert a column. Click B and right-click and select Cut then Paste the data into the new column.

Use Print Preview as a trial and error.

How would you most efficiently test a layout adjustment before printing on paper?

Calculate the required page area.

Measure and count the cells.

Adjust the font size to be displayed.

Use Print Preview as a trial and error.

Click Page layout on the bottom of the page in the status bar.

You want to view how your worksheet will print and make edits to the worksheet in this mode. What is the easiest way to accomplish this?

Click Design on the top of the page.

Click Page layout on the bottom of the page in the status bar.

Click Page layout on the top of the page in the ribbon.

Click Print and then Print Preview on the top of the page.

A2:D6

In the image below, which cells would you use to create a chart to represent the sales data?

A2:D6

B3:E7

A1-E7

B3:D6

Highlight the data you want to show in the chart.

What is the first step in creating a chart with Excel Office 365?

Highlight the data you want to show in the chart.

Insert a row of empty cells beneath the title.

Select the chart type you are interested in showing.

Create totals for each row and each column.

The bars will change to salespeople, and the colors will change to products.

You created a bar chart with the bars for the products and the colors for the salesperson. If you click Switch Row Column, what will happen?

The bar chart will change to a column chart displaying the same information.

The bars and colors will flip meanings, and the chart will become a column chart.

The data behind the chart will flip rows to columns and columns to rows.

The bars will change to salespeople, and the colors will change to products.

by double-clicking the split line

How can you remove a split other than by clicking the Split button under View?

by double-clicking the split line

by double-clicking each of the scroll bars

by double-clicking the row or column

by double-clicking the edge of the cell

Click in cell B2 and click Freeze Panes from the View menu.

You are reviewing data in the worksheet shown below. You need to view the employee name and the first row of the worksheet as you scroll through the information. What action will you take?

Click Freeze Top Row from the Freeze Panes drop-down.

Click in cell B2 and click Freeze Panes from the View menu.

Click Freeze Top Row and First Column under the Freeze Panes drop-down.

Click Freeze First Column from the Freeze Panes drop-down.

edit; grouping

You can _____ multiple sheets at the same time by _____ them first.

edit; grouping

zoom-in on; dragging

move; grouping

group; splitting

Retrieve the sheet from a saved version of the file.

If you accidentally delete a sheet that you wanted to keep, which action should you take?

Right-click any sheet tab, and then select Unhide.

Click the File tab, and then select Restore Deleted Sheet.

Retrieve the sheet from a saved version of the file.

Choose Undo or click Ctrl+Z.

Delineate the data you do not want to be sorted.

If you have a worksheet containing data you do not want to be sorted, what must you do before sorting?

Click the column or row you do not want to be sorted.

Use Print Area to identify only the data you want to be sorted.

Delineate the data you do not want to be sorted.

Highlight the data you do not want to be sorted.

You need to refresh the pivot table to include the updated data.

An Excel workbook tracks the sales data for a company. A pivot table is in the adjacent tab and provides a high level view of the sales by department. The sales numbers are updated in the workbook, but the pivot table is not reflecting these changes. What is most likely causing this problem?

There is an error in the updated data that prevents it from updating the pivot table.

You need to refresh the tab with the raw data to send the changes to the pivot table.

You need to refresh the pivot table to include the updated data.

You need to create a new pivot table to reflect the changes.

Enter the password to turn off the protection.

Which action will edit locked cells in a protected worksheet?

Enter the password to turn off the protection.

Use Save As to create a new worksheet, and then make your edits.

Highlight the data, right-click, and then select Unlock.

Unlock the cells in which you are interested.

You will lock the cells in all columns except column B and generate a password to protect the worksheet.

You manage the worksheet shown below. You want the building number in column B to be edited by anyone who has access to the sheet. You do not want any other information to be edited by others. How can you configure the worksheet to achieve this protection?

You will lock the cells in all columns except column B and generate a password to protect the worksheet.

You will unlock all the cells in the worksheet and then password protect the worksheet.

You will lock all the cells in the sheet and then password protect the worksheet.

You will unlock all the cells except column B and password protect the worksheet.

Mini Toolbar

A user wants to quickly format a cell and right-clicks the cell to use the features seen in the image. Which Excel tool is being used?

Mini Toolbar

Ribbon

Backstage View

Quick Access Toolbar

Right-click the button on the ribbon and select Add.

You would like to add some frequently used buttons to the Quick Access Toolbar. How can you do this?

Drag-and-drop the button to the Quick Access Toolbar.

Left-click the button on the ribbon and select Add.

Double-click the button on the ribbon and select Move.

Right-click the button on the ribbon and select Add.

when creating a list of employees with their ID numbers

When would you use Excel for a task?

when creating a presentation to give to a team

when writing an instruction guide for a team

when writing an article about a new product

when creating a list of employees with their ID numbers

Hover over the button, then click Tell me more at the bottom of the information displayed.

You are using a button on the ribbon, and you are interested in learning more about its functionality. How can you access "Tell me more"?

Left-click the button, then click Tell me more at the bottom of the information displayed.

Right-click the button, then click Tell me more at the bottom of the information displayed.

Click Help under the Help tab on the toolbar, then type in the button name in Search Help.

Hover over the button, then click Tell me more at the bottom of the information displayed.

Click Help from the menu bar.

You want to add an average in the worksheet shown below. You want to learn more about the average function in Excel and think that a video will assist you. How can you access training from the workbook?

Click Help from the menu bar.

Click Tell me more.

Click Tell me what you want to do.

Click Featured help.

with the same formatting as "2018 Sales" before you made any changes

You are working in your company's "2018 Sales" Workbook with each column widened and a color graph. When you add a worksheet "2018 Profit," how will the new worksheet appear?

with the same column changes as "2018 Sales" but without the graph

with the same formatting as "2018 Sales" before you made any changes

with the same changes as "2018 Sales" with a space for the graph included

with the Row and Column numbers starting where they ended in "2018 Sales"

A workbook is the file and a worksheet is the individual tab within the workbook.

What is the difference between a workbook and worksheet?

A workbook is a collection of Excel files and a worksheet is an individual file.

A worksheet is the file and a workbook is the individual tab within the worksheet.

A workbook is the file and a worksheet is the individual tab within the workbook.

A workbook is a file that contains one or more tabs which are worksheets.

Workbook and worksheet are terms that can be used interchangeably and are Excel files.

C1

The worksheet below has the split feature active as shown by the vertical line between columns B and C. Which cell was selected when split was chosen by the user?

• B2

• B1

• C1

• C2

Row 3, Column 4

To freeze rows 1 and 2, and columns 1, 2, and 3, which cell should you highlight before selecting Freeze Panes?

• row 3, column 4

• row 1, column 3

• row 2, column 3

• row 3, column 1

• Use slashes for dates and colons for time.

What is the best practice for entering dates and times in Excel?

• Use slashes for dates and colons for time.

• Use slashes for dates and periods for time.

• Use periods for dates and colons for time.

• Use dashes for dates and colons for time.

on the first empty cell to the right of the last cell containing a value

To use AutoSum with a row of four values, where must your cursor be to have the sum appear in the row if you have not highlighted the cells?

• on the last cell in the row containing a value

• on the first empty cell to the right of the last cell containing a value

• on the first cell in the row containing a value

• on the first empty cell to the left of the first cell containing a value

line chart

Which type of chart is preferable when you are dealing with a timeframe?

• column chart

• pie chart

• line chart

• clustered column chart

from the arrow to the right of Font on the ribbon

Where can you access the format cell's Dialog Box launcher?

Select an answer:

• from the arrow to the right of Font on the ribbon

• from the arrow to the right of Clipboard on the ribbon

• by right-clicking the cell in which you are working

• by typing "Dialog Box" in Search

Make sure your data has no empty rows or columns.

You would like to filter your large worksheet down to only sales from January. Which action must you take before using the Filter feature?

Select an answer:

• Make sure your data has no empty cells.

• Make sure your data has no empty rows or columns.

• Make sure your data is sorted in date order.

• Make sure your data has no zero values.

Use the Filter option to filter by full time employees and use the Numbers filter to select greater than $45,000.

You manage the worksheet shown below and it contains information for the company's 1500 employees. You are asked to provide a list of all of the employees who are full time and make more than $45,000 per year. What is the best way to create the list?

• Use the Sort option to sort by compensation and then sort by employee status to see only full time employees.

• Use the Filter option to filter by full time employees and mark the check box next to each compensation option that is greater than $45,000.

• Use the Filter option to filter by only full time employees and then sort the data by the compensation column to identify who makes over $45,000.

• Use the Filter option to filter by full time employees and use the Numbers filter to select greater than $45,000.

Add a blank column between columns B and C, click in cell A2, and select Sort.

You manage the worksheet shown below that contains a list of employees and a list of building numbers. The employee list is 1500 rows of data. What is the easiest way to sort the employee list without affecting the building number list?

• Add a blank column between columns B and C, click in cell A2, and select Sort.

• Add a blank row at the bottom of the employee list, click in cell A2, and select Sort.

• Highlight the data in columns A and B by dragging the cursor down to the bottom row and click Sort.

• Highlight the data in columns C and D by dragging the cursor down to the bottom row and click Sort.

You are changing the content in the formula to the content in cell D8.

You created a formula in cell C8. When you copy the formula into cell D8, which action are you taking?

• You are changing the content in the formula to the content in cell D8.

• You are replacing the content in cell D8 with the content in cell C8.

• You are repeating the results obtained in cell C8 to the values in D8.

• You are multiplying the result in cell D8 by the result in cell C8.

by typing =d5-d6

You want to subtract your cost of 250 in cell D6, from your selling price of 400 in cell D5, and have the result in cell D7. How would you do this so you can extend the calculation across your other products?

• by typing 400-250

• by typing d5-d6

• by typing =400-250

• by typing =d5-d6

Right-click any visible sheet, then select which worksheet you want to Unhide.

You hide three worksheets in a workbook and need to unhide them. How can you accomplish this?

• Right-click the + button, then select which worksheet you want to Unhide.

• Type the name of the hidden worksheet on the keyboard, then click Unhide.

• Click the View menu, then select Unhide Worksheets.

• Right-click any visible sheet, then select which worksheet you want to Unhide.

Check for data you have previously hidden.

You resurrected an old worksheet. It appears to contain most of the information that you need, but not all of it. Which step should you take next?

Check that the worksheet is not Read-Only.

Change the format to reduce the font size.

Make the columns wider.

Check for data you have previously hidden.

click in the tab name of the worksheet, hold the CTRL key, and drag the worksheet.

You have a workbook with multiple worksheets and want an exact copy of one of the worksheets. How can you duplicate this sheet within the same workbook?

• Click in the tab name of the worksheet and drag it to the end of the tabs in the workbook.

• Create a new worksheet and group the current worksheet with the new worksheet.

• Click in the tab name of the worksheet, hold the Ctrl key, and drag the worksheet.

• Click in the tab name of the worksheet, hold the Shift key, and drag the worksheet.

Double-click the sheet tab, and then type the new name.

Which shortcut can you use to rename a worksheet?

• Double-click the sheet tab, and then type the new name.

• Left-click the sheet tab, and then type the new name.

• Alt-click the sheet tab, and then type the new name.

• Shift-click the sheet tab, and then type the new name.

as a spreadsheet

Although it is not a term Excel uses, how do most people think of Excel?

• as an analytical tool

• as a set of lists

• as a spreadsheet

• as a set of charts

You can track changes

Why would you set up a workbook to be shared if you are the only one using the workbook?

• You can track changes.

• You can make a draft copy of the workbook.

• You can send emails of your edits to yourself.

• You can password protect the workbook.

You must share the workbook to track changes.

How does tracking changes relate to sharing a workbook in Excel?

• You must track changes to share the workbook.

• You must share the workbook to track changes.

• You cannot share the workbook if you track changes.

• You cannot track changes if you share the workbook.

Use a light-color fill.

What is a good rule-of-thumb for using color backgrounds in a worksheet?

• Avoid using colors.

• Use a light-color fill.

• Use a dark-color fill.

• Avoid using the Fill Color bucket.

You must add the feature to the Quick Access Toolbar.

What statement is accurate in regard to sharing workbooks?

• You cannot track the changes of each user.

• You must add the feature to the Quick Access Toolbar.

• It is a legacy feature that is no longer a feature in Excel.

• You can share and unshare from the Review option on the ribbon.

You will either have to click Refresh on the Analyze ribbon or click Alt + F5.

As you are working with a pivot table, you decide to change some of your source data. How will the pivot table recognize the change?

• Pivot tables adjust automatically to any changes made in the source data.

• You will have to save the worksheet, close out of it, and then reopen the worksheet.

• You will either have to click Refresh on the Analyze ribbon or click Alt+R.

• You will either have to click Refresh on the Analyze ribbon or click Alt+F5.

Print the first page

While it might be considered "old-school," which action should you take if you are unsure how a page will print, even after looking at Page Break Preview?

• Slide the solid blue line.

• Eliminate page breaks.

• Print the first page.

• Slide the dotted line.

The menu items available will differ depending on the context of what you are right-clicking.

What does it mean that the right mouse button is "context-sensitive"?

• The Shortcut menu will read the cell you right-click and provide commands customized to your needs.

• The menu items available will differ depending on the context of what you are right-clicking.

• The Shortcut menu items are commands that are not available in the ribbon.

• The Shortcut menu will only appear when there is content in the cell on which you are right-clicking.

Create a line chart and make the chart narrower than the default.

You are creating a chart using the data shown below. What is the best chart type and style to use in order to make the upward sales trend most obvious?

• Create a line chart and make the chart wider than the default.

• Create a bar chart and make the chart narrower than the default.

• Create a line chart and make the chart narrower than the default.

• Create a bar chart and make the chart wider than the default.

Track Changes is located under the Customize Quick Access Toolbar.

How can you access Track Changes in Microsoft Excel Office 365?

• Track Changes is located under the Customize Quick Access Toolbar.

• Track Changes is located under Review on the ribbon.

• Track Changes is located under Insert on the ribbon.

• Track Changes is not available in Microsoft Excel Office 365.

CTRL+ Z for Undo and CTRL+ Y for Redo

Which shortcuts can you use for Undo and Redo?

• Ctrl+Z for Undo and Ctrl+Y for Redo

• Alt+U for Undo and Alt+R for Redo

• Ctrl+U for Undo and Ctrl+R for Redo

• Ctrl+Y for Undo and Ctrl+Z for Redo

CTRL and Shift

Which two keys are commonly used to move or insert data?

• Tab and Ctrl

• Alt and Shift

• Ctrl and Shift

• Esc and Shift