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.
- All – pastes an exact copy of the selected cells, including formatting and data.
- Formulas – pastes the formulas from the copied cells so you can apply them to a new set of cells.
- Values – pastes only the numbers from the copied cells (no formatting or formulas).
- Formats – pastes only the formatting from the copied cells (not the values or formulas).
- 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).
- Validation – takes the data validation rules from the copied cells and brings them into the pasted ones.
- All using source theme – pastes the cell contents using the document theme formatting that’s attached to the copied data.
- 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.
- Column widths – takes the column width of the copied cells and applies it to the pasted area.
- 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
- None – Excel will not apply any mathematical operations to the selected cells.
- Add – Excel will add the copied data to the data that exists in the pasted destination cell(s).
- Subtract – Excel will subtract the copied data from the data in the destination cell(s).
- Multiply – Excel will multiply the data from the copied cells with that of the destination cell(s).
- Divide – Excel will divide the copied cell’s data by the destination cell’s data.
- 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.
- 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.
