Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Paste all Except Formulas in Excel?
Data copying and pasting is a frequent activity you may encounter when using Microsoft Excel, a robust tool for storing and analysing data. The values and formulas existing in the source cells are often included when data is pasted into Excel. What happens though if you only want to paste the values and leave the formulas out? When you need to share information with people who do not have access to the formulas or when you want to keep the integrity of your data, this can be especially helpful.
Regardless of your level of Excel proficiency, this video will arm you with the skills and information necessary to handle your data efficiently and without the burden of unnecessary formulas. So let's get started and discover how to paste everything in Excel except formulas!
Paste All Except Formulas
Here we will first create a VBA module, select the range of cells, and then run the module to complete the task. So let us see a simple process to know how you can paste all except formulas in Excel.
Step 1
Consider an Excel sheet where you have formula cells in sheet similar to the below image.
First, right-click on the sheet name and select View code to open the VBA application.
Right Click > View Code.
Step 2
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Code
Sub PasteSP()
Dim sourceRange As Range
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("C2:C8")
Dim destinationRange As Range
Set destinationRange = ThisWorkbook.Worksheets("Sheet1").Range("E2")
sourceRange.Copy
destinationRange.PasteSpecial xlPasteValues
destinationRange.PasteSpecial xlPasteComments
destinationRange.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub
In the code ("Sheet1").Range("C2:C8") is the source and (Sheet1").Range("E2") is destination.
Step 4
Finally, click F5 in the VBA to complete the task. Then you can see that the data will be pasted.
This is how you can paste all but formulas into Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can paste all but formulas in Excel to highlight a particular set of data.
45 Views
