Mark using conditional formatting for dates in Excel

This tutorial about using conditional formatting, to dates highlight of MVP Frédéric Le Guen, pulse for help with the translation from French into English associated with special recognition and thanks to Ken to us.

Date functions in Excel make it is possible, date calculations, such as addition or subtraction, which carry automatic or semi-automatic worksheets. The NOW function, the values based on the current date and time is calculated, is a good example of this.

This functionality make a step further if you mix date functions with conditional formatting, you can create tables automatically displayed the date alerts if a period close to is or distinguishes between types of days, such as weekends and weekdays.

Find conditional formatting for dates, go to

Home > conditional formatting > highlight cell rules > a date come forward.

You can select the following date options, from yesterday to next month:

These 10 date options generate rules based on the current date. When you create rules for other data like (such as greater than a month from the current date), you can create your own new rule.

Below you will find step-by-step instructions for a few of my favorite formats for dates.

When designing an automated calendar you need even colour not weekends. With the conditional formatting tool you can automatically change the colors of the weekends by the format are based on the WEEKDAY function.  Suppose you the date table-have a calendar without conditional formatting:

To change the color of the weekends, open the menu conditional formatting > revision

Select the menu in the next dialog box, the use a formula to determine which cell format.

In the text box values format, this formula is true, enter the following formula day of the week to determine whether the cell is a Saturday (6) or Sunday (7):

=WEEKDAY(B$5,2) > 5

Parameter 2 = means Saturday 6 and Sunday 7 =. This parameter is very useful to test for a weekend.

Note: In this case, you must lock the reference of the line, so the conditional formatting to the other cells in the table properly.

Then fit the format of your state by clicking on the format button and select a fill color (orange in this example).

To do this, click OK, and then open conditional formatting > manage rules

Choose to see this worksheet worksheet rules rather than the default selection. Change in the is valid forarea that corresponds to the first selection when creating rules to expand the entire column.

Now, you see a different color for the weekends. Note: This example displays the results in the Excel Web app.

Enrich to the previous workbook could identify even holidays colours. To do this, you need a column with the holiday, the highlight you want in your workbook (but not necessarily in a sheet of paper). In our example we have U.S. holidays in column AH (in reference to the year in the cell B2).

The menu again conditional formatting > new rule. In this case, we use the COUNTIF formula to calculate the number of holidays in the current month is greater than 1.

 =COUNTIF($AH$4:$AH$16,B$5) > 1

Then choose in the dialog box to manage rules, area B4: AF11. If you want to emphasize the holiday over the weekend, at the top of the list to move the holiday rule.

In the Excel Web app below shows the result in this example. Change the value of the month and year to see, such as the calendar has a different format.

For the case that we want to again change the color of the cells, which is based on our approach on a date, we will work to make for us by using conditional formatting.

We will show in the following example:

Yellow dates between 1 and 2 Monthsorange data between 2 and 3 Monthspurple dates more than 3 months

We create three rules then conditional formatting formula DATEDIF . Each of the three cases the following formulas:

=DATEDIF($B2,$E$2,"m") > 0

=DATEDIF($B2,$E$2,"m") > 1

=DATEDIF($B2,$E$2,"m") > 2

In the Excel Web app, try below are some dates to experiment, to change the result.

Rather than a different color for each period within our time frame set selected, work we with color scales possibility is that our cells color.

First, go in a new column (column E), calculated the difference in the number of days formula and the parameters "yd" in a year again with the DATEDIF.

=DATEDIF($D2,today(),"YD")

Then select "the" conditional formatting > new option format all cells according to their value depending on and select the following options:

Scale = 3 ColorsMinimum = 0 RedMidpoint = 10 YellowMaximum = 30 white

The result is a gradient color gamut with shades of white to red to yellow.  The more closer to 0, red, that it will have closer closer knows more of 10 more yellow and on 30.  In the Excel Web app, try below are some dates to experiment, to change the result.

--Frédéric Le Guen

View the original article here

Impress your teacher: Use conditional formatting to highlight data (video)

AppId is over the quota
AppId is over the quota

He's no slouch!  Melvin Corpuscle shows his accounting teacher a thing or two by walking him through how he used conditional formatting to make low and high sales numbers stand out.

 Learn more about conditional formatting:

View the original article here

Conditional formatting: marking your most important mails

(Our numbers tell us that many people want, learn conditional formatting in Outlook-a clear explanation is unten--so we back this post to the top spot better move it with you all over.)

Last I wrote about quickly create rules , to help to rid your Inbox of so-called Graymail month. This week I wanted another tool to ensure that the correct mail out stick, if you read below in the message list in your Inbox. This opinion comes directly from Outlook best practices - a set of guidelines to help, be as productive as possible with Outlook.

You can customize conditional formatting, different messages based on criteria that you set appears in your Inbox message list. Conditional formatting makes unread messages by default fat. By adding your own customizations, you can mark the messages that are most important to you.

For example, if you are at work, are emails that are sent directly to you and no one else probably some of the more important news for you review. For this reason, you can these messages greater than others are displayed in your Inbox.

When you set up conditional formatting from the Inbox on the view in the current view Group tab, click settings, and then click Bedingte the formatting. Click create to Add a rule to the conditional formatting.

Let me take you an example. I sent first a style rule for mail, which only to me, so after clicking on Add, I called it "only you."

Next State clicked and then specify that I want to request messages, where I am the only person in the line at .

Then click OK, I clicked the font and should be selected, as I see the text in the message list. Since these are the most important messages, I chose a red color and a larger, bold font.

I repeated this steps to create conditional formatting for mail, where I'm on the line at , with other people, mail, that I was on the CC line, and mail that has been sent to a specific distribution list (DL) rather than directly to me. Here are the results of what they look like in my Inbox.

The different sizes and colors help you will find the most important mails and let the others later. I recommend that you try conditional formatting rules that will help you keep more organized your Inbox. For some people, with a specific color for messages from family members or messages from your boss than others can mean. We love to hear what works for you, so a comment below.

Josh Meisel

Outlook program manager

View the original article here

Learn examples of conditional formatting: symbols, formulas and values for cells

When we released Excel 2010 we published a workbook for you to downloadcontains examples of conditional formatting rules. The examples are very helpful for learning how to track trends, status, site data check and find top values.

But explain not only abstract examples of conditional formatting; rules the rules apply to real Situationen--some for business and some just for fun. Find out how you compare bikes by using a rating scale; Calculation of sales for a specific region and destination; Compare heights of the highest mountains by data bars; Identify certain numbers, data and text in a list of products, and much more.

Here are the rules in the workbook:

Mobile phone value:Numbers, data, and text in a list of products identifying certain mobile phone value (with formula): identify a dynamically changed number or text value in a list of products of top/bottom values: determine which are the top two students in the classabove/below the average: upper/lower values identify, above, below, and on average values in a recently published report by the book-tour : determine, who are the best two students in the class unique/duplicate: find duplicate rows in a list of customers icon set: quickly see revenue status and trends from one quarter to the next icon set: compare different product criteria by using a rating scale icon set: examine profit trends from month to month icon set: identify regional sales under $900,000 data bars: heights of the highest mountains of compare 3 color chart: distribution in major product categories examining total sales formula-based: shade alternate rows in a range -formula-based: compare data in a cell outside the conditionally formatted cells -formula-based: shade an entire row where several criteria must be true formula-based: shade an entire row if the row is a unique value

View the original article here

Conditional Formatting Rules Simplified

AppId is over the quota
AppId is over the quota

Thanks to Amit Velingkar for writing this blog post.  (We are re-publishing this post first published in April 2010.  Why? Because so many of you keep searching for it. This is the second in a series of oldies but goodies Excel posts.) 

Conditional formatting is a popular feature and is a great way to easily identify cells with a range that meet some criteria. However, users often want to create conditional formatting rules that go beyond comparing a cell’s value to a single value or a single cell reference - row or column comparisons are commonly requested operations. In this blog post, we will learn how to use relative references in conditional formatting rules to make such tasks easier.

For example, consider the spreadsheet below where we have sales data for different branches of an organization over the years 2005 to 2009:

Let’s say we want to highlight the cells where sales have decreased compared to the previous year. By default, conditional formatting inserts an absolute reference when we select another cell as a reference. (ExcellsFun has a great video that explains relative and absolute cell references). When absolute references are used in conditional formatting rules, every cell to which the conditional formatting rule is applied is compared to that single referenced cell.

For complex conditional formatting rules, users often get around that problem by creating lots of individual rules applied to single cells. In our example, we select cell C2 and click on “Conditional Formatting -> Highlight Cell Rules -> Less Than” on the home tab. When the Less Than dialog pops up, we select the cell B2 against which we want to compare. Notice that the value is the dialog gets replaced by an absolute reference to cell B2 ($B$2).

To perform column comparisons in this fashion, the user ends up having to create a new rule for each cell and select the neighboring cell for comparison.

