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!

 

 

IF This Then That? (Quick Excel Tip!)

We all know how useful Microsoft Excel can be. And one of the most used and easy to remember functions is IF.
Many of us are not software developers, but in school or college have used function IF in one form or another, maybe in Pascal or C++. The same function can be found in Excel and can be really useful!

Summary

IF function can perform a logical test and return the desired value. You can use more than one condition by nesting IF functions. The IF function can also be combined with logical functions like AND and OR. To better explain the function we will look at one simple Example:

We will have a table with Existing and Finish Elevations. We want to know if we have Cut or Fill based on weather Exist. Elev. > Finish Elev. (Cut) or Exist. Elev. < Finish Elev. (Fill).

The Syntax is =IF (logical_test, [value_if_true], [value_if_false]). logical_test – Value or Logical Expression that can be Evaluated as Cut or Fill, value_if_true Value to return when Logical_Test is True, value_if_false Value to return when Logical_Test is False.

In our Case we have to compare cells A2 and B2 so we type =B2>A2 this will be our Logical Test, “Cut” and “Fill” will be our Values. Now to complete the function we just compose the whole Syntax.
=IF(B2>A2,”Fill”,”Cut”)
or in other words If Finish Elev. (B2) is more than Existing Elev. (A2) return Fill (“Fill”) else return Cut (“Cut”).

This Formula will appear in cell C2. Now we can easily drag it down and Excel will use if for all cells below.

Nested IF

Nested IF is used when we want to use more than one IF function so we can test more conditions and return more than two Values. Each IF statement needs to be carefully “nested” inside another so that the logic is correct. We can use more up to 64 IF functions, however, this will be a pain to compose and in case you have a more complex scenario you’d better use another function. Here is an example for more complex Nested IF function.

=IF(X13<0,IF(W13<0,D13,(D13-(W13/(T13-U13))*D13)),IF(W13<0,(D13-(X13/(U13-T13))*D13),0))

Logical Operators

A logical operator in Excel is used to compare two values. Logical operators are also called Boolean operators because the result of the comparison in any given case can be either TRUE or FALSE.

Condition Operator Description
Equal to = If value in A1 is equal to value in B1 formula returns TRUE,  otherwise FALSE.
Not equal to <> TRUE if A1 Is different than B1 and FALSE if they are equal.
Greater than > TRUE if a value in cell A1 is greater than a value in cell B1; otherwise FALSE.
Less than < TRUE if a value in cell A1 is less than in cell B1; FALSE otherwise.
Greater than or equal to >= TRUE if a value in cell A1 is greater than or equal to the values in cell B1; FALSE otherwise.
Less than or equal to <= TRUE if a value in cell A1 is less than or equal to the values in cell B1; FALSE otherwise.

 

How to Find and Remove Duplicates in Excel! (2 Easy Tricks!)

Duplicate information might be useful sometimes, but in most of the cases, it makes the understanding of our data hard and not usable. Here we will take a look at how to easily spot Duplicate Data and Remove it from our Excel Spreadsheet!

Highlight Duplicate Cells

First, we are going to look at How to Highlight Duplicate Cells in order to find them easily. This is an easy 3 step process:

  1. We have to select all the cells we want to Analyze.
  2. Secondly, we will click on Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. A window will pop up called Duplicate Values, here next to the values with we can pick the formatting we want and then hit OK.

As you can see most of the data in our Example is Duplicate. Now we can decide whether or not we need those duplicates.

Remove Duplicate Cells in Excel

Removing Duplicate rows is as easy as highlighting them. Again we will go through 3 simple steps:

  1. First, select cells that have duplicate values we want to remove
  2. Secondly, we click on the Data tab and then Remove Duplicates.
  3. A window will pop up, here we will check the columns where we want to remove the duplicates. After that, we hit OK.

This is the easiest way to Remove Duplicates in Excel with just 3 Clicks of the mouse!

Hope you like the post and find it informative! You can check out our other Microsoft Office Posts!

Excel formulas are not working (8 Common Mistakes and Solutions)

The biggest nightmare that we as professionals who use Microsoft Excel may have is not to be able to use Formulas in our spreadsheets. We have already discussed why Excel Formulas are not working, not updating & calculating or showing formula instead of the result. Today we will look at why Formula is not working correctly and why is it returning error or wrong result. We will summarize the most common mistakes that we make when creating and using Excel Formulas and try to fix them!

Number enclosed with quotes!

If you enter enclose a number with double quotes it will be interpreted as Text by Excel and you won’t get the result needed.
For example, if you use formula like =IF(SUM((A1:A5))>2, “5”), Excel will treat 5 as Text, then you won’t be able to use that 5 in other formulas. To fix this just remove double quotes and type =IF(SUM((A1:A5)>2, 5)

Always enter the full path to other Spreadsheets!

If your formula references other excel workbook, you have to enter entire path to the workbook. The format that you have to use is: [Workbook_name]Sheet_name!Cell_address

for example: =IF(‘P:\Projects\NewYork\Excel\102017\[Floor.xlsx]First’!B12>2, 3 ,4);

If the Spreadsheet is currently open you can just type in the name/sheet/cell address without the path

for example: [Floor.xlsx]First!B12

Use proper character for separating function arguments!

This problem may occur if you are using a new computer because in different regions of the world people are using different character separators. For example, in my country, we are using comma as character separator but in Germany, people are using a semicolon.  Here is how a formula will look in the two regions:

If you are using Comma =IF(A1>0, Bad, Good)

If you are using semicolon =IF(A1>0; Bad; Good)

So, if your formulas are not working and you have an error popping up you can go to your Regional Settings and check what character is set as List Separator. Then you can use that character in excel for your formulas.

You can find your Windows Regional Settings under Control panel >> Region and Language >> Additional Settings

Look after the Parentheses!

Arguments of Functions in Excel are entered within the parentheses if you are using complex formulas, you may need to enter more than one set of them. So don’t hurry and take some time to look after opened parentheses and close them. Newer versions of excel pair parentheses with different colors and makes checking them much easier.

Enter all required Arguments!

Excel formulas have one or more required arguments, some of them also have optional arguments, which are enclosed in square brackets [ ]. You need to enter all the required arguments or you will end up with “You’ve entered too few arguments for this function” alert. On the other hand, if you have entered more than allowed arguments you will have this message: “You’ve entered too many arguments for this function”

So don’t forget to look at the formula you are about to use and see how many arguments you need to have in order to use it!

Don’t nest too many formulas!

This was a problem back when we were using excel 2003 because you could nest only up to 7 nested functions in one cell. With the newer versions of Excel, we can nest much more functions in a cell – up to 64!

Don’t use numbers that are formatted as Text!

If your numbers are formatted as Text, Excel formulas won’t work, so make sure all your number cells are formatted as Number or General.

Use numbers without formatting!

When you are using a number in Excel formula, don’t add signs like currency or units for length, area, etc.

We need to remember that in most cases comma is used to separate function’s arguments and dollar sign makes an absolute cell reference.

When you want to have for example cell that shows currency in dollars just go to Format Cells and under Custom make your Cell display a dollar sign after the number.

 

 

Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!)

When we talk about Microsoft Excel the first things that come to mind are Excel’s formulas and functions. Can you imagine a case when formulas stop working? Well if you are here maybe you have asked questions like: Why formula is not updating its value automatically? Is my Excel or PC broken? How to make my Excel calculating and updating formulas again? Don’t worry, this may happen to everyone, there is nothing wrong with your Excel!

Excel formulas are not updating

The value returned by Excel’s formula doesn’t update automatically – the cell with the formula continues to show the old value even after changing the values of the dependent cells.

The problem is most likely caused by accidentally changing the calculation setting from Automatic to Manual. To fix this we just have to set the Calculation option back to Automatic.

To change the Calculation option we have to go through these simple steps

  • In Excel 20102016, click on File >> Options >> Formulas >> Calculation options section >> Under Workbook Calculation Select Automatic
  • In Excel 2007, click on the Office button >> Excel Options >> Formulas >> Workbook Calculations here select Automatic
  • In Excel 2003, click on Tools >> Options >> Calculation >> Calculation select Automatic

If you are using new ribbon interface you can go to the Formulas tab > Calculation group, click on Calculation Options and from the drop-down menu select Automatic.

Force Excel Formulas to Recalculate!

Ok then if you have really good reason to keep your calculation method to Manual at least how can you refresh all formulas?  To make excel formulas recalculate just hit Calculate button on the ribbon!

If you like using shortcuts you can try one of those:

  • To recalculate the whole workbook press F9 or go to Formulas tab on the ribbon and under Calculation group hit Calculate Now

  • To run recalculation only on Active Sheet press Shift + F9 or go to Formulas tab on the ribbon and under Calculation Group hit Calculate Sheet

  • If you want to recalculate all the sheets in all open workbooks press Ctrl + Alt + F9

If you need to recalculate only One Formula on one sheet just enter the editing mode by either double click on the cell or pressing F2, then hit Enter!

Excel shows the Formula, not the Result

There are three main reasons why this may happen to you, we will discuss all three of them!

Formula is entered as Text

Frequently when we enter formulas in excel don’t think of the Number Format. However, sometimes we may enter a formula that has been formatted as Text, to check this just select the cell with the formula and look at the Number Format box on the Home Tab.

If that is the reason just change the Number Format to General and hit Enter!

Show Formulas is turned ON

The second reason why you see your formula instead of the result may be the Show Formulas option.  To turn that option OFF you just have to go to Formulas tab and under Formula Auditing turn off Show Formulas button.

There is a space before your Formula

The last case in which you can have cells showing formulas instead of results is when you have entered a space or apostrophe before the Formula (Equal sign).
In that case Excel formats the cell as Text and you won’t see the result. To fix this issue, just delete the leading space or apostrophe from the cell!

If your formulas are not still not working make sure you check our post about 8 Most common mistakes when using Excel Formulas.

 

 

Flip Columns in Excel – 2 Extremely Quick and Easy Methods!

Many times I myself faced this problem – Flip column data in Excel. It was extremely painful and inefficient to fill all the cells again by hand, that’s why I was so happy when I learned those two tricks!

Flip data upside down using Sort command!

This method requires us to create “help” column besides our data. Then we would use Sort command to reverse the data in our main column. Let’s go through the whole process step by step:

  1. We create the “Help” Column next to our main one. The new column must consist of numbers from 1 to X. (the same number as cells in our main column)
  2. Now we have to click on “Sort Largest to Smallest“. The button is located in “Data” ribbon tab under Sort & Filter.
  3. After hitting “Sort Largest to Smallest” in the Sort Warning dialog window, we will have to check Expand the Selection and then click Sort.

We now have our column revised in order. This method will flip all the columns that are right next to our “Help” column.

Bonus tip: Flip data in rows using sort!

To flip rows, we have to go through identical steps!

  1. We create “Help” row under our main one. The new one again must have numbers from 1 to X.
  2. This time we hit “Sort” button located again in Data under Sort & Filter tab.
  3. Under Sort Warning dialog box we again hit Expand the Selection, but now a new window called Sort will pop up. Here we hit Options… button.
  4. Here, we change orientation from “Sort top to bottom” to “Sort left to right
  5. In sort window we have to choose our “Help” row in this example we choose “Row 2”

Our rows are now Flipped!!

Flip Column in Excel using Formula!

This method requires filling up one simple formula. We will go through the steps again:

  1. We have to fill up this formula =INDEX($A$1:$A$12,ROWS(A1:$A$12)) in the column where we want to flip the data.

    Note: You have to select your range of cells in the example we are flipping cells 1 to 12 that is why they are used in the formula.
  2. After filling up the formula we just have to drag it down until all the cells are flipped!

Save hours of typing by using those two simple tricks!

 

 

How to Remove Background from Pictures Without Specialized Software? EASY!

Photo editing

Removing background from pictures is really common task. This is not something only bloggers have to do, there are lots and lots of times you will need to remove background from a picture. For example if you are creating Presentations, writing articles, creating videos, selling stuff online and much more! It is really useful to know how to do this with Photoshop for example, but not everyone is familiar with this software. On the top of everything it costs a lot.. Removing a single-color background isn’t that hard to be done, but removing complex background from photo seems like an impossible task!

Or is it impossible ? Here I will share with you two methods i usually use to get the job done.

Set Transparent Color in MS Office

 

This method works only for single-color backgrounds, it is a bit dull. However sometimes this is all we need. It is really fast and easy method which can save us in many cases. The best part of it is that you can do it in MS Office 2007!

Take note that if there is more than one color in the background this won’t work even if it looks like everything is single color. If we have some problems there are more solutions below. Now without further ado let’s start with the trick:

  1. We have to drop our image in PowerPoint (it will work as well in Word or Excel).
  2. Now as our image is in PowerPoint we have to click on it and go to FORMAT tab under PICTURE TOOLS, there we Click on Color and from the drop down menu we select Set Transparent Color.
  3. Now our mouse will turn into little pen or something like that, we just have to click on background. Thats it! our picture is now with transparent background. We just have to right click on it and Save it As Picture..!

