Subdomain 2.1 Manipulate data in worksheets
2.1.1 Paste data by using special paste options
Directions:
1.) Download the file (above).
2.) Select the range A4 to K9 and copy it by clicking CTRL+C
3.) Click the dropdown for paste.
4.) In the space below paste the values (this will get rid of any formulas).
To Get Credit: Paste the values of A4 to K9 in the space below.
1.) Download the file (above).
2.) Select the range A4 to K9 and copy it by clicking CTRL+C
3.) Click the dropdown for paste.
4.) In the space below paste the values (this will get rid of any formulas).
To Get Credit: Paste the values of A4 to K9 in the space below.
2.1.2 Fill cells by using Auto Fill
Directions:
1.) Download the file (above).
2.) Drag down the formula from K5 to K9.
To Get Credit: Drag down the formula from K5 to K9.
1.) Download the file (above).
2.) Drag down the formula from K5 to K9.
To Get Credit: Drag down the formula from K5 to K9.
2.1.3 Insert and delete multiple columns or rows
Directions:
1.) Download the file (above).
2.) Add 2 columns before column D
3.) Delete the three red columns.
To Get Credit: Add 2 columns before column D and delete the three red columns.
1.) Download the file (above).
2.) Add 2 columns before column D
3.) Delete the three red columns.
To Get Credit: Add 2 columns before column D and delete the three red columns.
2.1.4 Insert and delete cells
Directions:
1.) Download the file (above).
2.) Put cursor in G3
3.) Right click and click on Insert
4.) Shift cells down.
5.) Right click again and press delete.
To Get Credit: Demonstrate this process for me.
1.) Download the file (above).
2.) Put cursor in G3
3.) Right click and click on Insert
4.) Shift cells down.
5.) Right click again and press delete.
To Get Credit: Demonstrate this process for me.
Subdomain 2.2 Format cells and ranges
2.2.1 Merge and unmerge cells
Directions:
1.) Download the file (above).
2.) Select cells A1, B1 and C1
3.) Go to Home tab and select Merge & Center
4.) Select Merge Across
To Get Credit: Show me the cells merged across.
1.) Download the file (above).
2.) Select cells A1, B1 and C1
3.) Go to Home tab and select Merge & Center
4.) Select Merge Across
To Get Credit: Show me the cells merged across.
2.2.2 Modify cell alignment, orientation, and indentation
Directions:
1.) Download the file (above).
2.) Merge cells A1 to K1. Set alignment to right.
3.) Merge cells A2 to K2. Set orientation to "Angle Counterclockwise"
4.) Merge cells A3 to K3. Click Format Cell Properties. Manually set the orientation.
To Get Credit: Show me steps 2 and 3. Demonstrate step 4 for me.
1.) Download the file (above).
2.) Merge cells A1 to K1. Set alignment to right.
3.) Merge cells A2 to K2. Set orientation to "Angle Counterclockwise"
4.) Merge cells A3 to K3. Click Format Cell Properties. Manually set the orientation.
To Get Credit: Show me steps 2 and 3. Demonstrate step 4 for me.
2.2.3 Format cells by using Format Painter
Directions:
1.) Download the file (above).
2.) Go to Home tab.
3.) Click on cell B3.
4.) Click on format painter.
5.) Paint that format on cells D5:E8
To Get Credit: Show me that cells D5:E8 have the same format as cell B3
1.) Download the file (above).
2.) Go to Home tab.
3.) Click on cell B3.
4.) Click on format painter.
5.) Paint that format on cells D5:E8
To Get Credit: Show me that cells D5:E8 have the same format as cell B3
2.2.4 Wrap text within cells
Directions:
1.) Download the file (above).
2.) Go to Home tab.
3.) Go to alignment group.
4.) Click on cell B4.
5.) Click "Wrap Text"
To Get Credit: Show me cell B4 with wrapped text.
1.) Download the file (above).
2.) Go to Home tab.
3.) Go to alignment group.
4.) Click on cell B4.
5.) Click "Wrap Text"
To Get Credit: Show me cell B4 with wrapped text.
2.2.5 Apply number formats
Directions:
1.) Download the file (above).
2.) Select range B2:B10
3.) Set this range to a percentage by going to home tab numbers group.
To Get Credit: Set B2:B10 to a percentage.
1.) Download the file (above).
2.) Select range B2:B10
3.) Set this range to a percentage by going to home tab numbers group.
To Get Credit: Set B2:B10 to a percentage.
2.2.6 Apply cell formats from the Format Cells dialog box
Directions:
1.) Download the file (above).
2.) Go to Home tab.
3.) Click Number dialog box launcher.
4.) Use the number dialog box launcher to complete the worksheet.
To Get Credit: Show me the worksheet with the correct settings. (fraction, currency, scientific notation)
1.) Download the file (above).
2.) Go to Home tab.
3.) Click Number dialog box launcher.
4.) Use the number dialog box launcher to complete the worksheet.
To Get Credit: Show me the worksheet with the correct settings. (fraction, currency, scientific notation)
2.2.7 Apply cell styles
Directions:
1.) Download the file (above).
2.) Click on cell A2.
3.) Go to home tab.
4.) Go to styles group.
4.) Click on Cell Styles
5.) Hover over the options until you see and choose Light Blue, 60% - Accent 5
To Get Credit: Add cell style Light Blue, 60% - Accent 5 to cell A2
1.) Download the file (above).
2.) Click on cell A2.
3.) Go to home tab.
4.) Go to styles group.
4.) Click on Cell Styles
5.) Hover over the options until you see and choose Light Blue, 60% - Accent 5
To Get Credit: Add cell style Light Blue, 60% - Accent 5 to cell A2
2.2.8 Clear cell formatting
Directions:
1.) Go to home tab
2.) Go to editing group.
3.) Click on cell A2
4.) Go to editing group.
5.) Hit Clear drop down.
6.) Click clear formats.
To Get Credit: Clear the formatting in cell A2
1.) Go to home tab
2.) Go to editing group.
3.) Click on cell A2
4.) Go to editing group.
5.) Hit Clear drop down.
6.) Click clear formats.
To Get Credit: Clear the formatting in cell A2
Subdomain 2.3 Define and reference named ranges
2.3.1 Define a named range
Directions:
1.) Download the file (above).
2.) Select F5:F9
3.) Go to formulas tab.
4.) In the "Defined Names" section click "Name Manager"
5.) Select New
6.) Name the range whatever you want. Set the scope to Workbook.
7.) Hit OK
To Get Credit: Show me your named range (remember how to do this from previous videos).
1.) Download the file (above).
2.) Select F5:F9
3.) Go to formulas tab.
4.) In the "Defined Names" section click "Name Manager"
5.) Select New
6.) Name the range whatever you want. Set the scope to Workbook.
7.) Hit OK
To Get Credit: Show me your named range (remember how to do this from previous videos).
2.3.2 Name a table
Directions:
1.) Download the file (above).
2.) Select B9:D14
3.) Go to Home tab and click "Format As Table"
4.) Click anywhere in the table and you will see the "Design" tab. (If your cursor is outside the table, you won't see it).
5.) On the left hand side of the screen, you will be able to name the table. (Name it whatever you want)
To Get Credit: Name the table on your worksheet and show me in the name manager.
1.) Download the file (above).
2.) Select B9:D14
3.) Go to Home tab and click "Format As Table"
4.) Click anywhere in the table and you will see the "Design" tab. (If your cursor is outside the table, you won't see it).
5.) On the left hand side of the screen, you will be able to name the table. (Name it whatever you want)
To Get Credit: Name the table on your worksheet and show me in the name manager.
2.4 Summarize data visually
2.4.1 Insert Sparklines
Directions:
1.) Download the file (above).
2.) Go to insert tab.
3.) Go to Sparklines group and click "Line"
4.) Follow the instructions in the video to select the ranges.
To Get Credit: Show me sparklines! I want sparklines darn it!
1.) Download the file (above).
2.) Go to insert tab.
3.) Go to Sparklines group and click "Line"
4.) Follow the instructions in the video to select the ranges.
To Get Credit: Show me sparklines! I want sparklines darn it!
2.4.2 Apply built-in conditional formatting
Directions:
1.) Download the file (above).
2.) Select D2:D31
3.) Go to Home Tab
4.) Go to Styles Group
5.) Highlight any number in column D that is above 90 in Green.
To Get Credit: Highlight any number in column D that is above 90 in Green.
1.) Download the file (above).
2.) Select D2:D31
3.) Go to Home Tab
4.) Go to Styles Group
5.) Highlight any number in column D that is above 90 in Green.
To Get Credit: Highlight any number in column D that is above 90 in Green.
2.4.3 Remove conditional formatting
Directions:
1.) Download the file (above).
2.) Go to Home Tab and Styles group.
3.) Click Conditional Formatting Dropdown.
4.) Select Manage Rules
5.) Delete the rule for Conditional Formatting.
To Get Credit: Remove All Conditional Formatting.
1.) Download the file (above).
2.) Go to Home Tab and Styles group.
3.) Click Conditional Formatting Dropdown.
4.) Select Manage Rules
5.) Delete the rule for Conditional Formatting.
To Get Credit: Remove All Conditional Formatting.