Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm struggling to solve a problem with my Excel document so I'm hoping that someone more experienced can help?

I have a cell that updates my account balance after each trade is recorded. I then have in my ROW for each trade the % P&L based on the account balance at the time the row is completed - the problem is that the account balance cell is dynamic so as that changes then so does the individual trade P&L % (win/loss) in each row? I have tried to use the $h$1 cell lock but this is not working?

I started with a £5k balance so what I need to achieve is the % P or L for that particular trade at the time the trade was closed; currently it keeps changing as the account balance changes which is no use for recording purposes?

Can any one help? Thank

share|improve this question
Welcome to superuser. Can you give a screenshot or a short example XLS uploaded somewhere? That would make it easier to help. – nixda Feb 6 '13 at 20:19
You’re probably going to need to use VBA for this. P.S. It’s often helpful to specify what version of Excel you are using. – Scott Feb 7 '13 at 4:14

This is possible, but is not 100% secure. I suggets using dedicated accounting software for this.

In excel you are limited to making a calculation only update when you manually tell it to update.

From the top menu select Formulas then under Calculation Options select Manual...

enter image description here

You need to be aware that this will NOT prevent the calculation from happening again but it will not happen until someone tells it to calculate again.

share|improve this answer
Many thanks Kevin - I'll give that a try! Is there a way of deleting the formula once it has activated and replacing the cell value with the result? That way once calculated it wouldn't change? – Danny Feb 7 '13 at 9:56
That is very simple, simply copy and "paste value" on top of the cell. You can right click to get the option to "paste value" – Kevin Anthony Oppegaard Rose Feb 7 '13 at 10:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.