Data Visualization in Excel Coursera Quiz Answers

Get Data Visualization in Excel Coursera Quiz Answers

Week 1: Data Visualization in Excel

Quiz 1: Trend-based conditional formats

Q1. Conditional formatting allows you to automatically apply formatting to one or more cells based on the cell value. This means that conditional formatting can automatically change the cell colour, font, border or even show a different number of decimal places.

• True.
• False.

Q2. What does trend-based formatting do?

• Trend based formatting highlights values that are over a specified value.
• Trend based formatting sorts the data to reveal the trend.
• Trend based formatting ranks the data set against itself and applies some form of visual scale.
• Trend based formatting filters the data to only show values that meet specific criteria.

Q3. When applying Icon Sets the original values still appear in the cell.

• True.
• False.

Q4. Which of the following methods allow us to remove conditional formatting (more than one may apply)?

• Selecting the cells and pressing the Delete key.
• Selecting the cells and choosing Home > Clear > Clear Formats.
• Selecting the cells and choosing Home > Conditional Formatting > Clear Rules.
• Selecting the cells and choosing Home > Conditional Formatting > New Rule.

Q5. What type of conditional formatting has been applied to the data below?.

• Color Scales.
• Chart Sets
• Icon Sets.
• Data Bars.

Quiz 2: Value-based conditional formats

Q1. Which would be the appropriate Conditional Formatting set if you wanted to highlight all values over 300?

• Highlight Cell Rules
• Top/Bottom Rules

Q2. Conditional formatting can be used to highlight duplicate values.

• TRUE
• FALSE

Q3. If we select cells A1:A3, go to Conditional Formatting and select Text that Contains and then type dr which of the cells will change?

• A1 and A3
• A1 only
• A1, A2 and A3
• A3 only

Q4. Conditional formatting by using a formula is advantageous over other conditional formatting because:

• We can choose from a greater range of formatting options.
• We can more easily see the rule that the conditional formatting is based on.
• We can easily change the value that the conditional formatting is based on.
• You can check for more complex conditions, like checking if two criteria are met.

Q5. In the following scenario, which conditional formatting rules may have been used (more than one may apply)?

• Highlight Cell Rules → Greater Than
• Top/Bottom Rules → Bottom 10 Items → [change the number 10]
• Top/Bottom Rules → Bottom 10 % → [change the number 10]
• Highlight Cell Rules → Less Than

Quiz 3: Macros for interactive visualizations

Q1. Excel allows us to record macros to automate difficult or repetitive procedures. Which of the following is true of recording macros?

• When recording a macro, Excel records your mouse clicks but it cannot record your keystrokes.
• When recording a macro, Excel cannot record your mouse clicks but it can record your keystrokes.
• When recording a macro, Excel records your mouse clicks and your keystrokes.

Q2. Which of the following are mixed cell references in Excel?

• $B$2
• $B2
• B$2
• B2

Q3. When using a formula to apply conditional formatting to a whole row such as below, how should we refer to cell B34 in our formula?

• $B$34
• B34
• B$34
• $B34

Q4. Refer to the screenshot below. The user applied a conditional format on row 34 to format the relevant part of the row blue, however, some of the cells are appearing green which is the result of another conditional format. What can be done to ensure that the blue conditional formatting takes precedence over the green conditional formatting?

• The only way to do it is to delete the Blue rule and add it again as the most recently added rule always takes priority.
• Go to Manage Rules and move the Blue rule above the Green rule using the up arrow.
• Go to ManageRules and move the Blue rule below the Green rule using the down arrow.
• Go to Manage Rules and tick the box that says Stop if True next to the Blue rule.

Q5. Which of the following file types support macros (more than one may apply)?

• Excel workbook *.xlsx.
• CSV (Comma Separated Values) *.csv
• Macro Enabled Workbook *.xlsm.
• Excel Binary Workbook *.xlsb

Quiz 4: Visualizations with Sparklines and Shapes

Q1. What is the key difference between a sparkline and a chart in Excel?

• A sparkline sits within a single cell, unlike a chart which does not sit within a cell.
• A sparkline does not change after being created even if you change your data.
• A sparkline is not customisable, unlike a chart which is customisable.
• A sparkline only works in macro-enabled workbooks, unlike charts.

Q2. Sparklines are a useful data visualisation tool for which of the following cases (more than one may apply):