This small table consists of as many as 20 individual rules - each comparing the cell’s value to its neighbor on the left. Besides being a tedious task (users often automate such tasks by using subroutines), there are other problems with this approach:

- Performance hit: Having lots of individual conditional formatting rules can degrade performance.

- Difficult to Maintain: Such rules are hard to maintain. Besides having to edit multiple rules for any changes, the rules won’t adjust if you insert a new row or column.

There is an easier way to do this by using just one Conditional Formatting Rule applied to the entire range. To do this, select the entire range of cells to which you want to apply the rule (in our example: C2:F6) and click on “Conditional Formatting -> Highlight Cell Rules -> Less Than” on the home tab. When the Less Than dialog pops up, we again select the cell B2 to get an absolute reference ($B$2). However, this time we use change the reference to be a relative reference (B2).

TIP: you can use the F4 key to cycle through different types of relative references until you get B2 without the $ signs.

And there you have it; we have just one Conditional Formatting rules where every cell’s value is compared. We get exactly, the same result as before. And there’s more, the rules automatically adjust to if new rows are added in the middle of this range. For example, let us add information about a new branch in Boston.

In conditional formatting rules, cell references are relative to the top-left most cell in the Applies to range. To simplify things, I like to pretend as if I have only selected the top-left most cell and I am writing a formula in that particular cell. In our case, the top-left cell C2 was compared against the cell to its left (B2). Hence, all the other cells in the range were compared with the cell to their left.

The diagrams below are examples of how to setup two commonly used CF rules – row and column comparisons. The cells with blue borders indicate the range to which this CF rule is applied, the bold blue border is used to illustrate the top-left most cell in the CF range and the red border shows the cell referenced by the CF formula. The black arrow indicates the direct relationship between the top-left cell and the cell reference, while the gray arrows indicate the implied relationships that are formed as a result.

You can always verify this by using the Conditional Formatting Rules Manager (select any cell within this range and clicking on the “Conditional Formatting -> Manage Rules” on the home tab) as shown below:

Also, you can toggle between the different types of relative references to compare against a particular column or a particular row. Relative references can also be used inside formulas to create complex Conditional Formatting rules.

Anytime you need to conditional formatting using complex cell comparisons, consider using relative references to make things easier. Remember, rules are relative to the top-left most cell in the Applies To range. I would love to hear more feedback on Conditional Formatting and ideas that you might have for future versions of Excel.

View the original article here

Make your data pop with conditional formatting

They have out your numbers fought and organized just right, but not directly to the people who view your worksheet. Would you give them the big picture at a quick glance. This is where the conditional formatting can come to help.The embedded workbook below shows three examples of conditional formatting. The first two examples use, easy to use predefined formats, and in the third example a rule is based on criteria that you define and apply by using a Formel.Schauen to you the examples, and learn how to get a better look at its data, by this workbook download (bonus: you do not have to log on to an account to download it!).

If the icon view full-size workbook in the Microsoft Excel Web app black bar (above), then, the workbook in a new browser window (or tab) appears, and you will see a button Excel Download above the worksheet grid.

If you Excel on your computer (Excel 2007 or later) installed, download the workbook on your computer and then open it in Excel. In the workbook, you will see, the rules for the conditional formatting to each of the data regions such as the following: 1. Select the range, click the Start on conditional formatting tab, and then click manage rules. 2. Select to check the following ranges of cells in that, their rules to the conditional formatting: for the city temperatures, you select annual total. choose Notendurchschnitte B7, for the students: B20.You choose for the East Regiion Actuual cost B24: E24.Select region B26 actual costs for the North: E26.Select region B28 actual costs for the South: E28.Select region B30 actual costs for the West: E30. 3. Conditional formatting rules Manager dialog box, which appears, click Edit Rule.Learn more about formatting with these articles Conditonal and videos on Office.com: and be sure to see that some great Excel blog posts product team looking after the keyword "conditional".

--Gary Willoughby

View the original article here

Power tip: supercharge conditional formatting with a little code

Over the past the product team has few versions of access constantly more conditional formatting functionality for forms and reports added. In access 2010, you can add conditional formatting for each object, for example, up to 50 rules without having to write any Visual Basic for applications (VBA) code. However, if you work in an earlier version, or if you prefer only VBA for formatting tasks on your forms and reports use, Juan Soto of AccessExperts.net has blogged some VBA-based conditional formatting techniques , which he has recently introduced. It provides a code example, you can copy and try for yourself, so take a look!

Thanks Juan!

--Chris downs

You have a power tip you would like to share? Send us at accpower@microsoft.com.

View the original article here