Excel Assignment — Student Guide
This is the Week 1 application project from Information Systems Today (Valacich & Schneider, 8th ed.), Chapter 10, exercise 10-45. The task sounds straightforward — add some statistics to a CSV file — but students consistently lose points on the formatting requirements and miss the Range formula entirely. This guide covers every required step: formulas, conditional formatting, and professional layout.
💻 Need help completing the EthicsSurvey Excel project or another IS application assignment? Our spreadsheet specialists can help.
Get Assignment Help →What the Assignment Actually Requires — Before You Open Excel
You have a CSV file with 30 employee responses across 10 survey items (rated 1–9). Your job is to: open it in Excel, add descriptive statistics beneath the data using formulas, apply color scale conditional formatting to the means row, and professionally format the workbook before saving as .xlsx.
The scenario is Campus Travel, a travel agency whose employees have started using company IS resources for personal use. Management is rolling out an acceptable use policy and surveyed 30 employees to gauge their concerns. Higher scores indicate stronger concern about specific ethical implications. Your boss wants to know which items are generating the most concern — and that’s what the statistics will reveal.
The deliverable is a single Excel workbook file: EthicsSurvey.xlsx. Not a PDF, not a screenshot, not a Google Sheets link. An actual .xlsx file.
| Requirement | Points | What It Means |
|---|---|---|
| Open EthicsSurvey.csv in Excel | 10 pts | Start from the provided CSV — don’t retype the data manually |
| Add statistic labels in column A | 20 pts | Label each row beneath the data: Mean, Standard Deviation, Mode, Minimum, Maximum, Range |
| Add correct formulas for all five statistics across all ten items | 50 pts | AVERAGE, STDEV, MODE, MIN, MAX for columns B through K — plus Range (MAX minus MIN) |
| Color scale conditional formatting on means row | 20 pts | Highlight mean values visually — low means get red/orange, high means get green |
The Range formula isn’t built into Excel
The assignment prompt specifically notes you’ll have to calculate Range yourself. There’s no =RANGE() function in Excel. Range = Maximum − Minimum. The formula is =MAX(B2:B31)-MIN(B2:B31). Students who skip this lose points even when every other formula is correct.
Opening EthicsSurvey.csv in Excel — Don’t Skip This Step
The starting file is a .csv (comma-separated values) file. Your final submission must be a .xlsx (Excel workbook) file. Those are two different formats. If you just rename the .csv to .xlsx, it will not work correctly and Excel may corrupt the file.
Open Excel first, then open the CSV through Excel
Go to File → Open → Browse, then change the file type filter from “Excel Files” to “All Files” so the .csv appears. Select EthicsSurvey.csv and click Open. Excel may launch a Text Import Wizard — if it does, confirm the delimiter is set to “Comma” and click Finish. The data should land cleanly in columns A through K with Employee in column A and Items 1–10 in columns B–K.
Immediately save as .xlsx
Once the data is in Excel, go to File → Save As. In the “Save as type” dropdown, select “Excel Workbook (*.xlsx)”. Name the file EthicsSurvey.xlsx. Now you’re working in the correct format. Don’t continue editing the .csv version — all your formula work will happen in the .xlsx.
Verify the data layout
Row 1 should be headers: Employee, Item 1, Item 2 … Item 10. Rows 2–31 should be the 30 employee records. Column A holds employee numbers 1–30. Columns B through K hold the numeric responses (4–9). If anything looks off — data split across wrong columns, extra characters — fix it now before adding formulas.
Add Statistic Labels in Column A — The 20-Point Setup
The rubric awards 20 points just for correctly labeling the statistics rows in column A. It sounds trivial. It’s not — get the labels wrong and your formulas will be pointing to the wrong rows, costing you points on both the label step and the formula step.
Starting in cell A32 (immediately below the last data row, which is row 31), add one label per row in this order:
A33: Standard Deviation
A34: Mode
A35: Minimum
A36: Maximum
A37: Range
Some instructors want a blank row between data and statistics for readability. If your rubric doesn’t specify, leave row 32 blank and start labels at row 33 — then adjust all your formula row references accordingly. The key is consistency: whatever row your Mean label is in, your Mean formulas go in that same row across columns B–K.
Bold the labels and right-align them
Select A32:A37, apply bold formatting, and right-align the text. It’s a small touch, but it makes the statistics section look intentional and distinct from the raw data. This falls under “professional formatting” and costs you nothing to do.
Entering All Six Formulas — The 50-Point Core
Half the points in this assignment live here. Each statistic needs a formula in every column from B to K — that’s 10 columns × 6 statistics = 60 formula cells total. The good news is you enter the formula once per row and drag across. Here’s exactly what goes where.
Mean — AVERAGE Function
Row 32 (or your Mean row) — measures the typical response per item
In cell B32, enter the following formula. The range B2:B31 covers all 30 employee responses for Item 1.
=AVERAGE(B2:B31)After entering this in B32, click B32, then grab the fill handle (small square at the bottom-right corner of the cell) and drag right to K32. Excel will automatically adjust the column references: C32 becomes =AVERAGE(C2:C31), D32 becomes =AVERAGE(D2:D31), and so on through K32. Format this row to 2 decimal places so values like 7.33 display clearly rather than as integers.
Standard Deviation — STDEV Function
Row 33 — measures how spread out responses are around the mean
Standard deviation tells you whether employees mostly agreed (low SD) or had widely varying views (high SD) on each item. A high mean with a low standard deviation means strong consensus around high concern.
=STDEV(B2:B31)Enter in B33, drag right to K33. Format to 2 decimal places. Note: use STDEV (which calculates sample standard deviation), not STDEVP (which calculates population standard deviation). For a survey sample like this, STDEV is the correct choice.
Mode — MODE Function
Row 34 — the most frequently occurring response value
=MODE(B2:B31)Enter in B34, drag right to K34. If a column has no repeating values, MODE will return a #N/A error. That’s technically correct behavior — it means no mode exists for that column. If your instructor expects a clean spreadsheet with no error messages, you can wrap it in IFERROR: =IFERROR(MODE(B2:B31), "No mode"). Check your rubric to see if that level of error handling is expected.
Minimum — MIN Function
Row 35 — the lowest response given for each item
=MIN(B2:B31)Enter in B35, drag to K35. The minimum tells you the lowest concern score any employee gave that item — useful for identifying items where at least one person felt very differently from the group.
Maximum — MAX Function
Row 36 — the highest response given for each item
=MAX(B2:B31)Enter in B36, drag to K36. Combined with minimum, you can see the full range of employee sentiment for each item.
Range — MAX minus MIN (No built-in function)
Row 37 — this one you calculate yourself; it’s specifically called out in the assignment
There’s no =RANGE() function in Excel. The assignment prompt explicitly notes this and tells you to calculate it yourself. Range = Maximum − Minimum.
=MAX(B2:B31)-MIN(B2:B31)Enter in B37, drag to K37. This gives the spread between the highest and lowest responses for each item. A large range means employees were very split; a small range means more uniform responses regardless of the mean level.
Drag-and-fill saves you from entering 60 formulas individually
Enter the formula for column B (Item 1) in each statistic row. Then select B32:B37 (all six formulas in column B), grab the fill handle, and drag right to column K. All six formulas copy across all ten columns simultaneously, with column references automatically updating. That’s the whole statistics section done in six formula entries plus one drag.
Color Scale Conditional Formatting on the Means Row — The 20-Point Visual
The rubric awards 20 points for applying color formatting to highlight items needing attention. The prompt specifically mentions values below 6.5 as a suggested threshold for “needing attention.” You can use either a color scale (gradient) or highlight cells rule — both satisfy the requirement. The color scale approach looks more polished.
Select the Mean row cells (B32:K32)
Click B32, hold Shift, click K32. You should have all 10 mean values selected — one per survey item.
Open Conditional Formatting → Color Scales
On the Home tab, click Conditional Formatting in the Styles group. Hover over Color Scales. You’ll see a gallery of 2-color and 3-color scale presets. Choose the Red-Yellow-Green scale (first option in the top-left of the gallery). Red will be applied to lower means (items needing more attention), green to higher means.
Optional: Set a specific threshold using Highlight Cells Rules
If your rubric specifically mentions the 6.5 threshold from the prompt, add a second rule: Conditional Formatting → Highlight Cells Rules → Less Than → enter 6.5 → choose a red or orange fill. This flags items explicitly rather than just coloring by relative rank. Both approaches satisfy the requirement; the explicit threshold approach is more precise.
The color scale doesn’t change the numbers. It makes the pattern visible at a glance — your boss can see which items scored lowest without reading every cell.
— The purpose of conditional formatting in data analysisApply the color scale only to the Mean row — not the entire data range
A common mistake is selecting the full data table before applying the color scale, which colors every cell based on relative value. That makes the raw data hard to read and doesn’t satisfy the requirement. The color scale should be on the mean row (B32:K32) only.
Professional Formatting — The Detail That Separates Good from Great
The rubric says “professionally format the pages before submitting.” That’s vague, which is intentional — it gives you room to demonstrate judgment. Here’s what professional formatting actually means for a spreadsheet like this.
Consistent Font
Use one font throughout — Calibri 11pt is Excel’s default and works fine. Don’t mix Arial in some cells and Times New Roman in others. Apply bold to headers and stat labels.
Column Widths
Auto-fit all columns so headers aren’t cut off. Select all (Ctrl+A), then double-click any column border in the header row to auto-fit everything at once.
Header Row Styling
Give the header row (Employee, Item 1–10) a background fill color and bold white text. Dark navy or gray works well. Center-align the column headers.
Number Formatting
Format the Mean and Standard Deviation rows to 2 decimal places. The other statistics are whole numbers, so they’re fine as-is. Don’t leave means displaying as integers.
Borders
Add thin borders around the data table and the statistics section. A slightly thicker border separating data from statistics helps visually distinguish the two areas.
Print Setup
Set print orientation to Landscape so the 11-column table fits on one page. Use Page Layout → Orientation → Landscape, and set Scale to Fit to 1 page wide.
Add a title above the data
Insert a row above the headers and add a title like “Campus Travel — Ethics Survey Analysis.” Merge and center it across columns A–K, apply a fill color and larger bold font. It takes 30 seconds and immediately makes the workbook look like something you’d send to an actual boss rather than something you cranked out in class.
Grading Rubric Breakdown — Where Every Point Comes From
The rubric is specific. Here’s exactly what earns each block of points and what loses them.
| Rubric Item | Points | What Earns Full Credit | Common Point Loss |
|---|---|---|---|
| Open EthicsSurvey.csv | 10 | Data from the original CSV is present and intact — all 30 rows, all 10 items | Retyping data by hand; starting with a blank file instead of the provided CSV |
| Stat labels in column A | 20 | Six correctly named labels: Mean, Standard Deviation, Mode, Minimum, Maximum, Range — in that order, directly below data | Missing Range label; putting labels in wrong column; labels not matching formula rows |
| AVERAGE formula | ~8 | =AVERAGE(B2:B31) across all 10 item columns | Hardcoding calculated values instead of using formulas; wrong row range |
| STDEV formula | ~8 | =STDEV(B2:B31) across all 10 item columns | Using STDEVP instead of STDEV; missing from some columns |
| MODE formula | ~8 | =MODE(B2:B31) across all 10 item columns | Leaving #N/A errors without instructor guidance; missing columns |
| MIN / MAX formulas | ~8 | =MIN() and =MAX() across all 10 columns | Skipping one or both; using wrong range |
| Range formula | ~8 | =MAX(B2:B31)-MIN(B2:B31) — not a built-in function, must be calculated | Skipping it entirely (the most common single mistake in this assignment) |
| Color scale on means | 20 | Conditional formatting applied to B32:K32 (or wherever the mean row is) using color scale or highlight rule | Applied to wrong range; not applied at all; color scale reversed (red on high values) |
Common Mistakes — What Costs Students Points
Mistakes That Cost Points
- Forgetting the Range row entirely — there’s no Excel function for it, so students who look up “Excel statistics functions” in a list never find it
- Submitting the file as EthicsSurvey.csv instead of .xlsx — the rubric explicitly requires .xlsx format
- Applying the color scale to the full data table instead of only the mean row
- Using hardcoded numbers (typing 7.23) instead of formula references in the statistics rows
- Selecting the wrong data range in formulas — =AVERAGE(B2:B30) misses the 30th employee (row 31)
- Leaving MODE showing #N/A errors without addressing them
- No professional formatting — plain default Excel with no headers styled, no borders, no title
What Full-Credit Submissions Do
- Open the actual EthicsSurvey.csv file rather than starting from scratch
- Save immediately as .xlsx and work only in that format
- Enter formulas for all six statistics (including Range) across all 10 item columns
- Use B2:B31 as the data range — row 2 is the first employee, row 31 is the thirtieth
- Apply conditional formatting specifically and only to the mean row
- Add a title, bold headers, column fill colors, and decimal formatting for mean/SD
- Set print layout to landscape so the full table fits on one page
FAQs — What Students Ask Most About This Assignment
=AVERAGE(B2:B31) — Standard Deviation: =STDEV(B2:B31) — Mode: =MODE(B2:B31) — Minimum: =MIN(B2:B31) — Maximum: =MAX(B2:B31) — Range: =MAX(B2:B31)-MIN(B2:B31). Enter each formula in the leftmost column (B) and drag right to K. Range is the only one without a dedicated Excel function — you calculate it as MAX minus MIN. Microsoft’s official support page for Excel statistical functions is at support.microsoft.com if you need to look up function syntax.=IFERROR(MODE(B2:B31),"No mode"). Check whether your instructor accepts #N/A or wants it suppressed.What the Assignment Is Actually Testing
This project isn’t really about formulas. Excel can calculate all six statistics in under a minute. What the assignment tests is whether you can set up a spreadsheet correctly — open the right file, structure the statistics section logically, use the right function for each statistic, apply formatting that makes data readable, and deliver a file in the correct format.
The Range formula is the most common single point of failure, and it’s specifically flagged in the textbook because it tests whether you understand what you’re calculating rather than just looking up a function name. AVERAGE gives you AVERAGE. There’s no RANGE function, so you have to reason through it: highest value minus lowest value.
Get the formulas right, apply the color scale to the means row only, format the workbook so it looks like something you’d actually send to a manager, and save it as .xlsx. That’s the whole assignment. If you need help completing it or any other IS application exercise, information systems specialists at Smart Academic Writing are available to support you through the process.