Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel VLOOKUP Function
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Find closest match in Excel
  • Lookup entire row in Excel
  • Count unique text values with criteria

Data Analysis Examples

  • Understanding Pivot Tables in Excel
  • Remove Duplicates Example in Excel
  • How To Create Frequency Distribution in Excel
  • How to Create Thermometer Chart in Excel
  • Error Bars in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation must begin with
  • Data validation must not exist in list
  • Excel Data validation don’t exceed total
  • Excel Data validation allow uppercase only

Understanding Various Paste Options in Excel

by

This example illustrates the various paste options (Paste, Values, Formulas, Formatting, Paste Special) in Excel . Cell B5 below contains the SUM function which calculates the sum of the range B2:B4. Furthermore, we changed the background color of this cell to yellow and added borders.

Paste

The Paste option pastes everything.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell F5, right click, and then click Paste under ‘Paste Options:’ (or press CTRL + v).

Result.

Worked Example:   Find Most Frequently Occurring Word in Excel Worksheet

Values

The Values option pastes the result of the formula.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell D5, right click, and then click Values under ‘Paste Options:’

Result.

Note: to quickly replace the formula in cell B5 with its own result, select cell B5, press F2 (to edit the formula) and press F9.

Formulas

The Formulas option only pastes the formula.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

Worked Example:   How to get Weekdays, Working days between Two Dates in Excel

2. Next, select cell F5, right click, and then click Formulas under ‘Paste Options:’

Result.

Formatting

The Formatting option only pastes the formatting.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell D5, right click, and then click Formatting under ‘Paste Options:’

Result.

Note: the Format Painter copy/pastes formatting even quicker.

Paste Special

The Paste Special dialog box offers many more paste options. To launch the Paste Special dialog box, execute the following steps.

Worked Example:   Transpose: Switch 'Rows to Columns' or 'Columns to Rows' in Excel

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell D5, right click, and then click Paste Special.

The Paste Special dialog box appears.

Note: here you can also find the paste options described above. You can also paste comments only, validation criteria only, use the source theme, all except borders, column widths, formulas and number formats, values and number formats. You can also use the Paste Special dialog box to perform quick operations, skip blanks and transpose data.

Post navigation

Previous Post:

Dynamic Named Range in Excel

Next Post:

Subtract Numbers in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • Extract last name from full name — Manipulating NAMES in Excel
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to get last line in cell in Excel
  • How to create email address from name in Excel
  • Convert Unix time stamp to Excel date
  • How to calculate Quarter of Date in Excel
  • Get days before a date in Excel
  • How to get workdays between dates in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
  • Calculate original loan amount in Excel
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • DISC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles