How to copy and paste all cells including blanks in Excel

Typical pasting in Microsoft Excel: the bane of format-oriented users such as myself. Some who use the default pasting method for Excel have come to find that blank cells are not accounted for as they would’ve expected. What I mean is, Excel will ignore empty cells.

Solution:

  1. Select all cells (ctrl+a)
  2. Copy your selection (ctrl+c)
  3. Select paste location
  4. Click the dropdown under “Paste”
  5. Select “Paste Special”
  6. Uncheck “Skip Blanks”
  7. Click OK

Microsoft Excel features configurable paste settings under a “Paste Special” menu. The menu in question allows users to control what within their selection they would like to paste and how it should be pasted over non-empty cells. In addition, within the menu is a toggle, which determines whether or not Microsoft Excel will account for (or ignore) empty cells.

How to paste blank cells in Excel

Step 1 – Select and copy all cells in your sheet

First, use the ctrl+a shortcut to select each cell. Next, couple the first shortcut with ctrl+c to copy your selection. You can also use the triangular icon next to cell A1 to select all cells. After copying your selection, select the space you wish to paste it in.

Step 2 – Use Excel’s special paste

In the home tab of Excel, you can find the paste button- but that’s not what we want. Instead, we want what’s below the button, a downward-facing arrow. Click the arrow to reveal a dropdown; select the “Paste Special” option inside the dropdown.

Step 3 – Uncheck skip blanks

The “Paste Special” window features a series of toggles used to configure what you will paste and how you will paste it. Today, we only need to worry about one toggle: “Skip blanks.” Ensure the toggle is unchecked before clicking “OK.”

Recap & Farewell

Recap

When you paste content from your clipboard, Excel ignores empty cells within your selection by default. However, you can circumvent this setting by using the “Special Paste” menu. Excel’s “Special Paste” menu contains an assortment of toggles to customize how you paste content- including whether or not you’d like to ignore or account for blank cells.

Farewell

If you found this post helpful, I’d like to recommend the rest of Business Tech Planet; troubleshooting, application guides, and query answers await you. Thanks for supporting us. We look forwards to aiding you in the future.

Our related articles:

  • Excel won’t allow insert row: Troubleshooting guide – Excel allows users to create cutomizable tables for their data. When needed, additional columns or rows can be inserted to their table. In some cases however, Excel users find that they’re unable to insert rows to their table. I’m optomistic this post will help compensate for that.
  • Excel: How to get day of week from date – If you’re not the best with dates and times, you may (like myself) find figuring out which weekday corresponds with a date tedious. I’m glad to say that with a simple process in Excel, you can automate the process.
  • Can’t click into cells in Excel: Troubleshooting tips – For several reasons Excel users may experience a phenomena in which they’re unable to click any cells. The issue, as you would expect, negatively impacts your experience- fortunately theres a number of solutions.
  • Excel isn’t returning the sum of cells: Troubleshooting guide – If you’re having issues with summing cells in Excel, I’d like to request you check this post out. The solution is quite simple and easy.
Need help? This is a free service for Business Tech Planet readers. Pop in your problem below and we'll be in touch soon!

Jack Mitchell

Jack Mitchell has been the Operations manager at telecoms and MSP Optionbox for more than 4 years. He has played a crucial role in the company, from marketing to helpdesk, and ensures that the IT requirements of over 300 clients are continuously met. With his innate passion for technology and troubleshooting and a particular interest in Apple products, Jack now delivers the most comprehensive tech guides to make your life easier. You can connect with Jack on LinkedIn.

Recent Posts