All articles with 'paste-special' Tag

Copy Paste Visible Cells only (Two more ways to do it)

Published on Aug 28, 2017 in Excel Howtos, Keyboard Shortcuts, Learn Excel
Copy Paste Visible Cells only (Two more ways to do it)

Last week, we talked about how to copy and paste visible cells alone (ie exclude any filtered rows or hidden columns etc.) In the comments section many of you suggested two more ways to deal with this annoying problem. Let’s take a look them.

Continue »

Copy & paste visible cells only [Excel Trick]

Published on Aug 24, 2017 in Keyboard Shortcuts, Learn Excel
Copy & paste visible cells only [Excel Trick]

Here is something annoying with Excel.

Open any Excel file with few columns of data. Hide some of those columns (select the columns and press CTRL+0). Now, copy a few rows of data. Paste it else where. Excel will paste the values in hidden columns too. We thought Excel would omit the values in hidden columns.

What the filter Excel?!? I thought we were friends, but you annoy me with some of these quirks.

Continue »

Hide columns one one tab same way as they were in another place [quick tip]

Published on Apr 5, 2017 in Excel Howtos
Hide columns one one tab same way as they were in another place [quick tip]

One of the regular reporting tasks I do involves a manual step I hated. It goes like this:

  • Dump several columns of data in the template file.
  • Hide a particular set of columns (these are not together, so must be done one at a time or with CTRL+selection)
  • Save and publish the file.

After doing this manually for last few fortnights, today I wanted to automate the column hide process. I was about to write a VBA macro to clone the hide settings from one workbook to another. But then I thought, may be paste special can be of use.

And what do you know. It does exactly that.

Continue »

Format faster with paste special & double click [video]

Published on Aug 12, 2015 in Learn Excel
Format faster with paste special & double click [video]

Making your workbooks, charts, dashboards & presentations beautiful is a time consuming process. It is a mix of art & craft. Naturally, we spend hours polishing that important slideshow or visualization. But do you know about simple features in Excel that can save you a lot of time and help you create gorgeous output?

Continue »

Use Paste Special to multiply (or add, divide etc.) a range with a variable [quick tip]

Published on Jun 16, 2015 in Excel Howtos, Learn Excel
Use Paste Special to multiply (or add, divide etc.) a range with a variable [quick tip]

Here is a fun way to use Paste Special to quickly multiply everything in a range with 1.1 (why 1.1? Well, imagine you have a report with everything in US $s and your boss wants to see the numbers in Australian $s…)

Since your report has different formulas for each cell, you can’t multiply first cell with a rate variable and drag it down. You have to manually edit each formula and add *rate at the end of it.

Oh wait…, you can use Paste Special.

Continue »

Quickly convert numbers stored as text [tip]

Published on Sep 2, 2014 in Excel Howtos
Quickly convert numbers stored as text [tip]

Here is a quick tip to start the week.

Often, we end up with a situation where a bunch of numbers are stored as text.

In such cases, Excel displays a warning indicator at the top-left corner of the cell. If you click on warning symbol next to the cell, Excel shows a menu offering choices to treat the error.

Continue »

CP017: Top 10 non-Excel MS Office tips for you – Interview with Paul Woods – Office MVP & Blogger

Published on Aug 14, 2014 in Chandoo.org Podcast Sessions
CP017: Top 10 non-Excel MS Office tips for you – Interview with Paul Woods – Office MVP & Blogger

In the 17th session of Chandoo.org podcast, lets leave Excel aside and talk about other MS Office apps.

Thats right. We will be learning 10 tips on how to use Word, Power Point, Outlook etc. Ready?

In this podcast, you will learn,

  • About Paul
  • Ten tips for MS Office
  • 1. Use Excel to communicate instead of just calculations
  • 2. Paste Special
  • 3. Double click trick!
  • 4. Inserting screenshots
  • 5. Turning off notifications
  • & more…
Continue »

Transpose a table quickly using Copy – Paste [Quick tips]

Published on Feb 4, 2013 in Excel Howtos
Transpose a table quickly using Copy – Paste [Quick tips]

On Friday, we learned how to transpose a table of data using Excel formulas. Today lets learn a quicker & easier way to do this by just using copy, paste, find & replace.

