MS Excel Tricks

ExcelTricks

Quick and dirty way to create primary key in excel:

If you would want to have the quickest way to create a primary key which is text based on row number of excel. Here is how you do it. Use the forum

=TEXT(ROW(A1),"000-000"). This is what it would give:000-001

You can copy this formula till the number of records you want and you have a very neat nice primary key gerenated then and there!! It would save a lot of time via other methods. For over a million records you don't have to worry as excel 2010 has over a million rows. 


How to convert wide form of data to long form

First, make sure that in your data table that variable is the leftmost for which you want multiple entries (e.g. if you want to collapse all the vars except the one like date for which you have repeated values for these vars, then Date should be the leftmost var

 Click anywhere within the data table
- hit the key combination Alt - d - p to bring up the Pivot Table Wizard (which is not on the ribbon)
- select "Multiple consolidation ranges" and click Next
- select "I will create the page fields" and click Next
- select your data table (in the example it's A1:E11)  and click Next,
- select the location and click Next

Now you will see a pivot table that looks just like your data table, but we're not finished.
- click the Pivot table and in the field list uncheck Row and Column (and uncheck any Page Field, if present)
- now select the cell with the single remaining total number and double click it. 

The result is a new sheet with your data laid out one item per row.

Highlighting nth Highest Values of a Row for Multiple Rows


While working, just stumbled across this nifty tricky in MS excel. I wanted to know the second and third highest values of a row for multiple rows. Using conditional formatting for each row one at a time would have defeated the purpose. Hence, explored a little and found this formula which worked like blessing!
Here is what you need to do: 
   1.Select the whole array-> 
      2.Hit “conditional formatting”->
          3.Hit “Use a formula to determine which cells to format->In the formula bar enter this formula “=B25=LARGE($B25:$I25,2)”->
              4.Hit “format” button and use the color and other options by which you want to highlight the cells.  
And you are done!!
(I am assuming that B25 is the cell from where your array starts. You have to specify the top left cell of the array which in this case is B25. Also, it can go to as many columns as you want.)
What it does really is, it highlights the second highest values of each row in your array with the format that you specified. Now if you would also want to highlight the 3rd highest value of each row, just repeat the same steps but in the formula replace “2” by “3” essentially this formula “=B25=LARGE($B25:$I25,3)”. 
Likewise you can do it for 4th, 5th or any nth value. 
Why don’t you try it out and check it yourself!!