Domain 1: Manage Worksheets & Workbooks

Domain 1 Lesson 1
​Introduction
(Introduction lesson...no skills to be learned)

​Domain 1 Lesson 2
​Import Data & Navigate Workbooks

1 Importing from a Text File

Open the 111-instructors.txt file from the GMetrixTemplates folder. Indicate that the data has headers. Tabs are the delimiters.

1. Click the File tab
2. Ensure the Open tab is selected
3. Browse to the GMetrixTemplates folder
4. Click the File type drop-down arrow
5. Click All Files
6. Click the 111-instructors.txt file
7. Click the Open button
8. Ensure that the My data has headers check box is selected
9. Click the Next button
10. Ensure that the Tab check box is the only delimiter check box selected
11. Click the Next button
12. Click the Finish button

2 Importing from a .csv File

Import the 112-class schedule.csv file from the GMetrixTemplates folder as a table to the current worksheet.

1. Click the Data tab
2. In the Get & Transform Data group, click From Text/CSV
3. Browse to the GMetrixTemplates folder
4. Click the 112-class schedule.csv file
5. Click the Import button
6. In the preview window, click the Load dropdown and select Load To...
7. Ensure that Table is selected under Select how you want to view this data in your workbook.
8. Under Where do you want to put the data?, select Existing worksheet
9. Click OK

3 Search for Data

Find all instances of the word, Yoga, on the class schedule worksheet. Then, change the first instance of the word, Yoga, to Hot Yoga.

1. In the Home tab, Editing group, click the Find & Select drop-down arrow
2. Click Find
3. Type: yoga
4. Click the Find All button
5. Click the first instance of the word, Yoga (it will take you to cell A3)
6. Change the contents of cell A3 to the words, Hot Yoga
7. Press the Enter key

4 Workbook Navigation

​Navigate to the Instructor4 named range. Then, change the contents of the cell to Charles.

1. In the Home tab, Editing group, click the Find & Select drop-down arrow
2. Click Go To
3. Click Instructor4
4. Click the OK button
5. Click into cell A5
6. Change the instructor name in cell A5 to Charles
7. Press the Enter key

​Domain 1 Lesson 3
Format Worksheets & Workbook

​​5 Page Setup
For this worksheet, set the page margins to wide and the orientation to landscape.

1. Click the Page Layout tab
2. In the Page Setup group, click the Margins drop-down arrow
3. Click Wide
4. In the Page Setup group, click the Orientation drop-down arrow
5. Click Landscape

6 Row Height and Column Width

On the current worksheet, set the row height for row 2 to 30 and the width of columns F and G to 12.

1. Click the number 2 in the rows to select the entire row
2. Click the Home tab (if necessary)
3. In the Cells group, click the Format drop-down arrow
4. In the Cell Size section, click Row Height
5. Type: 30
6. Click the OK button
7. Click and drag over the letters F and G to select columns F and G
8. In the Cells group, click the Format drop-down arrow
9. In the Cell Size section, click Column Width
10. Type: 12
11. Click the OK button

7 Headers and Footers

​Add the file name as a header to the current worksheet. Then, add the current date to the left side of the footer.

1. Click the Page Layout tab
2. Click the Page Setup dialog box launcher
3. Click the Header/Footer tab
4. Click the Header drop-down arrow
5. Click the file name (133-classes by month.xlsx)
6. Click the Custom Footer button
7. Ensure the cursor is in the Left section
8. Click the Date icon
9. Click the OK button twice

Domain 1 Lesson 4
​Customize Options & Views

8 Quick Access Toolbar
Add the Fill Color icon to the Quick Access Toolbar.

1. In the Home tab, Font group, right-click the Fill Color button
2. Click Add to Quick Access Toolbar

9 Workbook Views
View the current worksheet in Page Layout view. While in Page Layout view, add the sheet name to the right-side header.

1. Click the View tab
2. In the Workbook Views group, click the Page Layout button
3. Click in the right side of the header
4. Click the Design tab under Header & Footer Tools (if necessary)
5. In the Header & Footer Elements group, click the Sheet Name button
6. Click anywhere on the worksheet

10 Freezing Panes
Freeze the top two rows of the current worksheet.

1. Select row 3
2. Click the View tab
3. In the Window group, click the Freeze Panes drop-down arrow
4. Click Freeze Panes

11 Window Views
Split the worksheet into two sections between rows 11 and 12.

1. Select row 12
2. Click the View tab
3. In the Window group, click Split

