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

Written by Laura Schomaker
Published on 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.

Latest Articles

Related Articles

Acumatica Pro Tip: What Is Acumatica Open University and Why Should I Use It?

When you’re new to it, learning new software can be frustrating. But I have great news for Acumatica users. Acumatica Open University takes the stress out of the training process. Whether it’s your first day using the system or you’re a seasoned user looking to learn something new, Acumatica Open University has you covered.

Acumatica Pro Tip: Acumatica Mobile App Setup, a How to Guide

Do you work outside the office, have you ever wished you could take Acumatica with you? Well I have great news there’s a mobile app for Apple and android smartphones and tablets that put the power of Acumatica in your pocket. And the best part is, all it takes is seven simple steps to get started.

Top 10 Technology Blog Posts for 2019

With 2020 quickly approaching, we thought it’d be fun to look back at our top 10 technology blog posts for the past year. This year’s list is a great mixture of tips and tricks for current users of Acumatica and Microsoft software solutions, plus expert advice for people in the market for a new ERP.

Acumatica Pro Tip: Keys for a Successful Acumatica Year End Closing

With any accounting system, there are tasks you must do to close out each year. And while many of the general principles remain the same, each system is a bit different. So, last week we brought you our year end close guide for Dynamics GP users, and this week we bring you one for Acumatica users.

Acumatica Pro Tip: Eight Simple Steps for Acumatica Password Reset

Not being able to remember your password when you need to log into an important system like your bank account or your ERP system can be a very frustrating experience. Luckily, most online services have a streamlined the process for retrieving or resetting a lost password. Acumatica is no different. The Acumatica password reset process is easy.

About the Author

As the head of marketing for Intelligent Technologies, Inc., my goal is to educate our current and prospective customers, helping them to navigate the complex technology buying process. If you have questions about our products and services, or would like to learn more about working with us, let me help you get connected to the right person. Message me via the chat window on our website or call (336) 315-3935 x5007.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *