How to use TOCOL: Google Sheets & Excel guide

If you work in sales, marketing, or at an agency, you live in spreadsheets. Campaigns by channel, territories by rep, SKUs by region – everything ends up in a grid. The problem is that most tools downstream (CRMs, BI tools, AI models) want clean, single‑column lists. That’s exactly what Excel’s TOCOL function – and TOCOL‑style patterns in Google Sheets – are for.

Below are three layers of sophistication: from manual, to no‑code, to fully agentic automation.

‍

1. Manual ways to flatten data with TOCOL

‍

1.1 Basic TOCOL in Excel

Use this when you have a tidy 2D range and just need it stacked into one column.

1. Open your Excel workbook and identify the 2D range (for example, A2:D10 with campaigns as columns and weeks as rows).
2. Click a blank cell where you want the stacked list to start (say, F2).
3. Enter:
4. =TOCOL(A2:D10)
5. Press Enter. Excel will spill a single column of values starting in F2.
6. If you want to ignore true blank cells, use:
7. =TOCOL(A2:D10,1)
8. To read by column instead of by row, use:
9. =TOCOL(A2:D10,1,TRUE)

Microsoft’s official TOCOL documentation is at: https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed

‍

1.2 Cleaning blanks and errors

TOCOL has an ignore argument:

• 0 – keep everything (default)
• 1 – ignore blanks
• 2 – ignore errors
• 3 – ignore blanks and errors

Example for a messy export with errors:

=TOCOL(A2:D500,3)

