Can you / How do you chart a the relationship between two cells (preferably
without making two tables). It feels like this should be a simple function
of excel.

I want to see how a cell is effected when I put numbers 1-1,000,000 in it.

For example, I have a worksheet that asks how many units I am managing. I
put in that number in a single cell and my worksheet comes alive telling me
how many people I need to have, how much space I need, etc, etc. But most
importantly it shows me the bottom line profit. I want to chart the
relationship between those two cells (number of units and profit).

example data:
If I have 10 units, my profit is -$200.
20 units / -$50 profit
30 units / +$40 profit
50 units / -$70 profit (because with 50 units I need to buy more space)
80 units / +$160 profit
etc.

A chart would help show how well my worksheet flows.