Useful Excel Files

PSUG Excel Skills Test

I saw a thread on the Yahoo! PSUG asking about an Excel skills test.

I thought I'd be remiss in my duties were I not to provide one.

I got a lot of requests for it. Thank you to everyone who asked for it; I'm humbled.

Two files are available on this page - the test, and the answer key.

The Files

The Answers

Basic Formulas

For cell C80, the formula is =MAX(C2:C78)
For cell C81, the formula is =MIN(C2:C78)
For cell C82, the formula is =SUM(E2:E78)
For cell C83, the formula is =COUNT(E2:E78)
For cell C84, the formula is =AVERAGE(D2:D78)

That's the easy stuff. The next sheet or two get a little trickier, depending on your version.

Chart and Formatting

(Most of this can be done from the Formatting toolbar (2003 or older on Windows, most Mac versions)
1) Insert a row above row 1
2) In the new row 1, type "Employee Total Sales 2007" in A1,
3) Merge and center A1:F1,
4) Give "Employee Total Sales 2007" a white font, blue background
5) Bold and center A2:F2
6) Currency format cells B3:F11 (Don't just click the dollar sign; go to Format->Cells->Currency)
7) Wrap the text in row 2 (Format->Cells->Alignment->Wrap Text)
8) Re-size column A to automatically fit the contents
9) Put a thick border around cells A1:F11
10) Put a thin border on the individual cells
11) Put a gray background on cells A2:F2
12) Bold, center, and wrap text in cells A2:F2.
13) Set the page to be Landscape (Go to Page Setup, set it there; on 2007-2010, use the Page Layout tab)

The Column Chart

This is the easy one. Select cells A2:E11, and press F11. (If you're on a Mac, see below):
Double-click the chart tab, and re-name it

Mac Users:
Go to your System Preferences
Go to Dashboard & Expose (may also be called Mission Control, depending)
Make sure "Show Desktop" is not set to F11
Press FN+F11 to get the desired result (the column chart)

The Pie Chart

Select cells A2:A11 with your mouse/touchpad
Hold down your CTRL (Mac users, CMD) key
Select cells F3:F11 - you have to get them in one movement; if you click twice, it won't work
Insert > Chart > Pie Chart
Click the legend and delete it
Right-click on one of the wedges, choose Format Data Labels (if you don't have that option, choose Format Data Series, then go to Labels)
Check the marks next to Category and Percentage
Do a Print Preview, so that you can see the lines where the pages break
Click and drag the chart so that it's on the second page, and re-size it to fit the second page
Select the data, and the page with the chart
Go to Page Layout > Set Print Area (in older versions, File > Print Area > Set Print Area)

Sample Test Scores


In my Excel Cleanup data, I alluded to the concept behind this file - what if you had a test score file from the state, or some other entity (ETS, College Board)?

Chances are the file will be a series of rows of data, with no discernible delimiter between first name, last name, address, any of that.

Chances are also equally good that the organization has a file you can download that says something like this: "The first two characters are the school code, the fourth through the eighth characters are the district code..."

Creating an Excel file to tackle the data is the next step.

In brief, the first sheet shows a sample data map.

In the second sheet, I used Paste Special Transpose to get the data map turned 90 degrees.

Then, I copied the test data into the third sheet.

In the second sheet, I wrote a series of MID functions that said, "get the data out of A1 of the third sheet."

The MID function requires three arguments: the text (A1, in our case), the start number, and the number of characters.

The good news is, the start and number of characters are delineated in the data map.

So it's just a question of referencing the cells correctly.

See the attached file, you'll see what I mean.