I JUST NEED THE STEP-BY-STEP PROCESS ON HOW TO DO THESE EXCEL ACTIONS FOR 1 PROBLEM, IF YOURE ABLE TO SOMEHOW USE THE SAME DATA THAT WOULD BE GREAT, BUT IF NOT THEN A STEP-BY-STEP (ie. CLICK THIS TAB THEN CLICK THIS) WOULD SUFFICE. THANKS!!!

Building a Data Dashboard. Bogdans Express, a chain of sporting goods stores in Washington, wants to construct a technical support data dashboard to monitor how effectively its technical support group deals with IT problems. Management is primarily interested in the time it takes the IT support group to respond once a problem has been reported (response time) and how long it takes the group to resolve the issue after the initial response by the IT support group (time to resolution) over the most recent four months. They would like to be able to review the IT groups performance by date, type of technical problem (email, hardware, or internet), and office (Bellingham, Olympia, Seattle, or Spokane).

Each reported problem is immediately logged and issued a case number, and the data collected by Bogdan from its relational database includes the case number, date, office, type of technical problem, response time (in minutes), and time to resolution (in minutes). They have also created a new field for the month during which the problem was reported. Note that both Response Time and Time to Resolution only include time that elapses during normal business hours.

Bogdans staff has already created the following components for the data dashboard in Excel. LO 6

• A line chart of average response time across months by office (in the Chart1 worksheet)

• A line chart of average time to resolution across months by office (in the Chart2 worksheet)

• A clustered bar chart of average time to resolution across offices by type of technical problem (in the Chart3 worksheet)

• A stacked cluster column chart of number of problems reported across office by type of technical problem (in the Chart4 worksheet)

The data and these charts are available in the file BogdanCharts, and the name of the worksheet in which each chart can be found is given with the descriptions of the charts Bogdan has developed.

1. Create the following slicers for the charts created by Bogdan.

   • A slicer for month and a slicer for office for the chart in the Chart1 worksheet

   • A slicer for month and a slicer for office for the chart in the Chart2 worksheet

   • A slicer for office and a slicer for type of technical problem for the chart in the Chart3 worksheet

   • A slicer for office and a slicer for type of technical problem for the chart in the Chart4 worksheet

2. Create the data dashboard by creating a new worksheet and naming it Dashboard; moving the charts and slicers from the Chart1, Chart2, Chart3, and Chart4 worksheets to the Dashboard worksheet; repositioning these charts and slicers on the Dashboard worksheet; and adding a title to the dashboard and doing whatever formatting and editing is necessary to make the dashboard functional and visually appealing.

3. Amend the data dashboard you created in part b in the following ways.

   • Create a single slicer to filter month for the charts created in the Chart1 and Chart2 worksheets

   • Create a single slicer to filter office for the charts created in the Chart1, Chart2, Chart3, and Chart4 worksheets

   • Create a slicer to filter type of technical problem for the charts created in the Chart3 and Chart4 worksheets

   Once you have amended the data dashboard, rearrange the charts and slicers to create an effective and visually appealing dashboard. Test each slicer to ensure it works on the appropriate charts.

4. Protect the data dashboard in part c from being revised by users. Ensure that the slicers cannot be resized or moved, password protect the Dashboard worksheet (use the password Problem820), and hide all worksheets except the Dashboard worksheet.

5. The following seven entries for April 2930 in the following table were not logged. Add these data to the BogdanData table and refresh all PivotTables and PivotCharts. Comment on differences between the resulting dashboard and the dashboard from part d.

*NOT SURE WHAT SUBJECT TO POST THIS SORRY, IF THERES ANYWAY FOR ME TO SEND YOU THE EXCEL THAT WOULD BE GOOD TOO, IF NOT THATS OKAY*

