Final Assignment Data.xlsx Download Final Assignment Data.xlsxincludes a summary of monthly US energy statistics since January $1973.$ These numbers show the equivalent trillion BTU of energy consumed each month.

$1.$ Clean up the formatting on the "Energy Overview" worksheet:

Make row $1$ height equal to $45$

Make all text in row $1$ bold, font $10$

For row $1,$ wrap text, center and middle align $($both top to bottom and left to right$)$

Add a bottom border to all cells that contain headers in row $1$

Make all column widths $15$

These should be the only formats applied to the "Energy Overview" worksheet

$($Module $1)$

$2.$ Using the data from Energy Overview, copy a single line chart titled "Imports and Exports" that contains Primary Energy Imports, Primary Energy Exports, and Primary Energy Net Imports over the sheet titled $"2."$ The $\text{'}$Y$\text{'}$ axis should be import$/$export amounts. The $\text{'}$X$\text{'}$ axis will be months since December, $1972.$

$($Module $2)$

$3.$ On the "Energy Overview" worksheet, apply conditional formatting according to the criteria and the associated formatting:

Fossil Fuels, column C: Top $20\%$

Green Fill with Dark Green Text

Nuclear Electric Power, column D: Above Average

Yellow Fill with Dark Yellow Text

Total Renewable Energy, column E: All values between $0\mathrm{.}4$ and $0\mathrm{.}45$

Light Red Fill with Dark Red Text

$($Module $2)$

$4.$ In column N of the "Energy Overview" sheet, create a formula that returns $$Consumption Up$$ if in a given month Primary Energy Stock Change is negative and Total Fossil Fuel Consumption is greater than the previous month $($start in row $3,[$February $1973]).$ Otherwise return $$Consumption Down$.$ Use "Text that Contains..." conditional formatting to apply light red fill to the results where consumption is up and light green fill for months where consumption is down.

$($Module $5)$

$5.$ Use the advanced filter to show only records according to the criteria below. Build the criteria range in cells V$1$:X$3$ and select the copy to a new location designation to output the filtered results to cell Z$1.$

Total Fossil Fuels Consumption is greater than $6$ AND Total Renewable Energy Consumption is greater than $0\mathrm{.}9$

OR Nuclear Electric Power is greater than $0\mathrm{.}8.$

$($Module $6)$

$6.$ Use the SUMIF function in cell range Q$2$:T$13$ of the "Energy Overview" sheet to find the total of the variables in row $1$ associated with each corresponding month. Use mixed referencing in such a way that the formula you create in cell Q$2$ may be copied to all cells in the Q$2$:T$13$ range and will output the correct answers.

$($Module $2)$

$7.$ On worksheet $7,$ use the VLOOKUP function in cell range B$2$:E$8$ to look up the observation from the data set in the "Energy Overview" sheet that corresponds to the associated variables in row $1$ and the months in column A$.$ Use the VLOOKUP function in cell range G$2$:G$8$ to return the proper Energy Price Index from the table to the right $($cell range I$1$:I$13).$

$($Module $8)$