If you would like to test out your Excel Chops, here are a few practice files based upon what was presented in each class.
Class 1
Exercise 1: Download this workbook and do the following:
- Save the workbook as a template named MyTemplate.
- Use the MyTemplate template to create a new Excel workbook.
- Save the workbook as MyMacros in the macro-enabled format.
Exercise 2: Download this workbook and do the following:
- Enable macros in the workbook.
- Open the Visual Basic Editor and copy the macros from the ExcelExpert_1-1b workbook to the MyMacros workbook, and then close the Visual Basic Editor.
- Return to the MyMacros workbook and save it.
- Record a simple macro and store it in the Personal Macro Workbook.
- Return to the Visual Basic Editor, copy the macros from the MyMacros workbook to the Personal Macro Workbook, and then close the Visual Basic Editor.
- Unhide the Personal Macro Workbook, then hide it again.
Exercise 3: Reopen the ExcelExpert_1-1a workbook and do the following:
- In cell A2, enter = to start a formula.
- Switch to the ExcelExpert_1-1b workbook and click cell A1 on Sheet1.
- Confirm your external reference formula.
Exercise 4: Switch to the ExcelExpert_1-1b workbook and do the following:
- For the table in the Inventory worksheet, in cell G1, create a formula that uses a structured reference to return the sum of the values in the Qty On Hand field.
- In cell G2, create a formula that returns the smallest value in the List Price field.
- Add a column named Discount and populate it with formulas that multiply the values in the Standard Cost column by 0.75.
Class 2
Exercise 1: Download this workbook, dismiss the circular reference
warning, and do the following:
- Change the formula calculation method to Manual, and turn on iterative calculations.
- Use the formula precedence and dependence to see how the formula is a circular reference.
- Select cell C6, which contains the circular reference formula, and manually calculate the formula result.