How to use Excel’s IF function is easy. Follow these steps:
- Enter two values in a worksheet.These values should have some meaning to you, such as the inventory levels example shown above.
- Click the cell where you want the result to appear.
- Type =IF( to start the function.
- Decide what test you want to perform.You can see whether the two values are equal; whether one is larger than the other; whether subtracting one from the other is greater than, equal to, or less than 0; and so on. For example, to determine whether the first value equals the second value, click the first cell (or enter its address), enter an equal sign (=), and then click the second cell (or enter its address).
- Type a comma (,).
- Enter the result that should appear if the test is true.
- Type a comma (,).
- Enter the result that should appear if the test is false.For example, enter “The values are not equal”.
- Type a ) and press Enter.
The Excel IF function can do a whole lot more. Nested IF functions give you a lot more flexibility in performing tests on your worksheet data. A bit of perseverance is necessary to get through this. Nested means that you can place an IF function inside another IF function.
Convert UPPERCASE to lower or Proper case without retyping
Stop the retyping and follow these steps. This is a two part process. First you will create a formula to convert the text then you will copy and paste the value into a new column.
- Insert 2 blank columns to the right of the column in question (A). The example below shows column A in UPPERCASE column B and C are blank, column D has a location.
- In this example a formula would be placed in the first cell to the right of the first name B2.
- Type the following formula =Proper(A2) pressing enter will convert the UPPERCASE NAME to the proper case name. Example below shows both the formula and the proper case name.
- Now using the Autofill handle in the lower right hand corner of B2 click hold and drag this down the column to copy the formula that converts the character case. DO NOT DELETE COLUMN A at this time.
- Now that column B has the proper case displayed you must copy Column B and paste it into column C using Paste Special. Reminder: Column B is only displaying a value of a formula not the actual text. Click on the column heading B to select the column. Copy using CTRL+C or the copy icon.
- Paste Special: Right mouse click in cell C2, then select Paste Special from the quick menu. The Paste Special dialog box will appear.
- Click on the radio button for Values. This will paste the value of the convert formula into column C.
- If Column C looks correct you can now delete Column A and B.
Note: There are two other formulas that change case as well: =UPPER(cell) will convert to all upper case characters. =LOWER(cell) will convert to all lower case characters. Replace (cell) with the actual cell location.
Hide what you don’t need or want to see
Select the column/row or columns/rows to hide. The shortcut is to right-mouse click on the selected column or rows and select Hide from the menu.
Example: columns D and E are missing. The column separator becomes thicker.
To bring the hidden column or rows back into view, select the column before and after the hidden column. In this example you would select column C and F. Right mouse click and you will find Unhide on the menu.
Note: You will find the Hide / Unhide feature on the Home ribbon, under the Cell grouping. The Format drop-down has a Visibility Hide & Unhide, Rows, Columns and Sheets.