Spreadsheet Challenge Exam Instructions & Help

Preparing for the SBE Spreadsheet Competency Waiver Exam

School of Business and Economics / Seattle Pacific University

All students pursuing an undergraduate major in the School of Business and Economics must successfully document their proficiency with Microsoft Excel. Most students will satisfy this requirement by passing the 1-credit BUS 1700 Spreadsheets course (formerly CSC 1123). Achieving a passing score on the SBE Spreadsheet Competency Exam described below is another way you can document your proficiency with Excel. The full waiver policy can be read here. Required courses such as MAT 2700 Statistics for Business and Economics, ACCT 2362 Managerial Accounting, and BUS 3250 Business Finance also require this documented competency as a prerequisite.

Overview of the Challenge Exam

The SBE Spreadsheet Competency Exam will assume that you will be using Microsoft Excel 2007 or Excel 2010. (Excel 2013 is excepcet at SPU in autumn 2014.) The exam can be divided into six parts. These six areas will be found on each version of the exam. The Excel program is 'feature-rich.' Many of its advanced features will not be needed to pass the exam. However, to help you prepare for the exam, a summary of the specific features required is given here.

1. Obtaining the data.

You need to be prepared to locate and use raw text and numerical data from any of a variety of sources. The exam data may be provided in a Word document or a text (.txt) file. It may be on a web page. It may be in a table already or compressed in a delimited format. You will need to open the data file and, if necessary, copy and paste it into Excel. Once in Excel you may need to spread the data across the columns using the Text to Columns feature found on the Data ribbon.

2. Preparing the data.

While normally, one does little or nothing to a data file except to make sure that proper column headings are entered into row one on the data worksheet, occasionally it is necessary to sort the data or format it in some specific way to prepare it prior to being used.

3. Preparing the output with 'Page Setup' features.

Sometimes the Page Setup features (found on the Page Layout ribbon) are ignored until one is preparing to print a document. It is a better idea to take care of layout questions before one begins to design the output. On this exam you will be expected to prepare your solution in portrait orientation, centered horizontally on the page, with specific headers and footers, and without gridlines. Gridlines should appear on the computer screen but not on the paper output. Gridlines are not to be confused with the thick and thin lines to be drawn around cells in a table.

4. Formatting a summary table.

To save time, it is best to format your results after you have calculated them. Drawing lines, adding shading, or changing fonts, often has to be redone if begun too early. You should master the features on each tab of the the Format Cells dialog box (except Protection). It is accessible under Font, Alignment and Number on the Home ribbon.

5. Using formulas properly.

This is the key item on the exam. You should be able to translate basic mathematical formulas for use in Excel. You need to be familiar with a number of Excel functions. And most especially, you need to use functions and formulas properly. This means that when you enter formulas on the solution worksheet, if the values on the data worksheet were to be updated, the answers on the solution worksheet should change automatically. In general, using Copy and Paste with formulas will likely produce errors. Simply using the "=" on the solution worksheet will simplify this task. (See the sample exam videos below.)

There are over 300 functions in Excel. You should at least be familiar with these:
MAX, MIN, SUM, AVERAGE, MEDIAN, PI, EXP, SQRT, FACT, COUNT, COUNTA, COUNTIF, and TODAY. You may need to use the CONCATENATE function or learn how to use the & symbol in a formula. (For example: =A4&" "&D4&" "&B4 )

6. Creating a graph.

One feature where Excel 2003 changed greatly in Excel 2007 is the area of graphs and charts. Excel can create dozens of types of graphs. However, the only ones you will need to be familiar with are Bar, Column, Line, Pie, Area, and Scatter.

Online Learning Resources for Excel

If you are new to Excel 2010 , please watch the online demonstration provided by Microsoft titled Getting Started with Excel 2010.

You may also find this Microsoft lesson series helpful. Make the switch to Excel 2010.
Other free tutorial help is available by searching the web for sites like About.com: Spreadsheets.

Sample Challenge Exams

The Excel "Problem Files" below provide the basis for these two sample exams.

Sample 1: Athens Olympic Medal Counts (basic)
[ Excel Problem File 1 ]

(Click on these links to view the video lessons.)

Introduction to Sample Exam 1.

1. Obtaining the data.

2. Preparing the data.

3. Preparing the output with 'Page Setup' features.

4. Formatting the summary table.

5. Creating the graph.

Sample 2: Working with Equations (advanced)
[ Excel Problem File 2 ]

Introduction to Sample Exam 2.

1. Obtaining the data.

2. Preparing the data.

3. Preparing the output with 'Page Setup' features.

4. Using formulas properly.

5. Formatting the summary table.

6. Creating the graph.

Scheduling a Challenge Exam

The the SBE Spreadsheet Competency Exam is scheduled with Dr. Dick Sleight. It can be taken between 9:00AM and 3:00PM on most weekdays. The exam can be completed in as little as 20 minutes by a very proficient Excel user. Students are allowed up to 60 minutes to complete the exam (prepare the data, create a summary table with formulas and functions, and create a graph.) The challenge exam may be repeated once (taking a new version) at the discretion of Dr. Sleight, the technology manager of the School of Business and Economics.

Dr. Sleight (pronounced "Slate") can be reached at 206.281.2265 and at SL8@spu.edu.

◄ To SBE