• Observe a trend for a row or column.
• Observe a detailed display of a trend that allows a user to hover over each data point.
• Observe an overlay of trends for comparison.
• Observe multiple trends for various rows or columns in one dashboard.

Q3. After creating a sparkline we can drag the fill handle to create multiple sparklines.

• True
• False

Q4. If you use the fill handle to create multiple sparklines, the sparklines will form a set of connected charts, meaning if you change one you change all of them. It is possible to unlink sparklines if you do not wish them to behave this way.

• False
• True

Q5. To get this collection of shapes in a neat row at exactly the same height and evenly spaced we would need to apply which two operations from the Align tools?

• Align Top and Distribute Horizontally
• Align Center and Distribute Horizontally
• Align Middle and Distribute Vertically
• Align Center and Distribute Vertically

Quiz 5: Custom number formats

Q1. Custom number formats can be accessed in which of the following ways (multiple answers may apply):

• Go to the Number group in the Home tab in the ribbon, click the dropdown, select More Number Formats.
• Go to Conditional Formatting and select Custom Number Format.
• Right-click a cell and select Format Cells.
• Go to the Data tab and select Custom Number Format.

Q2. When customising a number format:

• We first specify how positive values should look, secondly how negative values should look, and thirdly how zero values should look and lastly, how text should look.
• We first specify how zero values should look, secondly how positive values should look, and thirdly how negative values should look and lastly, how text should look.
• We first specify how negative values should look, secondly how positive values should look, and thirdly how zero values should look and lastly, how text should look.
• We first specify how negative values should look, secondly how zero values should look, and thirdly how positive values should look and lastly, how text should look.

Q3. When customising a number format, which character acts as the separator for specifying different formats for different types of values?

• A semi-colon “;”
• A colon “:”
• A comma “,”
• A dash/hyphen “-“

Q4. What type of brackets should we use when specifying colours for our custom number formats?

• Round brackets ( )
• Square brackets [ ]
• Angle brackets <>
• Curly brackets {}

Q5. If a cell contains the value 0, what will display when we apply the following custom number format:

[Red]▲;[Green]▼;

• A green down triangle and a green 0.
• A zero in the default text colour.
• Nothing.
• A red up triangle only.

Week 2: Data Visualization in Excel

Quiz 1: Charting techniques

Q1. A chart can only be drawn if we select the labels as well as the data.

• True
• False

Q2. What is the difference between a column chart and a bar chart?

• Column charts are when the series in the chart touch each other, whereas bar charts are when the series in the chart are separated by gaps.
• Column charts only work for multiple series, whereas bar charts work for a single series.
• Column charts are when the series in the chart are separated by gaps, whereas bar charts are when the series in the chart touch each other.
• Column charts are when series are represented vertically, whereas bar charts are when series are represented horizontally.

Q3. When Excel refers to a clustered column chart, what is the cluster referring to?

• We have to provide both a value for the x-axis as well as the y-axis.
• Different data series are placed on top of each other to form a single column.
• Different sets of data are grouped together according to a certain category such as a year, or a type of data.
• Observations which are more frequent are highlighted.

Q4. There is a problem with the chart that has been generated below:

What is the problem?

• The Year series has been charted by Excel as a column, and needs to be added as an axis label instead.
• The Year series is not appearing clearly as it is of a different scale, and a secondary axis has not been added to reflect this.

Q5. To fix the problem that we noted in the previous question and removeYearas a series, what is the best option to choose from the Select Data Source dialog box?

• Unselect 1 under Horizontal (Category) Axis Labels
• Unselect Year under Legend Entries (Series)
• Click on Switch Row/Column
• Click on Edit under Horizontal (Category) Axis Labels and choose Year as the label.

Q6. To continue to fix the problem that we noted in the previous questions and add Year as a label to each cluster, what is the best option to choose from the Select Data Source dialog box?

• Click on Switch Row/Column
• Unselect Year underLegend Entries (Series)
• Click on Edit under Horizontal (Category) Axis Labels and choose Year as the label.
• Unselect 1 under Horizontal (Category) Axis Labels

Quiz 2: Pies and doughnuts

Q1. A pie chart is useful when we want to show:

• The proportions of a whole
• The trend in one series over time

Q2. For a pie chart to be an effective visualisation, the number of categories should be:

• Small
• Large

Q3. The key advantage that a doughnut chart has over a pie chart is that:

• A doughnut chart can display multiple series, unlike a pie chart.
• A doughnut chart can display non-numeric data, unlike a pie chart.
• A doughnut chart can display any type of numeric data, unlike a pie chart.

Quiz 3: Line charts and trendlines

Q1. How can we modify the line chart below to adjust the vertical axis to better display the range of the data?

• Adjust the chart to be bigger.
• Click on the axis and delete the irrelevant numbers.
• Go to Axis Options and adjust the Minimum Bound.

Q2. What does the R-squared value represent?

• A larger R-squared value indicates that no data points are missing.
• A larger R-squared value indicates raw data which is reliable.
• A larger R-squared value indicates a better fit between the raw data and the trendline.

Q3. What will choosing a Polynomial trendline likely do to the R-squared value below?

• It should stay the same.
• It should decrease.
• It should increase.

Q4. How can we forecast forward into the future using the chart below?

• Excel currently does not support this functionality.
• Click on the trendline and drag this forwards.
• Go to Trendline Options and go to Forecast and then Forward.

Q5. If we choose Display Equation on Chart, why is this equation useful?

• The equation can be used to forecast forwards.
• The equation can be used to create data values.
• The equation can be used to create trendline values.
• Editing the equation will automatically adjust the trendline.

Quiz 4: Area charts

Q1. Both area charts and line charts are mainly useful for time series data.

• True
• False

Q2. Which chart from the following list would be useful to visualise both the individual contribution as well as the total contribution to the trend in a time series data set of several categories?

• Area chart.
• 100% Stacked Area chart.
• Stacked Area chart.

Q3. Which Excel functionality would allow you to quickly recolour your chart according to a set of preset options?

• Quick Layout under the Chart Design tab.
• Themes under the Page Layout tab.
• Chart Styles under the Chart Design tab.
• Format Selection under the Formattab.

Q4. If you apply a theme after you have individually customised one or two series colours, these series will not be reset to fit the theme.

• True.
• False.

Q5. When applying a theme, the default setting in Excel is to apply the theme to only the active sheet, and not the entire workbook

• True.
• False.

Quiz 5: Scatter and bubble charts

Q1. A scatter chart is essentially an x-variable versus y-variable plot just like in standard mathematics.

• True.
• False.

Q2. A bubble chart is like a scatter chart, with a third variable, and solves the problem of displaying three variables in a 2-dimensional chart.

• True.
• False.

Q3. What is the likely cause of the equal-sized bubbles in this 2-D bubble chart?

• The third variable is a replica of the x-variable.
• The third variable is not selected.
• The third variable is a replica of the y-variable.

Q4. Outliers in scatter or bubble charts should always be removed and no notes need to be made to supplement your chart.

• True.
• False.

Q5. If we were to add a third set of data which included the same three variables used to plot the graph below, where the bubble size is the company size, Excel will usually add the new data set in a new colour.

• True.
• False.

Quiz 6: Data Visualization in Excel

Q1. Open the attached workbook. Have a look at the Top 20 Vendors sheet which shows percentage market share for the leading mobile phone vendors from July 2010 to July 2020. Apply a conditional format to highlight (in red) the top 10 market share percentages for the whole data set. Which companies are represented in the top 10?

C2 Week 1-2 Assessment
XLSX File
Download file

• Nokia and Samsung
• Nokia
• Apple
• Apple and Samsung

Q2. Apply conditional formatting to the range E4:X124 to highlight all values over 20 in green. View the results and determine which of the following assertions is true. (More than one answer may apply).

• Nokia’s market share dropped below 20% for the first time in January 2014 and did not get above 20% after that.
• Only three vendors achieved a market share of over 20% for the dates shown.
• Apple only held a market share of over 20% for 3 months in 2016.
• Since August 2012, Samsung’s market share has not dropped below 20%.

Q3. A conditional format has been applied to the Huawei data. Go to Manage Formats to take a closer look at the format. Which of the following are true of the format applied?

• Data Bar with no border, a minimum number 0 and a maximum number 20.
• Color Scale with no border, a minimum number 0 and a maximum number 20.
• Color Scale from Yellow to Orange, with a minimum percentage 0 and a maximum percentage of 25.
• Data Bar with a gradient fill, a minimum percentage 0 and a maximum percentage 25.

