AfterDesk
EN
Apply

Foundations

Spreadsheet essentials

Most overnight tasks pass through a spreadsheet at some point: a list to clean, two exports to reconcile, a CSV that arrives broken. This course teaches the tool as we actually use it, in Google Sheets and LibreOffice Calc, both free. You finish able to protect the original file, find problems you cannot see, match data across sheets, survive the CSV traps, and deliver work an operator can approve on the first pass.

7
lessons
~34
minutes
12
exam questions

Free · No paid tier · No certificate fee

After this course

Everything, and what is in it.

Never touch the only version

~4 min

The first move on any file

When you claim a task, the files you download may be the only copy of the client's data that exists. Treat the download as evidence, not as a workspace. Before you change a single cell, make a duplicate and do all your work in the duplicate. If a formula goes wrong, a sort scrambles rows, or the operator sends the task back for revision, the untouched original is your restart point. Without it, a mistake can be permanent, and permanent mistakes on client data are how tasks get rejected. This habit costs ten seconds. Every other technique in this course assumes you have done it.

Name the working copy clearly

Keep the original file exactly as it arrived, name and all. Save your duplicate with the original name plus a suffix that says what it is, such as orders_working or orders_clean_v1. If the task runs long, save a new version at each major stage instead of overwriting: v1 after import, v2 after de-duplication, v3 ready to deliver. Versions cost nothing and let you step back one stage instead of starting over. When you deliver, send only the finished file, with a name the client will recognize, unless the brief asks for something else. A folder that reads original, working, delivered tells its own story if you ever need to retrace a step.

Freeze the header row

Before you scroll anywhere in a big sheet, lock the header row so column names stay visible. In Google Sheets, use View, then Freeze, then 1 row. In LibreOffice Calc, click the cell below and to the right of what you want locked, then View, then Freeze Rows and Columns. Without frozen headers, row 4,000 is a wall of unlabeled numbers, and entering data one column off is a quiet, serious error. If the sheet is wide, freeze the first column too, so the ID or name that identifies each row travels with you. Widen cramped columns while you are at it. Seeing the data clearly is not cosmetic. Most spreadsheet mistakes start with misreading what a cell belongs to.

Remember

  • The download is evidence. Duplicate it, name the copy, and edit only the copy.
  • Save a new version at each major stage instead of overwriting.
  • Freeze headers before you scroll. Many errors start with misreading which column a cell belongs to.

Sort and filter are investigation tools

~4 min

Sorting shows you the edges

Sorting is the fastest way to meet a dataset. Sort a numeric column and the extremes surface: blanks and zeros at one end, suspicious outliers at the other, and any text trapped among the numbers grouped by itself. Sort a name column and near-duplicates land next to each other where you can see them. Before you clean anything, sort each important column once and skim both ends. Two minutes of this tells you what kind of trouble the file holds: missing values, impossible amounts, dates typed as text. We treat sorting as a diagnostic, not just a way to arrange the final file. The problems you find here decide what the rest of the task looks like.

Sort whole rows or scramble the file

The most destructive spreadsheet mistake is sorting one column by itself. If only the Amount column moves, every amount detaches from its row and reattaches to the wrong customer, and there is no way to see it happened. Every value still looks valid. Protect yourself two ways. First, select the entire table before sorting, or use Data, then Sort range in Google Sheets with the header option ticked. Second, when LibreOffice Calc asks whether to extend the selection, always say yes. If you ever suspect a partial sort happened, stop and go back to your untouched original. This is the single strongest reason the working copy exists.

Filters isolate one question at a time

A filter hides every row except the ones that answer your current question. Filter Status to a single value and the row count in the corner tells you how many there are without counting. The filter dropdown itself is a finding: it lists every distinct value in the column, so typos like Shipped, shipped, and Shiped stand out immediately, and a blank entry in the list means missing data. Two warnings. Rows hidden by a filter are still in the file, so clear every filter before you export or deliver, or you will misjudge what you are sending. And when you delete filtered rows, check the visible row numbers first so you know exactly what is going.

Remember

  • Sort each key column and skim both ends before cleaning anything.
  • Never sort a single column alone. Whole rows move, or nothing moves.
  • The filter dropdown lists every distinct value, exposing typos and blanks at a glance.
  • Clear all filters before exporting or delivering.

The dirt you cannot see

~5 min

Invisible whitespace

Two cells can look identical and still not match, because one carries a trailing space, a doubled space, or a non-breaking space pasted in from a web page or PDF. This invisible dirt is why lookups fail, why filters show the same name twice, and why counts come out wrong. To test whether two suspicious cells really match, type an equals formula comparing them, such as =A2=B2, or compare their lengths with LEN. To clean a column, add a helper column with =TRIM(A2) and fill it down. TRIM removes leading, trailing, and repeated spaces. Wrap it as =TRIM(CLEAN(A2)) to also strip non-printing characters that arrive in exported files. Clean the helper, then move the clean values back, as the last section shows.

Numbers stored as text

