Work Smarter with Microsoft Excel Coursera Quiz Answers

Gell All Weeks Work Smarter with Microsoft Excel Coursera Quiz Answers

This course is intended for learners with some experience using Microsoft Windows, who are seeking to build presentation skills with Microsoft Excel. To be successful in this course you should have an understanding of the basics of using a Windows-based computer. Experience with printing and using a web browser are an asset, but not required.

Knowing how to use Microsoft Office products is essential in any professional setting. Whether you want to expand your expertise to apply for a new role, start your own business, or simply develop a new personal skill this course is for you.

This course will introduce you to Microsoft’s powerful and easy-to-use spreadsheet program, which provides you with an extensive set of tools to organize, analyze, present, and store data. If you are familiar with Excel’s basic features you can take your skills to the next level. If you are a beginner you will get the opportunity to develop basic Excel skills.

Enroll on Coursera

Work Smarter with Microsoft Excel Coursera Quiz Answers

KNOWLEDGE CHECK: PRINTING WORKBOOK CONTENT QUIZ ANSWER

1. Where will you find the command to add a Page Break into your Worksheet?

  • On the Insert Tab
  • On the right-click Shortcut Menu
  • On the Page Layout Tab

2. You are about to print your Worksheet and would like to have Page Numbers at the bottom of the printed pages. Where would you do this in Excel?

  • From a command on the Insert Tab.
  • In-Page Setup on the Print Options in Backstage View
  • From a command on the Layout Tab.

3. What do the highlighted buttons on the bottom of the Excel screen allow you to change?Three different views highlighted Normal (which is the default), Page Break Preview and Page Layout.

  • The Worksheet View
  • The Print Area Settings
  • The Worksheet Zoom settings

4. When you begin to print your Worksheet, you realize that there will be two pages in the printout. Which feature would you use to make sure that the headings in your data repeat on both pages?

  • Print Area
  • Header/Footer
  • Print Titles

5. When your print your Worksheet you realize that there is one line of print appearing on a second page. How could you ensure that everything is printed on a single page? Select all options that apply.

  • On the Page Layout Tab, change the choice in the the Height box in the Scale to Fit group.
  • Decrease the Margins
  • Add a Manual Page Break into the content on your Worksheet.

GET STARTED WITH EXCEL MODULE GRADED QUIZ

  1. What is this highlighted area in the Excel screen called?
The Formula bar is highlighted.
  • The Formula Bar
  • The Quick Access Toolbar
  • The Status Bar

2. If you wish to save your Excel file, what method can you use? Select all options that apply.

  • Ctrl + S
  • File, Save
  • F1
  • F12

3. When you open an Excel File you see that it has opened in Compatibility Mode. You can see this from the Excel Title Bar. As you will be the only person working on this file, you want to convert this file to the Excel Office 365 format. Where can you find the Convert button?

  • File, Options
  • File, Export
  • File, Info333

4. What types of data can you type into an Excel Cell? Select all options that apply.

  • Text
  • Text and numbers together
  • Date and time entries
  • Numbers

5. When your print your Worksheet you realize that there is one line of print appearing on a second page. How could you ensure that everything is printed on a single page? Select all options that apply.

  • On the Page Layout Tab, change the choice in the in the Height box in the Scale to Fit group.
  • Decrease the Margins
  • Add a Manual Page Break into the content on your Worksheet.

Knowledge check: Modify a worksheet quiz answers

1.What kind of Format would the highlighted command on the Home Tab apply to numbers?

A comma  is highlighted on the Home Tab numbers group.
A comma is highlighted on the Home Tab numbers group.
  • Only sets two decimal places
  • Applies a Format that has two decimal places and a currency symbol
  • Applies a Format that has two decimal places and a character separating the 100s and 1000s

2. You type some numbers into the Worksheet and see the following symbols appear in a cell. Why have they appeared?

Seven hashtags appear highlighted in column D.
  • The number Format is incorrect.
  • The Column is too narrow.
  • The data entered is not recognized.

3. You type the heading Customer Name into a cell. Part of the heading is hidden because of the Column width. You would like the word Name to appear under the word Customer. How can you do this?

Customer Name cell is highlighted in column E
  • Type the word Name in the cell underneath.
  • Use the Top Align command in the Alignment group on the Home Tab.
  • Use the Wrap Text command in the Alignment group on the Home Tab.

4. You want to apply a Range Name to a block of cells. Which of the following statements are true? Select all options that apply.

  • The Name cannot contain spaces.
  • The Name must be unique.
  • The Name will be case-sensitive.
  • The Name can contain a mixture of text and numbers.

5. You are using the Find and Replace feature to locate some pieces of text in your Excel file that you need to replace. Which of the following can you do to customize the search? Select all options that apply.

  • Make the search case-sensitive.
  • Search the whole workbook.
  • Search for text entered after a certain date.

Knowledge check: get started with data quiz answer

  1. What will the highlighted area of the cursor allow you to do?
Column A,  row two, year twenty twenty one - the right bottom corner area of the cell is highlighted.
  • Open a Shortcut Menu
  • Use the Autofill feature
  • Move the selected items.

