I'm using Excel 2010

I have a monthly menu. menu days are listed by row with 2 menu items list for each day.

Col-C Col-D

Day 1

  • Cheesburger
  • Hamburger

Day 2

  • Taco
  • Quesadilla

Day 3

  • Spaghetti
  • Lasagna


I want to highlight the items for a given day if the items on that day are NOT in alphabetical order.

I can create the conditional format for Day 1, but want to repeat the format for the rest of the items. I've tried copying the format, but the absolute reference of day 1 causes all rows to be highlighted ONLY when day 1 is NOT alphabetical. In the example above, only Day 3 should be highlighted since alphabetically "Lasagna" comes before "Spaghetti".

  • Your menu is making me hungry. :-) – jftuga Jun 16 '11 at 16:20
  • any chance seeing what you already have in your conditional formula? – datatoo Jun 16 '11 at 16:56
  • 4
    Why do you want to highlight items that aren't in alphabetical order? Is this so that you can correct the order? If so, why can't you just use custom sorting to resolve it for you? – Iszi Jun 16 '11 at 17:16
  • @Iszi it might help to see an example of the custom sorting you mention in your comment – datatoo Jun 17 '11 at 20:03

This doesn't answer the question you've asked, but I think it addresses the need you seek to have filled.

First, I'll start with a spreadsheet that simply displays the data you've given. Note that I've merged the cells for each day, for a simpler display. I've also deliberately put the Items in non-alphabetical order, so you can better recognize the end result.

enter image description here

Next, we add a column that will copy the value in the Day column to an individual cell for each row in the Item column. This column doesn't have to be where I've placed it, but it must be adjacent to the Item column.

enter image description here

Now, for the sorting. For this to be done accurately, the following must be true:

  • Each Day must have exactly the same number of rows as the others.
  • All cells that will be sorted (better shown below) must be non-merged cells.

Now that you have all the data entered, select only columns B and C. Then, on the Data tab, select Sort. In the Sort dialog, make sure that "My data has headers" is selected. Then, choose DayValue from the "Sort by" drop-down and leave the defaults ("Values" and "Smallest to Largest") in the other drop-downs. Now, click "Add Level". For this next level, you want to select Item as the Column, and leave the defaults of "Values" and "A to Z". When done, your screen should look like this:

enter image description here

Now, just get rid of the extra column by selecting the column, right-clicking, and pick "Hide". After that, all that's displayed is just the data you want shown, in the order you wanted it.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.