Continue »

Transpose a table of data using Excel Formulas

Published on Feb 1, 2013 in Excel Howtos
Transpose a table of data using Excel Formulas

Today lets tackle a familiar data clean-up problem using Excel – Transposing data.

That is, we want to take all rows in our data & make them columns. Something like this:

Learn these 4 techniques to transpose data:
1. Using Paste Special > Transpose
2. Using INDEX formula & Helper cells
3. Using INDEX, ROWS & COLUMNS formulas
4. Using TRANSPOSE Formula

Continue »

Quickly Fill Blank Cells in a Table [Reader Tip]

Published on Oct 17, 2011 in Excel Howtos
Quickly Fill Blank Cells in a Table [Reader Tip]

This post is authored by Martin, one of our readers.

Situation:

Sometimes I encounter data in my tables with blank cells where there is a repeated value from the cell directly above. See below:

This can be annoying when it comes to interpreting the data and when sorting columns.

Solution:

To know how I solve this, just read rest of the article.

Continue »

10 Excel Keyboard Shortcuts I can’t live without!

Published on Aug 8, 2011 in Learn Excel
10 Excel Keyboard Shortcuts I can’t live without!

Everyone and their colleague knows the importance of Excel keyboard shortcuts. While it may be a good idea to know a 97 different shortcuts for everything in Excel, the fact is, you don’t use them all the time. So, it all boils down to memorizing the few shortcuts that you use all the time.

In this post, I am sharing 10 such shortcuts that I can’t live without, especially, when I am analyzing data, making charts and formatting my workbooks.

Continue »

Quickly Remove Data Validation Rules using Paste Special

Published on Mar 4, 2011 in Learn Excel
Quickly Remove Data Validation Rules using Paste Special

Here is a quick tip on removing data validation rules. We all know that data validation is a powerful feature in Excel to control what data is entered. But, sometimes, you may want to remove the rules from a set of cells. How would you do it? The normal approach is, Select the cells with […]

Continue »

220 Excel Tips, Tutorials, Templates & Resources for You [Celebrating 20k RSS Members]

Published on Jan 20, 2011 in Charts and Graphs, Learn Excel
220 Excel Tips, Tutorials, Templates & Resources for You [Celebrating 20k RSS Members]

I have an exciting news & massive post for you.

Chandoo.org reaches 20,156 RSS Subscriber mark on Jan 19, 2011As of Jan 19, 2011, our little blog has registered our 20,000th RSS Subscriber. While this is not a huge achievement or anything, It certainly calls for celebration. I am so happy to see our mission to make people awesome in Excel is reaching out to more people everyday. Thank you.

To celebrate this milestone, I am doing a massive post with 220 Excel tips, tricks, tutorials & templates.

Formulas [52 tips]
Formatting & Conditional Formatting [36 tips]
Charting [60 tips]
Tables & Pivot Tables [15 tips]
Using Excel [47 tips]
Free Downloads [5 tips]
Recommended Resources [5 tips]

Continue »

How to Paste Numbers Only [quick tip]

Published on Sep 8, 2010 in Excel Howtos, Learn Excel
How to Paste Numbers Only [quick tip]

Sometimes when you are working with data, you may need to just copy and paste numbers from one range to another. Here is a handy little trick to achieve that.

Use Paste Special > Add operator to paste numbers only.

1. Select the source range, press CTRL+C
2. Now, go to target range, press ALT+ES to activate paste special dialog
3. Select “Add” operator (you can press d)
4. Click ok.

Continue »

Excel School Closing in a Few Hours – Join Now!

Published on Jun 21, 2010 in Learn Excel, products
Excel School Closing in a Few Hours – Join Now!

As you may know, I run an online excel training program, called as, Excel School. I have opened registrations for 2nd batch of this program on last Monday. Thank you very much for supporting this program wildly. In a few hours, I will be closing the registrations for Excel School.

Click here to sign up for Excel School

At the time of writing this post (around 9 AM IST on 21st), we have 103 students signed up for Excel School. This is certainly a bit more than what I expected. But, I am also confident and eager to help as many of you as possible. So go ahead and join the program, because, you want to be awesome.

Continue »