2. You want to Cut some data from one location in an Excel spreadsheet so that you can Paste it in at a different spot. How can you Cut the data? Select all options that apply.

  • Use the Ctrl + V option
  • Use the Cut command in the Clipboard section of the Home Tab
  • Use the Shortcut Menu\

3,You notice that there is a column initial missing at the top of the Excel Worksheet. What does this tell you about the Worksheet?

Column D and H highlighted. You notice that there is a column initial missing at the top of the Excel Worksheet.
  • That a Column has been resized
  • That a Column has been deleted
  • That a Column has been hidden

4. Where would you find the command button to clear content from a Cell or block of Cells in the Worksheet?

  • On the Review Tab
  • On the Data Tab
  • On the Home Tab

5. You typed a series of headings across a Row in your Worksheet but then decided that it would be better if they were vertically down the Column instead. What feature can you use to change the

  • The Autofill feature
  • The Flash Fill feature
  • The Transpose feature

MODULE GRADED QUIZ: STARTED WITH EXCEL WEEK 01 ANSWER

Q1. A client has sent an Excel File for you to review. When you open the file, you notice that it has the words Compatibility Mode on the Title Bar beside the filename. What does this mean and what can you do if you are not sending the same file back to the client?

  • The file has been created in an older version of Excel and you should go to File, Info, and choose to convert it to a new Excel format.
  • The file has been created in an older version of Excel and you should work on it but make sure that you save the file in the original Excel format.

2. Excel gives you many quick shortcuts for adding data to the worksheet. You typed a date in cell A1, hovered over the square in the bottom right of the cursor, and then dragged it down. What feature have you used and what will the highlighted icon allow you to?

  • The feature is called Autofill and the highlighted icon will allow you to reverse the process.
  • The feature is called Flash Fill and the highlighted icon will allow you to customize the entries.
  • The feature is called Autofill and the highlighted icon will allow you to customize the entries.

3. You are having a conversation with a colleague who is more experienced with Excel. You mention that you sometimes find it difficult to completely empty a cell and that you will often use the delete option on the right-click menu option as a quick solution. Your colleague reminds you that if you use the delete on the right-click menu you are shifting cells on the worksheet and there is always a possibility that figures would then line up under the wrong heading. They recommend using the Clear button on the Home Ribbon instead. What can you delete using this button? Select all options that apply.

The Clear icon.
  • Clear all contents, comments, and formats.
  • Clear the entire row or the entire column that the cursor is in.
  • Clear the content but leave the formatting and comments in place.
  • Clear only the comments or notes on a cell.
  • Clear the formatting only.

4. You are working on an Excel file that other people in your department also have access to. While checking a worksheet updated by a colleague, you notice that they have manually formatted a heading. You like the changes they have made and would like to format the two new headings you have just typed in your worksheet in a similar way. What would be the quickest way for you to do this?

  • Position the cursor on the heading formatted by your colleague. Click on the Copy option on the Home Ribbon. Move the cursor to the first new heading you have just typed in your worksheet and click on Paste in the Home Ribbon. Move the cursor to the second new heading and click on Paste aga
  • Position the cursor on the heading formatted by your colleague. Double click on the Format Painter button on the Home Ribbon. Move the mouse pointer to the first of the new headings you have just typed in your worksheet and click the mouse button. Move the mouse pointer to the second heading and click again. Click on the Format Painter button again.
  • Position the cursor on the heading formatted by your colleague. Click on the Format Painter button on the Home Ribbon. Move the mouse pointer to the first of the two new headings you have just typed in your worksheet and click on Paste in the Home Ribbon.

5. You need to format a block of financial information so that the figures are showing the correct currency symbol. How can you do this? Select all options that apply.

  • Click on the Number Format drop-down in the Number group on the Home Ribbon and choose Currency.
  • Right-click on the cell and choose Format Cells.
  • The currency symbol will need to be typed in the cell with the numbers.
  • Click on the currency symbol in the Number group on the Home Ribbon.

6. You are creating a spreadsheet to track new stock. Each product that you are listing will be assigned a category code which will simply be two letters. You can see that Column G is not wide enough to fully display the heading Sales Category. As the entries underneath the heading will only ever be two letters, you do not want to widen the column. How can you format the heading so that both words will be visible?

Text is truncated in a cell.
  • Use the Wrap Text feature.
  • Use the Merge Cells Feature.
  • Use the Center button in the Alignment group on the Home Ribbon

7. Question 7 You have created a workbook containing several sheets of information for a client. You would like to change some information, and you decide that a Search and Replace would be the quickest way to do this. When you go to Home, Find and Select and then click on Find what options are you given in the Find dialog that can make your search quicker and more efficient? Select all options that apply.

  • You can specify worksheet names to limit the search to a subset of the worksheets in the file.
  • You can search for entries formatted in a particular way.
  • You can perform a search that looks through the whole workbook.
  • You can ask Excel to match the case if you are searching for text.
  • You can limit the search to any notes or comments that might have been added.

8. You have created a worksheet that contains many technical terms, and you decide to run the spellcheck feature to make sure that everything is spelled correctly. When the spellchecker begins, you realize that it is checking in the wrong language. How can you quickly change the?

  • In the Spellchecker dialog, click on the Dictionary Language drop-down and change to the correct dictionary.
  • Click on the Options button and change the dictionary there.
  • Click on the Add Dictionary button in the Spelling dialog.

9. You are updating a worksheet and the cursor is currently on D3. The data in the sheet ends in column E and in row 8. This is the only content in the spreadsheet. If you go to the Page Layout ribbon and use the Breaks command to add a manual page break, how many pages would you have in the worksheet?

  • Two
  • One
  • Four

10. You have a worksheet that contains a detailed breakdown of figures. At the top of the worksheet is a summary. In the Print Setup options dialog which setting should you adjust to print only the summary?

  • Change the Rows to Repeat at the top setting.
  • Change the Print Order setting.
  • Change the Print Area setting.

Knowledge Check: Working with formulas and functions quiz answer

Q1. What does the highlighted Fx command do in Excel?

fx button highlighted
  • Forces Excel to calculate and give a result.
  • Tells Excel that you are keying in a Formula
  • Opens the Insert Function dialog.

2. Which of these mathematical symbols has the highest precedence in Excel?

  • > (Greater Than)
  • + (Addition)
  • / (Divide)

3. You have typed the following Formula into your Worksheet. What does the $ symbol guarantee in the A4 Cell Reference?

Column C, equals A Dollar four asterisk ten. Dollar sign highlighted.
  • It means that if the Formula is copied or moved, the Column Initial will stay the same.
  • It means that if the Formula is copied or moved, the Row Number will stay the same.
  • It means that if the Formula is copied or moved both the Column Initial and Row Number will stay the same.

4. Where can you find the AutoSum Function? Select all options that apply.

  • On the Formulas Tab
  • On the right-click Shortcut Menu
  • In the Editing group on the Home Tab
  • In the Insert Function dialog

5. Where can you find the Calculate Now command?

  • On the Formulas Tab
  • In the Insert Function dialog
  • On the Shortcut Menu

Knowledge check: Sorting and filtering data quiz answer

1. You have clicked on the Sort button and the following message appears. If you select the highlighted choice, what will happen?

Sort Warning, Continue with current selection highlighted
  • Excel will use the column that you have highlighted as the key to the Sort.
  • The whole block of data will be sorted using the Column you have highlighted as the key.
  • Excel will only Sort the data that you have selected.

2. Why is this highlighted check box in the Sort dialog so important?

My data has headers is highlighted.
  • Without this ticked, Excel will not know what kind of Sort to perform.
  • Without this ticked, Excel will Sort the headings with all the other rows.
  • Excel uses this to decide on the dimensions of the block of data.

3. When you open a file sent by a colleague, you notice that the row numbers on the left-hand side are in blue. What does this mean?

The row numbers on the left-hand side are in blue and highlighted
The row numbers on the left-hand side are in blue and highlighted
  • That the data has been Sorted
  • That there are hidden Columns in the data
  • That the data has been Filtered

4. You have a block of data that contains a list of addresses in one Column. You would like to Filter the data based on a particular location, which would be a part of the address. What is the quickest way to Filter so that you only see the Rows where that area is mentioned in the address?

  • Sort the data by location first and then use the Filter option.
  • Use the drop-down Filter list and remove the tick mark for any address that is not in the correct area.
  • Use the Text Filters option on the drop-down Filter list.

5. When you open a File, you realize that there are several Filters in place, so you are not seeing all the rows in the data. How can you clear the Filters? Select all options that apply.

  • Click on each Filter drop-down arrow and use the Clear Filter option.
  • Use the Clear command in the Data Tab.
  • Right-click and choose “Clear All” on the Shortcut Menu.
  • Turn off the Filter feature.

Knowledge check: Apply range in Excel quiz answer

1. Where is the Name Box on the Excel screen

  • On the Formulas Tab
  • On the Status Bar
  • To the left of the Formula Bar

2. You want to apply a Range Name to a block of cells. Which of the following statements are true? Select all options that apply.

  • The Name can contain a mixture of text and numbers.
  • The Name can be a duplicate of another one in the Worksheet.
  • The Name will not be case-sensitive.
  • The Name cannot contain spaces.

3. You are creating a Formula and would like to use a Name that you’ve previously created in the calculation. When the Autocomplete suggestion list appears, what symbol will be beside the Name in the drop-down list?

One is cell or block of cells,  two is a table with the first row highlighted, three is the Function symbol.
  • 2
  • 3
  • 1

4. You created a Formula that contains both Cell References and a Name. You are about to copy the Formula. If you want the formula to remain unchanged in its new location, where do you need to put Dollar Signs?

  • On the Cell References and the Name
  • On the Cell References only
  • You do not need Dollar Signs.

5. Which option would you use to remove a Name from the File if you no longer need it?

  • The Define Name drop-down on the Formulas Tab
  • The Name Box
  • The Name Manager dialog

Knowledge check: Specialized functions quiz answer

1. you are typing a Function Formula and the floating Screen Tip is visible. Some of the words in the Screen Tip are enclosed in square brackets. What do these indicate?

  • That those pieces of information are required in the Formula
  • That those pieces of information are optional in the Formula
  • That you need to type square brackets at that point in the Formula

2. When you create a Formula in Excel, you see the calculation on the Formula Bar and the results in the Worksheet. If you wanted to see the Formulas in the Worksheet, where can you find the Show Formulas command?

  • On the Home Tab
  • On the View Tab
  • On the Formulas Tab

3. What is a Nested Function?

  • A Function that allows you to add in Criteria
  • A Function added in using the Insert Function dialog
  • A Function used inside another Function

4. You are adding a function formula to your worksheet. When the Formula is complete, apart from the Function name what other characters must it include? Select all options that apply.

  • An Equals Sign
  • An opening and closing parenthesis
  • Commas
  • Square Brackets

5. Question 5Where can you find a complete list of available functions? Select all options that apply.

  • On the Formula Tab
  • In the Insert Function dialog
  • The Insert Ribbon

Knowledge check: Text functions quiz answer

1. does the number 3 indicate in this Formula?

The number 3 highlighted in formula =LEFT(A1,3).
  • That the Formula should return 654
  • That the Formula should return ABC
  • That the Formula should return 456

2. What does the TRIM Function do?

  • Removes all spaces from a piece of text, except for the spaces between words.
  • Removes all spaces from a piece of text.
  • Removes all spaces and symbols from a text string.

3. Which Functions can change the case of the letters in a text string? Select all options that apply

  • LEN
  • PROPER
  • LOWER
  • UPPER

4. What would the result of this TEXTJOIN Formula be?

TEXTJOIN Formula highlighted =TextJoin(,True,A1,C1)
  • John Smith
  • JohnSmith
  • Joh,Smith

5. You are entering a TRANSPOSE formula to swap a vertical range of Cells to horizontal. What keys do you press to finish the Formula?

  • Alt + Shift + Enter
  • Ctrl + Alt + Enter
  • Ctrl + Shift + Enter

Knowledge check: Logical functions quiz answer

Q1. What does the following logical operator mean?

greater than and less than symbols used incorrectly.
greater than and less than symbols used incorrectly.
  • Is Not Equal To
  • Is Less Than
  • Is Greater Than
  • Is Equal To

2. What outputs can a Logical Function produce? Select all options that apply.

  • True
  • N/A
  • False

3. What will the result of this Formula be?

An OR Function.
An OR Function.
  • TRUE
  • FALSE
  • C1

4.What will the result of this Formula be?

An IF function.
  • 1,500
  • 15,000
  • 300

5. What will the result of this Formula be?

An IFS function.
.
  • Friday
  • Wednesday
  • Sunday

Module graded quiz: Working with functions quiz answer

Q1. You have been asked to check stock levels for a product which is in high demand. There are two lists of customers who would like to purchase as many items of this product as possible. Which formula below would work out the correct answer of 6 per customer?

  • =(A3+B3)/D3+E3
  • =A3+B3/D3+E3
  • =(A3+B3)/(D3+E3)

2. You have decided to award a 10% discount to a valuable customer and in D2 you want to calculate the discount amount that will appear on their monthly invoices. You have typed 10% into A2, and you know that if you multiply the monthly total in C2 by A2, Excel will work out the 10 percent. You also want to be able to use the Autofill feature to copy this formula for the other three months. Which formula below would work out the correct result for you?Data showing a 10% discount in January

  • =C2*A2
  • =C2*$A2
  • =C2*A$2

3. Functions are a useful and vital part of Excel. Excel contains over a hundred of these predefined formulas which range from the simple and straightforward to the very complex. Which of the following must every function formula contain? Select all options that apply.

  • A pair of parentheses
  • Commas
  • An Equals sign
  • The Function name

4. You are working with this block of data and need to sort it. What kinds of sort can you do with this information? Select all options that apply.

A dataset.
  • A Sort by Date Order
  • A sort based on a partial text entry, such as Shoes in column C
  • A Numeric Sort
  • An Alphabetical Sort

5. You are sent a worksheet by a colleague, and you notice an error message. You need to fix this issue so what is likely to be the cause of this message?

hashtag Name error in a cell.
  • Your colleague has forgotten to assign the name Price_increase to any cell.
  • Your colleague has forgotten to enter a value into the cell called Price_increase.
  • Your colleague has used an incorrect character in the cell Name. They should have used space instead of the underscore

Knowledge check: Create and modify tables quiz answer

1. Which Excel Ribbon contains the command to transform a block of data into a Table?

  • The Data Ribbon
  • The Home Ribbon
  • The Insert Ribbon

2. You have created a Table that starts in cell A1 and extends down as far as D20. If you put your cursor in A21 and begin typing, what would you expect to happen?

  • The new data you are typing will not be part of the Table. If you want the new material to be included in the Table, you will have to manually extend the borders of the Table.
  • The bottom border of the Table will drop down to incorporate the cells A21 to D21.

3. You have created a Table and it currently has the default blue and white banded color scheme. If you wished to change this color scheme quickly or customize it, where will you find the commands to do this?

  • On the formatting sections of the Home Ribbon
  • On the Table Styles section of the Table Design Ribbon
  • On the Themes section of the Page Layout Ribbon

4. if you switch on the Total Row feature in a table, what Function is used to create the results that appear?

  • SUBTOTAL
  • SUM
  • SUMPRODUCT

