How do I use the IF formula in Excel?

IF Definition:  IF(CONDITION, action or value if CONDITION is true, action or value if CONDITION is false)  “If” Formula can work for anything that is based off of the data in another column.

=IF(E15=“ALUMINUM 2219”, “TRUE”, “FALSE”)

If the text of cell E15 is exactly “ALUMINUM 2219-T81” then the selected cell will display “TRUE”, otherwise “FALSE” will be the value.



More on IFs including nested ifs and use in conjunction with OR formula coming soon!

How do I use the LEFT, RIGHT, and LEN functions in Microsoft Excel spreadsheets?

The LEFT or RIGHT function returns the leftmost or rightmost characters from a text value. Example:

=LEFT(A2,3)

If value in cell A2 were 184770 the formula would display the number 4.

=Right(B3,2)

If value in cell B3 were 16579 the formula would display the number 7.

LEN (length) returns the number of characters in a text string. Example:

=LEN(A2)

If the value in cell A2 is 16498 then the formula will display 5.


Combine the two in a single formula example. Say you have a single digit which indicates length in .125 increments. Double digits indicates length in whole inches AND .125 increments.

=IF(LEN(D2)=2,LEFT(D2,1)+(RIGHT(D2,1)*0.125),RIGHT(D2,1)*0.125)

If the length of the value of cell D2 (the length number) equals 2 (which means it is double digit), then add the left digit plus the right digit multiplied by 0.125, otherwise take the single digit and multiply by 0.125.

How do I create a message box vba macro in Excel?

This is a fairly easy macro to write and is good practice for beginners. Now we will make a message box automatically pop up when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.

Sub Auto_Open()
Msgbox "Hello"
End Sub

This code would be located in the module. However, if you use the second method, the code must be in the workbook (double click "This Workbook" in the explorer window). Click on the drop down list (that says General) and select Workbook. Click on the drop down list (that says declarations) and select Open.

Private Sub Workbook_Open()
Msgbox "Hello"
End Sub

You've just created your first macro. I will try to integrate different levels of difficulty from beginner, intermediate, and advanced tips for macros, formulas, and Excel tips in general.

What is an absolute reference in Microsoft Excel 2007 spreadsheet?

Dollar signs specify an absolute reference to a cell’s column and/or row.

Examples: =A1, =$A1, =$A$1

When copying a formula with an absolute reference, the $'d column or row will not change.

Example:

=A11 moved down will be A12
=A$11 moved down will be A11

How do I check for duplicate entries in Excel?

Go to ‘Data’ tab
Click ‘Remove Duplicates’
Click ‘Select All’ or pick which columns to remove duplicates.
Click ‘OK’
A window will pop up letting you know how many duplicates were removed, if any, and how many unique values remain. Click OK. That's all there is to it!

How do I make a random list in Excel?

How do you make a random list in Microsoft Excel 2007? Well, one way to go about it is as follows. First we have our list.



In the column next to it, put a =rand() into every cell.



This is the random number function, which generates a random number between 0 and 1.



Next, highlight both columns. Sort by the random numbers column and that's it!



Your list has been randomized!

How do I combine two cells in Excel?

Sometimes you may want to combine into one column text that is currently contained in two columns. One way to do this is to write a formula in a third column which uses the concatenation operator, the ampersand (&). This formula in cell C1 would return the contents of A1 followed by the contents of B1:

=A1&B1

If you want to include a literal character, say a space, use the form:

=A1&" "&B1