Nugget Challenges and Case Studies

In this section of the web we present you with practice projects.  Each challenge will have a stated objective, suggested components to work with, and a posted answer.  Work through all the challenges to reinforce the concepts for each respective video series.  Have fun.

Microsoft Excel 2000 Beginner Level Challenges
Nugget Challenge #1: Working with Cells [download answer]
Nugget Challenge #2: Working with Worksheets and Workbooks [download answer]
Nugget Challenge #3: Working with Charts and Objects [download answer]

Microsoft Excel 2000 Expert Level Challenges

Nugget Challenge #4: Advanced Data Display Topics [download answer]
Nugget Challenge #5: Using Macros [download answer]
Nugget Challenge #6: Using Analysis Tools [download answer]

Nugget Challenge #1: Working with Cells 

Overview:

The point of this challenge is to get you familiar with how to use several of the tools and features that are commonly used when working with cells. The features covered in this exercise include: Find and Replace, Goto, Creating and Applying Custom Lists, Creating and Editing Hyperlinks.  This exercise is in the same format as the MOUS Excel 2000 certification exams.

 The Challenge:

  1. Replace all of the 555-CBTN phone numbers on this worksheet with 555-CBTS using the Replace feature.
  2. Goto cell Z1 using the Goto feature and create a custom list with the names listed in cells Z1 through Z6.
  3. Type the name "Iris" in cell A11 and use the Fill Handle to autofill the names from the custom list in cells A12 through A16.
  4. Create a hyperlink in cell B8 that will automatically create a new E-mail message to Robert@cbtnugget.com when selected.
  5. Edit the hyperlink in cell B15 and change the webpage that it references to http://www.cbtnuggets.com.

Click here to download the Excel Workbook for this Nugget Challenge.

Hints:

  • Read each question carefully prior to proceeding with the action.
  • Follow the instructions in the order that they appear.
  • Know several different ways to accomplish the same task within Excel (e.g. Ctrl+C  and Edit > Copy perform the same function).  Be sure to check the RIGHT CLICK quick menu for easy access to commonly used options.

back to top

Nugget Challenge #2: Working with Worksheets and Workbooks

Overview:

The point of this challenge is to get you familiar with how to create, manage and link information between different worksheets within the same workbook.  . The features covered in this exercise include: Moving/Copying a Worksheet, Renaming Worksheets, Modifying Multiple Worksheets Simultaneously by Grouping and Linking Worksheets.  This exercise is in the same format as the MOUS Excel 2000 certification exams.

The Challenge:

  1. Create a copy of the February Worksheet, Rename it to MARCH, and position it between the February and April worksheets.
  2. Move the June worksheet after the May worksheet.
  3. Type in a value of 30 in cell C3 on the January, February and March worksheets simultaneously by using the multiple worksheet selection feature.
  4. Create a formula in cell E4 on the All Months worksheet that links it directly to cell I3 on the March worksheet.

Click here to download the Excel Workbook for this Nugget Challenge.

Hints:

  • Read each question carefully prior to proceeding with the action.
  • Follow the instructions in the order that they appear.
  • Know several different ways to accomplish the same task within Excel (e.g. Ctrl+C  and Edit > Copy perform the same function).  Be sure to check the RIGHT CLICK quick menu for easy access to commonly used options.

back to top

Nugget Challenge #3: Working with Charts and Objects

Overview:

The point of this challenge is to get you familiar with how to use several of the tools and features that are commonly used when creating charts and objects. The features covered in this exercise include: Using the Chart Wizard, Modifying Chart Options, Inserting an Object (WordArt), Modifying Object Properties.  This exercise is in the same format as the MOUS Excel 2000 certification exams.

The Challenge:

  1. Using the Chart Wizard, create a chart that represents all of the sales information from the above data table and has the following characteristics:
    • Type: STACKED COLUMN
    • Data Series in: ROWS
    • Chart Title: MONTHLY SALES
    • Location: New Worksheet named "Sales Chart"
  2. Change the X-axis title of the chart in the Sales Chart worksheet to "MONTH"
  3. On the Sales Flyer worksheet, insert a WordArt graphic with the words "HOP TO IT" within the boundaries of the green box at the top of the page.
  4. On the Sales Flyer worksheet, change the orientation of the RED ARROW between the OLD PRODUCT and NEW PRODUCT text boxes so that it points from left to right.

Click here to download the Excel Workbook for this Nugget Challenge.