5. When this symbol appears what does it allow you to do?

FlashFill button.
  • Summarize with a PivotTable
  • Customize AutoExpansion
  • Resize the Table

Knowledge checky: Table references quiz answer

1. When you are naming Tables, which of the following rules do you need to observe? Select all options that apply.

  • The name cannot contain spaces.
  • The name must start with a number.
  • The name must not be used anywhere else in the workbook.

2. Where in Excel can you see a list of all the Tables that exist in the workbook?

  • In the Name Manager dialog
  • In the Properties of the Table Design Ribbon

3. If you are creating a formula which references Table information, what kind of parentheses are used in a Structured Reference?

  • [ ]
  • ( )
  • { }

4. What is the main difference between the SUBTOTAL Function and the DCOUNT Function?

  • The DCOUNT Function can be used in Tables.
  • The DCOUNT Function allows you to set criteria.

5. Where can you find the command to convert a Table back to a Range?

  • In the Data Tools section of the Data Ribbon.
  • It is one of the choices in the Clear drop-down on the Home Ribbon.
  • In the Tools section of the Table Design Ribbon.

Knowledge check: Visualizing data with basic charts quiz answer

1. You decide to visualize your data by adding a Pie Chart to your Worksheet. Which of the following methods can you use to add in the chart? Select all options that apply.

  • Go to the Insert Ribbon and click on Recommended Charts.
  • Go to the Charts group of the Insert Ribbon and click on the Pie Chart Icon.
  • Use the options button on the Charts group to open the Insert Chart Dialog.

2. You have already created a Chart in your worksheet. You add some new information to the data that you based the Chart on. How can you include this new data in the Chart?

  • Highlight all the data and choose Recommended Charts from the Insert Ribbon.
  • Use the Dialog Options button in the Charts group to start the Insert Chart process and highlight your data again.
  • Select the chart and drag the resize handles to include the new data.

3. Once you have created a Chart from your data, what would the highlighted button allow you to do?

The Chart Elements options button.
  • You can use it to increase the size of the Chart.
  • You can use it to add new elements to enhance your Chart.
  • You can use it to add new data to the chart.

4. You have added a Chart to your worksheet and want to make it stand out more. You decide to use the Chart Styles feature to do this. Which ribbon will you find this feature on?

  • The Chart Format contextual ribbon
  • The Chart Design contextual ribbon

5. You have created a Chart from data that shows company revenue steadily rising over the last six months. You decide to add a Trendline to the Chart, to make this pattern clear to anyone viewing the Chart. Which type of Trendline should you choose?

  • Linear
  • Exponential

Knowledge Check: Analyzing data quiz answer

Q1. Where would you find the command to insert a Pivot Table?

  • In the Data Tools section of the Data Ribbon
  • In the Tables section of the Insert Ribbon
  • In the Add-Ins section of the Insert Ribbon

2. When you click to create a Pivot Table, where is the Pivot Fields Pane?

  • In the grid area of the worksheet
  • On the left-hand side of the Excel Screen
  • On the right-hand side of the Excel Screen3.

3. If you move one of the field headings into the highlighted box, what will happen?

The Values field in the PivotTable task pane.
  • The field data will be used as criteria.
  • This will create rows of unique Field entries.
  • Excel will perform calculations on the field data.

4. You have added a Slicer to your worksheet to filter the results generated in the Pivot Table. Which of the following settings can you customize for Slicers? Select all options that apply.

  • The color
  • The position
  • The size

5. You have created a Pivot Table, totaling the company revenue for the last six months. In which situation would you use the options in the highlighted tab?

The Show Values as tab in the Value Field Settings dialog box.
  • You would use it to display the individual monthly totals as percentages of the overall total.
  • You would use it to change the results in the Pivot Report from Sums to Averages.
  • You would use it to change the formatting of the figures generated by the Pivot Table.

Knowledge Check: Managing large workbooks quiz answer

Q1. When you are naming Sheets, which of the following rules do you need to observe? Select all options that apply.

  • The name can contain spaces.
  • You must avoid all punctuation marks.
  • The name cannot exceed 31 characters.

2. You would like to copy a sheet from one workbook to another. What guidelines do you need to keep in mind? Select all options that apply.

  • The two workbooks must be saved in the same area.
  • The two workbooks must be open.
  • You must tick the “Create a Copy” checkbox.

3. In this worksheet, if you use the Freeze Panes choice while the cursor is in the cell shaded yellow, what areas of the worksheet will stay static?

A dataset with cell C2 colored yellow.
  • Only Row 1
  • Row 1 and Columns A and B
  • Only Columns A and B

4. Where will you find the command to Split the work window so that you can see different areas of the worksheet?

  • On the Data Ribbon
  • On the View Ribbon
  • On the Home Ribbon

5. If the Title Bar of the Excel screen shows the following, what does it mean?

Number one in the document title.
  • That the file called Sample is open is more than one Excel window
  • That the Split feature has been used to split the screen
  • That the number 1 is part of the filename

Module graded quiz: Organizing and visualizing data quiz answers

Q1. You are tracking sales data for the team that you manage and have already typed in some of the data. A colleague suggests that rather than typing it as a plain data list, you could format it as a Table. What extra functionality will be possible if you make this list into a Table? Select all options that apply,