Q4. Select B4:Z124 and create a custom conditional formatting rule to highlight the value for the selected Year and Month shown in A27 (these values can be changed from the Dash sheet). Which of the following formulas achieves the correct result?

• =$B4=$A27
• =$B4=$A$27
• =B$4=A$27
• =B$4=$A27

Q5. In the range E125:Z125 insert column sparklines. Change the colour to Blue Accent 1 (the main blue used in the workbook) and add a highpoint in red. Which vendor has the following sparkline?

• Sony
• Nokia
• HTC
• Blackberry

Q6. We would like to be able to focus on one year at a time. We have put a Slicer for Year in the left-hand panel. A Slicer acts like a filter (you will learn more about them in Week 4).

Click on the 2016 slicer button to only show rows for 2016. Which vendor is represented by this sparkline?

• Lenovo
• Xiaomi
• Huawei
• Oppo

Q7. Clear the Slicer by clicking on the Funnel in the top right-hand corner of the Slicer (or you use the Clear Filter button on the Data tab). Add a 2-D line chart to compare the market share percentages for Apple and Samsung from Jul 2010 to Jul 2020. Turn off the Horizontal Axis. Use the slicer to filter the data to only show data for 2012 Which of the following represents this data correctly?

Q8. Clear the filter. In the chart you created in the previous question, add a trendline to the Samsung series. Turn on the R-Squared value and test out the different options. Of the following trendline options, which one returns the highest R-Squared value?

• Logarithm
• Power
• Linear
• Exponential

Q9. None of the options we looked at in the previous question return a very strong correlation. Change the trendline to a Polynomial order 3. What is the R-squared value for this choice?

• 0.9763
• 0.8945
• 0.9662
• 0.9575

Q10. To calculate metrics needed for the Dash sheet, a separate Calcs sheet has been added that needs a few additional inputs. Go to the Calcs sheet. In F17:F21 we wish to change the values to just show a ▲ where there has been an increase and a ▼ where there has been a decrease. Apply custom number formatting so that positive numbers show as ▲, negative numbers as ▼ and 0 shows nothing. Which of the following achieves this result?

Q11. On our Dash sheet, we would like to include a chart that for a given year shows how much of total market share was held by the top 5 vendors (combined) and how much was held by other vendors. The data has already been prepared and the values we need are sitting in C22:D22 on the Calcs sheet. Which of the following chart types would be suitable to achieve our purpose?

• Bubble Chart
• Pie Chart
• Stacked Area Chart
• Line Chart

Q12. We have decided to use a Doughnut chart to show the combined market share for the top 5 vendors compared to the total for the other vendors. Select the data in C16:D16 and C22:D22 on the Calcs sheet and insert a doughnut chart. Remove the chart title and move the legend to the right. Select the doughnut and change the border to No Line and then change the “Other” series to light grey. Select the whole chart and change fill to No Fill, border to No Line, and font to white. Copy the chart and paste it into the Dash sheet, in the top right corner and resize to fit. Use the drop-downs to set the month to July and year to 2014, which of these is correct?

Q13. In the Dash sheet, examine the horizontal gauges under the %Market Share figures. You will find they are in fact small charts. What type of chart are they?

• 100% Clustered Column Chart with 0% spacing
• Stacked Line Chart
• Clustered Column Chart with 0% spacing
• Stacked Bar Chart

Q14. The second horizontal gauge chart looks a bit shorter than the others but is in fact the same length. A possible reason is that the Horizontal axis bounds are incorrect. Check the horizontal axis bounds (you will need to show the horizontal axis) and take note of what they are, then change them to 0 and 1 and hide the axis again. What was the maximum bound before you corrected it?

• 1.95
• 1.7
• 1.8
• 1.75

Q15. Go to the Calcs sheet, select the data A9:M10 and insert an area chart to show the market share trends for Apple for 2014. Paste the chart into the Dash sheet and size to fit nicely under the header information. Change the year to 2015. Which of these looks like the chart for 2015?

Q16. On the Dash sheet, we want to be able to change the chart to show trends for different Vendors, by clicking on the appropriate button. The buttons have been created using shapes, but they are not looking very neat. Using Excel’s shape formatting tools line all the buttons up with the Apple button (in its current position) and space them evenly. Which of the following set of steps accomplish this without manually dragging any of them?

• Align Left, Distribute Vertically
• Align Right, Distribute Vertically
• Align Centre, Distribute Horizontally
• Align Left, Distribute Horizontally

