{"id":390,"date":"2016-12-22T16:14:12","date_gmt":"2016-12-22T22:14:12","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=390"},"modified":"2016-12-22T16:16:07","modified_gmt":"2016-12-22T22:16:07","slug":"replacing-an-excel-formula-with-a-number","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/replacing-an-excel-formula-with-a-number\/","title":{"rendered":"Replacing An Excel Formula With A Number"},"content":{"rendered":"

Formulas are at the heart of what makes Excel so useful. But sometimes you need to replace a formula with the number that is the result of that formula. Maybe you’ve used VLOOKUP()<\/a> to pull in data from another workbook, or used RIGHT()<\/a> to extract the zip code from a city\/state\/zip column and now you just want that data, not a link to data in another location. Obviously, you could manually type the numbers in the cells. \u00a0But that brings the opportunity for errors and isn\u2019t efficient when changing more than a few cells. \u00a0So here are 3 methods that are faster and more reliable.<\/span><\/p>\n

Calculate using F9: <\/span><\/strong>If you only have a few cells that you want to convert from a formula to a value, you can use this simple method that probably goes back to the early days of Lotus 123. (Google it if you\u2019re too young to know what that is.) \u00a0Select the cell you want to change, press F2 to edit it. Then press F9 and you will see the formula change to a number. \u00a0Instead of using F2, you could also double click the cell, or click in the formula bar. \u00a0This method is quick and simple, can be used while entering a formula, and you can even highlight part of a formula to calculate just that portion of it. \u00a0But it can only be used on one cell at a time.<\/span><\/p>\n

Paste Special \u2013 Values<\/span><\/strong> is more useful when you need to convert multiple formulas to numbers. \u00a0Just highlight the cells you want to convert, then right click on them and choose Copy<\/span><\/em> (or whatever method you like for copying). \u00a0Next, right click wherever you want to paste the numbers (right click on the already highlighted cells if you want to replace them) and choose Paste Special<\/span><\/em>, then Values<\/span><\/em> from the next menu. \u00a0Excel will paste the result of the formula instead of the formula itself. \u00a0Excel 2010 and later will give several choices for Paste Values<\/span><\/em>, each with a different formatting option.<\/span><\/p>\n

Copy Here As Values Only:<\/span><\/strong> The third method is very quick and easy if you don\u2019t want to paste the numbers very far away from the original cells. \u00a0Highlight the cells as in the last example, then right-drag the cells to where you want to paste them. \u00a0If you just want to replace your formulas with numbers, you can drop the cells back on their original location instead of on a different cell.\u00a0When you release the mouse button, a menu will pop up. \u00a0Choose\u00a0Copy Here As Values Only<\/span><\/em>\u00a0to copy\/paste the amount calculated by the formula, instead of the formula itself. \u00a0To \u201cright-drag\u201d, click with the right mouse button on the dark line surrounding the highlighted cells; continue holding the button down while moving your mouse pointer to the desired location.<\/span><\/p>\n