Excel 2024 In Practice - Ch 4 Guided Project 4-3
SIMnet Excel project instructions
· Excel Projects Help
Guided Project 4-3 - SIMnet Project Steps to complete This Project
Mark the steps as checked when you complete them.
1.Open the Clemenson-04 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2.Rename a sheet in the Navigation pane.
3.Click the Navigation button [View tab, Show group]. NOTE: If your version of Excel does not have a Navigation button, click the worksheet tab name instead of the name in the pane.
4.Click the Patients&Equip name in the Navigation pane to move to that sheet.
5.Copy the Patients&Equip sheet and place it before the Criteria sheet.
6.Right-click the Patients&Equip(2) name in the Navigation pane and choose Rename.
Name the copy Adv Filter and click OK (Figure 4-110).
A screenshot shows a Navigation pane.The Navigation pane consists of a Search bar at the top. Followed by a list of sheet name categories with drop-down arrows. A Rename dialog box is opened across the Navigation pane. It consists of a Rename to feature with a text box. The text, A d v Filter, is typed inside the box. The OK and Cancel buttons are present at the bottom of the Rename dialog box. The OK button is selected.
Figure 4-110 Rename a sheet in the Navigation pane
7.Create and resize an Excel table.
8.Select cell A5 on the Adv Filter sheet and click the Format as Table button [Home tab, Styles group].
9.Choose White, Table Style Light 1.
10.Confirm that the range is $A$5:$D$25 and that the My table has headers box is selected. The source data is from multiple files and not all rows were recognized.
11.Click OK to create the table.
12.Scroll or zoom out to determine the last row of data.
A screenshot shows the Resize Table dialog box.The Resize Table dialog box consists of a Select the new data range for your table feature. This feature contains a box with an upward arrow at the far right to upload the desired data range. The data range $ A $ 5 to $ D $ 74 is selected inside the box. A Note is present below the box, which states: The header must remain in the same row, and the resulting table range must overlap the original table range. The OK and Cancel buttons are present at the bottom right of the dialog box. The OK button is selected.
Figure 4-111 Resize the table to reach row 74
13.Click the Resize Table button [Table Design tab, Properties group] to display the Resize Table dialog box.
14.Click after $25 in the dialog box, change the value to 74, and click OK (Figure 4-111).
15.Select cell A5 and click the Table Name box [Table Design tab, Properties group.
Name the table tblFilter.
16.Adjust the zoom size and close the Navigation pane.
17.Create an output range for an Advanced Filter.
18.Select cells A5:D5 and copy and paste them to cell G5.
19.Type Extract Range in cell G4 and set the font to 14 pt. italic.
20.Adjust column widths for columns G:J so that each label displays on a single line.
21.Create an Advanced Filter.
22.Click the Criteria sheet tab. Notice that the Month/Year label displays twice in row 2.
23.Type >12/31/22 in cell A3 to find records after 2022.
24.Type <1/1/24 in cell B3 to create an AND filter. This criteria will extract records from 2023. It finds records in which the date is greater than (after) 12/31/22 and before 1/1/24 (Figure 4-112).
Criteria for Advanced Filter
Figure 4-112 Criteria for Advanced Filter
25.Click cell A5 on the Adv Filter sheet and click the Advanced button [Data tab, Sort & Filter group].
26.Select the Copy to another location radio button.
27.Verify that the List range is cells $A$5:$D$74. If the range is incorrect, click and drag to select the range including the header row.
28.Click the Criteria range box and select cells A2:B3 on the Criteria sheet.
29.Click the Copy to box and select cells G5:J5 on the Adv Filter sheet.
30.Click OK in the Advanced Filter dialog box.
31.Sort data in the output range.
32.Select cell G5 and click the Sort button [Data tab, Sort & Filter group].
33.Choose Image Type for the first level.
34.Sort by Cell Values in A to Z order.
35.Add Month/Year as the next level.
36.Sort by Cell Values and Oldest to Newest and click OK.
AutoFit column H.
37.Apply conditional formatting and borders.
38.Select cells I6:I26 and click the Conditional Formatting button [Home tab, Styles group].
39.Choose Highlight Cells Rules and Greater Than.
40.Format cells that are greater than 100 with Green Fill with Dark Green Text.
41.Select the extract range starting at cell G6 and apply All Borders (Figure 4-113).
Advanced Filter results
Figure 4-113 Advanced Filter results
42.Press Ctrl+Home.
43.Import a comma-separated values file.
44.Click the Expense Info sheet tab and select cell A4.
45.Click the From Text/CSV button [Data tab, Get & Transform Data group].
46.Find and select the ClemensonCSV-04.csv file downloaded from the Resources link in the Import Data window.
47.Click Import.
48.Verify that Comma is selected as the Delimiter in the preview window.
49.Click the arrow with Load and select Load To.
50.Select Existing worksheet.
51.Verify that cell $A$4 displays as the destination and that Table is how the data will be imported.
52.Click OK to import the text data.
Convert an Excel table to a range.
Cut and paste (or drag and drop) the labels in row 3 to replace the labels in row 4.
53.Select cell A5 and click the Convert to Range button [Table Design tab, Tools group].
54.Click OK to remove the query definition and create a normal data range.
55.Select cells A4:D34 and clear the formats [Home tab, Editing group].
56.Select cells A4:D34, if necessary, and change the font size to 11 pt Aptos Narrow.
Center align the labels in row 4 and apply bold format.
57.Format the dates in column C with the Short Date format.
58.Set the column width for columns A:D to 13.
59.Close the Queries & Connections pane.
60.Use the Subtotal command to display average expenses by last name.
61.Click cell A5 and sort by last name in ascending order.
62.Click the Subtotal button [Data tab, Outline group].
63.Complete the dialog box to show the average amount for each Last Name.
64.Format the values in column D as Accounting Number Format.
Collapse the outline to display only the averages for each last name (Figure 4-114).
A screenshot of an Excel sheet shows the use of the Subtotal command.The Excel sheet consists of a table. The table is titled Clemenson Imaging, Expense Data for Specified Year, in the Header section. The table consists of four columns and six rows. The column headings are Last Name, First Name, Date, and Amount. The First Name and Date columns contain no entries. The rows 4, 11, 18, 25, 32, 39, and 40 are only visible. The remaining all are hidden. The names in the Last Name column are arranged in alphabetic order. The corresponding average amount in dollars is displayed in the Amount column. The Last Name and their respective Average dollar amount are as follows: Allen: $ 536.67, Bashir: $ 638.33, Gabrys: $ 708.33, Gomez: $ 570.00, McAllister: $ 585.83, and Grand: $ 607.83.
Figure 4-114 Subtotals displayed for group
65.Create a PivotChart and its PivotTable.
66.Click the Patients&Equip sheet tab.
67.Select cell A5 and click the Analyze Data button [Data tab, Data Tools group]. Office 2024 Note: If you are using the Excel 2024 version, and it does not have an Analyze Data button on the Data tab in the Data Tools group, open Clemenson-04_Supplement.xlsx from your student data files. Copy Sheet1 into your file and rename the sheet PivotData. Continue with step 12.
68.Find a suggestion for a PivotChart and click Insert PivotChart (Figure 4-115). Your options may be different from the figure. The chart and its associated PivotTable display on a new sheet.
A screenshot shows the Analyze Data pane.The Analyze Data pane consists of a Query bar at the top, followed by two Suggested questions such as Percentage of total 'Equipment Usage' for each 'Image Type', and Total 'Equipment Usage' by 'Image Type' as a column chart. A Discover Insights feature is present below the Suggested questions, with a Settings button located at the right end. Three layout suggestions are present below the Discover Insight feature, which are Pivot Table, Pivot Chart, and Scatter plot. Each layout suggestion consists of the heading: 'Equipment Usage' and 'Patients' by 'Image Type', Equipment Usage' by 'Image Type', and Equipment Usage' by 'Patients', respectively. An Insert Pivot Table button is present at the bottom left of the Pivot Table layout. An Insert Pivot Chart button is present at the bottom left of the Pivot Chart layout.
Figure 4-115 Analyze Data pane
69.Close the Analyze Data pane.
70.Rename the sheet PivotData.
Pivot and format fields in a PivotTable.
71.Select a cell in the PivotTable and display the PivotTable Fields pane.
72.Confirm or place the Patients field in the Values area and the Image Type field in the Rows area.
Remove other fields that were placed in the Values and Rows areas by the Analyze Data command.
73.Click cell B3 and open the Value Field Settings dialog box.
74.Click Number Format, choose Number as the Category, set zero decimal places, and display a comma as the 1000 Separator.
75.Click the Quick Styles button [Design tab, PivotTable Styles group] and choose Dark Gray, Pivot Style Dark 9.
76.Display Banded Rows.
77.Display Banded Columns.
Style a PivotChart.
Position the chart object so that its top-left corner is at cell A12.
78.Drag the bottom-right selection handle to reach cell J35.
79.Click the Quick Styles button [Design tab, Chart Styles group] and choose Style 7.
80.Edit the chart title to display Number of Patients by Image Type. Remove any single quotes.
81.Add a calculated field in a PivotTable.
82.Select cell B3 in the PivotTable and display the PivotTable Fields pane if necessary.
83.Click the Fields, Items, & Sets button [PivotTable Analyze tab, Calculations group] and choose Calculated Field.
84.Edit the Name to display 20% Increase.
85.Delete the zero in the Formula box and double-click Patients in the Fields list.
86.Type *120% after Patients to multiply by 120%.
87.Click OK. The field displays in the PivotChart also.
88.Complete formatting.
89.Edit the label in cell B2 to display # of Patients.
90.Edit the label in cell C2 to display Projected 20% Increase.
91.Format the values in column C for zero decimal places, and AutoFit columns.
Show the legend at the Bottom. Increase its font size to 11.
92.Format the 20% Increase bars to use Green, Accent 6, Lighter 80% fill.
93.Format the # of Patients bars to use Dark Teal, Accent 1, Lighter 60%.
94.Apply a Round Bevel to both series.
95.Save and close the Excel workbook (Figure 4-116).
Excel 4-3 completed
Figure 4-116 Excel 4-3 completed
96.Upload and save your project file.
97.Submit file for grading.