If you are planning a budget for a trip involving two different currencies, fluctuations in conversion rates can create more work for you. The solution is to use a formula that automatically updates all the results if the conversion rate changes.
At the top of my worksheet, I have added a currency conversion rate. I know that today my £1 buys me €1.24.
Tip: to add the € symbol, hold down the the ALT Gr button, located on the right of the space bar, and press 4. This will add your euro symbol
I now type in the details of my budget, and then calculate the total cost from pounds to euros using the conversion rate at the top of my worksheet.
The trick is to use the ‘$’ symbol in the formula, on the cell reference containing the conversion rate. This is known as an absolute reference.
Tip: Use format painter to copy the euro formatting in B2 into the columns with euro costs. Click on B2 then click and drag over the cells you want to apply the format to.
So my worksheet will look like this:.
Later in the week, the conversion rate changes to €1.35. I don’t have to manually adjust all the figures in the Total cost in £ in column F – I update cell B2 with the new rate and all the figures in column F will be updated automatically.
So all my figures are updated and my budget now looks like this:
I find that my formula today is Excel + training = useful skills!
This is a simple formula with a practical application and it save time as you update one cell for an impact on many calculations. To build on your understanding and use of formulas at all levels of Excel , have a look at https://www.stl-training.co.uk/microsoft/excel-training-london.php.
