Excel paste tips and tricks

Paste in reverse order

If you have a column with some values that are not sorted and you want to paste them in reverse order you can do it by two ways:

Paste in reverse order with extra column

First way is by adding extra column with numbers from 1 to N (the last row number). This one is better if you have multiple columns and data.

  • Add extra column with 1, 2, 3 from first the the last row.
  • Copy and paste the range on clean area
  • Sort by the extra column in reverse order

Paste in reverse order with Formula

If you need to reverse only one column or if you prefer to use formula you can do it by this formula.

=OFFSET($B$12,-(ROW(B1)-1),0)
=OFFSET($B$12;-(ROW(B1)-1);0) # if you use ";" instead of ","
  • Add the formula on clean area - I2

  • Expand the formula up to last row - I12

  • Copy and Paste values

  • copy and paste (from the menu and select 'Values')

    or with keyboard shortcuts

  • copy by pressing CTRL+C

  • paste special - CTRL+ALT+V

  • Select 'Values' - V

  • Press 'OK' - ENTER

Paste Table from Web

If you want to paste information from browser ( web site) very often there is a problem with formatting. One way to solve this problem is by pasting the information in notepad or another text editor and then select and copy it from the text editor and paste finally in excel. This will solve most problems related to formatting:

  • Open table(for example from wikipedia)
  • copy it
  • paste it in notepad
  • Select again and copy
  • Paste in excel
  • Select "Use Text Import Wizard"
  • from the left corner menu

or by

  • CTRL+V
  • Select menu'Options' - CTRL
  • Press "Use Text Import Wizard" - U

  • in "Use Text Import Wizard"
  • select separator way - Delimited or Fixed
  • Select Column format - text, general, date, do not import.
  • Finish.

Paste rows to columns

  • copy by pressing CTRL+C
  • paste special - CTRL+ALT+V
  • Select 'Values' - V
  • Select 'Transpose' - E
  • Press 'OK' - ENTER

Paste rows to columns

  • copy by pressing CTRL+C
  • paste special - CTRL+ALT+V
  • Select 'Values' - V
  • Select 'Transpose' - E
  • Press 'OK' - ENTER

Related Article