INTELLIGENT TECHNOLOGIES, INC’S NEWS & TIPS BLOG

Do You Do A Lot of Work in Excel? Paste Special Should Be Your Best Friend!

Laura-Square

Laura Schomaker, Senior Marketing Associate

June 6, 2019

A lot of our clients work in the accounting field. This often means long hours collecting data from many places to create spreadsheets, performing calculations and formatting the spreadsheets in an eye-catching way. All these tasks can be time consuming. But paste special is like a Swiss Army knife for Excel users that can save you a great deal of time and effort on a wide variety of tasks.

When most people think of the paste special Excel feature, they think of using it to paste the values you derived from your formulas instead of the formulas themselves. This is a great use of the paste special Excel feature, but it only scratches the surface of what this powerful tool can do. Have you ever taken a moment to look at all the options that come up when you select paste special in Excel? If you haven’t checked it out lately, look at the screenshot below. This dialog box gives an impressive list of 17 tools you can use to manipulate your data.

What do all the paste special options in Excel do?

At first, the list of options the paste special dialog box presents you with can seem a little daunting. It’s hard to know which function to use for what. So, we put together this list you can use as a reference to see which paste special function is right for your needs.

 

  1. All – pastes an exact copy of the selected cells, including formatting and data.
  2. Formulas – pastes the formulas from the copied cells so you can apply them to a new set of cells.
  3. Values – pastes only the numbers from the copied cells (no formatting or formulas).
  4. Formats – pastes only the formatting from the copied cells (not the values or formulas).
  5. Comments – if the copied cells have comments attached to them, this option will paste at the comments only to the new cells (no values, formatting or formulas).
  6. Validation – takes the data validation rules from the copied cells and brings them into the pasted ones.
  7. All using source theme – pastes the cell contents using the document theme formatting that’s attached to the copied data.
  8. All except borders – pastes the cell contents, and the formatting applied to it except for any borders that may exist in the copied part of the spreadsheet or table.
  9. Column widths – takes the column width of the copied cells and applies it to the pasted area.
  10. Values and number formats – this takes the values option I mentioned earlier one step further, pasting any number formatting along with the values themselves.

Paste special mathematical operations options

  1. None – Excel will not apply any mathematical operations to the selected cells.
  2. Add – Excel will add the copied data to the data that exists in the pasted destination cell(s).
  3. Subtract – Excel will subtract the copied data from the data in the destination cell(s).
  4. Multiply – Excel will multiply the data from the copied cells with that of the destination cell(s).
  5. Divide – Excel will divide the copied cell’s data by the destination cell’s data.
  6. Skip blanks – if the range of cells you’re copying includes blank cells, using this option will keep Excel from pasting the blank cells over the values.
  7. Transpose – Turn columns into rows and rows into columns (useful in pivot tables or in formatting data for Power BI, Google Data Studio or other data visualization software).

Especially for those in the accounting field, the mathematical operations options can be real timesavers. They allow you to do calculations fast without typing in tedious formulas. Also, a less obvious use of the multiply function is using it to turn positive numbers into negative ones and vice versa.

Your turn, what’s your favorite Excel paste special feature? Is there one we forgot to mention? Please share it with us.

Want other helpful Excel tips like this? Visit this webpage.

Other Posts You May Enjoy

Do You Do A Lot of Work in Excel? Paste Special Should Be Your Best Friend

Accounting and finance pros, who work in Excel, would you like to get more done in less time? Meet Excel’s little known Swiss Army knife, the paste special feature can speed calculations, formatting and other tedious tasks. Once you discover all you can do with this tool, you’ll wonder how you got along without it for so long.

How Do I Contact Microsoft Tech Support?

Have you run into an issue with your Microsoft software? Not sure if you should ask your VAR for help or contact Microsoft directly? Our experts explain the when and how of contacting Microsoft tech support. Even if you’ve done it before, you should read this before opening your next support ticket.

5 Awesome Word Tips to Make Document Creation a Breeze

Want to sharpen your Microsoft Word tips? In this post, our experts share their favorite Word tips. You’ll learn how to turn your Word formatting nightmares into dreams come true. Plus, we’ll show you how you can become multilingual in an instant and much more.

Become a Spreadsheet Master with Five New Excel Tips for 2019

Do you spend much of your workday in Excel? Would you like to learn the fastest way to create Pivot Tables, charts or tables? Do you struggle building formulas in Excel? There’s a built-in tool to help with that. Learn more about each of these items & more in our Excel tips updated for 2019.

5 All New Time-Saving Outlook Tips For 2019

With the sheer volume of email most professionals receive each day, it’s easy to let it take over your workday. But it doesn’t have to. With our new Outlook tips you can finally tame you inbox and get back to more important things.