can someone help me with this
please let me know if you need more info
Required information [The following information applies to the questions displayed below.) This Applying Excel worksheet includes an explanation of the Pivot Table and other Data Visualization charts. Download the Applying Excel form below. Follow the tutorial on the first tab using Excel's Pivot Table function and Charts. After following the tutorial to create a Pivot Table, your final table should match the table shown below. a Sum of Total Order Row Labels Agendas IA $110.00 IL $470.00 IN KY $275.00 OH TN WI Grand Total $855.00 Column Labels Folders Notebooks Grand Total $ 26.00 $ 33.75 $ 169.75 $ 30.00 $ 22.50 $ 522.50 $ 7.60 $ 63.75 $ 71.35 $ 9.00 $ 18.75 $ 302.75 $ 55.50 $ 55.50 $ 13.00 $ 13.00 $ 38.00 $ 56.25 $ 94.25 $123.60 $250.50 $1,229.10 Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Product Notebooks Folders Agendas Price per Unit $1.00 $0.10 $4.50 After refreshing the Pivot Table data, the total order in KY of folders purchased should be $4.50 and the Grand Total for all states and products should be $1,165,30 If you did not get these answers, try to refresh the data again for the Pivot Table and follow the tutorial steps again. Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse". Next click "Save". You will use this worksheet to answer questions in Part 2. Product Price per una 0.75 ..20 5.00 Age 9 Data Visualization Step by Steve Data Visualization can be displayed in multiple ways in Excel, but it all requires one consistent item-dean data Clean data simply means that all the information included in your spreadsheet is formated consistentlyFor example If customers are split into First and last name cells but some rows include both first and last names in the same the data is not con Clean data can be used to gather and display information for management to obtain insights about the company Note: For the purposes of this tutorial and the exorcise folowing con data will be provided One of the first ways the data can be used into useable information is by putting it into a Pivot Table in Excel The data provided to a small portion of the customer sales for three products and their Price per Unt Notebooks cost $0.75, Folders cout 50 20, and Agendas cost $5.00. The products and price per unit are provided in G3H6. The was data for a month has been provided starting in Use the following steps to create a Pivottable Place your cursor anywhere in the group of Gutshat ranges from GSM Then click on the Insert tab, then click the Plved Table button in the Tables group To Create Pabout box wil.coe. The talorare should be adolected and say Data Visualization Tutorials M838. It does not, then to the data range ndicated In the second half of the popup choose where to place to Portable me a New Worksheet The OK. A new tab weled Right click on the tab name and choose Renate Change the name of the th to be able Town Now dick on the Bank Table on the new the Protatie window that played on the side of your screen. The Pivottabile function automatically recognize the column header rows to label the data property and whep to organize the data in the Phvorable. These flats will help you create a custom table with Information that is summerved. You can drag and drop the field names into the wale wreas of the Pivotale the bottom of the window The Areas we Founow..and Values The Free is a manter for the entire Pivoatie and is best used for filering content to only show a particulat year, for example. The "Columns' area in the columns that show across the top of the Pivoable. Similarly, the "Rows" we defines the rows that showdown first column of the Pivottable. The values area is the data being summarized, which is generally a sum or count of data. Arons can contain more than one datafield. To continue the tutoriallers find the total sales for each State of Residence and Type of Product Ordered -Drag and drop the State of Residerosold to the "Rows are - Drag and drop Product Ordered field to the Columns area Fray, drag and drop the Total Order field to the values are Customer Law Name Car First one we of Residence Prendered More IN Notebook Murg Aga Long Tory IN Sah For As A ru w Nebook IA David w ER IA Foder Cores Agenda Dem Dan Ordered Price per U TO 25 1 0.5 1.75 103 0205 200 213 6.00 105.00 30 0.75 22 50 6201 10.00 16 500 75.00 401 . 3000 460 O.TO. 20 020 14.00 10. 2. 100 25 100 25.00 NO OP 23 400 0208 800 00 30 0.5 22.50 0203 000 22 1 8.00 18.00 25 0.75 651 20. 3.00 255 5.00 125.00 215 9.755 15.75 IT 5 755 2025 YDS 020 14.00 455 0205 3.00 30$ 5.00 3 150.00 365 0.755 1850 Mine wy A IN Agenda be Po Quine FUM SW mw 5 F Page OH No Buah Un Veronica Wyatt Zoey Dolores Shane KY KY OH Foldiers Aga Depo Evane Ab Lor Note that any field that is put into the values are wil display as the name of the table along with the words "Sum of This means the Pivot Table Fundion is summing up the values induded in the field name. If you want to change the way the data is sured, right-click on any value fold coll in the Prvofable and select Value Fold Solting Then select the summary type (Sun, Count Average, Max. Mino) For now, keep Sum as the summary type for Pet LG Robert Watson IL w Agenda Notebook oces 38 3 155 35 705 0201 500S 0.75 1 0.203 TA 75.00 2025 14.00 De LA Next, light.dio on any value cell and choose Number Format. Select Currency as the format, displaying 2 decimal places and click OK Your Pivot Table should now look to this Semua Order Column Label Pere Nee Great Tomat 1110.00 $28.00 39078078 141000 10:00 1220 122 IN 37 3898 57135 KY M. $0 $10.78 02.18 GH 35850 385.00 TH 13.00 13.00 13800 35337425 Grand # you ever need to change your original data very eaty to quickly update the Pivot Tabi Alor updating your original data, dok back to your Pivottable. Root-click on anyol in the Pivot Table, then choose Refresh to update the data in the Pivolatie to match the updated originaldate Petables are a great way to summarize data, but they can become even more power when you create visual charts and graphs from Portable Amost any type of charter graph can be made from here, and the moment you change your Plotatie. Same grach or chart will change the a good feature when you are trying to create the best possible visual for you. However. It's not good when you want to create i oferit de wonine Polo One way to work around this is to copy and paste the Pivolatie data to mother before creating the chart or graph Add another tab to your wo book andmet"Charts TutorCopy the won the Portable Tutorial and bestrom the Pivoatie) and Paste it on the Charts at should look like the table above, exceptow the pasted data is no longer connected to the PivoTable since it's on a new Click on any value cell in the Pivot Table on the Charts Tutorial tab. Then click on the Insert tab, and in the Charts group click on Insert Column or Bar Chart. From the dropdown list, select the first item in the 2-D Column section, called Clustered Column. Note that Excel will auto-select data from the table, but it needs some adjustments. Click on the Chart, then click on the Chart Design menu tab. Then click on the Select Data button in the Data group. Change the "chart data range" to be ='Charts Tutorial'!$A$2:$D$9. Click OK. The reason the chart range was changed is to only include the States and Products the sum of total orders. The Grand Total and labels in the first row are not necessary to be included. Any chart can also be altered in its setup. Click on the chart, then click on the Design tab in the special PivotChart Tools tab group. (Note that these tabs are only visible after you click on a chart made from data in a Pivottable. In the Data group, click the Switch Row/Column button to change the layout of the chart. It will swap the columns so they are grouped either by State or by Product Ordered. Set the table so that the States are displayed across the X- axis. To adjust the title of the chart, double click on the Chart Title to change the name. Other chart formatting such as changing font and colors can be done by right-clicking on the chart and choosing Format Chart Area or using the Format tab in the special PivotChart Tools tab group. Follow these similar steps to create other types of charts, including line graphs and pie charts. Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Product Notebooks Folders Agendas Price per Unit $1.00 $0.10 $4.50 After refreshing the Pivot Table data, the total order in KY of folders purchased should be $4.50 and the Grand Total for all states and products should be $1,165,30 If you did not get these answers, try to refresh the data again for the Pivot Table and follow the tutorial steps again. Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse". Next click "Save". You will use this worksheet to answer questions in Part 2. Required information [The following information applies to the questions displayed below.) This Applying Excel worksheet includes an explanation of the Pivot Table and other Data Visualization charts. Download the Applying Excel form below. Follow the tutorial on the first tab using Excel's Pivot Table function and Charts. After following the tutorial to create a Pivot Table, your final table should match the table shown below. a Sum of Total Order Row Labels Agendas IA $110.00 IL $470.00 IN KY $275.00 OH TN WI Grand Total $855.00 Column Labels Folders Notebooks Grand Total $ 26.00 $ 33.75 $ 169.75 $ 30.00 $ 22.50 $ 522.50 $ 7.60 $ 63.75 $ 71.35 $ 9.00 $ 18.75 $ 302.75 $ 55.50 $ 55.50 $ 13.00 $ 13.00 $ 38.00 $ 56.25 $ 94.25 $123.60 $250.50 $1,229.10 Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Click on any value cell in the Pivot Table on the Charts Tutorial tab. Then click on the Insert tab, and in the Charts group click on Insert Column or Bar Chart. From the dropdown list, select the first item in the 2-D Column section, called Clustered Column. Note that Excel will auto-select data from the table, but it needs some adjustments. Click on the Chart, then click on the Chart Design menu tab. Then click on the Select Data button in the Data group. Change the "chart data range" to be ='Charts Tutorial'!$A$2:$D$9. Click OK. The reason the chart range was changed is to only include the States and Products the sum of total orders. The Grand Total and labels in the first row are not necessary to be included. Any chart can also be altered in its setup. Click on the chart, then click on the Design tab in the special PivotChart Tools tab group. (Note that these tabs are only visible after you click on a chart made from data in a Pivottable. In the Data group, click the Switch Row/Column button to change the layout of the chart. It will swap the columns so they are grouped either by State or by Product Ordered. Set the table so that the States are displayed across the X- axis. To adjust the title of the chart, double click on the Chart Title to change the name. Other chart formatting such as changing font and colors can be done by right-clicking on the chart and choosing Format Chart Area or using the Format tab in the special PivotChart Tools tab group. Follow these similar steps to create other types of charts, including line graphs and pie charts. Product Price per una 0.75 ..20 5.00 Age 9 Data Visualization Step by Steve Data Visualization can be displayed in multiple ways in Excel, but it all requires one consistent item-dean data Clean data simply means that all the information included in your spreadsheet is formated consistentlyFor example If customers are split into First and last name cells but some rows include both first and last names in the same the data is not con Clean data can be used to gather and display information for management to obtain insights about the company Note: For the purposes of this tutorial and the exorcise folowing con data will be provided One of the first ways the data can be used into useable information is by putting it into a Pivot Table in Excel The data provided to a small portion of the customer sales for three products and their Price per Unt Notebooks cost $0.75, Folders cout 50 20, and Agendas cost $5.00. The products and price per unit are provided in G3H6. The was data for a month has been provided starting in Use the following steps to create a Pivottable Place your cursor anywhere in the group of Gutshat ranges from GSM Then click on the Insert tab, then click the Plved Table button in the Tables group To Create Pabout box wil.coe. The talorare should be adolected and say Data Visualization Tutorials M838. It does not, then to the data range ndicated In the second half of the popup choose where to place to Portable me a New Worksheet The OK. A new tab weled Right click on the tab name and choose Renate Change the name of the th to be able Town Now dick on the Bank Table on the new the Protatie window that played on the side of your screen. The Pivottabile function automatically recognize the column header rows to label the data property and whep to organize the data in the Phvorable. These flats will help you create a custom table with Information that is summerved. You can drag and drop the field names into the wale wreas of the Pivotale the bottom of the window The Areas we Founow..and Values The Free is a manter for the entire Pivoatie and is best used for filering content to only show a particulat year, for example. The "Columns' area in the columns that show across the top of the Pivoable. Similarly, the "Rows" we defines the rows that showdown first column of the Pivottable. The values area is the data being summarized, which is generally a sum or count of data. Arons can contain more than one datafield. To continue the tutoriallers find the total sales for each State of Residence and Type of Product Ordered -Drag and drop the State of Residerosold to the "Rows are - Drag and drop Product Ordered field to the Columns area Fray, drag and drop the Total Order field to the values are Customer Law Name Car First one we of Residence Prendered More IN Notebook Murg Aga Long Tory IN Sah For As A ru w Nebook IA David w ER IA Foder Cores Agenda Dem Dan Ordered Price per U TO 25 1 0.5 1.75 103 0205 200 213 6.00 105.00 30 0.75 22 50 6201 10.00 16 500 75.00 401 . 3000 460 O.TO. 20 020 14.00 10. 2. 100 25 100 25.00 NO OP 23 400 0208 800 00 30 0.5 22.50 0203 000 22 1 8.00 18.00 25 0.75 651 20. 3.00 255 5.00 125.00 215 9.755 15.75 IT 5 755 2025 YDS 020 14.00 455 0205 3.00 30$ 5.00 3 150.00 365 0.755 1850 Mine wy A IN Agenda be Po Quine FUM SW mw 5 F Page OH No Buah Un Veronica Wyatt Zoey Dolores Shane KY KY OH Foldiers Aga Depo Evane Required information 3. Which rep has the highest total sales for each region? (Enter your Total Sales to 2 decimal places.) Rep Name Total Sales Region East Midwest South West 4. What is the total annual sales for each region? (Enter your Total Sales to 2 decimal places.) Total Sales Region East Midwest South West Office Warehouse Inc. currently makes four products. The Sales Director has asked you to run a preliminary analysis on last year's sales data, which has been cleaned and is given on the Original Data tab. The products made by the company and sales price per unit are listed below. Use the different data visualization methods recommended in each requirement to analyze the company's sales data. Products Available Price per Unit Paper $ 21.00 Iper case Pens $ 8.00 per box Sticky Notes $ 10.00 per package Envelopes $ 4.00 per box Required Create a PivotTable to answer each question below while analyzing the data. Which product had the highest total units ordered and highest total sales? Product Total Units Ordered Total Sales Which rep has the highest total sales for each region? Rep Name Total Sales Region East Midwest South West What is the total annual sales for each region? Total Sales Region East Midwest South West Using the data above, create a pie chart to determine the sales percentage for each region. Sales Percentage Region East Midwest South West Now, create a PivotTable to determine the monthly sales earned by each region. Use that data to create a 2D clustered column chart. Based on that column bar chart, identify the regions that had the highest and lowest monthly sales for the entire year. Region Month Total Sales Region with Highest Earning Month Region with Lowest Earning Month Required information [The following information applies to the questions displayed below.) This Applying Excel worksheet includes an explanation of the Pivot Table and other Data Visualization charts. On the Data Visualization - Student tab in your Excel spreadsheet, update the price per unit for all four products for Office Warehouse Inc. with the data below: Products Available Price per Unit Paper $19.00 per case Pens $ 6.00 per box Sticky Notes $8.50 per package Envelopes $ 3.00 per box Required: Create a Pivot Table and determine the following information while analyzing the data: 2. Which product has the highest total units ordered and the highest total sales? (Enter your Units Ordered to the nearest whole unit and your Total Sales to 2 decimal places.) Product Total Units Ordered Total Sales Ab Lor Note that any field that is put into the values are wil display as the name of the table along with the words "Sum of This means the Pivot Table Fundion is summing up the values induded in the field name. If you want to change the way the data is sured, right-click on any value fold coll in the Prvofable and select Value Fold Solting Then select the summary type (Sun, Count Average, Max. Mino) For now, keep Sum as the summary type for Pet LG Robert Watson IL w Agenda Notebook oces 38 3 155 35 705 0201 500S 0.75 1 0.203 TA 75.00 2025 14.00 De LA Next, light.dio on any value cell and choose Number Format. Select Currency as the format, displaying 2 decimal places and click OK Your Pivot Table should now look to this Semua Order Column Label Pere Nee Great Tomat 1110.00 $28.00 39078078 141000 10:00 1220 122 IN 37 3898 57135 KY M. $0 $10.78 02.18 GH 35850 385.00 TH 13.00 13.00 13800 35337425 Grand # you ever need to change your original data very eaty to quickly update the Pivot Tabi Alor updating your original data, dok back to your Pivottable. Root-click on anyol in the Pivot Table, then choose Refresh to update the data in the Pivolatie to match the updated originaldate Petables are a great way to summarize data, but they can become even more power when you create visual charts and graphs from Portable Amost any type of charter graph can be made from here, and the moment you change your Plotatie. Same grach or chart will change the a good feature when you are trying to create the best possible visual for you. However. It's not good when you want to create i oferit de wonine Polo One way to work around this is to copy and paste the Pivolatie data to mother before creating the chart or graph Add another tab to your wo book andmet"Charts TutorCopy the won the Portable Tutorial and bestrom the Pivoatie) and Paste it on the Charts at should look like the table above, exceptow the pasted data is no longer connected to the PivoTable since it's on a new Required information 5. Using the data from requirement 4, create a pie chart to determine the sales percentage for each region (Enter your Sales Percentage to the nearest whole percent.) Sales Percentage Region East Midwest South West % % 6. Now, using the Pivot Table, determine the monthly sales earned by each region. Use that data to create a 2D clustered column chart. Based on that column bar chart, identify the regions that had the highest and lowest monthly sales for the entire year. (Enter your Total Sales to 2 decimal places.) Region Month Total Sales Region with Highest Earning Month Region with Lowest Earning Month Required information [The following information applies to the questions displayed below.) This Applying Excel worksheet includes an explanation of the Pivot Table and other Data Visualization charts. Download the Applying Excel form below. Follow the tutorial on the first tab using Excel's Pivot Table function and Charts. After following the tutorial to create a Pivot Table, your final table should match the table shown below. a Sum of Total Order Row Labels Agendas IA $110.00 IL $470.00 IN KY $275.00 OH TN WI Grand Total $855.00 Column Labels Folders Notebooks Grand Total $ 26.00 $ 33.75 $ 169.75 $ 30.00 $ 22.50 $ 522.50 $ 7.60 $ 63.75 $ 71.35 $ 9.00 $ 18.75 $ 302.75 $ 55.50 $ 55.50 $ 13.00 $ 13.00 $ 38.00 $ 56.25 $ 94.25 $123.60 $250.50 $1,229.10 Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Product Notebooks Folders Agendas Price per Unit $1.00 $0.10 $4.50 After refreshing the Pivot Table data, the total order in KY of folders purchased should be $4.50 and the Grand Total for all states and products should be $1,165,30 If you did not get these answers, try to refresh the data again for the Pivot Table and follow the tutorial steps again. Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse". Next click "Save". You will use this worksheet to answer questions in Part 2. Product Price per una 0.75 ..20 5.00 Age 9 Data Visualization Step by Steve Data Visualization can be displayed in multiple ways in Excel, but it all requires one consistent item-dean data Clean data simply means that all the information included in your spreadsheet is formated consistentlyFor example If customers are split into First and last name cells but some rows include both first and last names in the same the data is not con Clean data can be used to gather and display information for management to obtain insights about the company Note: For the purposes of this tutorial and the exorcise folowing con data will be provided One of the first ways the data can be used into useable information is by putting it into a Pivot Table in Excel The data provided to a small portion of the customer sales for three products and their Price per Unt Notebooks cost $0.75, Folders cout 50 20, and Agendas cost $5.00. The products and price per unit are provided in G3H6. The was data for a month has been provided starting in Use the following steps to create a Pivottable Place your cursor anywhere in the group of Gutshat ranges from GSM Then click on the Insert tab, then click the Plved Table button in the Tables group To Create Pabout box wil.coe. The talorare should be adolected and say Data Visualization Tutorials M838. It does not, then to the data range ndicated In the second half of the popup choose where to place to Portable me a New Worksheet The OK. A new tab weled Right click on the tab name and choose Renate Change the name of the th to be able Town Now dick on the Bank Table on the new the Protatie window that played on the side of your screen. The Pivottabile function automatically recognize the column header rows to label the data property and whep to organize the data in the Phvorable. These flats will help you create a custom table with Information that is summerved. You can drag and drop the field names into the wale wreas of the Pivotale the bottom of the window The Areas we Founow..and Values The Free is a manter for the entire Pivoatie and is best used for filering content to only show a particulat year, for example. The "Columns' area in the columns that show across the top of the Pivoable. Similarly, the "Rows" we defines the rows that showdown first column of the Pivottable. The values area is the data being summarized, which is generally a sum or count of data. Arons can contain more than one datafield. To continue the tutoriallers find the total sales for each State of Residence and Type of Product Ordered -Drag and drop the State of Residerosold to the "Rows are - Drag and drop Product Ordered field to the Columns area Fray, drag and drop the Total Order field to the values are Customer Law Name Car First one we of Residence Prendered More IN Notebook Murg Aga Long Tory IN Sah For As A ru w Nebook IA David w ER IA Foder Cores Agenda Dem Dan Ordered Price per U TO 25 1 0.5 1.75 103 0205 200 213 6.00 105.00 30 0.75 22 50 6201 10.00 16 500 75.00 401 . 3000 460 O.TO. 20 020 14.00 10. 2. 100 25 100 25.00 NO OP 23 400 0208 800 00 30 0.5 22.50 0203 000 22 1 8.00 18.00 25 0.75 651 20. 3.00 255 5.00 125.00 215 9.755 15.75 IT 5 755 2025 YDS 020 14.00 455 0205 3.00 30$ 5.00 3 150.00 365 0.755 1850 Mine wy A IN Agenda be Po Quine FUM SW mw 5 F Page OH No Buah Un Veronica Wyatt Zoey Dolores Shane KY KY OH Foldiers Aga Depo Evane Ab Lor Note that any field that is put into the values are wil display as the name of the table along with the words "Sum of This means the Pivot Table Fundion is summing up the values induded in the field name. If you want to change the way the data is sured, right-click on any value fold coll in the Prvofable and select Value Fold Solting Then select the summary type (Sun, Count Average, Max. Mino) For now, keep Sum as the summary type for Pet LG Robert Watson IL w Agenda Notebook oces 38 3 155 35 705 0201 500S 0.75 1 0.203 TA 75.00 2025 14.00 De LA Next, light.dio on any value cell and choose Number Format. Select Currency as the format, displaying 2 decimal places and click OK Your Pivot Table should now look to this Semua Order Column Label Pere Nee Great Tomat 1110.00 $28.00 39078078 141000 10:00 1220 122 IN 37 3898 57135 KY M. $0 $10.78 02.18 GH 35850 385.00 TH 13.00 13.00 13800 35337425 Grand # you ever need to change your original data very eaty to quickly update the Pivot Tabi Alor updating your original data, dok back to your Pivottable. Root-click on anyol in the Pivot Table, then choose Refresh to update the data in the Pivolatie to match the updated originaldate Petables are a great way to summarize data, but they can become even more power when you create visual charts and graphs from Portable Amost any type of charter graph can be made from here, and the moment you change your Plotatie. Same grach or chart will change the a good feature when you are trying to create the best possible visual for you. However. It's not good when you want to create i oferit de wonine Polo One way to work around this is to copy and paste the Pivolatie data to mother before creating the chart or graph Add another tab to your wo book andmet"Charts TutorCopy the won the Portable Tutorial and bestrom the Pivoatie) and Paste it on the Charts at should look like the table above, exceptow the pasted data is no longer connected to the PivoTable since it's on a new Click on any value cell in the Pivot Table on the Charts Tutorial tab. Then click on the Insert tab, and in the Charts group click on Insert Column or Bar Chart. From the dropdown list, select the first item in the 2-D Column section, called Clustered Column. Note that Excel will auto-select data from the table, but it needs some adjustments. Click on the Chart, then click on the Chart Design menu tab. Then click on the Select Data button in the Data group. Change the "chart data range" to be ='Charts Tutorial'!$A$2:$D$9. Click OK. The reason the chart range was changed is to only include the States and Products the sum of total orders. The Grand Total and labels in the first row are not necessary to be included. Any chart can also be altered in its setup. Click on the chart, then click on the Design tab in the special PivotChart Tools tab group. (Note that these tabs are only visible after you click on a chart made from data in a Pivottable. In the Data group, click the Switch Row/Column button to change the layout of the chart. It will swap the columns so they are grouped either by State or by Product Ordered. Set the table so that the States are displayed across the X- axis. To adjust the title of the chart, double click on the Chart Title to change the name. Other chart formatting such as changing font and colors can be done by right-clicking on the chart and choosing Format Chart Area or using the Format tab in the special PivotChart Tools tab group. Follow these similar steps to create other types of charts, including line graphs and pie charts. Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Product Notebooks Folders Agendas Price per Unit $1.00 $0.10 $4.50 After refreshing the Pivot Table data, the total order in KY of folders purchased should be $4.50 and the Grand Total for all states and products should be $1,165,30 If you did not get these answers, try to refresh the data again for the Pivot Table and follow the tutorial steps again. Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse". Next click "Save". You will use this worksheet to answer questions in Part 2. Required information [The following information applies to the questions displayed below.) This Applying Excel worksheet includes an explanation of the Pivot Table and other Data Visualization charts. Download the Applying Excel form below. Follow the tutorial on the first tab using Excel's Pivot Table function and Charts. After following the tutorial to create a Pivot Table, your final table should match the table shown below. a Sum of Total Order Row Labels Agendas IA $110.00 IL $470.00 IN KY $275.00 OH TN WI Grand Total $855.00 Column Labels Folders Notebooks Grand Total $ 26.00 $ 33.75 $ 169.75 $ 30.00 $ 22.50 $ 522.50 $ 7.60 $ 63.75 $ 71.35 $ 9.00 $ 18.75 $ 302.75 $ 55.50 $ 55.50 $ 13.00 $ 13.00 $ 38.00 $ 56.25 $ 94.25 $123.60 $250.50 $1,229.10 Check your Pivot Table setup by changing the original data on the Data Visualization - Tutorial tab. Change the values in cells G3:46 as seen below. Click on any value cell in the Pivot Table on the Charts Tutorial tab. Then click on the Insert tab, and in the Charts group click on Insert Column or Bar Chart. From the dropdown list, select the first item in the 2-D Column section, called Clustered Column. Note that Excel will auto-select data from the table, but it needs some adjustments. Click on the Chart, then click on the Chart Design menu tab. Then click on the Select Data button in the Data group. Change the "chart data range" to be ='Charts Tutorial'!$A$2:$D$9. Click OK. The reason the chart range was changed is to only include the States and Products the sum of total orders. The Grand Total and labels in the first row are not necessary to be included. Any chart can also be altered in its setup. Click on the chart, then click on the Design tab in the special PivotChart Tools tab group. (Note that these tabs are only visible after you click on a chart made from data in a Pivottable. In the Data group, click the Switch Row/Column button to change the layout of the chart. It will swap the columns so they are grouped either by State or by Product Ordered. Set the table so that the States are displayed across the X- axis. To adjust the title of the chart, double click on the Chart Title to change the name. Other chart formatting such as changing font and colors can be done by right-clicking on the chart and choosing Format Chart Area or using the Format tab in the special PivotChart Tools tab group. Follow these similar steps to create other types of charts, including line graphs and pie charts. Product Price per una 0.75 ..20 5.00 Age 9 Data Visualization Step by Steve Data Visualization can be displayed in multiple ways in Excel, but it all requires one consistent item-dean data Clean data simply means that all the information included in your spreadsheet is formated consistentlyFor example If customers are split into First and last name cells but some rows include both first and last names in the same the data is not con Clean data can be used to gather and display information for management to obtain insights about the company Note: For the purposes of this tutorial and the exorcise folowing con data will be provided One of the first ways the data can be used into useable information is by putting it into a Pivot Table in Excel The data provided to a small portion of the customer sales for three products and their Price per Unt Notebooks cost $0.75, Folders cout 50 20, and Agendas cost $5.00. The products and price per unit are provided in G3H6. The was data for a month has been provided starting in Use the following steps to create a Pivottable Place your cursor anywhere in the group of Gutshat ranges from GSM Then click on the Insert tab, then click the Plved Table button in the Tables group To Create Pabout box wil.coe. The talorare should be adolected and say Data Visualization Tutorials M838. It does not, then to the data range ndicated In the second half of the popup choose where to place to Portable me a New Worksheet The OK. A new tab weled Right click on the tab name and choose Renate Change the name of the th to be able Town Now dick on the Bank Table on the new the Protatie window that played on the side of your screen. The Pivottabile function automatically recognize the column header rows to label the data property and whep to organize the data in the Phvorable. These flats will help you create a custom table with Information that is summerved. You can drag and drop the field names into the wale wreas of the Pivotale the bottom of the window The Areas we Founow..and Values The Free is a manter for the entire Pivoatie and is best used for filering content to only show a particulat year, for example. The "Columns' area in the columns that show across the top of the Pivoable. Similarly, the "Rows" we defines the rows that showdown first column of the Pivottable. The values area is the data being summarized, which is generally a sum or count of data. Arons can contain more than one datafield. To continue the tutoriallers find the total sales for each State of Residence and Type of Product Ordered -Drag and drop the State of Residerosold to the "Rows are - Drag and drop Product Ordered field to the Columns area Fray, drag and drop the Total Order field to the values are Customer Law Name Car First one we of Residence Prendered More IN Notebook Murg Aga Long Tory IN Sah For As A ru w Nebook IA David w ER IA Foder Cores Agenda Dem Dan Ordered Price per U TO 25 1 0.5 1.75 103 0205 200 213 6.00 105.00 30 0.75 22 50 6201 10.00 16 500 75.00 401 . 3000 460 O.TO. 20 020 14.00 10. 2. 100 25 100 25.00 NO OP 23 400 0208 800 00 30 0.5 22.50 0203 000 22 1 8.00 18.00 25 0.75 651 20. 3.00 255 5.00 125.00 215 9.755 15.75 IT 5 755 2025 YDS 020 14.00 455 0205 3.00 30$ 5.00 3 150.00 365 0.755 1850 Mine wy A IN Agenda be Po Quine FUM SW mw 5 F Page OH No Buah Un Veronica Wyatt Zoey Dolores Shane KY KY OH Foldiers Aga Depo Evane Required information 3. Which rep has the highest total sales for each region? (Enter your Total Sales to 2 decimal places.) Rep Name Total Sales Region East Midwest South West 4. What is the total annual sales for each region? (Enter your Total Sales to 2 decimal places.) Total Sales Region East Midwest South West Office Warehouse Inc. currently makes four products. The Sales Director has asked you to run a preliminary analysis on last year's sales data, which has been cleaned and is given on the Original Data tab. The products made by the company and sales price per unit are listed below. Use the different data visualization methods recommended in each requirement to analyze the company's sales data. Products Available Price per Unit Paper $ 21.00 Iper case Pens $ 8.00 per box Sticky Notes $ 10.00 per package Envelopes $ 4.00 per box Required Create a PivotTable to answer each question below while analyzing the data. Which product had the highest total units ordered and highest total sales? Product Total Units Ordered Total Sales Which rep has the highest total sales for each region? Rep Name Total Sales Region East Midwest South West What is the total annual sales for each region? Total Sales Region East Midwest South West Using the data above, create a pie chart to determine the sales percentage for each region. Sales Percentage Region East Midwest South West Now, create a PivotTable to determine the monthly sales earned by each region. Use that data to create a 2D clustered column chart. Based on that column bar chart, identify the regions that had the highest and lowest monthly sales for the entire year. Region Month Total Sales Region with Highest Earning Month Region with Lowest Earning Month Required information [The following information applies to the questions displayed below.) This Applying Excel worksheet includes an explanation of the Pivot Table and other Data Visualization charts. On the Data Visualization - Student tab in your Excel spreadsheet, update the price per unit for all four products for Office Warehouse Inc. with the data below: Products Available Price per Unit Paper $19.00 per case Pens $ 6.00 per box Sticky Notes $8.50 per package Envelopes $ 3.00 per box Required: Create a Pivot Table and determine the following information while analyzing the data: 2. Which product has the highest total units ordered and the highest total sales? (Enter your Units Ordered to the nearest whole unit and your Total Sales to 2 decimal places.) Product Total Units Ordered Total Sales Ab Lor Note that any field that is put into the values are wil display as the name of the table along with the words "Sum of This means the Pivot Table Fundion is summing up the values induded in the field name. If you want to change the way the data is sured, right-click on any value fold coll in the Prvofable and select Value Fold Solting Then select the summary type (Sun, Count Average, Max. Mino) For now, keep Sum as the summary type for Pet LG Robert Watson IL w Agenda Notebook oces 38 3 155 35 705 0201 500S 0.75 1 0.203 TA 75.00 2025 14.00 De LA Next, light.dio on any value cell and choose Number Format. Select Currency as the format, displaying 2 decimal places and click OK Your Pivot Table should now look to this Semua Order Column Label Pere Nee Great Tomat 1110.00 $28.00 39078078 141000 10:00 1220 122 IN 37 3898 57135 KY M. $0 $10.78 02.18 GH 35850 385.00 TH 13.00 13.00 13800 35337425 Grand # you ever need to change your original data very eaty to quickly update the Pivot Tabi Alor updating your original data, dok back to your Pivottable. Root-click on anyol in the Pivot Table, then choose Refresh to update the data in the Pivolatie to match the updated originaldate Petables are a great way to summarize data, but they can become even more power when you create visual charts and graphs from Portable Amost any type of charter graph can be made from here, and the moment you change your Plotatie. Same grach or chart will change the a good feature when you are trying to create the best possible visual for you. However. It's not good when you want to create i oferit de wonine Polo One way to work around this is to copy and paste the Pivolatie data to mother before creating the chart or graph Add another tab to your wo book andmet"Charts TutorCopy the won the Portable Tutorial and bestrom the Pivoatie) and Paste it on the Charts at should look like the table above, exceptow the pasted data is no longer connected to the PivoTable since it's on a new Required information 5. Using the data from requirement 4, create a pie chart to determine the sales percentage for each region (Enter your Sales Percentage to the nearest whole percent.) Sales Percentage Region East Midwest South West % % 6. Now, using the Pivot Table, determine the monthly sales earned by each region. Use that data to create a 2D clustered column chart. Based on that column bar chart, identify the regions that had the highest and lowest monthly sales for the entire year. (Enter your Total Sales to 2 decimal places.) Region Month Total Sales Region with Highest Earning Month Region with Lowest Earning Month
