How to automate manual Excel work: cut copying and totals while keeping Excel

You do not have to give up Excel. Copying figures each month, building summary tables, creating invoices and delivery notes, reshaping CSV files exported from a system: as long as it is one task, you can automate it and keep using Excel.

This article is about removing one manual chore while Excel stays. Whether to move from Excel to a business system, and how to digitize fax orders, are covered in when Excel hits its limits: how to move off spreadsheets. Here we go through which task to pick, which tool to use, how to fall back when it stops, and whether the cost is worth it.

First, narrow it down to one task

A request to "automate our Excel work" cannot be built. Start by choosing one task and writing it on the following card.

If you cannot fill in this card, the task is not ready to automate. If the procedure differs between people, or decisions about exceptions live only in one person's head, automation stops at every exception and you end up back at manual work. Standardize the procedure first.

Tasks that suit automation share some traits:

• Input of the same shape arrives at a fixed frequency
• Most steps need no judgement
• A person can confirm quickly whether the result is right
• The impact of a mistake can be caught at the review step

By contrast, for tasks that need many judgement calls, have differently shaped input every time, or send mistakes straight to customers (sending invoices automatically, for example), do not automate everything. Consider drawing the line so that creation is automated and sending stays with a person.

Choosing a tool by type of task

The type of task decides the tool. Knowing how each one fails saves trouble later.

Some notes follow. Each point was checked against official documentation such as Microsoft Learn.

Power Query is the first tool to try. It ships with Excel, and the steps you configure remain listed on screen, so others can read them later. You can also set it to refresh when the workbook opens, or automatically at a set interval. The caution is that a renamed source column breaks the step that refers to it. When importing a CSV, set columns where leading zeros carry meaning, such as customer codes, to load as text. Excel strips leading zeros automatically and cannot keep precision beyond 15 digits.

The limits of driving Excel from Power Automate are documented. The Excel Online (Business) connector supports files up to 25 MB, and a file may stay locked for up to 6 minutes after the connector last used it. Writing from a flow to a workbook a person is editing at the same moment is unsupported. Keep "the workbook people type into" and "the workbook the flow writes to" separate. Also, the action that lists rows in a table returns only 256 rows by default; getting all of them needs pagination turned on. "The results went missing once rows grew at month end" is the classic accident.

VBA is still valid, but how you distribute it needs care. In Office on Windows, macros in email attachments or downloaded files are blocked by default. Circulating macro-enabled workbooks by email may not work in the recipient's environment. Agree on an approach with your administrator, such as keeping them in a trusted location on a shared drive.

Python is strong at validation and bulk processing. But you need to manage whose PC it runs on and which version of Python, and it is also the tool most likely to cause trouble once its author has gone. The official documentation for openpyxl, a library that edits Excel files directly, warns that it does not read every item in a workbook, so shapes are lost when a file is saved under the same name, and that saving overwrites existing files without warning.

Whatever the tool, when in doubt ask whether a successor can read the configuration on screen. If they do the same job, Power Query settings beat code, and a written procedure beats Power Query settings, when it comes to handover.

Designing around Excel: four rules that keep it from breaking

Whatever the tool, automation that does not break easily has a common shape.

[Import folder] → [Check] → [Working table] → [Output (summaries, documents)]
 originals dropped    ↓              ↑
 here, untouched  [Rows to review]  a person fixes and resubmits

1. Never overwrite the original. Keep imported files as they are, and write output to a different file. When something goes wrong, you can rebuild from the original.
2. Separate where people type from where automation writes. If both write to the same sheet, nobody can tell which is right. It also avoids Power Automate's simultaneous-editing limit.
3. Check before importing, and set aside rows needing review. Avoid both silently importing bad rows and halting everything over one bad row. Process the good rows and hand the rest to a person as a list with reasons.
4. Never import the same file twice. A common automation accident is importing the same month twice and doubling sales. Record files already imported and stop the second time.

Start input checks with Power Query's "Keep errors"

Rules 3 and 4 can be started without writing code.

1. In the import query, change the customer-code column to Text, and the date and amount columns to Date and a number type. Values that cannot be converted (a nonexistent date, NaN and so on) become cell-level errors.
2. A blank customer code does not cause a type-conversion error, so add a filter step that picks out blank rows.
3. Duplicate the query. In one copy use "Remove errors" to keep only the good rows; in the other use "Keep errors" to keep only the rows with errors. The latter is your review list.
4. To prevent double imports, name files dropped into the import folder by month, such as "売上_2026-10.csv", and move files already imported to another folder.

The person in charge reads the review list, fixes the source data and imports the corrected file again. What matters is not the tool but having a route that separates good rows from rows to review and hands the latter back to a person.

If you want finer control over the checks, or have many files, you can write them in Python instead. A working example is in the appendix at the end of this article.

Falling back to manual work when it stops

Automation will stop sooner or later: a source column is renamed, a licence lapses, someone's PC is replaced. So that the business does not stop with it, fill in this table before you build.

The assumption behind this table is never throw away the manual procedure. Nobody reads it while automation is running, but you will need it the day it stops. Checking once a year that you can still process by hand from the procedure gives peace of mind.

The simplest and most effective check on results is matching counts and totals. Show the source row count and total amount next to the output's row count and total every time, and have the reviewer confirm by eye that they agree.

Who fixes it after the builder has gone

The smaller the automation, the more likely handover is skipped. At build time, leave at least these four things.

The last item is often missed. Microsoft calls a flow with no valid owner after its creator leaves the organization an "orphaned flow", and explains that it can fail if it uses connections tied to that user's account. Give business flows a co-owner other than their creator from the start, and include a flow check in your leaver process.

A formula for whether the cost is worth it

Measure the benefit of automation in "time saved". You can turn it into money by multiplying by an hourly rate, but comparing in hours first makes misjudgements less likely.

Months to pay back = time to build ÷ (time saved each month − monthly maintenance time)

Time to build          = internal time + outsourcing fees ÷ internal hourly rate
Time saved each month  = time spent before automation
                       − time spent checking and handling exceptions afterwards

Hypothetical example: Suppose you automate the monthly summary from the task card above (about 4 hours a month). All figures are invented for illustration.

The formula tells you three things.

• Infrequent tasks take long to pay back. Automating a once-a-year task may save too little ever to pay back. Tidying up the written procedure works better.
• Many exceptions erase the time saved. If checking and handling exceptions after automation takes nearly as long as the task did before, there is no point automating.
• Write benefits other than time separately. No more copying errors, or the work carrying on when someone is off, do not fit the formula. When judging whether it is worth it, write them in words next to the numbers.

Try the first one in-house, and check your software's built-in documents first

The first one is often something you can try in-house.

• Write the task card and standardize the procedure inside the company
• Try setting up the CSV import and reshaping in Power Query
• Add a field matching counts and totals for each run's check

Your accounting or sales-management software may already have the document or data-link feature you need. Check its feature list before building something in Excel. Built-in features are maintained by the software vendor, so they last longer than something you built yourself.

Consider outsourcing when exception handling is too complex for configuration through the interface, when the task crosses several systems, or when nobody in-house can maintain it. Giving the vendor the task card, sample input and output, and the list of exceptions makes the estimate more accurate. When you commission the work, confirm whether the fallback procedure and handover material are included in the deliverables.

Sometimes Excel is carrying so much of the role of a business system that automation does not solve the root problem. If you separate what each trading partner may see by splitting files, for example, the decision in how to move off spreadsheets comes first.

A real example: a SaaS that kept Excel at the entrance

In the B2B SaaS for the lumber distribution industry that I developed on my own (the client received the Minister of Economy, Trade and Industry Award for this product), ordering that ran on phone, fax and Excel was brought together on the web. Rather than making users abandon the Excel files they were used to, I built a feature that imports existing Excel files into the database, and one that generates purchase orders, delivery notes and invoices as Excel or PDF. The import includes a file-size limit and neutralizes values that would be interpreted as formulas.

That is a different scale from the automation in this article, but the idea is the same: keep the formats people know at the entrance and exit, and automate the checking and processing in between.

If you are considering working with me, the scope and how costs are set are on routine work automation. Where built-in features or Power Query are enough, I will recommend those instead.

Appendix: a pre-import check written in Python

This is rules 3 and 4 of the four above, written in Python. It checks the required columns, date, customer code and amount in a sales CSV, then writes good rows and rows needing review to separate files. A second import of the same file is refused.

import csv, hashlib, io, re, sys
from datetime import datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path

REQUIRED = ["伝票日付", "得意先コード", "金額"]
FORMULA_START = ("=", "+", "-", "@", "\t", "\r", "\n", "＝", "＋", "－", "＠")
PLAIN_NUMBER = re.compile(r"[+-]?\d+(?:\.\d+)?")


def decode(raw):
    try:
        return raw.decode("utf-8-sig")
    except UnicodeDecodeError:
        return raw.decode("cp932")  # raises and stops if neither encoding can read it


def find_problem(row):
    if None in row or any(row.get(c) is None for c in REQUIRED):
        return "列数が合わない"
    try:
        datetime.strptime(row["伝票日付"].strip(), "%Y/%m/%d")
    except ValueError:
        return "日付が YYYY/MM/DD でない"
    if not row["得意先コード"].strip():
        return "得意先コードが空"
    if any((v or "").startswith(FORMULA_START) for k, v in row.items() if k != "金額"):
        return "数式として読まれる値がある"
    try:
        amount = Decimal(row["金額"].strip())
    except InvalidOperation:
        return "金額が数値でない"
    if not amount.is_finite() or amount < 0:
        return "金額がマイナスまたは不正"
    return None


