Wednesday, November 12, 2014

Excel videos for excel beginners

Excel-formulas.com is a nice place for beginners to start learning excel formulas. Video tutorials are also available which in a short time explains the basic concept and various scenarios that the formulas or excel feature can be used.

Thursday, October 16, 2014

Classify numbers in a Range


Suppose we have data in excel. It has ticket ids in column A, column B is the status of ticket, column C is the Request date of the ticket, column D is the Latest update, column E is the Ticket age (days) and we need to classify Ticket age into different ranges in column F (Ticket age - Range) as follows: ">300 days", ">200 days", ">100 days", "100 days or less"

We can achieve this using If condition in cell F2:

=IF(E2>300,">300 days",IF(E2>200,">200 days",IF(E2>100,">100 days",IF(E2<=100,"100 days or less","")))) 

You then simply have to drag the formula down to the very last row having data to get the Ticket age - Range for all the rows with data.

Check out the image below which displays the final result:





Friday, June 3, 2011

HOW TO conditionally format entire row/s based on a text value in a cell in Excel 2007

Scenario: You have a column E named "Priority". You have to highlight the rows with different colors based on whether it is "Urgent", "High" or "Normal".

1) Select the entire range of rows for which you want to apply specific background colors. If you have 1000 rows and say 10 columns of data, then select this entire range of cells.

2) In the Home tab, click on Conditional Formatting item in the Styles panel

3) Select the last option Manage Rules

4) This brings up the Conditional Formatting Rules Manager.

5) Click on the New Rule button

6) This opens up a dialog box titled New Formatting Rule. Select "Use a formula to determine which cells to format".

7) In the textbox that says Format values where this formula is true, type this formula:

=EXACT($E2,"Urgent")
The EXACT function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.

8) Click on the Format button on the same dialog box. This opens a another dialog box Format Cells

9) Move to the last tab Fill, select a color that you want to mark as the background color and then click on OK

10) This takes us back to the original Conditional Formatting Rules Manager dialog box. When you click on the OK button, the background color is applied to 2 rows that contains the text value "Urgent".

11) To specify a background color for rows that contains the text value "High", repeat steps 3 to 9, this time specifying a different background color.

12) Repeat this procedure for other criteria (text value "Normal") and finally you have entire rows conditionally formatted.

Courtesy: http://www26.brinkster.com/mvark/codegallery/conditionally_format_entire_row.aspx

Prevent duplicate entries in a column


  1. Select the range of cells in which you want to prevent duplicates (for example, B2:B1048576).
  2. From the Data menu select Validation.
  3. Click on the Settings tab.
  4. From the Allow dropdown box select Custom.
  5. In the formula box type =COUNTIF($B$2:$B$1048576,B2)=1 (see explanation below)
  6. Click on the Error Alert tab.
  7. In the Title box type "Duplicate Entry!"
  8. In the Error Message box type "You cannot enter a value already in the list."
Explanation:

This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($B$2:$B$1048576,B2)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert.