Paste transposed in Excel and Keep Formula References! (Two great ways to achieve that!)

In Excel, the paste as transpose is extremely useful for transposing a column to a row or vice versa. But if there are formulas in the cells, the reference will change and mess up our desired values.
In this post, we will show you, two easy methods on how to paste as transpose and keep formula/value references.

In the first method, we are going to Paste as transpose and keep formula references using Find and Replace functionality.
When using the second method we will Paste transpose and keep the values referenced from the original cells.

Paste Transposed and keep reference in Excel using Find And Replace!

To transpose and keep formula references in Excel just follow these 5 Simple Steps:

  1. First, Select the cells you want to transpose, and click on Find and Replace button on the Home tab (You can also hit Ctrl + H to trigger the Find and Replace function)
  2. A window will pop up, there under Find what type =, and under Replace with type 1=. Then hit Replace all and Close the window.
  3. Now, Select the newly modified cells again and Copy them using Ctrl + C
  4. Click on the cell where you would like to transpose them and right click, then click on Paste special > Transpose to paste transposed cells.
  5. Select the transposed cells and again open Find and Replace (Ctrl +H), this time under Find what type 1=, and under Replace with type =. Then again hit Replace all and Close.

Good Job! The cells are pasted and transposed while the formulas keep the references!

Paste transpose in Excel and keep the values referenced from the original cells.

To transpose Column to row or vice versa in Excel while keeping the values referenced we will use the TRANSPOSE function. We can do this by Copy/paste transpose but this will create duplicate data. If you don’t want this fallow these steps:

  1. First, select the same number of cells as the original set. In our example, we need 13 horizontal cells.
  2. Now while those cells are selected type =TRANSPOSE(
  3. Make sure all cells are still selected and then type in the range of the original cells. In our example, it would be =TRANSPOSE(B4:N4)
  4. STOP! DON’T HIT ENTER!
  5. Finally, hit Ctrl + Shift + Enter. The TRANSPOSE function is only used in array formulas, this is the way to finish an array formula, so it will be applied to more than one Cell.

Great! Now the cells are transposed and retain a reference to original cell values!

Learn more useful Excel Tricks here! And don’t forget to share your new knowledge with others!

Find and Filter unique Values in Excel (Only 4 Simple Steps!)

We discussed how to Find and Remove Duplicates in Excel! What if we are not sure if the data won’t be needed later on. This is why a good rule of thumb will be to first Filter and Highlight the Duplicate data and Remove it when we are sure this is the result we want.

Find and Filter unique Values

The first thing we will be doing is to find and filter the unique Values. We will be using a simple table containing X, Y, Z coordinates.

  1. Select range of cells to format.
  2. Go to Data tab, then under Sort & Filter click on Advanced.
  3. A new window will pop up. Here we can select Action either to Filter the list, in-Place or Copy to another location.
    1. If we choose to filter in-place, we Excel will hide the rows with duplicate data for us.
    2. If we choose to copy to another location, we will be able to directly copy the cells with unique values to a new place. To do that we just have to type in the number of the cell we want to copy the data to, or click on the small icon next to the blank field and select the desired cell with a click of a mouse.
  4. Check the Unique records only box, and click OK.

You can easily see that all the duplicate cells are gone in the new copy.

Don’t forget to Subscribe, or follow us on Social Media so you won’t miss any new piece of knowledge!

 

 

 

If This AND/OR That (A Quick Excel Tip)

We’ve already discussed the use of IF function in Microsoft Excel. Today we will dive a little bit deeper.

AND Function in EXCEL!

What If we want to check if more than one conditions are TRUE? In this case, we will use AND function.

Best way to describe the way AND works is to show you a little Example.

Testing different Cells

In this Example, we will test if we have Circular shape and Size smaller or equal to 20. If we do, we need “X” symbol for Usage, if we don’t we will display “-“.

As we are already familiar with the IF Syntax –
=IF (logical_test, [value_if_true], [value_if_false]). we won’t explain it again.
This time for Logical Test we will be an AND Function. The syntax for And Functions is:
=AND([logical 1],[logical 2], ..) 
Note: We can use more than two logical tests!

In our example AND Syntax will look like this:
A2=”circle”,B2<=20

And the whole test is:
=IF(AND(A2=”circle”,B2<=20),”X”,”-“)

Test a single Cell

We can use AND function to test a single cell. For example, If the quantity of items is between 100 and 150 we will store them.
In this case, the whole Formula will look like this:
=IF(AND(B2>100,B2<150),”Store”,”-“)
Where AND(B2>100, B2<150), is the syntax for AND Function.

Using OR function in Excel!

As useful as AND is there is an equally useful function, namely OR.
The main difference is that while by using AND, TRUE result will be given when All the logical tests are TRUE. Using OR requires only one of the tests to be TRUE in order to return value_if_true.

To better illustrate that we will use OR with our previous examples.

Testing different Cells

This time we want to get “X” every time the Shape is Circle or the size is smaller than 20.

The syntax for OR Functions is:
=OR([logical 1],[logical 2], ..) 
Note: We can use more than two logical tests!

The whole test is:
=IF(OR(A2=”circle”,B2<=20),”X”,”-“)

Testing a Single Cell

In this example, we will Store the items that are more than 150 or less than 100. So the formula will be:
=IF(OR(B2>150, B2<100),”Store”,”-“)

We hope you like the content and find it useful! Stay tuned for more also Share it with your Colleagues and Friends!

 

 

Posting....
Exit mobile version