To copy a whole column in Excel for Microsoft 365 on Windows, click its lettered heading, press Ctrl+C, click the destination column heading, and press Ctrl+V. This copies the column’s cells so you can reuse them elsewhere; check the destination first, because pasting can replace existing data.
What You’ll Need
- An Excel workbook open in Microsoft Excel for Microsoft 365. The same basic method works in Excel for the web and recent desktop versions.
- A source column and a destination location. The destination can be another column on the same worksheet, a different worksheet, or a compatible workbook.
- Permission to edit the destination sheet. If it is protected or the workbook is read-only, you may need to remove protection or make an editable copy.
Step-by-Step
- Open the worksheet and find the source column. Locate the letter at the top of the column you want to copy, such as B or F. Clicking the letter selects the entire worksheet column, including blank cells above and below the visible data. You do not need to drag through every populated cell.
- Select the whole column. Click its column heading once. The entire column should be highlighted. If you prefer the keyboard, click any cell in the source column and press Ctrl+Space on Windows. If the selection highlights only a cell or behaves unexpectedly, click a regular cell first and try the shortcut again; system keyboard settings can sometimes intercept it.
- Copy the selection. Press Ctrl+C on Windows. A moving dashed border around the selected column indicates that Excel has copied it. You can also right-click the selected column heading and choose Copy, or use Home > Clipboard > Copy on the ribbon. Keep the workbook open while you choose where to paste.
- Choose the destination. For a different column on the same sheet, click that column’s lettered heading. To paste to another worksheet in the same workbook, click the worksheet tab, then click the destination column heading. To use another open workbook, switch to it and select the destination column. Make sure you have selected the intended starting column, not a cell partway down, if you want a full-column copy.
- Paste the copied column. Press Ctrl+V, right-click the selected destination heading and choose Paste, or select Home > Clipboard > Paste. The copied cells go into the destination column. If it already contains information, Excel can overwrite the corresponding cells, so inspect the destination before continuing.
- Check what transferred. Look at several cells, including the top and bottom of the data, and confirm the values, number formats, formulas, and other cell formatting are what you expected. A copied formula may adjust its references to the new location. For instance, a formula that refers to a neighboring cell can point to a different neighboring cell after it is pasted.
- Finish the copy operation. Press Esc to clear the moving dashed border when you are done. This does not undo the paste; it simply exits copy mode. If the result is wrong, press Ctrl+Z immediately to undo the paste, then repeat the steps with the correct destination or paste option.
Mac: Click the column heading and use Command+C, then select the destination and use Command+V. To select a column from a cell, Control+Space is the usual Excel shortcut on Mac, though macOS keyboard settings may use that combination for another feature. If so, select the column by clicking its heading or change the conflicting system shortcut. Excel for the web uses the same general copy-and-paste approach; keyboard shortcuts may be affected by your browser, so the ribbon or right-click menus are alternatives.
Common Mistakes
- Selecting cells instead of the column heading. Dragging over visible data selects only that range, not the whole column. If you need every cell in the column, click the letter at its top. If you only need a used range, selecting just those cells can be more efficient and avoids copying a million-row column unnecessarily.
- Pasting over important information. A full-column paste can replace existing destination content. Before pasting, confirm the destination heading and check whether the column has data you need to keep. If you are unsure, paste into a blank column first or save a separate copy of the workbook.
- Expecting formulas to remain tied to the same cells. Excel normally adjusts relative references when formulas are copied. Absolute references marked with dollar signs, such as
$A$1, stay fixed, while relative references can shift. Review formulas in the destination if they must keep referring to the original cells. - Assuming the column width came along. Pasting cell contents and formatting does not always give the destination the width you want. If the copied data looks cut off or unusually spaced, adjust the destination width by dragging the boundary between its lettered headings. To copy the width specifically, use the Paste Special option described below.
- Clicking a cell when you mean to replace a complete column. Clicking a cell pastes the copied selection starting at that cell, rather than clearly selecting the entire destination column. For a full-column-to-full-column copy, click the destination’s lettered heading before pasting.
Tips
- Use Paste Special when you do not want everything. After copying, right-click the destination and choose Paste Special, then choose an option such as Values to paste displayed results without formulas, or Formats to copy appearance without replacing the values. The available options depend on what you copied and where you are pasting.
- Copy a column’s width separately. Select and copy the source column, select the destination, then choose Home > Clipboard > Paste > Paste Special and select Column widths, if that option is available in your Excel version. This adjusts the destination width without requiring you to estimate it by eye. You can also double-click the boundary to the right of a column heading to AutoFit the width to its contents.
- Copy several adjacent columns together. Click the first column heading, hold Shift, and click the last heading in the range. Copy and paste as usual. For nonadjacent columns, use Ctrl while selecting headings on Windows, but be aware that pasting a noncontiguous selection may not work as a single block in every situation. Copying each column separately is often more predictable.
- Choose a safer destination for a quick trial. If you are uncertain whether formulas, formatting, or data validation will transfer as intended, paste into an unused area or a duplicate worksheet first. Compare a few cells and then perform the final copy once you know which paste option suits the job.
- Remember that a worksheet column is large. A modern Excel worksheet has 1,048,576 rows per column. Selecting the heading copies the full column rather than only the cells containing data. For an especially large workbook, copying just the populated range may make the operation easier to manage and can reduce unnecessary work.
Troubleshooting
Nothing appears after you paste: Check that the source was copied, that the marching-ants border appeared, and that you selected an editable destination. Try copying again and use the ribbon’s Home > Clipboard > Copy and Paste commands. If you are moving between workbooks, keep both open and make sure the destination workbook is active before pasting.
Excel says the copy area and paste area are different sizes: This can happen when you selected a full column but then chose a destination range of a different shape, or when you copied a multi-column selection and selected only one destination column. Select an equally sized destination area, or select the complete destination column when copying one whole column. If the error remains, press Esc, select the source again, and copy it afresh.
The destination contains formulas or formatting you did not expect: Undo with Ctrl+Z on Windows or Command+Z on Mac, if the paste is the most recent action. Repeat the copy and choose a suitable Paste Special option, such as Values or Formats. If the copied formulas need to preserve their original references, inspect them and use absolute references where appropriate before copying.
You cannot select or paste into the destination: The worksheet may be protected, the workbook may be read-only, or the destination may be part of a restricted range. Check for a protection message or a read-only indicator. If you have authorization, use Review > Unprotect Sheet and provide the password if requested. If you do not have permission or the password, ask the workbook owner for an editable version rather than trying to bypass the restriction.
Only part of the column seems to have copied: Confirm that you clicked the column letter rather than selecting a block of cells, and check whether the visible result is simply limited by the worksheet’s display. Use the scroll bar to inspect farther down. If you copied a filtered list and need only visible records, do not assume a full-column copy excludes hidden rows; verify the pasted result and use the visible-cells-only command when you specifically need that behavior.
The column width is still wrong: Cell formatting and column width are separate. Adjust the destination manually, AutoFit it by double-clicking the boundary between its heading and the next, or repeat the operation using Paste Special > Column widths. If the option is unavailable in the version or context you are using, resize the column directly.
Frequently Asked Questions
How do I copy a whole column without using the mouse?
Click a cell in the source column, press Ctrl+Space on Windows to select the column, then press Ctrl+C. Move to the destination and press Ctrl+V. On Mac, use Control+Space to select the column if that shortcut is not assigned by macOS, followed by Command+C and Command+V.
Can I copy a column to another worksheet?
Yes. Copy the source column, click the destination worksheet tab, select the destination column heading, and paste. You can also copy between open workbooks. Check that the target sheet is editable and that the destination does not contain information you want to preserve.
How do I copy only the values, not the formulas?
Copy the column, select the destination, and use Home > Clipboard > Paste > Paste Special > Values, or right-click and choose the equivalent Paste Special option. This pastes the results shown in the cells rather than the underlying formulas. Review the result if the source includes errors or blank cells.
Will copying a column also copy its formatting?
A standard copy-and-paste generally transfers cell formatting along with the cell contents and formulas. If you want only the appearance, use Paste Special > Formats. Column width is a separate setting, so use Paste Special > Column widths or resize the destination if it does not match.
Can I copy a column that contains a filter?
You can copy the column, but selecting its heading includes the worksheet column, not just the rows currently visible through a filter. If the goal is to copy only visible records, select the relevant filtered range and choose Home > Find & Select > Go To Special > Visible cells only before copying, then check the destination carefully.
What is the difference between copying and moving a column?
Copying leaves the original column in place and creates a duplicate at the destination. To move a column instead, select its heading, press Ctrl+X on Windows or Command+X on Mac, select the destination, and paste. Because a move can replace or shift existing data depending on how it is performed, confirm the destination before completing it.
Related
- Convert Excel to Google Docs: A Step-by-Step Guide
- How to Compare Lists in Excel for Differences
- How to Compare Two XML Files in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Change Conversation View in Outlook on Any DeviceTo change conversation view in Outlook, turn on or off the option that groups email by conversation. In…
- Add a Shared Mailbox in Outlook for Mac: Setup GuideTo add a shared mailbox in Outlook for Mac, open File > Open > Shared Mailbox, enter the…
- Create a Distribution List in Outlook 365In Outlook 365, create a personal distribution list by opening People, choosing New contact list, naming the list,…
- Outlook Web Access Rules: How to Create and Manage ThemTo create a rule in Outlook Web Access, open Settings (the gear icon), go to Mail > Rules,…
- How to Convert an Excel Spreadsheet to WordTo convert an Excel spreadsheet to Word, select the cells you want in Excel, copy them, then paste…
- Excel Power Query: Extract Text from a StringTo extract text in Excel Power Query, select the relevant column and use Transform > Extract to choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.