Google sheet macro copy paste

When using Google Sheets, you may often find yourself needing to copy and paste values only, without formulas, and without formatting. There are a couple very simple ways of doing this, either through right-clicking and accessing the "Paste special" menu, or by using the keyboard shortcut Ctrl + Shift + V as explained below.

To copy and paste values only in Google Sheets (Not formatting and not formulas), simply copy the selection by pressing Ctrl + C on the keyboard to copy, and then press Ctrl + Shift + V to paste only the values into the cell. Formatting and formulas are NOT transferred when you hold "Shift" while pasting with the keyboard shortcut.

Google Sheets "paste values only" keyboard shortcut:

There is an easy keyboard shortcut that will allow you to quickly paste values only in Google Sheets.

After copying your selection and clicking the cell where you want to paste, simply press Ctrl + Shift + V on the keyboard.

When you are using the paste shortcut, and you want to paste values only, remember to hold the shift key.

Paste values only from the "Paste special" menu

To copy and paste values only in Google Sheets, copy the selection that you want to paste into your sheet, right-click on the cell where you want to paste your selection, then click "Paste special", and then click "Paste values only". This will paste only values into your sheet, without formulas, and without changing formatting.

Learn how to build dashboards in Google Sheets

There are a variety of reasons that you may want to paste values only in Google Sheets, such as the following:

• When you have a spreadsheet that is already formatted in the way that you want, and you want to paste values into it from another sheet without changing the formatting of the destination
• When you have formulas in your sheet that generate values, and you want to paste those values into another location without the formulas
• When you want to paste something into a spreadsheet from an outside source such as an article from the internet, without transferring the formatting

Below I have provided a variety of examples that demonstrate how "paste values only" can be used in situations when you do not want to paste formatting or formulas into your Google spreadsheet.

In this first example we will copy and paste values in a Google Spreadsheet without changing the formatting of the cells. Later I will show you how the same process also works when you are using formulas, but for now let's stick to pasting without affecting formatting.

The data below shows a list of clothing items that are in inventory, and the quantity of each item.

In this scenario, let's say that we have an updated quantity list, and that we want to copy and paste the new quantities over the original values, without changing the formatting.

Notice that in column E the cells are colored purple, are bold, and are centered. When using "paste values only" to paste into this location, only the values will be pasted/updated, and the formatting of the destination will remain the same.

To copy and paste the values from column G into column E as shown in the example below, do the following:

• Select the range G3:G14
• Copy the selection by pressing Ctrl + C on the keyboard
• Click on cell E3
• Paste values only by pressing Ctrl + Shift + V on the keyboard

How to copy and paste values, not formulas

Now that you see how pasting values only will not change formatting… let's see how you can use the same method to copy and paste the values that formulas display, without pasting the formula itself.

When there are formulas in your sheet and you want to copy and paste the values that they display instead of pasting the formulas themselves, then pasting values only is the perfect way to handle this.

The data below shows the same list of clothing items from the last example, but this time there are two lists that show quantity, and there is also a column that has formulas which add/total the two "Quantity" columns.

In this scenario, let's say that we want to paste only the values from the "Total" column into a different column, without the formulas being transferred.

Notice that in column G the cells are colored purple, are bold, and are centered. When using "paste values only" to paste data that was copied from this location, only the values and not the formulas will be pasted/updated, and the formatting of the destination will remain the same as well.

Note: In this case the paste destination is not formatted (plain), and this brings up a good point… which is that sometimes you will want to copy data from a nicely formatted location and paste it into a range with no/plain formatting… and vice versa you will sometimes find situations where you have plain data that you would like to paste into a range that is nicely formatted.

To copy and paste the values from column G into column I as shown in the example below, do the following:

• Select the range G3:G14
• Copy the selection by pressing Ctrl + C on the keyboard
• Click on cell I3
• Paste values only by pressing Ctrl + Shift + V on the keyboard

Copy and paste values only into Google Sheets from an outside source

You can also paste values only into a Google spreadsheet from an outside source such as something that you want to copy and paste from the internet.

For example, below is a picture that shows what it will look like if you copy and paste the title of this article that is found at the top of this page, into a spreadsheet.

In cell A1, the article title was pasted as "values only" by pressing Ctrl + Shift + V. You can see that the title was pasted in plain format there.

In cell A2, the article title was pasted normally by pressing Ctrl + V, without pressing the shift key. You can see that the title was pasted with the format taken from this article page, in big letters that are center aligned (which is why only a portion of the title shows, since it was pasted so far to the left).

Copy and paste values only in Google Sheets

Now let's see an example of using "paste values only" to paste an entire data report.

Below, the picture shows a student progress report that includes student name, course, progress, and grade.

Columns A through D are formatted with color, alignment, and text style… where columns F through I are not formatted.

Part 1- Pasting plain data into a formatted range

In this first part of this example, let's say that you need to paste the plain unformatted data into the colorful formatted portion of the sheet, while only updating the values and not affecting the formatting.

(This is commonly done in situations when you have a CSV report that you want to copy and paste into a spreadsheet that is already formatted in the exact way that you like it)

To do this, select the range F2:I17, copy the selection, click on cell A2, and then paste values only by pressing Ctrl + Shift + V.

Part 2- Pasting previously formatted data into a non-formatted range

In this second part of the example we will copy and paste from/to opposite locations.

Let's say that you need to paste the data from the colorful formatted portion of the sheet, into the plain non-formatted portion of the sheet, without pasting any of the color or other formatting.

(This is commonly done in situations when you want to create a plain version of a data set to send to a colleague, where the data was previously formatted in a way that the recipient does not need for their intended purpose)

To do this, select the range A2:D17, copy the selection, click on cell F2, and then paste values only by pressing Ctrl + Shift + V.