A dataset with Sort and Filter buttons at the top of each column.
  • You can create customized totals in the column to the right of the data.
  • The Table will be dynamic.
  • You can quickly enhance the appearance of the data by applying a color scheme.
  • You can make the data easier to read with banded colors.
  • The Table can be named and referred to from anywhere in the workbook.

2. You are creating formulas in your workbook that refer to a named Table on a different worksheet. What is the following formula calculating?

A formula for calculating a total for a column called Transactions in a Table called Sales.
  • This is calculating a total for a column called Sales in a Table called Transactions.
  • This is calculating a total for a column called Transactions in a Table called Sales.
  • This is calculating a total for a column called Sales in a worksheet called Transactions.

3. You are about to attend a weekly meeting with the Sales team that you manage, and you want to check who has been meeting their target so far this week. You need to create a formula in G5 in this Table which will calculate this for you. Which of the following is the correct calculation to do this?

  • =(Sales-Target)
  • =Sales-Target
  • =[Sales]-[Target]

4. You are renaming a worksheet to reflect its contents. You want to rename it ClientsPurchasingHistory – January/May. Which elements in this name will Excel have difficulty with? Select all that apply.

  • You have used capitals in the worksheet name which is not allowed.
  • You have used a punctuation symbol that is not allowed.
  • The name is too long.
  • You have used a term which is not allowed.
  • You have used spaces.

5. You have been sent an Excel file by a colleague and you are reviewing the formulas that have been added to the worksheet. One of the cells contains the following formula:

  • =SUM(A2:A20)/Targets!B3
  • What does the entry Target! Indicate?
  • This is the name of a column in a Table.
  • This is the name of a sheet.
  • This is a named cell in the worksheet.

6. You are reviewing a workbook that was prepared by a colleague. Some confidential figures are not included in any of the sheets in the workbook. Because of the confidentiality of the figures, you wonder if your colleague has hidden the worksheet that they are on. How can you check if this is the case

  • Go to the Home Ribbon and click on the Format drop-down. Move to the Visibility Section and choose unhide sheet from the sub-menu.
  • Right-click on the Plus symbol at the end of the sheet tab display.
  • Go to the View ribbon and click on the Visibility drop-down. From the list provided, choose unhide sheet.

7. You have created a Chart showing the sales achieved by each member of your team. Just before you are about to present this Chart at a meeting with your manager, one of the team calls you to say that they have received orders from two new customers. You want the Chart to show the details of these new orders. What do you need to do to ensure that the Chart shows the correct information?

  • Change the figure in the source data.
  • Delete the Chart. Amend the source and then highlight it and add the Chart again.
  • Use the Select Data button on the Chart Design ribbon to adjust what the Chart is showing.

8. You are preparing for a meeting with your manager to discuss how each member of the Sales Team is performing versus the target set for the team. To help make the picture clear, you have transformed the figures into a chart. One of the team members has outperformed her colleagues and you would like to format that result in a different color so that it stands out. What are the correct steps to do this? Select all options that apply.

  • Click on Leah’s result so that all the blue data series columns are selected. Click again so that only Leah’s result is selected. Right click and choose Format Data Point. Change the color in the Format Data Pane.
  • Click on Leah’s result so that all the blue data series columns are selected. Click again so that only Leah’s result is selected. Go to the Chart Format Ribbon and click on Format Selection. Change the color in the Format Data Point Pane.
  • Click on Leah’s result so that all the blue data series are selected. Click again so that only Leah’s result is selected. Click on the paintbrush icon that appears on the right-hand side of the Chart. Switch to the color Tab in the dialog and make a choice there.

9. You have been asked to create a summary of the sales figures achieved by your team and decide to create a Pivot Table to do this. The data is organized under the following headings:

Some Sales figures under specific Headings.
Some Sales figures under specific Headings.

You need the Pivot table to show you the overall sales total and the amount achieved by each person on the team. You also want to be able to see what percentage of the overall total each salesperson achieved, and you need to be able to filter by their name. You will be dragging the headings into the Pivot Table Task Pane.

What steps do you do need to take to design the Pivot Table to give you this information? Select all options that apply.

Add Weekly Sales to the Values box. Go to the Value Field Settings dialog and change the Show Values As setting to % of grand total.

You put the heading Weekly Sales in the Columns Box.

You add the staff member’s name to the Filters box so that you can filter by name.

You add the staff member’s name to the Rows box.

You put the heading Weekly Sales in the Values box.

10. What do the buttons on a Slicer represent?

  • Sorting Options for your PivotTable data
  • Unique entries that exist within the Field that the Slicer is based on
  • Headings that can exist within the PivotTable

Knowledge check: Conditional formatting quiz answer

Q1. Where would you find the command to set Conditional Formats for a cell?

  • In the Editing group on the Home Ribbon
  • In the Styles group on the Home Ribbon
  • In the Data Tools group on the Data Ribbon

2. You can create multiple Conditional Format rules to the same cell.

  • True
  • False