12 Workbook Properties
Add "fitness; classes; location" as tags to the current workbook. Then, add Uptown Athletic Club as the company for this workbook.

1. Click the File tab
2. Click Add a tag
3. Type: fitness; classes; location
4. Click Show All Properties
5. Click Specify the company
6. Type: Uptown Athletic Club
7. Press the Enter key

13 Display Formulas
​Display all of the formulas on the current worksheet.

1. Click the Formulas tab
2. In the Formula Auditing group, click Show Formulas

Domain 1 Lesson 5
​Configure Content For Collaboration

14 Set a Print Area
Set the cells A1 through M17 to be the print area for this worksheet.

1. Select cells A1 through M17
2. Click the Page Layout tab
3. In the Page Setup group, click the Print Area drop-down arrow
4. Click Set Print Area

15 Save in Alternative File Formats
Save the current file as a PDF to the GMetrixTemplates folder. Accept the default options for the file name and settings.

1. Click the File tab
2. Click Save As
3. Click Browse
4. If necessary, navigate to the GMetrixTemplates folder
5. Click the Save as type drop-down arrow
6. Click PDF
7. Click the Save button

16 Print Settings
Set the current worksheet to repeat the top two rows and the first column to repeat on every page when the worksheet is being printed.

1. Click the Page Layout tab
2. In the Page Setup group, click Print Titles
3. Click in the Rows to repeat at top field
4. In the worksheet select rows 1 and 2
5. Click in the Columns to repeat at left field
6. In the worksheet select column A
7. Click the OK button

17 Inspect Workbooks
​Perform an accessibility check on the current workbook. Rename the first warning instance to January and the second to Forecasting.

1. Click the File tab
2. Click the Check for Issues drop-down button
3. Click Check Accessibility
4. In the Accessibility Checker panel expand the Default Sheet Names warning
5. Select Sheet1 and then the Sheet1 dropdown
6. Under Recommended Actions select Rename Sheet
7. Type: January
8. Press the Enter key
9. Back in the Accessibility Checker panel select Sheet2 and then the Sheet2 dropdown
10. Under Recommended Actions select Rename Sheet
11. Type: Forecasting
12. Press the Enter key

Domain 2: Manage Data Cells & Ranges

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

Domain 3: Manage Tables & Table Data

Domain 3, Lesson 1
Create & Format Table

1 Create A Table
Using the locations and data, create a table with headers consisting of the data in row 2.

1. Select cells A2 through E11
2. Click the Insert tab
3. In the Tables group, click Table
4. Ensure the My table has headers check box is selected
5. Click the OK button

2 Apply A Table Style
Apply the Blue, Table Style Medium 20 style to the table on the current worksheet.

1. Click anywhere within the table
2. Click the Design tab under Table Tools
3. In the Table Styles group, click the Table Styles drop-down arrow
4. In the Medium section, click the Blue, Table Style Medium 20 style

3 Convert A Table To A Cell Range
Convert the table on this worksheet to a range of cells.

1. Click anywhere within the table
2. Click the Design tab under Table Tools
3. In the Tools group, click Convert to Range

​Domain 3, Lesson 2
​Modify, Filter & Sort Table Data

4 Add Rows And Columns To A Table
In the table on the current worksheet, add a column with the name, Item Type, in between the Item and Clearwater columns. Then, add a row with an item name of Dumbbells in between the rows for Climbers and Treadmills.

1. Right-click any cell between B3 and B11
2. Point to Insert
3. Click Table Columns to the Left
4. Click in cell B2
5. Type: Item Type
6. Right-click any cell between A8 and F8
7. Point to Insert
8. Click Table Rows Above
9. Click in cell A8
10. Type: Dumbbells
11. Press the Enter key or click away from cell A8

5 Table Style Options
Apply the First Column and Banded Columns table style options to the table on the current worksheet. Turn the Banded Rows option off.

1. Click anywhere within the table
2. Click the Design tab under Table Tools
3. In the Table Style Options group, select the First Column check box
4. In the Table Style Options group, select the Banded Columns check box
5. In the Table Style Options group, clear the Banded Rows check box

6 Total Rows In Table
Using the Totals feature for tables, generate totals for columns C through F within the table on the current worksheet.

1. Click anywhere within the table
2. Click the Design tab under Table Tools
3. In the Table Style Options group, select the Total Row check box
4. Click cell C13
5. Click the drop-down arrow within the cell
6. Click Sum
7. Click cell D13
8. Click the drop-down arrow within the cell
9. Click Sum
10. Click cell E13
11. Click the drop-down arrow within the cell
12. Click Sum