Case Number 1990 1994 2000 2005 2011 2012 2019 Date 29-Apr 29-Apr 29-Apr 29-Apr 30-Apr 30-Apr 30-Apr Month Apr Apr Apr Apr Apr Apr Apr Type of Technical Problem Olympia hardware Spokane hardware hardware internet hardware hardware email Office Bellingham Bellingham Olympia Spokane Spokane Response Time (Minutes) 59.9 15.8 26.7 41.4 96.1 125.6 4.5 Time to Resolution (Minutes) 23.1 64.1 53.4 12.8 14.6 55.0 45.9 File Paste E17 24 25 26 27 Home Insert Page Layout Cut Copy Formal Painter 28 29 7 Mar 8 Apri 9 Grand Total 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Ready Clipboard i R X A Calibr 1 2 3 Average of Response Time (Minutes) 4 Row Labels 5 Jan 6 Frb BIU tx Tant Formulas Data Review 11A A Y A. B Column Labels - Bellingham === - 73.53333333 83.92 136.525 35.9 91.21111111 Chart1 Chart2 Chart3 Chart1 Data Accessibility: Investigate View Help Power Pivot 2Wrap Text Alignment Marge & Center D & General S% Namber # Olympla Seattle Spokane Grand Total 103.4333333 77.40714286 55.57058824 73.95116279 86.45294118 91.35909091 77.91111111 86.55172414 73.58076923 81.30869565 68.43157895 78.1875 58.35 68.952 65.82 54.96041667 78.3421875 79.61309524 65.53272727 76.68552036 5 Conditional Format as Cell Formalling Table Styles Styles 160 141 120 100 YU Fr 10 20 H C Average Response Time (in Minutes) Across Months by Office Jan I |4| A ATT Insert Delete Format CHI J AutoSam rill Clear K AY Sort & Find & Filler Select Filing 999 CI Q Analyza Sensitivity Dala Analysis Apr M Comments [U] Sensitivity N Office Share -Uellingham -Olympia -Seattle -Spakane + +100% File Paste A1 Home Insert Page Layout Cut [Copy Formal Painter Ready Clipboard 6 Feb 7 Mar 8 Apr 9 Grand Total 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 1 Calibri BIU X Jx A . 1 2 3 Average of Time to Resolution (Minutes) 4 Row Labels 5 Jan Fant Formulas Data 11A AT & A. B Chart1 Chartz Chart3 Charl4 Data Accessibility: Investigata Review Column Labels ||- - Bellingham View Help Power Pivot Wrap Text =====Merge & Center Alignment C D F General $% E Number Spokane F # Olympia Seattle 36.93333333 37.42222222 30.96428571 Grand Total 32.6 33.37906977 39.72 36.04705BB2 29.61363636 46.73333333 35.89827586 36.2 33.22307692 35.10859565 38.94735842 35.50138889 45.9 25.75835555 31.904 39.65 32.27291567 38.81666667 33.1640625 32.025 38.38727273 34.49140271 Is 5 Conditional Format as Cell Formatting Table Styles Styles 50 45 10 35 30 25 20 15 10 5 H 0 Insert Delete Format Jan I Cals J Autosum Fill Clear Feb K Average Time to Resolution (in Minutes) Across Months by Office 27 Sort & Find & Filter Select Fditing Mar B A O Analyze Dala Analysis M Comments Apr M Sensitivity Sensitivity N Share Office Olympia *||** -Spokane + +133% File Pasie A1 10 11 12 13 14 15 16 17 15 19 20 21 22 23 24 25 26 Home Home 8 Spokane 9 Grand Total 27 25 29 X. Cut La copy Ready Format Painter Clipboard Insert Page Layout Formulas Data 11 A A - & A. 1 24 X 1 2 3 Average of Time to Resolution (Minutes) 4 Row Labels 5 Bellingham 6 Olympia 7 Seattle Calibri BJU. A fxx Fort 19 Chart1 Chart2 Chart3 Chart4 Data Accessibility Investigat 8 Column Labels email Review View === *- ===== Help Power Pivot Wap Text Morge i Center Alignment D hardware internet V F General $ E %% Number Grand Total 43.3 39.75 35.65 38.81555667 29.5 38.3173913 30.63214286 33.1640625 29.11666667 34.14444444 32.77857143 32.025 24.23333333 43.37222222 41.592 38.38727273 29.1754717 38.66031746 34.67333333 34.49140271 F Conditional Formal as Cel Formatting Table Styles Styles H Spokane Seattle Olympia Bellingham 4 HH Insert Delete Formal 5 Colla 10 15 mosino Z Average Time to Resolution (in Minutes) Across Offices by Type of Technical Problem 20 AutoSum Fill Clear K Sort & Find & Filter Select Editing L 25 30 35 40 I 4 Q TN Analyze Sensitivity Data Analysis M I Comments 45 Sensitivity 50 N Share Type af Technical Problem internet hardware email +199% 100% File Paste Al 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Home Insert Page Layout & Cut [Copy Format Painter Clipboard Ready Fy 1 > A Calibr BIU. fx 1 2 3 Count of Case Number Column Labels 1 Row Labels emall 5 Bellingham 6 Olympia 7 Seattle 8 Spokane 9 Grand Total 10 11 12 Font B Formulas Data Review View Help 11 A A ===> 4 13 24 12 53 Chart1 Chart2 Chart3 Chart4 Data Accessibility: Invest gata C 5 4 23 18 18 63 22 Wrap Text =====Merge & Center D Nigen hardware Internet Grand Total 18 64 84 55 221 10 28 42 25 105 E Power Pivot F S G General $%$# Namber 80 70 50 50 20 10 H 0 5 Conditional Format as Cell Formatting Table Styles Styles Bellingham J Number of Problems Reported Across Offices by Type of Technical Problem Olymais 20 IIX I Insert Delate Format A AutoSum L Seattle Fil Clear M Spokane 27 Sort & Find & Filter Select Edling N Type of Technical Problem internet hardware email 8 THI Q Analyze Data Analysis 0 Comments Sensitivity Sensitivity P + Share + 100% File Pasie A1 3 4 5 6 7 9 10 11 12 13 14 15 16 17 18 19 20 21 22 24 25 26 27 28 29 Home A 1 Case Number - 2 Ready X Cut La copy Format Painter Clipboard Insert Page Layout 1 Calibri BJU B Date 4 Jan-0.3 11 Jan-03 19 Jan-03 23 Jan-03 43 Jan 04 59 Jan-04 Jan-04 60 63 Jan-04 69 Jan-05 74 Jan 05 7G Jan-05 78 Jan-05 94 Jan-06 Jan-06 118 124 Jan 06 141 149 152 155 173 Jan 11 175 Jan-12 176 Jan-13 Jan-16 204 212 Jan-16 229 Jan-07 Jan-07 Jan-10 Jan-10 J Jan 17 Jan-17 Font Formulas Data 11 A A - A Case Number Review == Jan Olympia email Spokane hardware Jan Jan Jan Jan Jan Jan Jan Jan Spokane hardware Olympia hardware Olympia hardware hardware Jan email email internet Spokane Seattle Jan Seattle Jan Seattle Jan Bellingham email Jan Spokane email Jan Olympia internet Jan Spokane internet Jan Olympia email Jan Olympia email Jan Jan Jan Jan Jan Jan 255 Jan-17 Jan 256 Jan-18 Jan Chart Chart2 Chart3 Chart4 Data Bellingham hardware hardware internet Accessibility Investgele Seattle Spokane Wag Text =====Merge & Center hardware email internet View Help Power Pivot Seattle Spokane Olympia email email Seattle Seattle Spokane Seattle + hardware hardware internet Alignment C D F Type of Technical Problem - Response Time (Minutes) Month Office Spokane Spokane email email Jan Jan Spokane S Table Design General $% Number la 111.6 73.9 1.5 33.0 54.5 145.9 26.2 18.2 16.3 145.1 63.1 113.0 28.4 145.0 4.8 69.9 37.9 115.8 88.5 44.4 113.6 43.0 131.4 87.0 45.2 128.3 137.6 Conditional Formal as Cel Formatting Table Styles Styles Time to Resolution (Minutes) H Insert Delete Formal 5.8 22.2 28.3 22.9 44.2 57.8 49.6 46.5 65.2 19.8 55.8 29.3 6.2 43.9 20.6 49.4 62.2 40.0 3.5 32.6 22.7 48.7 7.5 35.9 33.7 50.4 23.5 31.8 Cells O Z AutoSum Fill > Clear Sort & Find & Filter Select H I J Editing K # L Comments Tes Analyze Sensitivity Data Analysis 14 M Sensitivity + N Share I 100% Case Number 1990 1994 2000 2005 2011 2012 2019 Date 29-Apr 29-Apr 29-Apr 29-Apr 30-Apr 30-Apr 30-Apr Month Apr Apr Apr Apr Apr Apr Apr Type of Technical Problem Olympia hardware Spokane hardware hardware internet hardware hardware email Office Bellingham Bellingham Olympia Spokane Spokane Response Time (Minutes) 59.9 15.8 26.7 41.4 96.1 125.6 4.5 Time to Resolution (Minutes) 23.1 64.1 53.4 12.8 14.6 55.0 45.9 File Paste E17 24 25 26 27 Home Insert Page Layout Cut Copy Formal Painter 28 29 7 Mar 8 Apri 9 Grand Total 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Ready Clipboard i R X A Calibr 1 2 3 Average of Response Time (Minutes) 4 Row Labels 5 Jan 6 Frb BIU tx Tant Formulas Data Review 11A A Y A. B Column Labels - Bellingham === - 73.53333333 83.92 136.525 35.9 91.21111111 Chart1 Chart2 Chart3 Chart1 Data Accessibility: Investigate View Help Power Pivot 2Wrap Text Alignment Marge & Center D & General S% Namber # Olympla Seattle Spokane Grand Total 103.4333333 77.40714286 55.57058824 73.95116279 86.45294118 91.35909091 77.91111111 86.55172414 73.58076923 81.30869565 68.43157895 78.1875 58.35 68.952 65.82 54.96041667 78.3421875 79.61309524 65.53272727 76.68552036 5 Conditional Format as Cell Formalling Table Styles Styles 160 141 120 100 YU Fr 10 20 H C Average Response Time (in Minutes) Across Months by Office Jan I |4| A ATT Insert Delete Format CHI J AutoSam rill Clear K AY Sort & Find & Filler Select Filing 999 CI Q Analyza Sensitivity Dala Analysis Apr M Comments [U] Sensitivity N Office Share -Uellingham -Olympia -Seattle -Spakane + +100% File Paste A1 Home Insert Page Layout Cut [Copy Formal Painter Ready Clipboard 6 Feb 7 Mar 8 Apr 9 Grand Total 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 1 Calibri BIU X Jx A . 1 2 3 Average of Time to Resolution (Minutes) 4 Row Labels 5 Jan Fant Formulas Data 11A AT & A. B Chart1 Chartz Chart3 Charl4 Data Accessibility: Investigata Review Column Labels ||- - Bellingham View Help Power Pivot Wrap Text =====Merge & Center Alignment C D F General $% E Number Spokane F # Olympia Seattle 36.93333333 37.42222222 30.96428571 Grand Total 32.6 33.37906977 39.72 36.04705BB2 29.61363636 46.73333333 35.89827586 36.2 33.22307692 35.10859565 38.94735842 35.50138889 45.9 25.75835555 31.904 39.65 32.27291567 38.81666667 33.1640625 32.025 38.38727273 34.49140271 Is 5 Conditional Format as Cell Formatting Table Styles Styles 50 45 10 35 30 25 20 15 10 5 H 0 Insert Delete Format Jan I Cals J Autosum Fill Clear Feb K Average Time to Resolution (in Minutes) Across Months by Office 27 Sort & Find & Filter Select Fditing Mar B A O Analyze Dala Analysis M Comments Apr M Sensitivity Sensitivity N Share Office Olympia *||** -Spokane + +133% File Pasie A1 10 11 12 13 14 15 16 17 15 19 20 21 22 23 24 25 26 Home Home 8 Spokane 9 Grand Total 27 25 29 X. Cut La copy Ready Format Painter Clipboard Insert Page Layout Formulas Data 11 A A - & A. 1 24 X 1 2 3 Average of Time to Resolution (Minutes) 4 Row Labels 5 Bellingham 6 Olympia 7 Seattle Calibri BJU. A fxx Fort 19 Chart1 Chart2 Chart3 Chart4 Data Accessibility Investigat 8 Column Labels email Review View === *- ===== Help Power Pivot Wap Text Morge i Center Alignment D hardware internet V F General $ E %% Number Grand Total 43.3 39.75 35.65 38.81555667 29.5 38.3173913 30.63214286 33.1640625 29.11666667 34.14444444 32.77857143 32.025 24.23333333 43.37222222 41.592 38.38727273 29.1754717 38.66031746 34.67333333 34.49140271 F Conditional Formal as Cel Formatting Table Styles Styles H Spokane Seattle Olympia Bellingham 4 HH Insert Delete Formal 5 Colla 10 15 mosino Z Average Time to Resolution (in Minutes) Across Offices by Type of Technical Problem 20 AutoSum Fill Clear K Sort & Find & Filter Select Editing L 25 30 35 40 I 4 Q TN Analyze Sensitivity Data Analysis M I Comments 45 Sensitivity 50 N Share Type af Technical Problem internet hardware email +199% 100% File Paste Al 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Home Insert Page Layout & Cut [Copy Format Painter Clipboard Ready Fy 1 > A Calibr BIU. fx 1 2 3 Count of Case Number Column Labels 1 Row Labels emall 5 Bellingham 6 Olympia 7 Seattle 8 Spokane 9 Grand Total 10 11 12 Font B Formulas Data Review View Help 11 A A ===> 4 13 24 12 53 Chart1 Chart2 Chart3 Chart4 Data Accessibility: Invest gata C 5 4 23 18 18 63 22 Wrap Text =====Merge & Center D Nigen hardware Internet Grand Total 18 64 84 55 221 10 28 42 25 105 E Power Pivot F S G General $%$# Namber 80 70 50 50 20 10 H 0 5 Conditional Format as Cell Formatting Table Styles Styles Bellingham J Number of Problems Reported Across Offices by Type of Technical Problem Olymais 20 IIX I Insert Delate Format A AutoSum L Seattle Fil Clear M Spokane 27 Sort & Find & Filter Select Edling N Type of Technical Problem internet hardware email 8 THI Q Analyze Data Analysis 0 Comments Sensitivity Sensitivity P + Share + 100% File Pasie A1 3 4 5 6 7 9 10 11 12 13 14 15 16 17 18 19 20 21 22 24 25 26 27 28 29 Home A 1 Case Number - 2 Ready X Cut La copy Format Painter Clipboard Insert Page Layout 1 Calibri BJU B Date 4 Jan-0.3 11 Jan-03 19 Jan-03 23 Jan-03 43 Jan 04 59 Jan-04 Jan-04 60 63 Jan-04 69 Jan-05 74 Jan 05 7G Jan-05 78 Jan-05 94 Jan-06 Jan-06 118 124 Jan 06 141 149 152 155 173 Jan 11 175 Jan-12 176 Jan-13 Jan-16 204 212 Jan-16 229 Jan-07 Jan-07 Jan-10 Jan-10 J Jan 17 Jan-17 Font Formulas Data 11 A A - A Case Number Review == Jan Olympia email Spokane hardware Jan Jan Jan Jan Jan Jan Jan Jan Spokane hardware Olympia hardware Olympia hardware hardware Jan email email internet Spokane Seattle Jan Seattle Jan Seattle Jan Bellingham email Jan Spokane email Jan Olympia internet Jan Spokane internet Jan Olympia email Jan Olympia email Jan Jan Jan Jan Jan Jan 255 Jan-17 Jan 256 Jan-18 Jan Chart Chart2 Chart3 Chart4 Data Bellingham hardware hardware internet Accessibility Investgele Seattle Spokane Wag Text =====Merge & Center hardware email internet View Help Power Pivot Seattle Spokane Olympia email email Seattle Seattle Spokane Seattle + hardware hardware internet Alignment C D F Type of Technical Problem - Response Time (Minutes) Month Office Spokane Spokane email email Jan Jan Spokane S Table Design General $% Number la 111.6 73.9 1.5 33.0 54.5 145.9 26.2 18.2 16.3 145.1 63.1 113.0 28.4 145.0 4.8 69.9 37.9 115.8 88.5 44.4 113.6 43.0 131.4 87.0 45.2 128.3 137.6 Conditional Formal as Cel Formatting Table Styles Styles Time to Resolution (Minutes) H Insert Delete Formal 5.8 22.2 28.3 22.9 44.2 57.8 49.6 46.5 65.2 19.8 55.8 29.3 6.2 43.9 20.6 49.4 62.2 40.0 3.5 32.6 22.7 48.7 7.5 35.9 33.7 50.4 23.5 31.8 Cells O Z AutoSum Fill > Clear Sort & Find & Filter Select H I J Editing K # L Comments Tes Analyze Sensitivity Data Analysis 14 M Sensitivity + N Share I 100%