| Task | Description |
|---|---|
IMPORTANT: BEFORE YOU BEGIN: Click the link. Download this Excel Spreadsheet. Open it on your screen. |
|
| Task 1 |
Remove Spaces Go to tab 2. Remove Spaces Column A Click on cell D2. Create a formula to remove the extra spaces from cell range A2 through A109. Save your changes. |
| Task 2 |
What Happened Go to tab 2. Remove Spaces Column A Delete Column A How would you fix the #REF! messages in Column C |
| Task 3 |
Separate Columns Go to tab 4. Separate columns Delete column D Separate column C into the following: City in Column C State in Column D Zip Code in Column E |
| Task 4 |
Gantt Chart Go to tab 5. Data Tracking Gantt Chart Look at the Gantt Chart. You will create one just like it. Open a new Excel workbook and create a Data Tracking Gantt Chart just like the one you just viewed. Once finished, close the workbook. Do not save your changes. |
| Task 5 |
Loan Amortization Go to tab 6. Loan Amortization Look at the Loan Amortization Schedule. You will create it one just like it. Open a new Excel workbook and create a Loan Amortization just like the one just viewed. Once finished, close the workbook. Do not save your changes. |
| Task 6 |
Duplicates Go to tab 8. Dupes red delete In column A, mark all the duplicate values with "Light red Fill with Dark Red Text". In column A, put the selected cell color on top ("Light red Fill with Dark Red Text") In column A, delete all the duplicate values Save your changes. |
| Task 7 |
Vertical Go to tab 11. Pose Look at the data in A12 through F20 The data is listed vertically. You want the information in range A2 through I7 to be posted just like A12 through F20 (vertically). Delete rows 12 through 20 Copy the data in A2 through I7. Post the information vertically in A12 through F20. Save your changes. |
| Task 8 |
Top 3 Go to tab 12. Top 3 In the sales column Use Conditional Formatting to mark the top 10 values in "Light red Fill with Dark Red Text" Delete the Conditional Formatting rule in the Sales column. In the sales column Use Conditional Formatting to find the top 5 values and mark them in "Light red Fill with Dark Red Text" Delete the Conditional Formatting rule in the Sales column. Save your changes. |
| Task 9 |
Billions to B Go to tab 13. Billions In range E4 through E11, change each number so only the first three digits show followed by a B. |
| Task 11 |
Protect Cells Go to tab 14. Protect cells Protect cells E4 through E11 so they cannot be changed, modified, or deleted. |
| Task 12 |
Empty Rows Go to tab 15. Empty Rows Using the commands in the Ribbon, delete all the empty rows at the same time. Save your changes. |
| Task 13 |
Macro Go to tab 16. Macro ten sheets Open a new workbook. Create a macro that creates ten tabs (worksheets). Name the macro ten_sheets When finished creating the macro, close the file and don't save the changes. Open a new workbook and run the macro named ten_sheets. Close the workbook. Do not save the changes. |
| Task 14 |
Macro on QAT Go to tab 16. Macro ten sheets Put the macro labelled ten_sheets on the Quick Acess Toolbar. |
| Task 15 |
Macro with Icon Go to tab 16. Macro ten sheets View the macro named ten _sheets on the Quick Access Toolbar. Add an icon to the macro on the Quick Access Toolbar. |