7 Filter Records 
In the table on the current worksheet, filter the records to display records with an item type of Machine.

1. Click the Filter drop-down arrow in cell B2
2. Clear the Select All check box
3. Select the Machine check box
4. Click the OK button

8 Sort Records In Table
Sort the table on the current worksheet by Item Type and then by Item. Use ascending order for both sorts.

1. Right-click anywhere within the table
2. Point to Sort
3. Click Custom Sort
4. Click the Sort by drop-down arrow
5. Click Item Type
6. Click the Add Level button
7. Click the Then by drop-down arrow
8. Click Item
9. Click the OK button

Domain 4: Perform Operations By Using Formulas And Functions

Domain 4 Lesson 1
​Insert References, Calculate & Transform Data

1 Basic Formulas
​Calculate the total for cells B4, B5, and B6 in cell B8. AutoFill that total through cell M8. Then, in cell B9, calculate the sales tax through the rate in cell Q2. AutoFill those values through to cell M9.

1. Click in cell B8
2. Type: =B4+B5+B6
3. Click the fill handle in cell B8 and drag it through cell M8
4. Click in cell B9
5. Type: =B8*$Q$2
6. Click the fill handle in cell B9 and drag it through cell M9

2 Named Ranges In Formulas
​In cell B9, multiply the contents of cell B8 and the named range, TaxRate. Then, AutoFill the formula to cell M9.

1. Click in cell B9
2. Type: =B8 * TaxRate
3. Click the fill handle in cell B9 and drag it through cell M9

3 Basic Functions
Below each of the headings for Sum, Average, Min, and Max, add functions to get the sum, average, maximum, and minimum values for cells B6 through M6. Then, AutoFill the results down through all of the morning classes.

1. Click in cell O6
2. Type: =SUM(B6:M6)
3. Click in cell P6
4. Type: =AVERAGE(B6:M6)
5. Click in cell Q6
6. Type: =MIN(B6:M6)
7. Click in cell R6
8. Type: =MAX(B6:M6)
9. Select cells O6 through R6
10. Click the fill handle and drag down through row 20

4 Count Functions
​In cell S6, use a function to display a count of the cells from B6 through M6 that contain a number. In cell T6, use a function to display a count of the cells from B6through M6 that contain nothing. AutoFill both cells down through all of the morning classes.

1. Click in cell S6
2. Type: =COUNT(B6:M6)
3. Click in cell T6
4. Type: =COUNTBLANK(B6:M6)
5. Select cells S6 and T6
6. Click the fill handle and drag down through row 20

5 If Functions
In cell Q2, insert a function that displays a YES if the total number of morning classes for January is at least 175 and a NO if not.

1. Click in cell Q2
2. Click the Formulas tab
3. In the Function Library group, click the Logical drop-down arrow
4. Click IF
5. In the Logical_test field type: SUM(S6:S20)>=175
6. Click in the Value_if_true field
7. Type: YES
8. Click in the Value_if_false field
9. Type: NO
10. Click the OK button

Domain 4 Lesson 2
​Format & Modify Text​

6 Text Functions
In cell D3, add a function to get the two leftmost numbers from cell B3. In cell E3, add a function to get the middle three numbers from cell B3. In cell F3, add a function to get the two rightmost numbers from cell B3. AutoFill the three results down through the remaining products.

1. Click in cell D3
2. Type: =LEFT(B3,2)
3. Click in cell E3
4. Type: =MID(B3,4,3)
5. Click in cell F3
6. Type: =RIGHT(B3,2)
7. Select cells D3 through F3
8. Click the fill handle and drag through row 8

7 Capitalization
Use the appropriate formulas and Autofill to display the names from cells A2 through B4 in cells D2 through E4, using all capital letters. Display the names from cells A2 through B4 in cells G2 through H4, using all lowercase letters. Use cells J2 through J4 to display the length of the text in cells A2 through A4.​

1. Click in cell D2
2. Type: =UPPER(A2)
3. Click the fill handle and drag to cell E2
4. Click the fill handle and drag to cell E4
5. Click in cell G2
6. Type: =LOWER(A2)
7. Click the fill handle and drag to cell H2
8. Click the fill handle and drag to cell H4
9. Click in cell J2
10. Type: =LEN(A2)
11. Click the fill handle and drag to cell J4

​8 Combining Text
​Display the combined first and last name from cells A2 and B2 in cell L2, adding a space in between the first and last name. Fill those values for the other two names. In cell N2, combine the values of cells H2 and G2 with a delimiter of a comma and ignoring empty cells. Fill those values for the other two names.

