Проблемы, возникающие при работе excel с power shell

Home > Data > Data Entry > Copy Paste

See how to fix Excel copy and paste problems and avoid multiple selections error messages. Also, prevent Excel from changing your formulas to values, when pasting multiple selections.

Copy & Paste Problems

This video tutorial shows two problems you might have in Excel, when copying and pasting multiple selections, and how to avoid these problems:

• Excel might show an error, “This action won’t work on multiple selections”
• Values might be pasted, instead of formulas.

The written instructions are below the video.

Video Timeline

• 0:00 — Introduction
• 0:15 — Copy Error Message
• 0:42 — Avoid the Error Message
• 1:54 — Paste Problem
• 2:40 — Copy 2 Ranges
• 3:23 — Paste With Formulas
• 4:13 — Get the Workbook

Copy Error for Multiple Selections

If you select more than one group of cells on a worksheet, and try to copy them, Excel might show an error message:

• “This action won’t work on multiple selections”

Or, in some versions of Excel, you’ll see this error message instead:

• “That command cannot be used on multiple selections”

Why You See Copy Error

Excel will show that copy error message if you selected multiple ranges, and those ranges don’t “match up”.

For example, Excel will show a warning message if you

1. select cells in columns A:C
2. press Ctrl, and make another selection in different columns, A:B
3. press Ctrl+C to copy the selected cells (or use another method to copy)

Avoid Excel Copy Error Message

To avoid that copy error message, be sure to select multiple regions that DO “match up”.

All of the selected regions that you want to copy must be in either:

• the exact same columns
• OR, the exact same rows

If even one of the selected cells does not match the other selections, Excel will show that error message.

The screen shot below shows an example of two selections that DO match up.

• both selections are in the exact same columns — A:C
• the selections have a different rows, which is fine

No error message will appear when copying this multiple selection

Paste Problem for Multiple Selections

If you copy more than one group of cells on a worksheet, and paste them in a different location, Excel might change your formulas to values.

NOTE: This problem can cause serious problems, because Excel doesn’t give you a warning message. If you don’t immediately notice that the formulas were changed to values, your workbook could have serious errors later.

Paste Problem Example

Here’s an example, from one of my Microsoft Excel workbooks, of how this copy and paste problem can damage your worksheets.

One worksheet had a named Excel table, where some columns had formulas

• In column G, the product cost was calculated with a VLOOKUP formula
• In column H, the total cost was calculated by multiplying quantity x cost

• First, I used the mouse to select the heading and first two rows of data (cells A1:H3)
• Next, I used the keyboard shortcut, Ctrl + C, to copy the selected cells
• Then, on a new sheet, I selected cell A1, and pasted that data, using the keyboard shortcut, Ctrl + V
• Next, I went back to the original table, and selected A5:H6
• Then I pressed the Ctrl key, and selected cells A8:H8.

• Returning to the new sheet, I selected cell A4, and pasted that data

A few minutes later, in the new worksheet, I noticed a problem:

• In cell F4, I changed the quantity from 10, to 15
• The total, in cell H4, did not change — it still shows the total cost for 10 items

For troubleshooting the problem, I did these steps:

• clicked the Formula tab at the top of Excel
• clicked the Show Formulas command

Here is the copied data, with the Show Formulas setting turned on

• There were formulas in rows 2 and 3
  • a single range was copied and pasted
• There were values, instead of formulas in rows 4, 5 and 6
  • multiple range selection was copied and pasted

How to Paste the Formulas

• Copy the multiple selections
• Right-click the cell where you want to start the paste
• In the pop-up menu, click Paste Special
• In the Paste Special window, click OK

With this technique, all the formulas will be pasted too.

This can can help you avoid potential problems, caused by Excel pasting values, instead of formulas

Pasted Numbers Change to Dates

If numbers such as 1-4 or 3/5 are pasted into Excel, they
will usually change to dates. Other types of numbers, such as zip codes with leading zeros, lose their formatting too. For example, copy the numbers below, and
paste them onto a worksheet, to see how Excel adjusts them.

• 3/4
• 02345
• 1-3

In the screen shot below, the original data is at the left, and the data
pasted onto a worksheet, at the right. Two of the numbers were changed
to dates, and the leading zero was dropped from the other number.

Prevent Numbers From Changing to Dates

• In Excel, select the columns where you will paste the data — be sure
  to include enough columns for all of the data that will be pasted
• On the Ribbon’s Home tab, in the Number group, click the drop down
  arrow, and click on Text

• Select the data and copy it
• In the column(s) that you formatted, right-click the starting cell
  where you want to paste the data
• In the popup menu, under Paste Options, click Match Destination Formatting

• The data will be pasted in its original formatting, instead of changing
  to dates.

Turn Off Paste Options in Excel

When you copy and paste in Excel, you might see option boxes, at
the bottom right of the pasted data. You can click those buttons,
to select an option for pasting the data, such as Paste Values.

If you don’t like those boxes, and never use them, watch this video
to see the steps to turn this feature off.

Note: For newer versions of Excel, click the File tab,
instead of the Office button.

Turn off Paste Options buttons

• At the top left of the Excel window, click the File tab
• In the list at the left, scroll to the end, then click Options
• The Excel Options dialog box opens, with a list of categories at the left
• In the list of Option categories, click Advanced
• Scroll down to the “Cut, Copy and Paste” section
  • Remove the check mark from ‘Show Paste Options button when content is pasted’
  • (optional) Remove the check mark from ‘Show Insert Options buttons’.
• Click the OK button, to close the Excel Options window.

Get the Sample Files

1. Copy and Paste Problems: Download the sample file for multiple selections copy and paste problems, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.
2. Numbers Paste As Dates: Get the sample file for pasting formatted numbers, to see how Excel sutomatically changes the numbers to dates. The zipped file is in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don’t miss my monthly Excel newsletter! You’ll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

More Tutorials

Copy Number Cells Only

Paste, Skip Blanks

Filtered List Paste Problems page

Data Entry Tips

Excel Humour

Has Excel ever ruined your day with annoying cut and paste errors? If you’re lucky, you notice the problem right away, so you can undo it!

"End of Row"

"Skip the row"







"End of the Row"

Похожее:

Простым языком о команде TRACERT: что это и как пользоваться «Изменение размера шрифта для устройств Nokia по сравнению с изменением общего масштаба шрифта приложения на Android с использованием Configuration.fontScale»? Что такое файл hosts, зачем он нужен и как его исправить? Защитить все листы excel от изменений