Domain 2, Lesson 1: Manipulate Data In Worksheet

1 Special Paste Options
Copy the totals from row 38 on the January worksheet and paste just the values into cells B38 through M38 on the Forecasting worksheet.

1. If necessary, click the January worksheet tab
2. Select cells B38 through M38
3. In the Home tab, Clipboard group, click the Copy button
4. Click the Forecasting worksheet tab
5. Click cell B38
6. In the Clipboard group, click the Paste drop-down arrow
7. Click the Paste Values option

2 Autofill
Use AutoFill to add 12 months to row 2. Then, use AutoFill to fill the gym membership price of $10 a month through the month of December.

1. Click cell D2
2. Use the AutoFill feature to fill the cell values through cell P2
3. Click cell D3
4. Use the AutoFill feature to fill the cell values through cell M3

3 Insert Multiple Rows And Columns
Insert two columns between December and January. Then, insert two rows below the top row on the worksheet.

1. Click and drag to select columns N and O
2. In the Home tab, Cells group, click the Insert drop-down button
3. Click Insert Sheet Columns
4. Click and drag to select rows 2 and 3
5. In the Cells group, click the Insert drop-down button
6. Click Insert Sheet Rows

4 Insert And Delete Cells
Insert a cell above cell A7. Then, delete cell B2, shifting the remaining cells upward.

1. Ensure cell A7 is selected
2. In the Home tab, Cells group, click the Insert drop-down button
3. Click Insert Cells
4. Ensure the Shift cells down option is selected
5. Click the OK button
6. Click cell B2
7. In the Cells group, click the Delete drop-down button
8. Click Delete Cells
9. Ensure the Shift cells up option is selected
10. Click the OK button

Domain 2, Lesson 2: Format Cells & Range

​5 Merging Cells
Merge and center the Student Budget heading across columns A through M.

1. Click and drag to select cells A1 through M1
2. In the Home tab, Alignment group, click the Merge & Center button

6 Alignment, Orientation and Indentation
Center-align the Month heading in cell A3. Indent the Total text in cell A8. Set the angle for the text in cell B3 to 30 degrees.

1. Click cell A3
2. In the Home tab, Alignment group, click the Center option
3. Click cell A8
4. In the Alignment group, click the Increase Indent button
5. Click cell B3
6. Click the Alignment dialog box launcher
7. Click and drag the text orientation angle to 30 degrees
8. Click the OK button

7 Format Painter
Paint the format of cell B3 to cells C3 and D3.

1. Ensure cell B3 is selected
2. In the Home tab, Clipboard group, click the Format Painter option
3. Click and drag across cells C3 and D3

8 Wrap Text Within Cells
Set the cells in column A to allow for wrapping the text onto multiple lines.

1. Click the A to select column A
2. In the Home tab, Alignment group, click Wrap Text

9 Format Numbers
Change the format for the numbers in cells B4 through M6 to show with dollar signs and decimal points with two numbers after the decimal point.

1. Select cells B4 through M6
2. In the Home tab, Number group, click the $

10 Format Cells
Format the Student Budget heading as follows: Arial font, size 14, dark blue text, and a dark blue outline border.

1. Select cell A1
2. In the Home tab, click the Font dialog box launcher
3. In the Format Cells window, Font tab under Font select Arial
4. Under Size select 14
5. Click the Color drop-down arrow and select Dark Blue
6. Navigate to the Border tab
7. Click the Color drop-down arrow select Dark Blue
8. Under Presets select Outline
9. Click the OK button

11 Cell Styles
Apply the 20% - Accent1 themed cell style to the categories and monthly amounts on the current worksheet. Then, apply the Note style to the contents of cell A12.

1. Select cells A4 through M6
2. In the Home tab, Styles group, click the Cell Styles drop-down arrow
3. Click the 20% - Accent1 style
4. Click cell A12
5. In the Styles group, click the Cell Styles drop-down arrow
6. Click the Note style

12 Clear Cell Formatting
Clear the formatting from the months on the current worksheet.

1. Select cells B3 through M3
2. In the Home tab, Editing group, click the Clear drop-down arrow
3. Click Clear Formats

Domain 2, Lesson 3: Define & Reference Named Ranges; Summarize Data

13 Define A Named Range
Assign a named range named, TaxRate, to cell Q2.

1. Click in cell Q2
2. Click in the Name box
3. Type: TaxRate
4. Press the Enter key

14 Name A Table
On the Table worksheet, assign the name, Budget, to the table on the worksheet.

1. Click the Table worksheet
2. Click anywhere inside the table
3. Click the Design tab under Table Tools
4. In the Properties group, select the current table name
5. Type: Budget
6. Press the Enter key

15 Add A Sparkline
In cell F3, add a line-based sparkline that plots the categories for the Clearwater location.

1. Click in cell F3
2. Click the Insert tab
3. In the Sparklines group, click Line
4. Select cells B3 through E3 to populate the Data Range field
5. Click the OK button

16 Conditional Formatting
Format the numbers for each category and city such that any number over 100 has a green fill with dark green text.

1. Select cells B3 through E6
2. In the Home tab, Styles group, click the Conditional Formatting drop-down button
3. Hover mouse over Highlight Cells Rules
4. Click Greater Than
5. Type: 100
6. Click the Format drop-down arrow
7. Click Green Fill with Dark Green Text
8. Click the OK button

​​17 Remove Conditional Formatting
Remove all conditional formatting from the current worksheet.

1. In the Home tab, Styles group, click the Conditional Formatting drop-down button
2. Hover the mouse over Clear Rules
3. Click Clear Rules from Entire Sheet