ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
last updated on:Monday, July 31, 202320.00Comments
2
Why we do thisWhat we are to doComponentStandardPoints Earned
3
Workbook and worksheet setup
4
to create a new workbook from existing worksheetsOpen the workbook named task04.datasets.xlsx and save all the worksheets to a new workbook named with a standard (i.e., yourname.YYYYMMDD.task04.xlsx) file name1000.10%0.1
5
to demonstrate cell formatting skills for display purposesOn the 01-Format worksheet, format row 1 with blue fill, 16 point Century Gothic font, but only for those cells that have data below them. For example on this specifications sheet, only columns A through F have data below them, so row 1 is formatted with a fill only over columns A1 through F1)1000.30%0.3
6
where you can, to make the sheets look betterOn the 01-Format worksheet, format the header row so that there is a visible color difference between the header descriptors in row 02 and the data that lies in all the rows below row 01. Look at the worksheets in the workbook named task04.datasets.xlsx for examples1000.30%0.3
7
to demonstrate cell formatting skills based on the type of data the cells containOn the 01-Format worksheet, format the cells that shows your home county name in column C with a hexidecimal #4b9cd3 color and a white font color (if you are from Bexar County, TX, for instance, your cell is C2543). If you have no home county in the US, pick any county name.1000.30%0.3
8
to demonstrate understanding of how and why to freeze panesOn the 01-Format worksheet, freeze the appropriate cell so that the header row always appears and the FIPS and State columns always appear1000.50%0.5
9
to validate data in a columnOn the 01-Format worksheet, validate the data in column D so that new data added to this column must be less than or equal to 91000.50%0.5
10
Set up skills% of task grade2.00%2
11
Page Setup
12
to set up the printed versions of the workbook so that the header rows will display on each printed pageSet up the 01-Format worksheet so that the appropriate header rows will display on each printed page. 1000.50%0.5
13
Suggestion: Wait until you have all your worksheets done, then group them and do the next four things to all of them at the same time.
14
to include footers on each worksheet pageOn all the worksheets together, use a footer to include your name on the left and the date on the right on each page in the workbook.1000.35%0.35
15
to include headers on each worksheet pageOn all the worksheets together, use a header to include the worksheet tab name centered and bold on the top of each printed page.1000.35%0.35
16
to demonstrate facility with placing the data on the printed pageOn all the worksheets together, set the margins for 1 inch top and bottom, left and right, and .5 inches for header and footer1000.10%0.1
17
to demonstrate facility with basic page layoutOn all the worksheets together, ensure that all pages are laid out in landscape orientation for printing on letter sized paper and that the page order is over, then down.1000.10%0.1
18
Page Setup Skills% of task grade1.40%1.4
19
Conditional Formatting
20
to highlight a value in the dataOn the 02-Conditional Formatting worksheet, use the conditional formatting tool on column G to have all the cells that values less than 1789 to display with a blue fill and a white font color1000.50%0.5
21
Conditional formatting skills% of task grade0.50%0.5
22
Hiding Data
23
to demonstrate how and why to hide dataOn the 03-Hide Column worksheet, hide columns B and F1000.01%0.01
24
to demonstrate how and why to hide dataOn the 04-Hide Row worksheet, hide row 631000.01%0.01
25
Hiding data skills% of task grade0.02%0.02
26
Functions & Formulas
27
to create new worksheetsInsert a new worksheet, place it fifth in the sequence of worksheets, and name it 05-Summary. 1000.10%0.1
28
to demonstrate facility with functions and formatting of the resultant dataOn the 05-Summary worksheet, in cell B4 enter the words "Highest Foreign Born Percentage in Alabama counties." In cell K4, insert a function that will return the highest ForeignBornPct from the data about Alabama counties (rows AI4 through AI71) on the 01-Format worksheet. (the cell references are A"eye"4 through A"eye"71)1000.90%0.9
29
to demonstrate facility with functions and formatting of the resultant dataOn the 05-Summary worksheet, in cell B5 enter the words "Earliest Foundation Date." In cell K5 insert a function that will return the foundation date from the data in the FC = Foundation century column on the 02-CONDITIONAL FORMATTING worksheet.1000.90%0.9
30
to demonstrate facility with functionsOn the 05-Summary worksheet, in cell B6 enter the words "Number of Navy programs in North Carolina". In cell K6, display the number of programs for which the Navy is the Organization and North Carolina is the State Country Title. Find the data on the 15-PIVOT TABLE DATA worksheet. Use your help tool to find the right function1001.00%1
31
to demonstrate facility with formulas, by pulling vectored data from two different worksheets into a new formulaCopy cells A5 through A56 from the 16-CHART 02 worksheet.
Paste these cells into cells B7 through B58 on the 05-SUMMARY worksheet. Then, in cell D7, insert a formula that will draw values from two other worksheets. The formula should divide Alabama's "Y1991" values from the 17-MEDICAID EXPENDITURES worksheet (cell G2) by Alabama's "Y1991" from the 18-MEDICAID AGGREGATE worksheet (cell G10).		1000.60%0.6
32
to demonstrate cell formatting skills based on the type of data the cells containOnce you have created the formula, format the cell to display percentages to two decimal places.1000.20%0.2
33
to demonstrate understanding of how and why to drag formatting information from one cell to anotherOnce you have formatted the data in the cell, drag the cell down the column to apply the same formula and formatting for all library systems1000.20%0.2
34
to demonstrate how to look up values in a separate worksheetuse a lookup table on the 06 -CALENDARS worksheet to populate data in column D on 07 - FALL 2020 CLASS SCHEDULE worksheet
select cell D2 on 06 - CALENDARS
in the formula bar , insert a VLOOKUP function so that the value in this cell equals the value you will find when you use this function to compare the value in the same line and one cell to the right on this worksheet, to the values in the array of cells on the 06 - Calendars worksheet in the absolute range of cells from cell F4 through G11, where it will look for a value that matches the value in cell E2 in this worksheet in column cells F4 through F11 on the 06 - Calendars worksheet, and find the value that resides in the related cell G4 through F11 on the 06 - Calendars worksheet, and insert that value back into this cell. Once you have the value in cell D2, you can use the fill handle and drag down the function from D2 to D84.		1000.20%0.2
35
Functions, formulas, and query skills% of task grade4.00%4.1
36
Sorting Data
37
to do a single level sortSort only the data rows on the 08-Single Level Sort worksheet by Population Estimate as of July 1 - 2016.
Have the sort display from smallest to the largest.		1001.10%1.1
38
to do a multi-level sortSort only the data rows on the 09-Two Level Sort worksheet
first by German Champion, from largest to smallest,
and then by Level from smallest to largest.		1001.20%1.2
39
Sorting Skills% of task grade2.30%2.3
40
Filtering Data
41
to demonstrate facility with a number filterFilter the data on the 10-Filter One Field worksheet to find the top ten values in the FY 2021 (Base + OCO) column1001.00%1
42
to demonstrate facility with a number filter, after having used an exclusion filter to show only one subset of data Use a multilevel filter on the 11-Multi Level Filter 01 worksheet to find out which of the properties managed by the National Park Service Reporting Bureau had a Number of Federal Employees greater than 101002.00%2
43
to demonstrate facility with a number filter, after having used an exclusion filter to show only one subset of data Use a multilevel filter on the 12-Multilevel Filter 02 worksheet to find out which of counties in the State of North Carolina had an above average ForeignBornPct1002.00%2
44
Filtering Skills% of task grade5.00%5
45
Pivot Table
46
to demonstrate the ability to create a pivot tableIn the 13-pivot table data select the data in columns A through R, and rows 1 through 204.
On the ribbon, select Insert, and then click the PivotTable Button. A create PivotTable dialog box will open. Verify that the Table/Range shown in the Select a table or range Table/Range box is '13-pivot table data'!$A$1:$R$204
Verify that New Worksheet is selected and click okay. A new sheet will be created. Rename the sheet 14-PIVOT TABLE REPORT.		1000.10%0.1
47
Pivot Table skills % of task grade0.10%0.1
48
Data Display
49
to create a chart that compares one data set by categoryUsing the data on the 15-Chart 01 worksheet, create a chart to display theFood grain demand, Other grain demand, Total grain demand, Grain production, and Implied additional supply required for the year 2031 for any Sub-Region you are interested in. Use a chart that compares values (Millions of metric tons) to categories (Element).
Place the chart on a separate worksheet (this means the chart stands alone on a worksheet and does not float above a grid of cells on a worksheet) named 15.Food Demand. You may use the chart wizard, but do not use the defaults. 		1001.70%1.715-Chart 01
50
to demonstrate facility with the chart formatting toolsYou will need to work with the chart tools to ensure that the X or Y axes (depending on whether you use a column or a bar chart) are labeled. Depending on how you build your chart, they may not need labeling1000.30%0.3
51
to demonstrate facility with the chart formatting toolsInclude a chart title and modify the display in some fashion to make it unique to your style.1000.20%0.2
52
to create a chart that compares multiple data sets by categoryUse the data from the 16-Chart 02 worksheet, we want to create a chart that compares the values in columns E, G, I, K, M, O, Q, and S to the categories in column A. Do not include the totals on row 57.
Use a chart that compares values to categories. We want to see the relative amount of each category of values in each state, so this will be a stacked chart of some type. Place the chart on a separate worksheet (this means the chart stands alone on a worksheet and does not float above a grid of cells on a worksheet) named 16-Veterans.Use any appropriate chart style that pleases you, but again here, do not settle for the defaults. Customize the chart in some fashion. 		1001.90%1.916-Chart 02
53
to demonstrate facility with the chart formatting toolsMake it very clear in the formatting which values are which. If you use a legend, be sure to have the legend pull its name from the appropriate cell in the worksheet. Format your axes and axes labels so that the viewer is in no doubt about what is being depicted. (If the data format is self-explanatory, you may not need a label for an axis)1000.30%0.3
54
to annotate the chart using a drawing objectAnnotate the chart by including a free-floating object to point out the state who has the highest number of Asian, non-Hispanic veterans1000.10%0.1
55
to demonstrate facility with the chart formatting toolsput the legend on top of the chart and it may overlap the data, but should not cover any important things that need to be seen.1000.08%0.08
56
Data display skills% of task grade4.58%4.58
57
Points for Task 04
20
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100