Written by Allen Wyatt (last updated June 22, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
When transferring data from his bank's website to Excel, Richard can remove formatting by using Paste Special. He wonders if there is a way to remove formatting when going the other way. The Formula bar in Excel displays unformatted numeric values, but when Richard tries copying to the bank's website, the formatting (like the dollar sign) is transferred too, and the value isn't accepted.
Unfortunately, Excel doesn't have a "copy values" type of function. That being said, there are a number of ways to get around the issue.
First, if you are trying to copy the contents of a single cell to your bank's website, you could simply copy from the Formula bar (which you noted already shows an unformatted value). Just select the contents in the Formula bar, copy (Ctrl+C will do), and then paste to the website.
Another approach that works well—particularly if you are wanting to paste values from multiple cells—is to format the cells as General. This removes things like commas and dollar signs from the cells and allows you to get at the unformatted values. When you are done pasting, you can then press Ctrl+Z to undo the formatting you applied to the cells.
Partially related to this is the idea to make an intermediate copy. In other words, copy the cells and then use Paste Values to paste just the unformatted values into a different worksheet. Provided you didn't format the cells in the worksheet before doing the paste, you end up with unformatted values you can then copy to your bank's website.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13558) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Want to format your data tables in a hurry? It's easy to do if you use the built-in table formatter provided in Excel.
Discover MoreInformation in a worksheet needs to be displayed using fonts. If you understand the two different types of fonts ...
Discover MoreWant to copy formatting from one cell and paste it into another cell? It's easy to do if you use the Paste Special ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-22 14:55:26
J. Woolley
My Excel Toolbox includes the CopyValues macro (Ctrl+Shift+C) to copy unformatted cell values from a selected range to the Windows clipboard, which can then be pasted into a website or other document.
See https://sites.google.com/view/MyExcelToolbox/
For a general purpose way to paste values without formatting, consider the Microsoft Store's PureText app. See https://stevemiller.net/PureText/
For more on this subject, see https://excelribbon.tips.net/T013669_Changing_the_Default_Paste_Mode.html
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments