Excel 2. 01. 0 Conditional Formatting Nightmare. Updated March 2. 01. I’m a big fan of Excel’s conditional formatting, and often use it to highlight cells on a worksheet.

How to Edit Data in Microsoft Excel. If you're not very familiar with Microsoft Excel, it might seem an intimidating program to use. Fortunately, it's easy to get. Excel provides a number of tools called Form Controls to allow you to interact with your models. This post looks at the types and uses of Form Controls.

It’s also useful for alerting people to problems, and other devious plots, like hiding cells’ contents, until other cells are filled correctly. But this week I ran into a conditional formatting crisis, and had to start from scratch. Fortunately, the fix didn’t take too long, but with complex formatting, things could have been much worse.

Separate the Dates in a List. Earlier this year, I posted a tip for separating dates in a list, by using a red border at the start of a date change. It’s a technique that I use in one of my own workbooks, which I update every morning.

1. How to control and understand settings in the Format Cells dialog box in Excel.
2. Drop-Down Using Data Validation in Microsoft Excel. This article demonstrates how to create a drop-down list using either an absolute reference or a named range.
3. Note: Manual formatting is not listed in the Conditional Formatting Rules Manager dialog box or used to determine precedence.
4. Gantt chart template here. Here’s more on using conditional formatting in your spreadsheets. Learn Cool Microsoft Excel Conditional Formatting Tricks

The red border really makes the list easier to read, especially if I’m looking at it before my morning coffee! The conditional formatting was set for cells A2: F9, and uses a simple formula to see if the date is equal to the date in the row above.

Quick Copy and Paste. In the workbook, I track some daily statistics for my website, and use the same headings every day. So, to save typing time, I copy and paste the headings from the day before. For example, in the screen shot above, I could copy the regions and products headings from B2: C4, and paste them into cell B1.

C1. 2, for the latest day’s data. The Excel table automatically expands to include the new rows, and fills in the formulas in column F. Awesome! And that copy and paste trick is what I’ve been doing every day, for months on end. Trouble With the Lines. Suddenly, this week, I started having trouble with the red border between dates – it wasn’t going across the full table. So, I went into the Conditional Formatting Manager, to fix the problem.

But, instead of one simple rule, there were hundreds of rules! You can see the tiny scroll box at the bottom right in the screen shot below.

That should give you some idea of how long that list was. Every time that I copied and pasted within the conditional formatting range, it created another rule for the worksheet. Yikes! Let’s take a look at the smaller example, where I just copied and pasted the Region and Product headings. Now, it has two rules, instead of one, after one copy and paste within the table.

Cleaning Up the Mess – UPDATEDUPDATE – March 2. I’ve found a quicker way to fix the problem, as you can see in this video. Get the detailed steps on my Conditional Formatting page. The previous solution is listed below, in case you’d rather try that.

Cleaning Up the Mess – ORIGINAL SOLUTIONI scrolled through the list, and deleted a few of the rules, but quickly realized that it would take far too long to delete all of them. So, to clean up the mess, I cleared the conditional formatting from the worksheet, and set it up again. Nokia Xpress Browser Download For Nokia 5130 Xpress. With all the extra rules gone, the conditional formatting borders were working correctly again.

Avoid the Conditional Formatting Problem. Now, when I want to save some typing time in the daily list, I copy the previous headings, and use the Paste Values button to create the new records. I’ve got a copy of that button on Excel’s Quick Access Toolbar, so it’s easy to click. That adds the text, and the table expands, including the formulas – but the conditional formatting doesn’t create a new rule. Be careful out there — don’t let this conditional formatting nightmare happen to you!

Another Solution. Update: Thanks to Khushnood, who suggested leaving 3 blank rows at the bottom of the table, and inserting new rows above that, when adding new data. Copy and pasting within the table doesn’t seem to produce the duplicate conditional formatting. It’s still an extra step though, instead of a simple copy and paste.

Drop- Down Using Data Validation in Microsoft Excel. This article demonstrates how to. If you need what is called .

Create a list of options. Start by first creating a list of.

Generally, when creating forms, you. If you create these on a separate. Because we use a. It. is not possible to use typical cell referencing when your option choices reside. Because of this, we recommend you always use a named range. We chose department names for our. You can list as many as you like, but don't leave any spaces.

Our. example contains the list of choices on a worksheet called . Make the first option in your list. Name the range that. We then select our range of. Name box (that. drop- down just above column A in any worksheet), as shown below, and then hit.

Enter. Alternatively, you can select the options, and choose from the menus. Insert. Our sample has a named range.

Assign validation to the. We put a nice heavy border on cell C5. Then, select cell C5 and choose. Data. From. the Allow: drop- down, we choose List, as shown below: We then type an equal sign. Source: box, as shown: Tip. Don't forget the equal sign! If you. do, the only option that will appear in your dropdown is the word that you.

Alternatively, if you have only. Step 1 and just type the list into the Source: box, separating.

When typing text options directly into. Source: box, no equal sign is required. Hit the OK button to apply the.

Data Validation to your cell. Click on the cell to activate it again, and a. Test to see that it.

Click the drop- down arrow to view. Select one of the choices.

Check out the Input Message tab. Data Validation dialog.

Any Input Message you provide is displayed as a. The arrow doesn't show unless the. There is no way to alter it to show the arrow using the Data. Validation method.

Use Conditional Formatting to. This lets the user. Making the dropdown provide unique.

Office. Artilces. May 2. 6, 2. 00. 5. Mr. Excel. com provides examples of Formulas, Functions and Visual Basic procedures.

The Formulas, Functions and Visual Basic procedures on.