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.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Custom styles can be a great help in formatting a worksheet. You may, at some point, want to get rid of all the custom ...
Discover MoreHave you ever seen a worksheet in which some zero values have a negative sign in front of them? There's a reason for ...
Discover MoreIf you get an error when you try to use one of your custom views, it could be due to the protection you have applied to ...
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