Spreadsheet Skills and Objectives Examined on the Microsoft Excel 2013 Portion of the CBA IT Skills Proficiency Exam

The following are spreadsheet skills and objectives examined on the Microsoft Excel 2013 portion of the CBA IT Skills Proficiency exam. In order to receive credit for this component of the Proficiency exam, a score of 80% or higher must be achieved.

Basic Excel 2013 Spreadsheet Functionality

The student must be able to:

  1. Add new worksheets and delete existing worksheets
  2. Create new blank worksheets
  3. Rename worksheet tabs
  4. Switch between worksheets
  5. Rearrange the order of worksheets
  6. Open, Save and Close worksheets and files
  7. Add and delete cells, columns and rows
  8. Change Zoom levels
  9. Freeze the top rows and/or first column of a spreadsheet
  10. Use Print Preview
  11. Apply preset margins and custom margins
  12. Create headers and/or footers on a spreadsheet
  13. Use the various paste special options (formulas, values, formatting in varying combinations)

Spreadsheet Formatting

The student must be able to:

  1. Change numbers and/or cell formats
  2. Change formats including fonts, borders, colors, alignment and numbers
  3. Apply various forms of conditional formatting
  4. Merge cells
  5. Center horizontally across cells
  6. Change the horizontal and vertical alignment of cell contents
  7. Clear selected types of formatting while preserving other formats
  8. Wrap text within a cell
  9. Use Format Painter
  10. Change Page Orientation (Portrait to Landscape and back again
  11. Specify “Fit to Page” options (where the entire spreadsheets fits on 1 page)
  12. Create and modify charts and graphs (pie charts, bar chart, column chart, line chart)
  13. Format charts and move charts (including move to new or different worksheets)

Use of Spreadsheet Formulas and Functions

The student must be able to:

  1. Cut, copy and paste formulas
  2. Create formulas according to specified instructions
  3. Create formulas using absolute references, relative cell references and mixed cell references
  4. Explain the difference (on paper) between absolute cell references and mixed cell references
  5. Explain (on paper) when someone would use an absolute cell reference and when someone would use a mixed cell reference
  6. Locate and use the Formula Bar to create specified formulas
  7. Use the autosum feature to find the maximum value, minimum value, average value, sum of all the values, and a count of how many values/items there are
  8. Display and hide formulas used on a spreadsheet (switch from values to formulas and then back to values)
  9. Create a formula that can be used to display data in one cell in other cells
  10. Create a formula that can be used to display data on one worksheet in another worksheet
  11. Correctly use the IF function
  12. Correctly use the VLOOKUP function
  13. Correctly use the PMT function to determine a monthly payment, quarterly payment amount or whatever the required payment would be for the specified period. Students may be asked to display the payment amount as either a negative number or a positive number.
  14. Correctly use the PV or FV function. Students may be asked to display the payment amount as either a negative or positive number.
  15. Use date and time functions such as TODAY and NOW.

Advanced Spreadsheet Skills

The student must be able to:

  1. Create pivot tables
  2. Modify pivot tables and change the summary functions used in pivot tables
  3. Sort by one field
  4. Specify multiple sort levels and sort directions (sort-within-a-sort)
  5. Correctly use the Goal Seek feature
  6. Correctly use Autofilters
  7. Correctly use the Totals Row
  8. Group and ungroup worksheets as needed
  9. Hide and unhide columns and/or rows as needed
  10. Use the Move or Copy dialog box to create a duplicate copy of a worksheet
  11. Use Scenario Manager and what-if analysis tools
  12. Begin recording a macro, as well as stop the recording of a macro.