Hints:

  • Read each question carefully prior to proceeding with the action.
  • Follow the instructions in the order that they appear.
  • Know several different ways to accomplish the same task within Excel (e.g. Ctrl+C  and Edit > Copy perform the same function).  Be sure to check the RIGHT CLICK quick menu for easy access to commonly used options.

back to top

Nugget Challenge #4: Advanced Data Display Topics

Overview:

The point of this challenge is to get you familiar with how to use several of the tools and features that can be used to ease the pain of dealing with large volumes of data in Excel workbooks. The features covered in this exercise include: Sorting , Subtotals, Autofilter and Data Validation.  This exercise is in the same format as the MOUS Excel 2000 certification exams.

The Challenge:

  1. Using the entire data range in the Sorts worksheet (A1:H51), SORT all of this this information in ASCENDING order using RATE 1 as a primary sort criteria, then
    • RATE 4 as the secondary sort criteria.
  2. Apply SUBTOTALS on the entire data range (A1:D30) in the Group.Outline worksheet so that the AMOUNT OF SALE is summed for each change in the CLIENT listed in the range.
  3. Using the entire data range in the Filter worksheet, apply an AUTOFILTER to this data and specify a filter criteria that only displays invoices that are over $5000.
  4. Apply data validation to the cell range E4:G4 on the Validation worksheet so that only values between the MSRP price and four times the MSRP price for the "Itty-Bitty Widget" can be entered into these cells.

Click here to download the Excel Workbook for this Nugget Challenge.

Hints:

  • Read each question carefully prior to proceeding with the action.
  • Follow the instructions in the order that they appear.
  • Know several different ways to accomplish the same task within Excel (e.g. Ctrl+C  and Edit > Copy perform the same function).

back to top

Nugget Challenge #5: Using Macros

Overview:

The point of this challenge is to get you familiar with how to create, run and edit macros, which are used for task automation.  . The features covered in this exercise include: Recording a Macro, Running a Macro, Assigning a Macro to a Toolbar Button and Editing Macros.  This exercise is in the same format as the MOUS Excel 2000 certification exams.

The Challenge:

  1. Record a macro contained in This Workbook, named "Challenge2", that automates the following tasks:
    • Applying a Currency Style to cell range B2:M7
    • Apply a Bold Format to cell ranges A1:A7 and B1:M1
    • Select cell A1
  2. Once the macro has been created, assign a keyboard shortcut of CTRL+q  to the macro.
  3. Edit the Challenge2 macro in the Visual Basic for Applications (VBA) Editor and change the Name of the macro to "Nugget2".
  4. Add a new macro toolbar button and assign the "Nugget2" macro to the button.
  5. Run the Nugget2 macro on the information contained in the Sheet2 worksheet.

Click here to download the Excel Workbook for this Nugget Challenge.

*** Please note that this Workbook contains Macros that are for educational purposes ONLY ***

Hints:

  • Read each question carefully prior to proceeding with the action.
  • Follow the instructions in the order that they appear.
  • Know several different ways to accomplish the same task within Excel (e.g. Ctrl+C  and Edit > Copy perform the same function).

back to top

Nugget Challenge #6: Using Analysis Tools

Overview:

The point of this challenge is to get you familiar with how to use some of the analysis tools when working with data-intensive workbooks. The features covered in this exercise include: Creating and Modifying Pivot Tables, Creating an Interactive Webpage from a Pivot Table.  This exercise is in the same format as the MOUS Excel 2000 certification exams.

The Challenge:

  1. Create a new pivot table using the data range (A1:D33) in the Pivots worksheet with the following characteristics:
    • INVOICE NUMBER is in the ROW field
    • DATE OF SALE is in the COLUMN field
    • CLIENT is in the PAGE field
    • AMOUNT OF SALE is in the cross-tab DATA area
    • Create the Pivot Table in a new worksheet
  2. Filter the newly-created pivot table so that only information from Client B54225 is displayed.
  3. Publish the worksheet containing the pivot table as an interactive webpage with a title of "NUGGET LAB CHALLENGE" and ensure pivot table functionality is included in the webpage.
  4. Change the annual interest rate (ann_rate) in the "Bank 3 - 48 Month / 20K" Scenario in the Goal.Solver.Scenario worksheet to 9%.

Click here to download the Excel Workbook for this Nugget Challenge.

Hints:

  • Read each question carefully prior to proceeding with the action.
  • Follow the instructions in the order that they appear.
  • Know several different ways to accomplish the same task within Excel (e.g. Ctrl+C  and Edit > Copy perform the same function).

back to top

 
CBT Nuggets - IT Certification Training Videos
buy videos online fax/mailing order form contact us