I85PREP
⌘ K
ICDL Spreadsheets study materials
ICDL Spreadsheets study resources

ICDL Spreadsheets (ICDL): Recovering the right price list and sharing it safely

ICDL Spreadsheets · Put your knowledge to work in context.

All case studies

Recovering the right price list and sharing it safely

Your team maintains a supplier price list in Prices.xlsx stored on OneDrive. Sheet Products has 1,240 rows: column A holds product codes such as 00417 and 01250, column B descriptions and column C unit cost. Sheet Costs contains formulas that multiply Products costs by a markup held in a named cell. Yesterday Excel closed unexpectedly mid-edit. You now have three candidates: the OneDrive file (last modified 09:42 today), an email attachment Prices_final_v2.xlsx from a colleague (modified three days ago), and a USB copy named Prices_FINAL(1).xlsx. The supplier needs the product list as a CSV for import, and the sales manager wants a non-editable copy for the team. A previous CSV export lost the leading zeros and contained only one sheet.

1. Which copy do you start from, and what do you verify before saving anything?

Read the worked answer

Start from the OneDrive file, because it is the only candidate with today's timestamp and it is the file Excel was autosaving to. Do not rely on filenames such as v2 or FINAL; they describe intent, not recency.

Before editing or overwriting:

  • Open File > Info > Version history and look for an autosaved version from just before the crash. If the current file is corrupt or truncated, restore the most recent version that opens cleanly.
  • Verify content, not just the date: confirm the row count is 1,240, check the total of column C against a known figure, and spot-check that codes still read 00417 and 01250.
  • Treat the email and USB copies as fallback sources only. Use them to recover a missing row or a deleted block, then paste values into the recovered file rather than saving over it.
  • Save the verified file under a new name (Prices_2025-03-14.xlsx) before distributing anything. This preserves the original and gives you a clean rollback point.

2. The supplier's CSV must keep codes such as 00417 and must not carry the Costs markup. Explain what you do and what will be lost.

Read the worked answer

CSV is plain text: it holds one sheet (the active sheet) and stores no formulas, number formats, colours, column widths, merged cells or multiple sheets. So you must prepare the Products sheet itself before exporting.

Steps:

  1. Keep the leading zeros by storing the codes as text. Either format column A as Text before typing, or prefix entries with an apostrophe. For codes already stored as numbers, add a helper column with =TEXT(A2,"00000"), copy it, then Paste Special > Values over column A and delete the helper.
  2. Make Products the active sheet. Only the active sheet is exported, which is why Costs disappeared last time.
  3. Use File > Save As > CSV UTF-8 (Comma delimited). Choosing plain CSV can mangle non-ASCII characters in descriptions.
  4. Reopen the CSV in Notepad or a text editor and confirm the first codes still read 00417, not 417.

What is lost: the Costs sheet, all formulas (only their current calculated values are exported), formatting, and any second sheet. Keep the .xlsx as the master and treat the CSV as a one-way export.

3. The sales manager asks for a non-editable copy and a printed summary. Compare the options and state what you would set up for printing.

Read the worked answer

Three options, with different levels of protection:

  • Mark as Final is only a flag. Anyone can click Edit Anyway, so it is a courtesy notice, not protection.
  • Protect Sheet or Protect Workbook with a password stops casual edits inside Excel, but the file remains an Excel file: rows can still be copied out, and a forgotten password is a real support problem.
  • Save as PDF gives the recipient a fixed-layout document that is impractical to edit. Text can still be copied, so it is not confidential, but it is the best fit for 'non-editable copy for the team'.

For the printed summary:

  • Set the print area to the used range only, so blank rows are not printed.
  • Use Page Layout > Orientation > Landscape and Scale to Fit: 1 page wide, with height set to Automatic.
  • Set Print Titles > Rows to repeat at top so the header row appears on every page.
  • Check the result in Page Break Preview, then export with File > Export > Create PDF/XPS.

Check for hidden rows, hidden columns and hidden sheets before exporting: hidden sheets are not printed by default, and a hidden column can silently remove a field the manager expects to see.