A column can look numeric while some cells are actually text, usually after a CSV import or a paste from another system. The signs: those cells hug the left edge while real numbers sit right, SUM returns far less than the visible values suggest, and sorting puts them in a strange order. Do not retype them. In a helper column, multiply each by 1, as =A2*1, or use =VALUE(A2); either forces text into a real number. In LibreOffice Calc, Data, then Text to Columns on the selected column also converts in place. Dates stored as text behave the same way and break date sorting, so check them too. A total that is too low is almost never missing data. It is text pretending.

Paste values, then remove the scaffolding

Helper columns are scaffolding, and scaffolding comes down before delivery. Once a helper column holds clean values, copy it, then paste it over the original column as values only: Edit, then Paste special, then Values only in Google Sheets, or Paste Special in Calc with only numbers and text ticked. Then delete the helper column. If you skip the values-only step, you paste formulas that still point at the helper column, and they all break the moment you delete it. Make this a reflex: formulas are for working, values are for delivering. A delivered file should contain no helper columns, no leftover formulas referencing deleted ranges, and no error cells, because the client sees exactly what you leave behind.

Remember

  • Cells that look identical but will not match usually differ by invisible spaces.
  • TRIM and CLEAN in a helper column fix a whole column at once.
  • Left-aligned numbers and a too-low SUM mean text pretending to be numbers.
  • Paste values over the original, then delete the helper column.

Flag duplicates before you delete them

~5 min

COUNTIF exposes every collision

Before touching duplicates, see all of them. Add a helper column beside the column that should be unique, such as order ID or email, and enter =COUNTIF(A:A,A2), then fill down. Every row now shows how many times its value appears in the whole column. Filter the helper for values greater than 1 and every collision is in front of you at once, with its full row visible. This beats scrolling a sorted column, because your eyes miss repeats that a count cannot. It also beats running a removal tool blind, because you see what would be affected before anything changes. Counting first, acting second is the pattern for every destructive operation in this course.

A match is a claim, not a fact

Two rows sharing an email are not automatically the same person. It could be one customer entered twice, a couple sharing an address, or a data-entry error that borrowed the wrong email. Built-in Remove Duplicates tools make this worse in two ways: they compare only the columns you tick, and they keep the first row and silently discard the rest, without ever showing you what was thrown away. If the discarded row held the correct phone number and the kept row held an old one, the tool just destroyed the better data. Reserve Remove Duplicates for rows that are identical in every column. Anything less than a full-row match is a judgement, and judgements about client data are not yours to make silently.

Flag first, delete only on instruction

Unless the brief explicitly says to delete duplicates, mark them and report them. Note in your delivery how many collisions you found and where they are, and leave the rows in place or in a clearly marked state, whichever the brief supports. If the brief does say to remove duplicates, remove only exact full-row matches and state the count you removed. When two rows collide but conflict, like one email carrying two different names, that is exactly the kind of uncertainty we want flagged, not resolved by guessing. A note that says you found 32 collisions and left 2 conflicting pairs for a decision reads as careful work. A silent deletion that later proves wrong reads as damage.

Remember

  • COUNTIF greater than 1 in a helper column shows every collision before you act.
  • Remove Duplicates keeps the first row and silently discards the rest.
  • Only exact full-row matches are safe to remove, and only when the brief says so.
  • Conflicting rows are a flag for the operator, not a coin flip.

Reconcile two sheets with lookups

~5 min

The reconciling job

A common overnight task hands you two lists that should agree and asks whether they do: an order export against a fulfillment report, a member list against payments received, last month's inventory against this month's. The question is always the same. Which rows appear in both, which are missing from one side, and where do the details disagree. Doing this by eye across thousands of rows is guesswork. A lookup formula does it exactly: for each row in the first sheet, it reaches into the second sheet, finds the matching key, and brings back a value you can compare. Master one lookup pattern and this entire family of tasks becomes routine.

VLOOKUP and XLOOKUP, exact match only

The workhorse is =VLOOKUP(A2, Sheet2!A:C, 3, FALSE). Read it as: take the key in A2, find it in the first column of Sheet2 columns A to C, and return the value from the third column of that range. The FALSE at the end forces an exact match, and for reconciling it is not optional. Without it, the formula returns the nearest value it can find and you get confident wrong answers. Google Sheets and newer LibreOffice Calc versions also offer XLOOKUP, which is easier to read: =XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "missing"). It looks in one range, returns from another, and shows your own message when there is no match. Use whichever your tool has, and always demand the exact match.

An #N/A is a finding, not a nuisance

When a lookup returns #N/A, it is telling you no exact match exists. Before you believe it, rule out the dirt from the previous lesson: TRIM both key columns, and make sure one side does not hold real numbers while the other holds the same digits stored as text, because those will not match either. After cleaning, run the lookup again. The #N/A rows that remain are the real answer to the task: these rows exist on one side only. Count them and report them in your delivery note. Never blank out or delete #N/A cells to make the sheet look tidy. The mismatches are what the client is paying to learn.

Remember

  • The FALSE fourth argument forces exact match. For reconciling it is never optional.
  • TRIM both key columns and fix text-numbers before trusting any lookup result.
  • The #N/A rows that survive cleaning are the finding. Report them, never hide them.

