WISDOMBYDATA
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me

Preventing Hidden Cells From Being Pasted Into The Spreadsheet

8/11/2014

0 Comments

 
I very often see Excel users encounter difficulties when it comes to copy/pasting data rages which have hidden columns/rows, as the hidden values also get pasted over while only the visible ones are required.

Some try to prevent this anomaly by separating the hidden columns from other columns and then selecting the range that they want to copy/paste. This could turn out into a very time consuming task! Consider the table below (on the left) as an example. I want to copy and paste only the visible values, however; the hidden columns (i.e. columns D and E) also get pasted after performing a traditional copy/paste.
Picture
Picture
Picture
As you could clearly observe in the illustration above, columns D and E which were hidden columns in the table on the left hand side have been pasted along with the unhidden columns. Here is a simple procedure you could follow in order to avoid having hidden columns get pasted.

Step 1] Select your full data range (which includes hidden columns as well as unhidden ones) and then Push "CTRL + G". The screen below will appear:  
Picture
Step 2] Click on "Special" in the screen above. A second screen will appear.
Picture
Step 3] Click on the "Visible Cells Only" radio button, and then click "OK"

Immediately after performing the procedure above push "CTRL+C" and then paste into the desired cells (or tab) by pushing "CTRL+V". As you could observe, The hidden columns no longer get pasted.

Picture
Picture
Picture
0 Comments



Leave a Reply.

    Categories

    All
    BI
    EXCEL
    MISC
    SQL

    Archives

    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013

Picture
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me