This flattens your range into a single column and silently skips empty cells and error values (like #N/A) so you get a clean list.

‍

1.3 Combining TOCOL with UNIQUE and SORT

For deduplicated, ordered lists:

=UNIQUE(SORT(TOCOL(A2:D500,3)))

This pattern is perfect for creating unique lead lists from multiple campaigns, consolidated product catalogs, or normalized UTM parameter sets.

If UNIQUE doesn’t seem to work after TOCOL (a common edge case), normalize the data first:

=LET(t,TOCOL(A2:D500,1), n,FILTER(VALUE(t),t<>""), UNIQUE(SORT(n)))

This coerces text numbers to real numbers and drops pseudo‑blanks before deduping.

‍

1.4 Google Sheets manual equivalent (FLATTEN pattern)

Google Sheets doesn’t have TOCOL (yet), but you can get similar behavior using FLATTEN and array formulas.

1. Put each source range in an array literal:
2. =FLATTEN({A2:D10;F2:I10})
3. Wrap with UNIQUE or SORT as needed:
4. =UNIQUE(SORT(FLATTEN({A2:D10;F2:I10})))

See Google’s official function reference here: https://support.google.com/docs/table/25273

This gives you a single, stacked column you can use across dashboards and pivot tables.

‍

1.5 Copy–paste values for static snapshots

Once TOCOL has spilled your list:

1. Select the spilled range.
2. Press Ctrl+C (Cmd+C on Mac).
3. Right‑click where you want a static snapshot.
4. Choose Paste Special → Values.

This is useful for one‑off analyses, list handoffs to SDRs, or importing into tools that don’t support dynamic arrays.

‍

2. No‑code methods with automation tools

Once you’ve got repeatable patterns, you don’t want to be the one opening files every day. Here’s how to offload the busywork without writing code.

‍

2.1 Excel + Power Automate

For Microsoft 365 users, Power Automate can run your TOCOL‑based flows on a schedule.

Typical pattern:

1. Create an Excel file in OneDrive or SharePoint with:
   • A raw data sheet (where CSVs or form responses land).
   • A “Cleaned” sheet with a TOCOL formula referencing the raw sheet.
2. In Power Automate, create a cloud flow:
   • Trigger: Recurrence (e.g., every morning at 7:00 AM).
   • Action: Get file content for your workbook.
   • Action: Refresh workbook or “Run script” if you’re using Office Scripts.
3. Ensure the workbook recalculates and TOCOL spills the updated list.
4. Add downstream actions (e.g., push the cleaned TOCOL output to SharePoint lists, Dynamics, or your CRM).

Reference on dynamic arrays and spilled ranges: https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-64e5c57e-11c3-4b99-b5f2-ef20c45c0b68

‍

2.2 Google Sheets + Apps Script / triggers (low‑code)

Even if you’re not an engineer, a simple Apps Script can help refresh TOCOL‑style FLATTEN formulas.

1. Store your raw exports in one sheet (e.g., “Raw_Leads”).
2. In “Flattened_Leads”, use:
3. =UNIQUE(SORT(FLATTEN({Raw_Leads!A2:D;Raw_Leads!F2:I})))
4. In Extensions → Apps Script, add a tiny script that just opens and recalculates the sheet, or triggers whatever light data prep you need.
5. Add a time‑driven trigger (e.g., hourly) so your flattened list is always fresh.

Official Apps Script docs: https://developers.google.com/apps-script

‍

2.3 Zapier/Make syncing into a “flat list” sheet

No‑code integration tools like Zapier or Make can:

• Listen to new rows in various Sheets or Excel files.
• Append them into a master “Flat_List” sheet that already has your TOCOL/FLATTEN logic.
• Notify a channel (Slack, email) when the list updates.

You’re still relying on spreadsheet formulas, but the ingestion of raw data is automated.

‍

3. Scaling TOCOL with an AI agent (Simular)

At some point, your workflows cross from simple to messy: multiple files, different layouts by client, dozens of little corrections that no formula or static integration fully captures. This is where an AI computer agent like Simular shines.

Simular Pro is a production‑grade computer‑use agent that can operate your entire desktop environment: open files, click through dialogs, type formulas, and integrate with browser‑based tools. Instead of you being the “glue” between exports and models, the agent becomes the glue.

‍

3.1 Agent pattern: daily lead‑list normalization

Story: Every night, your SDR manager drops CSVs from three ad platforms into a folder. You used to:

• Open each CSV in Excel.
• Paste into your “Raw” sheet.
• Update TOCOL formulas.
• Fix errors and blanks.
• Export a clean list for the team.

With Simular:

1. You record or describe the flow once:
   • Open the folder.
   • For each new CSV, insert into the “Raw” sheet.
   • Ensure the TOCOL formula (or FLATTEN pattern in Sheets) references the full range.
   • Copy the cleaned, deduped TOCOL output into “Leads_Final”.
2. The Simular agent replays and improves this process autonomously every day.
3. Because every action is logged and inspectable, you can tweak the workflow when layouts change.

‍

Pros:

• Handles multi‑step, multi‑app workflows across Excel, Google Sheets, email, and browsers.
• Production‑grade reliability: suitable for high‑volume sales and marketing ops.
• Transparent execution: every click and formula is auditable.

Cons:

• Requires an initial investment of time to design a robust workflow.
• Best suited when the process is repeated frequently (daily/weekly) and touches multiple tools.

‍

3.2 Agent pattern: agency reporting at client scale

For agencies juggling dozens of clients, each with their own report templates:

1. Store client‑specific report templates in Excel or Sheets.
2. Use TOCOL (or FLATTEN) inside each template to normalize multi‑tab data.
3. In Simular, define a playbook:
   • For each client, open the right template.
   • Pull the latest platform exports (via browser, API dashboards, or emailed attachments).
   • Paste into the “Raw” areas.
   • Let TOCOL‑based formulas recalc.
   • Export PDFs or share links back to the client.

Pros:

• Massive time savings for account managers.
• Consistent, repeatable output with fewer human errors.

Cons:

• You must clearly document edge cases (e.g., missing columns) for the agent.

By combining strong spreadsheet patterns (TOCOL, UNIQUE, SORT, FLATTEN) with a desktop‑class AI computer agent, you move from “I have to remember how to clean this data” to “The system cleans it, I just review the story it tells.”