3. You want to add a Conditional Format rule to a block of cells which would highlight all values over 10,000. Which of the following quick choice menus should you use on the Conditional Formats drop down?

  • Top/Bottom Rules
  • Highlight Cell Rules

4. You would like to check if there are any Conditional Format rules applied to a particular block of cells. Where can you find the Rules Manager which will allow you to do this?

The Manage Rules option at the bottom of the Conditional Formats drop-down.

The Format Cells Dialog

5. You have set three Conditional Format rules on the same cell. You want to ensure that if the criteria in the first rule is met, then only that rule will be enforced. Which setting controls this in Conditional Formatting?

  • This is controlled by the order in which the rules appear in the manage rules list.
  • The Stop if True checkbox.

Knowledge check: Enhancing and managing workbooks quiz answers

Q1. You are working on a spreadsheet in collaboration with a colleague who works a different shift to you. You have a question that you would like to ask about some of the content in the worksheet. You would like your colleague to see this question as soon as they open the workbook, and you would like them to be able to leave a reply. Which Excel feature would be the best for this?

  • Notes
  • Comments

2. How do you know that there is a Comment on a cell when you look at the worksheet?

  • There is a violet triangle in the top right corner of the cell.
  • There is a green triangle in the top left corner of the cell.
  • There is a red triangle in the top right corner of the cell.

3. You and your colleague have been having a conversation using the Comment feature in a worksheet. You would like to close a conversation but keep a record of it. Which of these would be the best approach?

  • Click on the ellipsis in the original comment and choose Resolve Thread.
  • Click on the ellipsis in the original Comment and choose Delete Thread. Click on the ellipsis in the original comment and choose Close Thread.

4. You would like to remove all personal information from a Workbook before you send it to a different department. Which Excel feature would you use for this?

  • The Compatibility Checker.
  • The Check Accessibility feature.
  • The Document Inspector.

5. Every Excel workbook contains metadata information that is stored in the file as it is being created. This information is known as Document Properties. Which of the following pieces of information are recorded?

  • The original author of the file.
  • The date the file was originally created.
  • The number of calculations in the workbook.
  • The number of worksheets in the file.

Knowledge check: Customize the Excel environment quiz answer

Q1. Where can you position the Quick Access Toolbar on the Excel Screen?

  • Select all options that apply
  • Below the Ribbon Area.
  • On the Status Bar.
  • On the Title Bar.

2. You cannot add a command to an existing Ribbon – True or False?

  • False
  • True

3. You have already used the Spellcheck feature to check your worksheet. You notice that a title which is in block capitals is incorrect, but Excel has not flagged it. What has gone wrong?

  • The Title appears in a merged cell.
  • The words used in the title are not in Excel’s built-in dictionary.
  • The “Ignore Words in Uppercase” checkbox in the Proofing options is ticked.

4. You would like to customize the color the Excel uses for the Error Indicator in Excel. Where do you go to do this?

  • File, Options, Advanced.
  • File, Options, Formula.
  • File, Options, General.

5. You would like to add a command to a Ribbon but when you go into the File, Options, Customize Ribbon dialog you cannot see it on the list of commands on the left-hand side. What should you do?

  • If the command is not immediately visible you cannot add it to the Ribbon.
  • Add it to the Quick Access Toolbar instead.
  • Change the Choose Commands From choice to All.

Knowledge check: Exporting and sourcing data quiz answers

Q1. You have created a file in Excel which you are now intending to save in a different file format. Which of the following formats will remove all formatting from the exported file?

Select all options that apply.

  • CSV
  • PDF
  • TXT

2. If you are choosing a format to export your Excel data to, which of the following results does the CSV, PDF, and TXT formats have in common?

  • They all use the same delimiter
  • They all remove formatting
  • They all save only one worksheet from the workbook.

3. What is the name of the grouping in the Data Ribbon that contains all the commands to access external data?

  • Get and Transform Data
  • Import and Transform Data.
  • Get Data

4. You used the Get Data command to import data from another file format. The data has loaded into the current Excel worksheet and the Query Editor is visible on the right-hand side of the work screen. If you click on the highlighted choice, what will happen?

Convert to Range button.

The Data will no longer be a table, but it will maintain the Query and the link to the original source file to allow for automatic refresh.

The Data will no longer be a table, and it will no longer be possible to run the Query.

Module graded quiz: Advanced Excel features quiz answer

Q1. You are working offline on some important Excel files which are saved on your PC. Your area has been subject to unexpected power cuts. How can you ensure that you do not lose any important data if a power cut happens?

  • Go to File, Options, Save and Tick the AutoRecover checkbox.
  • Go to File, Options, Save and Tick the AutoSave checkbox.
  • Turn on the Autosave choice on the Quick Access Toolbar.

2. The worksheets that you create typically contain many Charts. As the files will be circulated to a wide audience in the organization, you want to add Alternative Text to Charts as you create them. To speed up this process, you decide to have the Alt+Text option as a separate button on the Review Ribbon. Once you have begun the process by going to File, Options, Customize Ribbon, you cannot see Alt Text as a choice in the left-hand list. What steps do you need to take to successfully add Alt+Text to the Review Ribbon?

  • Click on the “Choose Commands From” drop down. Change the choice from Popular to Custom Tabs and Groups. Locate Alt+Text and then click the Add button between the two lists to position it on the Review ribbon to the right.
  • Click on the “Choose Commands From” drop down. Change the choice from Popular to All. Locate and select Alt+Text and then click the Add button between the two lists to position it on the Review ribbon to the right.
  • Click on the “Choose Commands From” drop down. Change the choice from Popular to All. Locate and select Alt+Text. With the Review Ribbon selected in the list to the right, click on the New Group choice. Type a name and press enter. Click the Add button between the two lists to position Alt+Text in the group.

