Row sorting in excel

Question

Please how can I highlight certain cells(one each/row) that are in rows? For example I have a list of quotes from different suppliers and I want to select only the min/least price of each item and choose the supplier that has that.

I tried using the MIN command but that only gives me the answer but does not include the colour (or colouring the cell if all cells in the row are white)of the cell which is my most desired result when comparing quotes/bids of suppliers.

Answer 1

If you are using Excel 2007 or newer, this should get you going in the right direction:

1. Add a new column to your worksheet based on your MIN calc (let's say the value is set to 1 if it is the required MIN
2. Highlight the entire column On the Home Tab, select Conditional Formatting >> Highlight Cells Rules >> Equal To...
3. Enter a 1 for the value (or whatever you used in step 1), and choose your desired formatting

Answer 2

It is pretty easy to do with conditional formatting using the below steps. You start by applying the conditional formatting to one cell and then applying it to every cell in the range.

1. Select one cell in one of the rows you are wanting to apply the formatting to.
2. Click on Conditional Formatting > New Rule
3. Select "Use a formula to determine which cells to format"
4. Paste the below formula into the formula box.

=MIN($G5:$P5)=H5

5. Change H5 to the cell you selected in Step 1
6. Change G5 to the leftmost cell in the same row of the range you are evaluating.
7. Change P5 to the rightmost cell. (For step 6 and 7 be sure not to remove the $)
8. Click on Format and set it to whatever settings you want for the highlighted cell
9. Click OK until you have closed the New Formatting Rule window.
10. Go back into Conditional Formatting, but this time select Manage Rules.
11. In the Applies to Box, change the range to the entire range you want to apply the formatting to.