Using Online Tool for background removal

Now we will try to remove complex background from a picture. In the example we will be using picture of girl having cup of coffee. It has really complex background and would be impossible for us to use first method.
There are lots of Online Tools that can get the job done. I will share with you 3 of them.

Clipping Magic

This is great tool that works very good and we can see the result in real time.

How it works? Well we just drag and drop our picture in the site, then we will see the main window where the Magic happens. Site will automatically explain in depth how to use it, but in a nutshell we have green and red tool in upper left corner of the screen. With green one we mark all we want to keep and then with the red one we mark what we want to get rid of. The result will be something like this:


The tool is really good but if we want to download the picture we’ve just created we have to pay a subscription which is not cool..

AutoClipping

This site is almost identical with Clipping Magic, but provides us with opportunity to Download our picture for free. However there is a little catch, if we want free picture we have to register, and there is a limitation – up to 0.5 megapixel images, which is not that bad in most of the cases.
Here is the interface:

EditPhotosForFree

This one works almost the same way as other two. The problem is that it is not interactive and you can’t see your progress in real time. Also I personally find it harder to get good results, it is not so well optimized. However it is free and that’s huge advantage!
Here is the interface:

Remove background using Microsoft Office 2010-2016!

This is by far my favorite method. It is really good because it works fast and is very well optimized for the job. If you have MS Office I will recommend it to you!

Now let’s see how to do it:

  1. Firstly we have to drag and drop our image in PowerPoint or Word or Excel..
  2. As our image is in PowerPoint we have to click on it and go to FORMAT tab under PICTURE TOOLS. In the upper left corner we will see Remove Background, just click on it!
  3. Now some parts of our picture will become violet this are parts that will be removed. Now we also have a new frame which we will adjust accordingly to what part of our picture we want to keep.
  4. As we can see there are some mistakes, to correct them we will use the markers which are in the upper left corner. We will Mark Areas to Keep (they will look as lines with + symbol on them) and Mark Areas to Remove ( they will look as lines with symbol on them)
  5. Now we just have to click on Keep Changes and then right click on image to Save it as Picture..!

How to Copy Multiple Text Objects from AutoCAD to Excel

The task?

Have you ever tried to copy AutoCAD text objects to Microsoft Excel? Usually, when you copy multiple text objects from AutoCAD and paste those to Excel they look like a picture. If you double click on that picture, AutoCAD automatically launches and opens a fragment of our drawing which we have copied as a new .dwg file.
Yet, oftentimes we need to copy multiple AutoCAD Text objects and have those pasted to different Excel Cells as texts for example copying coordinates of points in AutoCAD to Microsoft Excel.

Solution! Copy multiple Text from AutoCAD to Excel!

I am sure that there are some lisps or even software addons for AutoCAD that could help us copy multiple text objects to excel. However, today we are going to look at one basic method which is extremely fast and doesn’t require third-party software.
To complete the task of copying several AutoCAD Text objects to Excel we will go through some very simple steps:

  1. First, you need to convert the text objects to one Mtext object. To do that, type in the command line TXT2MTXT and select the text objects.

    Note: For best results, align the text objects the way you need them in Excel.
  2. Then, double click on the MText object and Copy the text (Ctrl + C)
  3. Now launch Microsoft Excel and paste the data there. All the text will be pasted in a single cell. Don’t worry it is normal!
  4. Now select the cell with the text, then go to the Data tab, there, we click on Text To Columns.

  5. A window will pop up called Convert Text to Columns Wizard. Here we have 3 pages with settings. But don’t worry they require maximum 1 click!
    1. On the first page, select Delimited and hit Next
    2. On the second page, set the delimiters. In the example we select “Space” and deselect “Tab“.
    3. There is nothing worth doing in the third page so we just hit Finish!
  6. The copied text is now Horizontaly aligned in Excel. The last step is to transpose the table (convert it from horizontal to vertical). To do that select the filled cells, copy them and then right-click on an empty cell somewhere on the sheet to select Paste Special > Transpose.

Cool! The text objects are now pasted in Excel. We will repeat the procedure for other text objects if we need it!

Check out more AutoCAD Related Articles in our blog! And don’t forget to share the trick with your friends and colleagues!

Posting....
Exit mobile version