Topics: microsoft excel course london
Image you have a list of prices in pounds in one column in Excel 2010, and you want to add another column of the same prices in dollars. This article shows you how to multiply all the cells in one column with a value such as a pound to dollars conversion rate, and produce a second column showing these converted values.
We can store the conversion rate in a particular cell and refer to this cell in our calculation. Should the conversion rate change we want to be able to type in a new value and have all the converted cells change to take account of the new value. We’ll create a list of prices in pounds, add the exchange rate in a single cell, and then show you how to use this to multiply all the prices in your list to create a list of prices in dollars.
We’ll begin with a new worksheet in Excel 2010. Then type POUNDS in cell D4 and DOLLARS in cell E4. Under the POUNDS heading we’ll type these numbers in successive cells down the column, 10, 20, 30 and so on until you reach 100. Don’t forget to press Enter after the last number has been added. Now we’ll format all these cells to show pounds currency, so first select the cells D5 to D14. Then in the Home tab, just right of centre, click the blue Accounting Format button. You’ll see that all the numbers are now showing the correct pounds currency.
Now we’ll add the exchange rate for pounds to dollars. If you Google “pounds to dollar exchange rate” you’ll see the latest rate. Today it’s 1.6043. We’ll add this above the table for convenience so in cell D1 enter RATE. Then in cell E1 enter 1.6043. So we’re going to multiply each of our prices in the list by this rate to convert from pounds to dollars.
Before we do the calculations we need to format the cells under DOLLARS correctly, so select cells E5 to E14, then carefully click the down arrow to the immediate right of the blue Accounting Format button, and left click on dollars. If the dollar symbol is not listed, choose More Accounting Formats. Then if the Format Cells panel click the down arrow right of Symbol and choose dollar, then click OK to finish. Now any numbers added to these cells will be displayed in dollar format. Next we’ll do the calculations.
To start this, select the cell to the right of the first price. So select cell E5 and type this formula =D5*E1 then press Enter to finish. You see that 10 pounds is indeed equal to $16.04. You could on course repeat this for every price in the list, but that means repeating the formula 10 times, and imagine how long it would take for 100 prices. Instead you could try using the Fill Handle to copy and paste the first formula down all the cells.
To do this ensure cell E5 is still selected. Then carefully hover over the small black square at the bottom right of the cell, press and hold down the left button and drag down - this will copy and paste the first formula to all the lower cells. However it will not work - try this and you’ll see that error codes are created. This happens because when you fill a formula down a cell, all the row numbers in the formula increase by one, so the formula changes from D5*E1 to D6*E2 and so on. As the currency rate is only in cell E1 we need to stop the 1 after the E from increasing.
To do this we need to edit the original formula in cell E5. So select cell E5 again. You’ll see the cell formula =D5*E1 in the white Formula Bar immediately about the spreadsheet cells. Carefully hover your mouse over the E1 part of the formula - still in the formula bar, and left click between the E and the 1. You’ll now see a flashing vertical cursor between the E and the 1. Then press the F4 function key - this key is in the top row of keys on your keyboard. You’ll see that dollar symbols have been added before the E and the 1 in the formula, which now reads =D5*$E$1 and then press the Enter key to complete the edit.
Now use the Fill Handle to fill the revised formula down the column as far as cell E14, then click into any other cell to remove the highlight. You’ll see that all the calculations have been completed in one go - and voila all the prices show in dollars. Have a look at the formula in cell E14. This should show =D14*$E$1 and confirms that we are still multiplying cell D14 by cell E1 where the conversion rate is stored. In fact all the formula in the E column contain the part *$E$1 which shows you that every calculation uses cell E1. The effect of the dollar symbol before the 1 in the formula stops the number increasing as you fill, so the price is always multiplied by cell E1 no matter how far down you fill.
Suppose the conversion rate changes to 1.7 and we want to update out table. All you need to do is to select cell E1 and type the new value 1.7 and press Enter. All the cells in the dollars column will change. And that’s our currency convertor completed. If you’re interested in finding out more about Excel formula and the use of the dollar symbol, and many other Excel features as well, consider attending a training course, which is often a good way to build Excel skills in a short time.
Author is a freelance copywriter. For more information on microsoft excel course london, please visit www.microsofttraining.net
Previous Articles Highlighter:
How To Sort A List Of Vehicle Number Plates In Year Order In Excel 2010 (1)
You should now see the single base year number. Fill this formula down the columns so all rows now show the base year for all the license plates. Lastly we'll extract the year identifier, so select cell H4 and type this formula =LEFT(F4,1) and press Enter.
An Essential Guide To Customising The Navigation Ribbon In Office 2010 (2)
This article describes how to do this in Word 2010, but the methods are the same for all these applications. We'll begin by describing what you can and can't do in ribbon customising and then we'll describe the practical steps in Word 2010.
Pictures Speak A Thousand Words With Microsoft Word (3)
Companies are aware of the importance of creating and maintaining their brand. Logos are linked to the ethos that forms the skeleton of your firm and making them instantly recognisable is a good way to attract attention to your organisation.
An Essential Guide To Using The Northwind Database In Access 2010 (4)
So an invoice might list all the order details, customer detail and the full staff name for the person who took the order, by extracting details from more than one table.
Essential Guide To The Backstage View In Office 2010 (5)
Choose Prepare for Sharing for the Document Inspector, Accessibility Checker and Compatibility Checker, all introduced in Office 2007. Choose Manage Versions and you can access auto recovered versions of the current file, provided you have enabled this within Options.
Word: A Way To Break Down Language Barriers? (6)
Topics: word courses Being bi-lingual is a great advantage in today's workplace and your PC could help to boost your language skills. As the EU opened up to companies and individuals there has been an increasing need for employees who can speak more than their native tongues.
A Brief Guide To The New Project 2010 Navigation Ribbon (7)
Above the ribbon tabs you'll see the new Quick Access toolbar with three pre-set commands Save, Undo and Redo. Be aware that you can easily add commands to this toolbar for easy one click access.
Project And Excel: Perfect Partners (8)
So why, with all these specialised features at their disposal, do so many professionals opt for Excel when planning those all important projects? Is it simply a reluctance to learn a new program or does Microsoft Excel really fulfil all the functions that a great many users require?
Excel And PowerPoint Prove To Be Top Of The Charts (9)
Fractals were studied and named by pioneer Benoit Mandelbrot in 1975 and the name is derived from the Latin fractus meaning broken or fractured. A mathematical fractal is based on an equation that undergoes iteration, a form of feedback based on recursion.
How Getting Together Is Now Even Easier With Excel (10)
And if you want to distribute workbooks to users who do not have Excel, that's not a problem. If you don't have access to Excel Services, you can install an add-in to save a workbook in PDF or XPS format, or you can save a workbook to other file formats and then send it to your recipients by using e-mail or fax, or by saving the file on a network share or Web share that the users can access.
Newer Articles Highlighter:
Learn The Secrets Of Customising An Autonumber Primary Key In Access (1)
Then add a new field in the same position with the same name Ref, select the autonumber data type and set it to be the primary key, then save your design changes.
How Photoshop Can Help You Get The Most From Dreamweaver (2)
Any image can be used as a button, and Photoshop makes it easy to design a button that's both clearly understandable and adheres to the consistent style of your site - and these can be simply slotted into your page and connected to a link within Dreamweaver.
No More Shaky First Steps Thanks To Microsoft Project's Ribbon (3)
Project now features a Backstage view, meaning you are able to navigate to these commands without the need to look through several tabs. Author is a freelance copywriter. For more information on ms project training, please visit www.microsofttraining.net
Why It's Time To Listen To The Natives And Get Online (4)
Save your Office files directly to the Web using Windows Live SkyDrive, which lets you access them from almost any Web browser, you can then edit and review your reports or presentations online with Web Apps from virtually anywhere.
How Office Clipboard Cuts Across Program Boundaries (5)
In the same way as no warning is offered that earlier copies will be lost when copying more than 24 items, if you close all Office applications then the contents of the Office clipboard will be lost.
Manage Your Macros With Access 2010 (6)
There are many more adaptations and improvements that have been made to the program aside from the enhanced ability in macro use and the best way to get your head around all of the benefits that the program can offer is to take a course in Access.
Simplifying The Web With Dreamweaver CS5 (7)
Once you have successfully got to grips with them creating web pages will be an infinitely smoother, more accessible experience. Author is a freelance copywriter. For more information on dreamweaver courses, please visit www.microsofttraining.net
How To Create A Real Line Graph In Excel 2010 Rather Than A Line Chart (8)
With the new chart selected, you can add X axis and Y axis labels as follows. Select the Layout tab, click the Axis Titles button, choose Primary Horizontal Axis Title, then select Title Below Axis.
Break The Mystery Of Code With Excel Macros (9)
Excel has a macro facility, known as Excel 4 macros (XLM for short), that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA.
Combining Dreamweaver With Other Adobe Software For The Perfect Website (10)
Given that 'perfect' is not how the pictures will always come off the camera, there's a lot to be gained from improving them with Photoshop - and it's simple to drop Photoshop images straight into a Dreamweaver site, without having to leave the program.