def neutralize(value):
    # write formula-like values as text by prefixing them with '
    # (a plain number such as "-500" or "+100" cannot run as a formula, so it is left as is)
    if isinstance(value, str) and value.startswith(FORMULA_START) and not PLAIN_NUMBER.fullmatch(value):
        return "'" + value
    return value


def save(path, fields, rows):
    with path.open("w", encoding="utf-8-sig", newline="") as f:
        writer = csv.DictWriter(f, fields, extrasaction="ignore")
        writer.writerow({c: neutralize(c) for c in fields})  # header cells are neutralized too
        writer.writerows({k: neutralize(v) for k, v in row.items()} for row in rows)


src, out = Path(sys.argv[1]), Path(sys.argv[2])
out.mkdir(parents=True, exist_ok=True)
raw = src.read_bytes()
digest = hashlib.sha256(raw).hexdigest()
ledger = out / "imported.txt"
if ledger.exists() and digest in ledger.read_text().split():
    sys.exit("中止: このファイルは取り込み済みです")

reader = csv.DictReader(io.StringIO(decode(raw), newline=""))
fields = reader.fieldnames or []
if missing := [c for c in REQUIRED if c not in fields]:
    sys.exit(f"中止: 必須の列がありません: {missing}")

good, bad = [], []
for row in reader:
    problem = find_problem(row)
    (bad if problem else good).append({**row, "行": reader.line_num, "理由": problem})

save(out / "ok.csv", fields, good)
save(out / "needs_review.csv", ["行", "理由", *fields], bad)
with ledger.open("a") as f:
    f.write(digest + "\n")
total = sum(Decimal(r["金額"].strip()) for r in good)
print(f"正常 {len(good)} 件 / 要確認 {len(bad)} 件 / 合計 {total}")

The column names and messages are in Japanese because they match a Japanese sales export; change them to your own headers. I ran it with Python 3.13 using only the standard library. Here is the result for an eight-row test CSV saved in Shift_JIS (containing a nonexistent date, a blank customer code, an amount with a comma such as 1,000, NaN, a negative amount and a row missing columns). The output reads "2 OK / 6 to review / total 5500", and on the second run, "Stopped: this file has already been imported."

$ python3 check_sales_csv.py 売上_2026-10.csv 出力
正常 2 件 / 要確認 6 件 / 合計 5500

$ python3 check_sales_csv.py 売上_2026-10.csv 出力
中止: このファイルは取り込み済みです

The review list (出力/needs_review.csv) carries the original line number and the reason.

行,理由,伝票日付,得意先コード,金額,摘要
3,日付が YYYY/MM/DD でない,2026/11/31,00124,2000,存在しない日付
4,得意先コードが空,2026/11/02,,3000,コード空
5,金額が数値でない,2026/11/03,00125,"1,000",カンマ入り
6,金額がマイナスまたは不正,2026/11/04,00126,NaN,NaN
7,金額がマイナスまたは不正,2026/11/05,00127,-500,返品
8,列数が合わない,2026/11/06,00128,,

With a three-row test CSV containing formula-like values, the result is "1 OK / 2 to review / total 200", and both flagged rows are written with a leading '.

$ python3 check_sales_csv.py 数式テスト.csv 出力2
正常 1 件 / 要確認 2 件 / 合計 200

行,理由,伝票日付,得意先コード,金額,摘要
2,数式として読まれる値がある,2026/11/01,00130,100,"'=HYPERLINK(""http://example.com"")"
5,数式として読まれる値がある,2026/11/03,00132,300,"'
改行始まり"

Here, in words, are the judgements this code makes.

• Encoding. CSVs exported from Japanese systems are often Shift_JIS (cp932 on Windows), so if the file cannot be read as UTF-8 it is read as cp932.
• Commas in amounts. 1,000 is sent for review rather than read as 1000, because the data alone cannot tell a thousands separator from a shifted column. Once you have confirmed that your system's export never uses separators, this rule is unnecessary.
• Negative amounts. If your system records returns as negatives, they should go to a separate total rather than review. That is a business rule, not a programming problem.
• Values read as formulas. Values starting with =, +, -, @, a tab or a line break may run as formulas when the file is opened in Excel (OWASP warns about this as CSV injection). This matters especially for values that come from outside, such as a web form.
• Neutralizing on write. People open the review list in Excel, so such values are written with a leading '. This is one of the mitigations OWASP lists, and the value is treated as text. Column headers get the same treatment. Plain signed numbers such as -500 or +200 cannot run as formulas, so they are written unchanged and the amount column stays numeric. The same treatment applies to the good-row output (ok.csv); the real fix is to have the source export amounts without a sign.
• Duplicate detection. It compares a hash of the file's contents. A file that differs by even one byte, for example a re-export of the same data, passes as a new file, so combine it with a rule of importing each month only once.