A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | last updated on: | Monday, July 31, 2023 | 20.00 | Comments | ||||||||||||||||||||||
2 | Why we do this | What we are to do | Component | Standard | Points Earned | |||||||||||||||||||||
3 | Workbook and worksheet setup | |||||||||||||||||||||||||
4 | to create a new workbook from existing worksheets | Open 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 name | 100 | 0.10% | 0.1 | |||||||||||||||||||||
5 | to demonstrate cell formatting skills for display purposes | On 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) | 100 | 0.30% | 0.3 | |||||||||||||||||||||
6 | where you can, to make the sheets look better | On 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 examples | 100 | 0.30% | 0.3 | |||||||||||||||||||||
7 | to demonstrate cell formatting skills based on the type of data the cells contain | On 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. | 100 | 0.30% | 0.3 | |||||||||||||||||||||
8 | to demonstrate understanding of how and why to freeze panes | On the 01-Format worksheet, freeze the appropriate cell so that the header row always appears and the FIPS and State columns always appear | 100 | 0.50% | 0.5 | |||||||||||||||||||||
9 | to validate data in a column | On 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 9 | 100 | 0.50% | 0.5 | |||||||||||||||||||||
10 | Set up skills | % of task grade | 2.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 page | Set up the 01-Format worksheet so that the appropriate header rows will display on each printed page. | 100 | 0.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 page | On 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. | 100 | 0.35% | 0.35 | |||||||||||||||||||||
15 | to include headers on each worksheet page | On all the worksheets together, use a header to include the worksheet tab name centered and bold on the top of each printed page. | 100 | 0.35% | 0.35 | |||||||||||||||||||||
16 | to demonstrate facility with placing the data on the printed page | On all the worksheets together, set the margins for 1 inch top and bottom, left and right, and .5 inches for header and footer | 100 | 0.10% | 0.1 | |||||||||||||||||||||
17 | to demonstrate facility with basic page layout | On 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. | 100 | 0.10% | 0.1 | |||||||||||||||||||||
18 | Page Setup Skills | % of task grade | 1.40% | 1.4 | ||||||||||||||||||||||
19 | Conditional Formatting | |||||||||||||||||||||||||
20 | to highlight a value in the data | On 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 color | 100 | 0.50% | 0.5 | |||||||||||||||||||||
21 | Conditional formatting skills | % of task grade | 0.50% | 0.5 | ||||||||||||||||||||||
22 | Hiding Data | |||||||||||||||||||||||||
23 | to demonstrate how and why to hide data | On the 03-Hide Column worksheet, hide columns B and F | 100 | 0.01% | 0.01 | |||||||||||||||||||||
24 | to demonstrate how and why to hide data | On the 04-Hide Row worksheet, hide row 63 | 100 | 0.01% | 0.01 | |||||||||||||||||||||
25 | Hiding data skills | % of task grade | 0.02% | 0.02 | ||||||||||||||||||||||
26 | Functions & Formulas | |||||||||||||||||||||||||
27 | to create new worksheets | Insert a new worksheet, place it fifth in the sequence of worksheets, and name it 05-Summary. | 100 | 0.10% | 0.1 | |||||||||||||||||||||
28 | to demonstrate facility with functions and formatting of the resultant data | On 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) | 100 | 0.90% | 0.9 | |||||||||||||||||||||
29 | to demonstrate facility with functions and formatting of the resultant data | On 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. | 100 | 0.90% | 0.9 | |||||||||||||||||||||
30 | to demonstrate facility with functions | On 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 function | 100 | 1.00% | 1 | |||||||||||||||||||||
31 | to demonstrate facility with formulas, by pulling vectored data from two different worksheets into a new formula | Copy 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). | 100 | 0.60% | 0.6 | |||||||||||||||||||||
32 | to demonstrate cell formatting skills based on the type of data the cells contain | Once you have created the formula, format the cell to display percentages to two decimal places. | 100 | 0.20% | 0.2 | |||||||||||||||||||||
33 | to demonstrate understanding of how and why to drag formatting information from one cell to another | Once you have formatted the data in the cell, drag the cell down the column to apply the same formula and formatting for all library systems | 100 | 0.20% | 0.2 | |||||||||||||||||||||
34 | to demonstrate how to look up values in a separate worksheet | use 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. | 100 | 0.20% | 0.2 | |||||||||||||||||||||
35 | Functions, formulas, and query skills | % of task grade | 4.00% | 4.1 | ||||||||||||||||||||||
36 | Sorting Data | |||||||||||||||||||||||||
37 | to do a single level sort | Sort 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. | 100 | 1.10% | 1.1 | |||||||||||||||||||||
38 | to do a multi-level sort | Sort 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. | 100 | 1.20% | 1.2 | |||||||||||||||||||||
39 | Sorting Skills | % of task grade | 2.30% | 2.3 | ||||||||||||||||||||||
40 | Filtering Data | |||||||||||||||||||||||||
41 | to demonstrate facility with a number filter | Filter the data on the 10-Filter One Field worksheet to find the top ten values in the FY 2021 (Base + OCO) column | 100 | 1.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 10 | 100 | 2.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 ForeignBornPct | 100 | 2.00% | 2 | |||||||||||||||||||||
44 | Filtering Skills | % of task grade | 5.00% | 5 | ||||||||||||||||||||||
45 | Pivot Table | |||||||||||||||||||||||||
46 | to demonstrate the ability to create a pivot table | In 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. | 100 | 0.10% | 0.1 | |||||||||||||||||||||
47 | Pivot Table skills | % of task grade | 0.10% | 0.1 | ||||||||||||||||||||||
48 | Data Display | |||||||||||||||||||||||||
49 | to create a chart that compares one data set by category | Using 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. | 100 | 1.70% | 1.7 | 15-Chart 01 | ||||||||||||||||||||
50 | to demonstrate facility with the chart formatting tools | You 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 labeling | 100 | 0.30% | 0.3 | |||||||||||||||||||||
51 | to demonstrate facility with the chart formatting tools | Include a chart title and modify the display in some fashion to make it unique to your style. | 100 | 0.20% | 0.2 | |||||||||||||||||||||
52 | to create a chart that compares multiple data sets by category | Use 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. | 100 | 1.90% | 1.9 | 16-Chart 02 | ||||||||||||||||||||
53 | to demonstrate facility with the chart formatting tools | Make 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) | 100 | 0.30% | 0.3 | |||||||||||||||||||||
54 | to annotate the chart using a drawing object | Annotate the chart by including a free-floating object to point out the state who has the highest number of Asian, non-Hispanic veterans | 100 | 0.10% | 0.1 | |||||||||||||||||||||
55 | to demonstrate facility with the chart formatting tools | put 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. | 100 | 0.08% | 0.08 | |||||||||||||||||||||
56 | Data display skills | % of task grade | 4.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 |