Lab - Create Visualizations in Excel Objectives In this lab, you will create charts to visualize data in Microsoft Excel. Part 4: Creating a Line Chart Part 2: Creating a Column Chart Part 3: Creating a Pie Chart Background / Scenario Data visualization assists with the analysis and interpretation of data by graphically presenting relationships, trends, and inferences that cannot always be clearly derived by examining the raw text and numeric values in a dataset. This lab uses sample datasets to demonstrate three visualizations of that data. Required Resources * Mobile device or PC/laptop with a browser, Microsoft 365 Excel online, and intemet access Note: The precise steps to format and manipulate data in Excel can vary between platforms and versions. The instructions in this lab are based on the free version of Excel available from Office.com and may have to be modified to match the user's platform, software, or version to achieve the results shown in this lab. Part 1: Creating a Line Chart This chart will display the Profit and Revenue for the years 2017, 2018, 2019, 2020 and 2021 Step 1: Download the data file a. Download the sample file Bike Sales_Visuali downloaded file in MS 365 Excel. ions_Lab.xIsx to your OneDrive. Open the This workbook contains four worksheets that each will be used separately throughout this lab. Step 2: Insert the line chart. a, Select the Revenue and Profit by Year worksheet. The worksheet contains the profit and the revenue totals for each of the years 2017, 2018, 2019, 2020, and 2021 b. Select the data in the cells A3 through to C8. c. From the Insert menu, expand the ribbon using the down arrow on the right side of the ribbon, click the Line chart tool, then select Line with Markers (bottom left option) This creates a line chart with an x-axis showing the years, and a y-axis dollar amounts. Step 3: Format the chart.a. To improve the clarity of the chart, change the vertical axis to display USD currency. 1. Right click on the chart and select Format. The Chart Format window pane opens on the right of the worksheet. 2. Expand the options for the Vertical Axis. 3. In the Number Format section change Gategory to Currency and change Decimal places to 0. b. Add a chart title 1. In the Chart Format window pane, change the Chart Title option switch to the on Position if itis not already and expand the Chart Title options. 2. Change the Chart Title to "Revenue vs. Profits". 3. Keep the Title Position at the default which is Above. c. Change the Legend names to "Annual Profit" and “Annual Revenue". 1. Select cell B3 and change the column name to Annual Profit. 2. Select cell C3 and change the column name to Annual Revenue. The legend names at the bottom of the chart should change to match the column names. d. Reposition the Legend to the right of the chart. 1. Right click on the chart to bring up the Chart Format window pane. 2. Expand the options for Legend 3. Change the Position option to Right. e. Add axis titles for both the vertical and horizontal axis... If necessary, right click on the chart to bring up the Chart Format window pane. Expand the Horizontal Axis options. Scroll down to the Axis Title and move the switch to the on position . Add an axis title of "Year™. . Expand the options for the Vertical Axis. . Scroll down to the Axis Title and move the switch to the on position . Add an axis title of "US Dollars”. NOgawNA The finished chart should appear as shown below. Part 2: Creating a Column Chart Step 1: Insert the Column Chart a. Select the Product Revenue by Country worksheet. The worksheet contains the revenue totals for each product category by country. b, Select the data in the cells A3 through to E10. c, From the Insert menu, click the Column chart tool, then select the Stacked Golumn (middle option) This creates a column chart with an x-axis showing the country, and a y-axis showing dollar amounts,Step 2: Format the chart. a. Using the same methods used for the line chart in Part 1 perform the following formatting changes to the chart. - Give the chart a title of "Product Revenue by Country’. . Change the vertical axis Number Format to Currency and the Decimal Places to zero . Change the Position of the Legend to the Right. . Add a horizontal Axis Title of "Country". . Add a vertical Axis Title of "US dollars" APeNe Once completed the chart should appear as shown below. Part 3: Creating a Pie Chart Step 1: Insert the Pie Chart a, Select the Revenue by Age Group worksheet. The worksheet contains the revenue totals for each product category. b, Select the data in the cells A3 through to B7. c, From the Insert menu, click the Pie chart tool, then select the 2D- Pie (top option) This creates a pie chart with each age group represented by an area on the cart representative of the revenue for that group. Step 2: Format the chart. a. Using the same methods used previously for the line and column carts make the following format changes: 1. Give the chart a title of "Revenue Comparison by Age Group". 2. Change the Position of the Legend to the Right. b. Add data labels to the chart area. 1. In the Chart Format window expand the options for Series Total: 2. Expand the options for Data Labels. 3. Check the boxes for Category Name and Percentage. Once completed the chart should appear as shown below.Reflection Questions Review each of the datasets given in this lab and select different options for each chart. Consider if the visualization of the data is enhanced, or not, with these different options. Answer Area Type your answers here. Challenge Activity Explore the possibilities of using other datasets to produce different visualization charts. A© 2017 - 2023 Cisco and/or its affiliates. All rights reserved. Cisco Public