Skip to content We use cookies to ensure that we give you the best experience on our website. read privacy policy... In an age now driven by “big data”, we need to cut through the noise and present key information in a way that can be quickly consumed and acted upon making data visualization an increasingly important skill. Visualizations need to not only present data in an easy to understand and attractive way, but they must also provide context for the data, tell a story, achieving that fine balance between form and function. Excel has many rivals in this space, but it is still an excellent choice, particularly if it’s where your data resides. It offers a wealth of tools for creating visualizations other than charts and the chart options available are constantly increasing and improving, so the newer versions now include waterfall charts, sunburst diagrams and even map charts. But what sets Excel apart is its flexibility, it gives us total creative control over our designs so if needed we could produce our own animated custom chart to tell the right story for our data. Enroll on Coursera 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. Q2. What does trend-based formatting do? Q3. When applying Icon Sets the original values still appear in the cell. Q4. Which of the following methods allow us to remove conditional formatting (more than one may apply)? Q5. What type of conditional formatting has been applied to the data below?. Q1. Which would be the appropriate Conditional Formatting set if you wanted to highlight all values over 300? Q2. Conditional formatting can be used to highlight duplicate values. 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? Q4. Conditional formatting by using a formula is advantageous over other conditional formatting because: Q5. In the following scenario, which conditional formatting rules may have been used (more than one may apply)? Q1. Excel allows us to record macros to automate difficult or repetitive procedures. Which of the following is true of recording macros? Q2. Which of the following are mixed cell references in Excel? 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? 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? Q5. Which of the following file types support macros (more than one may apply)? Q1. What is the key difference between a sparkline and a chart in Excel? Q2. Sparklines are a useful data visualisation tool for which of the following cases (more than one may apply): Q3. After creating a sparkline we can drag the fill handle to create multiple sparklines. 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. 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? Q1. Custom number formats can be accessed in which of the following ways (multiple answers may apply): Q2. When customising a number format: Q3. When customising a number format, which character acts as the separator for specifying different formats for different types of values? Q4. What type of brackets should we use when specifying colours for our custom number formats? Q5. If a cell contains the value 0, what will display when we apply the following custom number format: [Red]▲;[Green]▼;
Week 2: Data Visualization in ExcelQuiz 1: Charting techniquesQ1. A chart can only be drawn if we select the labels as well as the data. Q2. What is the difference between a column chart and a bar chart?
Q3. When Excel refers to a clustered column chart, what is the cluster referring to?
Q4. There is a problem with the chart that has been generated below: What is the problem?
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?
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?
Quiz 2: Pies and doughnutsQ1. A pie chart is useful when we want to show:
Q2. For a pie chart to be an effective visualisation, the number of categories should be: Q3. The key advantage that a doughnut chart has over a pie chart is that:
Quiz 3: Line charts and trendlinesQ1. How can we modify the line chart below to adjust the vertical axis to better display the range of the data?
Q2. What does the R-squared value represent?
Q3. What will choosing a Polynomial trendline likely do to the R-squared value below?
Q4. How can we forecast forward into the future using the chart below?
Q5. If we choose Display Equation on Chart, why is this equation useful?
Quiz 4: Area chartsQ1. Both area charts and line charts are mainly useful for time series data. 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?
Q3. Which Excel functionality would allow you to quickly recolour your chart according to a set of preset options?
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. 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 Quiz 5: Scatter and bubble chartsQ1. A scatter chart is essentially an x-variable versus y-variable plot just like in standard mathematics. 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. Q3. What is the likely cause of the equal-sized bubbles in this 2-D bubble chart?
Q4. Outliers in scatter or bubble charts should always be removed and no notes need to be made to supplement your chart. 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. Quiz 6: Data Visualization in ExcelQ1. 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 AssessmentXLSX File Download file
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).
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?
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?
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? 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? 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?
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? 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?
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?
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? 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?
Q17. To make the buttons work we are going to use simple recorded macros. Macros can be stored in a standard Excel Workbook. 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?
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. Week 3: Data Visualization in ExcelQuiz 1: Hierarchy chartsQ1. In Excel, what is hierarchical data?
Q2. Before creating a hierarchy chart, it is essential that the data is arranged correctly. The last column should contain
Q3. Before you create a hierarchy chart, you need to organise your data by size. 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. Q5. What type of chart is the following?
Q6. The key difference between the Change Colours and Themes functionalities in Excel is that Themes includes suggestions for fonts as well. Quiz 2: Waterfall and funnel chartsQ1. A waterfall chart is ideal for showing the cumulative impact of a series of positive and negative components. 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. Q3. What is the following chart type?
Q4. How can we add a picture that is incorporated in the chart, the way it has been below?
Quiz 3: Geospatial chartsQ1. When creating a Map chart, the countries (or states or regions) need to be in alphabetical order. Q2. Why are some parts of the world shaded in grey in the Map chart below?
Q3. What is the most likely explanation for the Map chart appearing the way below?
Q4. What is the most likely explanation for the Map chart appearing the way below?
Q5. To make a map interactive, what could you do?
Quiz 4: Custom chart techniques – population chartsQ1. To create a Population chart (population pyramid chart in demography) in Excel we need to begin with a:
Q2. How can we adjust the chart below so the female bars are lined up with the male bars in the chart?
Q3. How can we adjust the vertical axis in the chart below to appear on the extreme left of the chart?
Q4. When coding a macro that has to repeat a procedure, what are the Visual Basic code words?
Q5. You can right-click a shape and select Assign Macro. This will then run the macro when the shape is clicked. Quiz 5: Creative chart techniques – gauge chartsQ1. To begin the creation of a Gauge chart, we need to begin with a:
Q2. The pointer of a Gauge chart is essentially a very thin segment of a:
Q3. What type of fill has been used in the segments of the chart below?
Q4. Consider the Gauge chart below. What would happen to this Combo chart if both chart elements were converted to a Doughnut?
Week 4: Data Visualization in ExcelQuiz 1: Summarise data with PivotTablesQ1. Before you create a PivotTable, you need to ensure that you have nice clean data with: (more than one may apply)
Q2. Converting your data into a Table before creating a PivotTable is a good idea because: (more than one may apply)
Q3. When in PivotTable Fields, if you tick a field that contains numeric data, it will automatically populate which quadrant? Q4. When in PivotTable Fields, if you tick a field that contains non-numeric data, it will automatically populate which quadrant? Q5. Which of the following Excel screenshots would be most likely if we had the PivotTable Fields list shown below? 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. Q2. Rather than seeing discrete values, if you want to see percentages instead, you should go to:
Q3. Choosing the option Rank Smallest to Largest will result in the PivotTable being sorted and displayed from smallest to largest values. Q4. When does Excel automatically give you sub-totals for your PivotTable?
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. Quiz 3: Group and sort pivot dataQ1. 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. Q2. Which of the following buttons would we need to click to group the dates below?
Q3. Which of the following buttons would we need to click to group a few rows from our PivotTable?
Q4. What would moving the Europe cell to elsewhere in the PivotTable do?
Q5. Which of these are correct statements about custom lists in Excel? (more than one may apply)
Quiz 4: Filter pivot dataQ1. 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. Q2. To clear a filter in a PivotTable, you could: (more than one may apply)
Q3. The main difference between Label Filters and Value Filters is that Label Filters are for filtering based on text. 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?
Q5. If you choose Show Report Filter Pages after having selected multiple items in a filter, what would happen?
Quiz 5: Perform calculations in pivotsQ1. 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?
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?
Q3. Calculated fields will add new columns to your PivotTable, whereas Calculated items will add new rows to your PivotTable. Q4. When inserting a Calculated Field, you cannot use the VLOOKUP function because:
Q5. You cannot insert a Calculated Item for grouped fields. Quiz 6: Data Visualization in ExcelQ1. 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 AssessmentXLSX File Download file Waterfall 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?
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? 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?
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?
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?
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?
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?
Q10. The pointer still looks odd. Which of the following will make the pointer narrower, so it looks more like a pointer?
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? 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? Q13. Add Gender to the Columns section. How many Males started in 2008? 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:
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? 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? 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. 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?
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? In our experience, we suggest you enroll in Data Visualization in Excel courses and gain some new skills from Professionals completely free and we assure you will be worth it. Data Visualization in 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 Data Visualization in Excel Coursera Quiz Answers. I hope this Data Visualization in 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 Excel Skills for Data Analytics and Visualization SpecializationExcel Fundamentals for Data Analysis Coursera Quiz Answers Data Visualization in Excel Coursera Quiz Answers Excel Power Tools for Data Analysis Coursera Quiz Answers error: Content is protected !! |