Ever wanted to transpose columns to rows or rows to columns?
It’s easy with the the Paste Special dialog.
Consider the following cells

I really want the data the transposed so that the rows are in columns.
So copy the original data, select the destination cell then select Paste Special from the Edit menu

Select the Transpose option at the bottom right and click OK. You can still use the other options like Value or Formats.

This really is a powerful tool.

We all know about copy and paste. Here are a set of options that allow the Excel user to paste the elements that they want from the cells. That could be formulas, formats, values or comments. But there are other tools here too.
Look at the Operation section, You can use this tool to convert negative numbers to positives (by multiplying a range by -1).

I have been working with telephone numbers recently. As phone numbers are unique to individuals they are useful IDs to match two lists. However some can be formatted as numbers, some as text. This makes it hard to compare numbers in different lists.

I have a phone number 01234 567890, in Excel it will be displayed as 01234 567890 with the space. However  if it was entered as 01234567890, Excel will display it as 1234567890, without the leading zero. If the data has been read in from a text fill in could be formatted as text and still show 01234567890.

When I get data in from an unknown source, I use two functions to help me get a number that is usable in any situation.

First I substitute any spaces =SUBSTITUTE(A3,” “,”"). This will remove any spaces at the beginning, middle or end.

Then I format the number to text =TEXT(B3,”00000000000″). This gives me 01234567890 in my cell. Note the 11 zeros in the text function. this gives me the leading zero for phone numbers.

I do of course do all this in one cell =TEXT(SUBSTITUTE(A3,” “,”"),”00000000000″). I can then use this result as the lookup to compare to other lists and match up phone numbers.