Q17. To make the buttons work we are going to use simple recorded macros. Macros can be stored in a standard Excel Workbook.

• TRUE
• FALSE

Q18. In the Dash sheet, the cell B11 currently contains the value Apple. Record a macro called Samsung of you changing the value in B11 to Samsung and then pressing Enter. Stop recording. Go to the macro editor. Create a copy of the macro and change the name of the copy to Apple. Delete the comments and amend the code so when it runs the value in B11 changes to Apple. Which of the following code snippets changes the value in B11 to Apple?

Q19. Assign your two macros to their respective buttons and test. Which of the following describes a correct procedure for assigning a macro to a button?

• Right-click on the shape, select Format Shape, select Assign Macro
• Right-click on the shape, select Assign Macro
• Click on the shape, in the Developer ribbon click on the Macros button, select Assign Macro
• Click on the shape, in the Shape Format Tools ribbon select Assign Macro

Q20. We could allow our users to select a Vendor by creating a single dropdown list using data validation, rather than having to record and manage 8 macros.

• TRUE
• FALSE

Week 3: Data Visualization in Excel

Quiz 1: Hierarchy charts

Q1. In Excel, what is hierarchical data?

• Data that can be divided into sub-categories.
• Organisational data about managers and team members.
• Time series data that shows changes over time

Q2. Before creating a hierarchy chart, it is essential that the data is arranged correctly. The last column should contain

• The smallest sub-category.
• The totals for all sub-categories.
• The actual numeric values for each of the smallest sub-categories.
• The largest sub-category.

Q3. Before you create a hierarchy chart, you need to organise your data by size.

• True.
• False.

Q4. If we re-created the chart below but did not include the main category (in the inner-most ring) and highlighted the main-sub-category as our first category instead – each country would be coloured differently.

• True.
• False.

Q5. What type of chart is the following?

• A Hierarchy chart.
• A Rectangle chart.
• A Treemap chart.
• An Area chart.

Q6. The key difference between the Change Colours and Themes functionalities in Excel is that Themes includes suggestions for fonts as well.

• True
• False

Quiz 2: Waterfall and funnel charts

Q1. A waterfall chart is ideal for showing the cumulative impact of a series of positive and negative components.

• True.
• False.

Q2. If a waterfall chart is composed of data from different categories, the order of the categories will impact on the shape of the waterfall in the chart.

• True.
• False.

Q3. What is the following chart type?

• A Waterfall chart.
• A Hierarchy chart.
• A Funnel chart.
• A Treemap chart.

Q4. How can we add a picture that is incorporated in the chart, the way it has been below?

• Right-click on the chart and choose Format Plot Area and then go to Picture Fill.
• Click on the chart and go to Insert Picture.
• Go to Format Data Series and then go to Picture Fill.

Quiz 3: Geospatial charts

Q1. When creating a Map chart, the countries (or states or regions) need to be in alphabetical order.

• True
• False

Q2. Why are some parts of the world shaded in grey in the Map chart below?

• These are parts of the world that are not UN-member states.
• The data is zero for these parts of the world.
• We have not provided Excel with data for these parts of the world.

Q3. What is the most likely explanation for the Map chart appearing the way below?

• The user has gone to Map Features and chosen Display zeros as white.
• The user has gone to Map Area and chosen Only regions with data.
• The user has an old version of Excel that can only display 150 countries at a time.

Q4. What is the most likely explanation for the Map chart appearing the way below?

• The labels are displayed for the countries with the largest values.
• The user has gone to Map Labels and chosen Best fit only.
• The user has an old version of Excel that can only display the names of a few countries.

Q5. To make a map interactive, what could you do?

• Hover over each part of the map, and the map will automatically interact with you.
• Convert the data that makes the map into a Table, and then insert a Slicer.
• Click on the map, go to Map Options and choose Animate Map.

Quiz 4: Custom chart techniques – population charts

Q1. To create a Population chart (population pyramid chart in demography) in Excel we need to begin with a:

• Pyramid chart.
• Bar chart.
• Column chart.
• Line chart.

Q2. How can we adjust the chart below so the female bars are lined up with the male bars in the chart?

• Go to Format Data Series and then set Series Overlap to 0%
• Go to Format Data Series and then set Gap Width to 0%
• Go to Format Data Series and then set Series Overlap to 100%
• Go to Format Data Series and then set Gap Width to 100%

Q3. How can we adjust the vertical axis in the chart below to appear on the extreme left of the chart?

• Go to Axis Options and then set Label Position to None.
• Go to Axis Options and then set Label Position to Low.
• Go to Axis Options and then set Label Position to High.
• Go to Axis Options and then set Label Position to Next to Axis.

Q4. When coding a macro that has to repeat a procedure, what are the Visual Basic code words?

• If… Then
• For… Next
• Repeat… Until
• Go… To

Q5. You can right-click a shape and select Assign Macro. This will then run the macro when the shape is clicked.

• True.
• False.

Quiz 5: Creative chart techniques – gauge charts

Q1. To begin the creation of a Gauge chart, we need to begin with a:

• Pie chart.
• Doughnut chart.
• Sunburst chart.

Q2. The pointer of a Gauge chart is essentially a very thin segment of a:

• Doughnut chart.
• Sunburst chart.
• Pie chart.

Q3. What type of fill has been used in the segments of the chart below?

• Gradient Fill.
• Picture Fill.
• Texture Fill.

Q4. Consider the Gauge chart below. What would happen to this Combo chart if both chart elements were converted to a Doughnut?

• The pointer would extend outside the current outer ring.
• The pointer would change colour to match the outer ring.
• The pointer would only be visible in the outer ring, and not appear towards the centre.
• The pointer would disappear.

Week 4: Data Visualization in Excel

Quiz 1: Summarise data with PivotTables

Q1. Before you create a PivotTable, you need to ensure that you have nice clean data with:

(more than one may apply)

• No blank rows
• No blank cells
• No blank columns

Q2. Converting your data into a Table before creating a PivotTable is a good idea because:

(more than one may apply)

• It is easier to refer to the data when creating the PivotTable.
• It is not possible to create a PivotTable without the data being in a Table.
• It is more efficient to update the PivotTable.

Q3. When in PivotTable Fields, if you tick a field that contains numeric data, it will automatically populate which quadrant?

• Columns
• Filters
• Values
• Rows

Q4. When in PivotTable Fields, if you tick a field that contains non-numeric data, it will automatically populate which quadrant?

• Values
• Columns
• Filters
• Rows

Q5. Which of the following Excel screenshots would be most likely if we had the PivotTable Fields list shown below?

Quiz 2 : Customise PivotTables

Q1. To change the calculation within your PivotTable, you get more options by right-clicking one of the values, compared to customising the calculation in the Values quadrant.

• True
• False

Q2. Rather than seeing discrete values, if you want to see percentages instead, you should go to:

• Summarize Values By
• Value Field Settings
• Show Values As

Q3. Choosing the option Rank Smallest to Largest will result in the PivotTable being sorted and displayed from smallest to largest values.

• True
• False

Q4. When does Excel automatically give you sub-totals for your PivotTable?

• You have more than one field in your Filters.
• You have more than one field in your Values.
• You have more than one field in your Columns.
• You have more than one field in your Rows.

Q5. You can choose to display a sub-total as a sum and as an average, and both these items will appear separately at the bottom of the respective section in your PivotTable.

• True.
• False.

Quiz 3: Group and sort pivot data

Q1. If you have full dates and you create a PivotTable, Excel will group these dates, such as grouping by month. You can remove this by choosing Ungroup.

• True.
• False.

Q2. Which of the following buttons would we need to click to group the dates below?

• Ungroup
• +
• –
• Group Field
• Group Selection

Q3. Which of the following buttons would we need to click to group a few rows from our PivotTable?

• Group Field
• –
• Ungroup
• Group Selection
• +

Q4. What would moving the Europe cell to elsewhere in the PivotTable do?

• It would move Europe but not the associated summary data.
• It would move Europe and the associated summary data.
• It would not work — you would have to select the entire row.

Q5. Which of these are correct statements about custom lists in Excel?

(more than one may apply)

• You can create your own custom lists
• Some come standard with Excel
• You can only create custom lists for date-related data
• When sorting Excel favours custom lists over any other data

Quiz 4: Filter pivot data

Q1. One way to filter which columns and which rows appear in your PivotTable is to use the drop-down menu that appears next to Row Labels and Column Labels.

• True
• False

Q2. To clear a filter in a PivotTable, you could:

(more than one may apply)