1. Click in cell L2
2. In the Formulas tab, Function Library group, click the Text drop-down arrow
3. Click CONCAT
4. Click cell A2 to populate the Text1 field
5. Click in the Text2 field
6. Type: " "
7. Click in the Text3 field
8. Click cell B2
9. Click the OK button
10. Ensure that cell L2 is still selected and click the fill handle and drag to cell L4
11. Click in cell N2
12. In the Formulas tab, Function Library group, click the Text drop-down arrow
13. Click TEXTJOIN
14. In the Delimiter field type:","
15. Click in the Ignore_empty field
16. Type: TRUE
17. Click in the Text1 field
18. Type: H2
19. Click in the Text2 field
20. Type: G2
21. Click the OK button
22. Ensure that cell N2 is still selected and click the fill handle and drag to cell N4​

Domain 5: Manage Charts

Domain 5 Lesson 1
Create & Modify Charts

​1 Creating Charts
Using the data in cells A2 through E6, create a 2D Clustered Column Chart.

1. Select cells A2 through E6
2. Click the Insert tab
3. In the Charts group, click the Insert Column or Bar Chart drop-down arrow
4. Under 2-D Column, click the Clustered Column option

2 Moving Charts
Move the pie chart to its own worksheet. Accept the default worksheet name.

1. Select the Pie Chart
2. Click the Design tab under Chart Tools
3. In the Location group, click Move Chart
4. Click the New Sheet option
5. Click the OK button

3 Add Data Series To Charts
Add the Beginners data series to the pie chart on the Chart1 worksheet. While doing so, add the value in cell A8 as a series name to the chart.

1. Click the Chart1 worksheet
2. Click the Design tab under Chart Tools
3. In the Data group, click Select Data
4. In the Horizontal (Category) Axis Labels area, click the Edit button
5. Select cells B2 through F2
6. Click the OK button
7. In the Legend Entries (Series) area, click the Edit button
8. Click the arrow to the right of Series Values
9. Select cells B8 through F8
10. Press the Enter key
11. Click in the Series Name field
12. Click cell A8
13. Click the OK button twice

4 Switch Source Data
On the column chart, switch the source data so that the columns on the chart are showing numbers for locations by time of day instead of time of day by location.

1. Click the Column chart on the worksheet
2. Click the Chart Design tab
3. In the Data group, click Switch Row/Column

5 Chart Elements
On the column chart on Sheet1, delete the horizontal axis title and set a vertical axis title with the name, Students. Then, change the chart title to the name, Swimming Attendance.

1. Click the Column chart on the Sheet1 worksheet
2. Click the green plus sign
3. Click Axis Titles
4. While hovering over Axis Titles, click the arrow that appears to the right.
5. Clear the Primary Horizontal check box
6. In the chart, select the text, Axis Title
7. Type: Students
8. In the chart, select the text, Chart Title
9. Type: Swimming Attendance
10. Click anywhere on the chart

Domain 5 Lesson 2
Format Charts

6 Chart Layouts
​Change the chart type of the Swimming Attendance chart to a 3-D Clustered Column layout. Then, apply the Layout 5 layout to the chart.

1. Click the Swimming Attendance chart
2. Click the Design tab under Chart Tools
3. In the Type group, click Change Chart Type
4. Make sure that Column is selected on the left-hand side
5. Click the 3-D Clustered Column chart type from the list across the top of the All Charts tab
6. Click the OK button
7. In the Chart Layouts group, click the Quick Layout drop-down arrow
8. Click Layout 5

7 Chart Styles
Apply the Style 9 style to the Swimming Attendance chart on Sheet1. Then, apply the Monochromatic Palette 3 color combination to the chart.

1. Click the Swimming Attendance chart on the Sheet1 worksheet
2. Click the Design tab under Chart Tools
3. In the Chart Styles group, click the Chart Styles drop-down arrow
4. Click Style 9
5. In the Chart Styles group, click the Change Colors drop-down button
6. Click the Monochromatic Palette 3 color group option

8 Add Alternative Text
Add the text, Percentage of totals for each swim class, as alternative text to the Chart Area of the pie chart.

1. Select the entire pie chart
2. In the Chart Format tab, Current Selection group, ensure that the drop-down has Chart Area selected.
3. In the Chart Format tab, Accessibility group, click Alt Text
4. Click in the Alt Text field
5. Type: Percentage of totals for each swim class