The CSV minefield

~6 min

Opening is not importing

A CSV file is plain text, and something has to decide how to slice it into columns, what encoding the characters use, and whether a value is a number, a date, or text. Double-click a CSV and your spreadsheet makes all of those decisions silently, by guessing. Importing means you make them. In Google Sheets, use File, then Import, and review the options. LibreOffice Calc shows an import dialog every time it opens a CSV; read it instead of clicking through, because that preview is where every trap in this lesson gets disarmed. The rule: never let a client CSV be opened by guesswork. Import it, look at the preview, and set the columns yourself.

Leading zeros and long numbers die

Open a CSV of phone numbers by double-click and 09171234567 becomes 9171234567, because the tool decided it was a number and numbers do not start with zero. Long IDs suffer worse: a 16-digit tracking number becomes scientific notation like 9.3E+15, and the trailing digits are rounded away for good. If you save over the file in that state, the damage becomes permanent. The defense happens at import: in the preview, set every ID-like column, phone, tracking, SKU, postal code, to Text. Text columns keep exactly what the file contains. If the zeros are already gone in a sheet you were given, do not pad them back by formula, because you would be guessing which values ever had one. Flag it.

Dates and accents get mangled

The date 03/04/2026 is March 4 in one convention and April 3 in another, and an import can silently swap every day and month in the file. Watch the sample values in the import preview, and if the source format is ambiguous and the brief does not settle it, flag it rather than guess: a wrong assumption corrupts every date at once. Accents break differently. When names arrive as garbled sequences like José, the file was decoded with the wrong character encoding. Reimport and set the encoding to UTF-8, which fixes most files; if it still looks wrong, try Windows-1252, common in files from older systems. Never repair mangled names by hand, because you will miss some and invent others.

Delimiter surprises

Not every CSV uses commas. Files from European systems often use semicolons, and some exports use tabs or pipes. The symptom is unmistakable: the import preview shows every value crammed into one giant column. The fix is one click, changing the delimiter in the import dialog until the preview snaps into clean columns. The subtler trap is a comma inside a value, like Smith, John, in a file that does not quote its fields properly, which shifts everything after it one column right for that row. Skim the last column after import; stray values there usually mean a shifted row. Fix a handful by hand and note it, but if the file is riddled with them, flag it to the operator.

Remember

  • Never open a client CSV by double-click. Import it and read the preview.
  • Set phone, ID, and postal-code columns to Text at import, or zeros and digits die.
  • Ambiguous dates corrupt silently. If the brief does not settle the format, flag it.
  • Garbled accents mean wrong encoding. Reimport as UTF-8; never fix names by hand.
  • One giant column means the wrong delimiter. Change it in the import dialog.

Deliver without undoing your work

~5 min

Exporting back to CSV

If the brief asks for CSV back, export it deliberately. In Google Sheets, use File, then Download, then Comma Separated Values. In LibreOffice Calc, use Save As with Text CSV, keep UTF-8 as the character set, and use the delimiter the brief asks for. Two things to know before you click. CSV holds values only: formulas, colors, notes, and frozen panes do not survive, so paste everything as values first, as the earlier lesson showed. And CSV exports only the active sheet, so if your workbook has helper tabs, make sure the sheet on screen is the finished one. If the brief does not name a format, deliver the format the client sent, because they chose it for a reason.

Verify in a text editor, not by reopening

Here is the trap that catches careful workers. You protected the leading zeros, exported the CSV, and double-clicked it to check your work. The zeros look gone again. They are not. Reopening a CSV in a spreadsheet reruns the same guessing that stripped them the first time; you are looking at the display, not the file. The honest check is a plain text editor, Notepad on Windows or any equivalent, which shows the file's actual contents. If the zeros, dates, and accents are right there, the file is right, and that is what the client's system will read. Verify every export this way before uploading your delivery. It takes thirty seconds and it is the difference between knowing and hoping.

Confidentiality and the delivery note

Client files never leave the task. That means no uploading the file to an online converter, no pasting rows into an AI tool to fix a delimiter, no keeping a sample for your portfolio, unless the brief itself says to. When a file defeats your tools, that is a flag to the operator, never a reason to move data somewhere else. After the task is approved, delete the original, your working copies, and anything left in your downloads folder. Then write the delivery note like a map of your work: rows in and rows out, what you changed, what you flagged, and any counts that matter, such as collisions found or mismatches remaining. The note is what lets the operator approve your work on the first read.

Remember

  • CSV keeps values only and exports only the active sheet. Paste values first.
  • Verify exports in a text editor. Reopening the CSV repeats the guessing that broke it.
  • A stuck file is a flag to the operator, never a reason to upload client data elsewhere.
  • The delivery note maps your work: rows in, rows out, changes, flags, counts.

Sit the exam

The courses are free. The work is real.

Twelve scenario questions. Pass at ten. Three attempts a day. The bar is the point.

No paid tier. No certificate fee. No upsell. Not now, not later.

Create a free account

You can start the first course tonight.

The curriculum

Everything, and what is in it.