• Go to the Data tab and select Clear.
• Go back into the Row Label or Column Label dropdown menus and choose Clear Filter.
• Go to the PivotTable Analyze tab and select Clear Filters.
• Go to the Home tab and select Clear Filter.

Q3. The main difference between Label Filters and Value Filters is that Label Filters are for filtering based on text.

• True
• False

Q4. If you want to filter by a field that you do not necessarily want to show in the PivotTable itself, you should move this field to which quadrant?

• Rows.
• Filters.
• None of these.
• Columns.

Q5. If you choose Show Report Filter Pages after having selected multiple items in a filter, what would happen?

• You would get separate PivotTables which are presented side-by-side each filter item on one sheet of your Excel file.
• You would get separate PivotTables for each filter item on separate sheets in your Excel file.
• You would get one PivotTable that aggregates the multiple filters by Sum on one sheet of your Excel file.
• You would get separate PivotTables which are presented underneath each filter item on one sheet of your Excel file.

Quiz 5: Perform calculations in pivots

Q1. A PivotTable currently occupies some of Columns A and B as per the screenshot. The formula that is displayed is typed into cell D4. What would happen to the result of the formula if the filters of the PivotTable were modified?

• The result would be re-calculated if the filters in cell A3 changed.
• The original answer would remain unchanged even if the filters in cell A3 changed.
• The contents of cell D4 would be automatically moved to another cell, depending on the output of the new PivotTable.

Q2. For the PivotTable below, what would happen to the contents of cell D4 if we added a field to the Columns quadrant that resulted in the table expanding by two or more columns?

• The data in D4 would automatically be overwritten.
• The data in D4 will automatically shift to the right of the new PivotTable.
• Excel will prompt you about whether you want to overwrite the data in D4.

Q3. Calculated fields will add new columns to your PivotTable, whereas Calculated items will add new rows to your PivotTable.

• True
• False

Q4. When inserting a Calculated Field, you cannot use the VLOOKUP function because:

• The VLOOKUP function has more than one argument.
• Calculated Fields do not support Excel functions.
• This function will refer to data that is outside the PivotTable.

Q5. You cannot insert a Calculated Item for grouped fields.

• True.
• False.

Quiz 6: Data Visualization in Excel

Q1. Open the attached workbook. Have a look at the HR Data sheet which contains a list of all staff who have worked at the company since 1990. You are required to take over creating a report to show staffing levels and salary metrics. Go to the Calcs sheet. Some data has already been prepared. Look at the data in A3:C14, it shows the number of staff in each role within each department. Which of the following Excel chart types are suitable for showing this data visually? (More than one may apply.)

C2 Week 3-4 Assessment
XLSX File
Download file

Waterfall Chart

• Pie Chart
• Sunburst Chart
• Treemap Chart

Q2. Have a look at the chart that looks like a Waterfall Chart but was created in a version of Excel that does not support waterfall charts. What type of Excel chart has been used to create the Waterfall chart?

• Stacked Column
• Stacked Bar
• Clustered Bar
• Clustered Column

Q3. To clearly see the increase and decrease in staff levels, the vertical axis on the waterfall chart has been changed to start from a value other than 0. What values does it start from?

• 1000
• 1020
• 1010
• 1015

Q4. The data labels in the Waterfall Chart are a bit of a mess. Change the number format for the Decrease series data labels so that 0s do not show, and the 2 shows as a -2. Which number format achieves this?

Q5. Look at the chart titled Staff Numbers 2010-2020 under the Waterfall chart. This chart shows the number of male vs female employees from 2010 to 2020. It looks a bit like a funnel chart, but isn’t, what type of Excel chart is it?

• Stacked Column
• Stacked Bar
• Clustered Column
• Clustered Bar

Q6. Change the Staff Numbers 2010-2020 chart so that the bars for male and female line up for each year. Which of the following settings achieves this?

• Change Series Overlap to 0%
• Change Series Overlap to 100%
• Change Gap Width to 100%
• Change Gap Width to 0%

Q7. Cut and paste the Staff Numbers 2010-2020 chart into B3 in the HR Report sheet. Change the chart so that the vertical axis labels display on the left side of the chart. Which setting achieves this?

• In axis options, change the alignment to Left and Centered
• In axis options change Axis Position to be On Tick Marks
• In axis options change label position to Low
• In axis options change label position to High

Q8. Go back to the Calcs sheet. We now need to correct the last chart that has been started, have a look at the Gauge chart. It has been created using two different chart types on the same set of axes. Which chart types is it using?