3. You have created a worksheet which tracks the sales made by the Sales Team. You saved it in OneDrive and shared it with the team members. You have asked them to update their sales figures on an ongoing basis. You would like to create a Conditional Format rule so that you can check this file at any time and be immediately aware of the top three sales figures. What would be the most efficient way to do this?

  • Select the cells where the sales figures will be entered. Go to Home, Conditional Formatting, and choose Data Bars.
  • Select the cells where the sales figures will be entered. Go to Home, Conditional Formatting, and choose from Highlight Cell Rules.
  • Select the cells where the sales figures will be entered. Go to Home, Conditional Formatting, and Choose from Top/Bottom Rules.

4. The Manage Rules box allows you to monitor and control the Conditional Format rules set for any cell. Which of the following are settings you can control using Manage Rules?

  • Select all options that apply.
  • Edit a Rule
  • Copy a rule to apply to another block of Cells.
  • See what rules are applied to a cell.
  • If there are multiple rules, stop Excel processing the list at a certain point
  • Create a Rule

5. You created a spreadsheet to track your team’s Sales achievements. Now you want to create a Conditional Format Rule on the Weekly Sales column. You want it to change the color of the cell in Column E when the value in it matches or exceeds the target figure in column F. How would you do this?

Weekly Sales and Weekly Goal data columns.
Weekly Sales and Weekly Goal data columns.
  • Select E5 to E15. Click on Home, Conditional Formatting, New Rule and use the Create a Formula choice.
  • Select E5 to E15. Click on Home, Conditional Formatting, Highlight Cell Rules and use the Greater Than choice.

Select E5 to E15. Click on Home, Conditional Formatting, New Rule and use the Format All Cells Based on Their Values choice.

6. You have been collaborating on a worksheet with a colleague and you have both contributed to a Comment thread. You decide that the conversation should be closed. You need to keep the Comments until the final review of the worksheet is completed by a more senior colleague. What would you do to indicate that the conversation is closed?

  • Close the Comment Thread.
  • Delete the Comment Thread.
  • Resolve the Comment Thread.

7. Excel gives you two ways to add annotations to a cell in an Excel Workbook. You can add either a Note or a Comment to any cell. What features are available for Comments but not available for Notes?

Select all that apply.

  • Format the text with a different Font or Color.
  • Adding a Reply.
  • Ability to use the @ symbol to notify someone of what has been said.
  • A date and timestamp.

8. You have imported Data from an Access Database into your Excel Workbook. The Query Editor window is visible on the right-hand side of the Excel screen. Now that you have imported the data in your worksheet, you will only be working on it in Excel. You’re aware that colleagues amend the original data in Access regularly, but you do not need to see those changes in the Excel copy. What should you do to ensure that the data you have brought into Excel does not update when your Colleagues change the source?

  • Click on the Delete Query button in the top left corner of the Query Editor window.
  • In the Table Design Ribbon, click on the Convert to Range choice in the Tools Group.
  • In the Table Design Ribbon, click on the Refresh button and click on the delete link choice.

9. A client has asked you to send information to them in a CSV format that they can import into their own accounting system. Which of the following statements are true in relation to the file you are sending to the client?

  • The file will only contain plain text.
  • The file will only contain information from one Excel sheet.
  • The columns will be separated by a Tab character.

10. You have already created the CSV copy of the Excel file that the customer has requested. You decide to also send a PDF copy of the original file so that your customer can see the original layout. Which series of steps would be correct to complete this action?

  • Click on File, Share, and click on the PDF icon.
  • Click on File, Export, Click on the PDF button.
Work Smarter with Microsoft Excel Course Review:

In our experience, we suggest you enroll in Work Smarter with Microsoft Excel courses and gain some new skills from Professionals completely free and we assure you will be worth it.

Work Smarter with Microsoft Excel course is available on Coursera for free, if you are stuck anywhere between a quiz or a graded assessment quiz, just visit Networking Funda to get Work Smarter with Microsoft Excel Coursera Quiz Answers.

Conclusion:

I hope this Work Smarter with Microsoft Excel Coursera Quiz Answers would be useful for you to learn something new from this Course. If it helped you then don’t forget to bookmark our site for more Quiz Answers.

This course is intended for audiences of all experiences who are interested in learning about new skills in a business context; there are no prerequisite courses.

Keep Learning!

Get All Course Quiz Answers of Microsoft 365 Fundamentals Specialization

Work Smarter with Microsoft Word Coursera Quiz Answers

Work Smarter with Microsoft Excel Coursera Quiz Answers

Work Smarter with Microsoft PowerPoint Coursera Quiz Answers

Leave a Reply

Your email address will not be published.

error: Content is protected !!