How to create a process flow from Excel data
How to read a process flow out of the spreadsheet you already maintain: which existing column is the boxes, which is the arrows, which is the branch labels, and which one is a swimlane diagram.
A process flow from Excel data is a re-reading of columns you already have: the step column is the boxes, row order is the arrows, an exception column is the branch labels, an owner column the lanes.
The short version
- The step column is the boxes, and it needs no conversion at all.
- Row order gives you the arrows only as far as the first exception.
- A condition or exception column is the branch labels, one per outgoing arrow.
- Lanes are keyed by their label, so the owner column has to be normalised before it is mapped, not after.
- Whatever sits in a Notes column is usually a route nobody ever drew.
The conversion is really a column audit
A process spreadsheet does not need converting; it needs reading properly. The naive version pastes the step column, gets a ladder of rectangles, and stops, which discards the four other columns that were carrying the process. The steps were never the scarce part. What the sheet holds and cannot show is which step follows which, what makes the flow leave the sequence at all, and who is holding the work while it waits.
Which of your columns maps onto which editor column is the whole of the work, and the owner column is the one people leave behind. Kept as a note it is a caption on a box; mapped onto Vertical lane it draws the handoffs, and a tab nobody thought of as a diagram turns out to have been a process map with its lanes switched off. Normalise the spellings first: lanes are keyed by label, so "Credit control" and credit control become two of them.
The example below is an order-to-cash process, twenty-six rows and six owners, assembled one column at a time. "Supply the missing order details" points back at row 2, so the order the sheet was kept in was never the whole story. "Short-paid", "Unpaid" and "No response" (the words a real sheet keeps in a notes field) have nowhere to point until the exception rows they name exist, which is why they arrive only in the last two stages.
Operations sheet to process map
Order-to-cash assembled one spreadsheet column at a time. Watch which arrows row order could never give you, and which rows the notes column was hiding.
Row order is already the arrows
The step column supplies five labels, and most of their arrows are implied by the order they came in. Two are not. "Order and master data complete?" skips over row 3, and "Supply the missing order details" points back at row 2: a number smaller than its own row is what row order cannot say.
Which rows are really questions
The shape column turns three of the first nine rows into diamonds, and "Credit hold released?" reveals three exits with nothing written on them yet. Row 9 becomes a terminator: "Order cancelled on credit refusal" is where an order stops for good, and the sheet had it as an ordinary step.
The exception column arrives
Line text says which case each arrow carries: "Over limit", "Prepay first", "Refused", and "Obtained" on the loop back to "Proof of delivery received?". "Paid in full by the due date?" still shows one exit, because the rows its other two answers need are not in the sheet yet.
The rows the notes column hid
"Short-paid" now has somewhere to go: the deduction is coded, a dispute opened, "Deduction valid?" decides it, and a credit note clears the line back into "Invoice cleared and order closed". Four rows the sheet carried as one sentence, and a decision it never had at all.
Owners turn it into a map
The dunning ladder arrives, then the lane columns make every handoff visible. Three boxes drop into the "Customer" lane (the order, the details it had to supply, the prepayment), and a wait on the customer is not the same problem as a wait on "Credit control".
How it works
Inventory the columns before you paste
Write out every column heading in the sheet and put beside it which editor column it becomes: Box text, Line to, Line text, Horizontal lane, Vertical lane, or nothing. Most operational sheets already fill four of those. The headings that map to nothing (a status flag, a cycle time, a system name) belong in the step's comment.
Read the notes column for hidden routes
Go down the Notes or Comments column and mark every sentence containing if, on hold, unless, or chase. Each one is a step the sheet never gave a row to, and each needs its own row before any numbering starts. Expect to finish with more boxes than the row count you began with.
Paste the step column into Box text
Open a new chart in QueryChart and paste the step column, and only the step column, into Box text. One column wide is deliberate: the other four already exist in the sheet and get mapped one at a time, so you can watch what each of them adds instead of pasting five at once and unpicking the result.
Number the routes, backwards ones included
Line to holds the row numbers a step leads to, separated by commas. On the straight-through path that is just the next number down, which the row order already told you. The work is everything else: the correction that returns to an earlier check, the query that rejoins mid-flow. Those numbers are smaller than the row holding them.
Map the owner column onto Vertical lane
Move the owner column into Vertical lane and any phase or stage column into Horizontal lane. Check the phase column first: Horizontal lane orders the chart left to right, so its values must be phases the process passes through in sequence. A Status column records the state one order has reached, and those states have no sequence.
Hand it back to the sheet's owner
Whoever has been maintaining the tab is the only reviewer who can say which of the notes you promoted to rows were real exceptions and which were one-offs that happened twice. Send them the link rather than an exported image: the tab already exists in four versions because it was once attached to an email, and a picture starts that again.
Mistakes to avoid
Pasting a merged owner column
Merges paste as a blank for every row but the first, so a run of steps arrives owned by nobody. Unmerge and repeat the name, then check whether the merge was hiding two owners on one step, because that is a handoff.
Exceptions parked in a Notes column
A note reading if the customer disputes, credit control picks it up is a decision, a branch label and a step in one sentence. Left in the notes it stays invisible, and a reader of the diagram never learns the route exists.
Trusting row order after the first branch
Row order is a dependable arrow only while the process is a queue. Once a step can send work backwards, or somebody sorted the sheet, or a row was inserted late, the ordering is decoration and every number needs checking.
Rules that live in the fill colour
Operational sheets encode state in fill colour (red for escalated, grey for cancelled) and colour pastes as nothing at all. Ask what each shade meant and write the answer into a column, because a rule nobody typed is still a route.
Map your own order-to-cash process
Open the order-to-cash example as your own chart, then paste your own columns over it: steps, routes, exceptions and owners.
Related guides
Drawing quantities instead of steps?
If the numbers in your sheet are volumes rather than steps, for example units moving between stages, draw them as a Sankey diagram instead.
Frequently asked questions
How do I turn an owner column into swimlanes?
Map it onto the Vertical lane column, and the phase column, if you have one, onto Horizontal lane. Lanes are keyed by their label rather than by an id, so every row carrying the same text lands in the same lane and a single typo creates a second one. Two things the column will not give you: the steps your customer or supplier performs, because a tab records the work your own team is measured on, and the second owner on a step that two roles share. Both have to be added by hand, and both are handoffs.
Do I need to add a next-step column to my spreadsheet?
Yes: every row that is not an ending needs a number in Line to, because row order draws no arrows by itself. What you do not need is that column already finished in Excel. On the straight-through path the number is mechanical, the next row down, and it can be typed as you go. The one you almost certainly do not have is the branching set: a refusal returning to an earlier check, a query that rejoins mid-flow, an end state that stops the work early. Only three rows in the example carry no target.
How do I convert a spreadsheet with a column per team?
Unpivot it first. A matrix sheet (steps down the side, teams across the top, a mark where a team is involved) keeps its owner values in the column headings, and the editor wants them in one column: one row per step, the heading of the marked column in Vertical lane. A row bearing two marks will not collapse into one lane value, and it is usually two steps that arrive in different lanes. Columns holding dates instead of marks belong to a tracker: they record when one order passed each stage.
Why does the diagram have more boxes than my sheet has rows?
Because a sheet records the steps somebody does and hides the ones that depend on something going wrong. Every exception written into a notes field becomes a row of its own, together with the decision that routes to it and the end state where the process stops early. A forty-row process sheet producing fifty-odd boxes is normal, and the extra boxes are precisely the ones the sheet was worst at showing you.