• Pie and Doughnut
• Pie and Sunburst
• Doughnut and Sunburst
• None of the Above

Q9. The gauge chart is intended to be interactive. Change the value in L36 to 2019. The pointer has gone off the gauge. How can we fix this?

• Click on the pie chart and rotate 270˚ to the right.
• Select the pointer series and change the angle of the first slice to 270˚.
• Select the pointer series and change the angle of the first slice to 90˚.
• Click on the pie chart and rotate 90˚ to the left.

Q10. The pointer still looks odd. Which of the following will make the pointer narrower, so it looks more like a pointer?

• Click on the black pointer segment and change the fill to No Fill.
• Change the value in cell M39 to 10.
• Click on the black pointer segment and change the fill to No Fill and the line to Solid Linecolourwhite.
• Change the value in cell M40 to 50.

Q11. The business requirement has changed and rather than the gauge chart they would like to see a line chart showing how many staff joined the company each year. First, you will need to prepare the data and the easiest way to do that is with a PivotTable. Go to the HR Data sheet, click in the table and insert a PivotTable. Rename the PivotTable pvtNewStart, add StartYear to the Rows section and JobRole to the Value section. How many new people started in 2015?

• 59
• 25
• 86
• 153

Q12. Add a Filter to the Row Labels to only show data for 2000 to 2019. What was the total number of people who joined during this time?

• 1371
• 1239
• 1223
• 1243

Q13. Add Gender to the Columns section. How many Males started in 2008?

• 36
• 67
• 17
• 21

Q14. Click in the PivotTable and insert a suitable PivotChart, add it to the Report. This is one example of a PivotChart visualising the PivotTable we just created:

• Which of the following accurately describe some of the steps taken to create this chart?
• Insert a Stacked Area Chart, add title, move the legend to bottom, turn off Legend Field Buttons and Value Field Buttons.
• Insert an Area Chart, add title, move the legend to bottom, turn off all Field Buttons.
• Insert a Stacked Area Chart, add title, remove gridlines, turn off Legend Field Buttons and Axis Field Buttons.
• Insert a 100% Stacked Area Chart, add title, remove gridlines, turn off Value Field Buttons and Axis Field Buttons.

Q15. In the HR Data sheet, there is a column called Attrition, this refers to people leaving the organisation, if it says Yes it means that employee has left, if it says No they are still employed. Create another PivotTable called pvtLeavers that shows how many people have left and how many people are still employed by the Department. How many people have left who were previously in the R&D department?

• 129
• 87
• 11
• 899

Q16. Change the Value Field Settings in pvtLeavers so that it shows what percentage of people in each department that have left and have not. Change the number format (should be a parentage) to have one decimal place. What percentage of Sales staff have left?

• 20.9%
• 14.3%
• 16.6%
• 19.6%

Q17. We would like to show graphically the percentage of staff across all departments who have been retained (not left). Remove Department from pvtLeavers and move Attrition to Rows. Change the Show Values As calculation to show as Percentage of Grand Total. Insert a Doughnut Pivot Chart, change the title to Staff Retention and turn the Legend and Field Buttons off. Add a Data Label to the segment representing people who have not left and make it large and positioned in the centre. Which of the following most closely resembles the chart we want?

Q18. Calculated Items cannot be added to PivotTables that contain Grouped fields.

• TRUE
• FALSE

Q19. The last pivot table we will create will calculate the pension contributions the company makes for each department. The company contributes 9% of each staff members monthly income to their pension. Create a PivotTable called pvtPension that has Department in the Rows section and MonthlyIncome in the Values section. Add a calculated field called Pension that multiplies MonthlyIncome by 9%. Remove MonthlyIncome from the Pivot. What was the total pension paid to the Sales Department?

• $239,793.84
• $461,066.76
• $191,988.27
• $269,078.49

Q20. Finally, we would like to chart the difference in pension paid to Males and Females. Add Gender to rows and move Department to Columns. Insert a Stacked Column PivotChart. Which of these represents the PivotChart described above?

Get All Course Quiz Answers of Excel Skills for Data Analytics and Visualization Specialization

Excel Fundamentals for Data Analysis Coursera Quiz Answers

Data Visualization in Excel Coursera Quiz Answers

Excel Power Tools for Data Analysis Coursera Quiz Answers