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

Format and customize Excel 2013 charts quickly with the new Formatting Task pane

AppId is over the quota
AppId is over the quota

The new Excel offers a rich set of charting capabilities that make creating and customizing charts simpler and more intuitive. One part of the fluid new experience is the Formatting Task pane.  

Until now, precise adjustments to chart elements were made in the Format dialog box. The box sometimes obscured a portion of the chart, changes entered in the box were not visible until you closed it, and you had to select the exact element on the chart in order to see the options that were the best fit for the job.

In the new Excel, the Format dialog box is replaced by the Formatting Task pane. The pane aligns neatly with the right or left side of the screen, so it's less likely to obscure the chart, and changes happen in real time, so you can immediately see how your choices affect the chart. The Formatting Task pane also offers an element selector so you can jump quickly between different elements without having to select one to modify.

The new Formatting Task pane is the single source for formatting--all of the different styling options are consolidated in one place. With this single task pane, you can modify not only charts, but also shapes and text in Excel.

The fastest way to open the Formatting Task pane is to double-click a chart element. You can also use the keyboard shortcut CTRL+1 while a chart element is selected. There are two other ways to open the task pane:

The first way: On a chart, select an element. On the Ribbon, select the Chart Tools Format tab, then click Format Selection.

The second way: On a chart, select an element. Right-click, then select Format where is the axis, series, legend, title, or area that was selected.

Once open, the Formatting Task pane remains available until you close it. Since it always stays on the right or left side of the screen, the pane remains unobtrusive as you concentrate on other tasks. The options in the Formatting Task pane will change based on which element is currently selected. For example, if you select a legend, the Formatting Task pane offers layout, fill, and effect options uniquely tailored to the legend element. With this more intuitive experience, you can feel free to leave the Formatting Task pane open while you format different elements of your chart.

The element selector in the Formatting Task pane allows you to jump quickly between different elements without selecting them in the chart itself. Since some chart elements are small and perhaps difficult to select, the element selector is a great alternative to the "hunt and peck" approach.

The element selector is in the top left of the Formatting Task pane. The selected element is displayed, along with a down arrow. Clicking the down arrow opens a dropdown menu that shows all elements of the selected chart. When you choose an element from this menu, the Formatting Task pane displays options uniquely tailored to this element, and it also selects this element in the chart.

You can also find the element selector on the Ribbon on the Chart Tools Format tab.

Chart options in the Formatting Task pane are in two categories: Size & Properties and element-specific.

Size & Properties deals with sizing, alignment, and miscellaneous properties such as alt text and locks.

The Chart Area element is unique in that its Size & Properties options affect the chart as a whole. Size, scale, and aspect ratio can be adjusted only on the Chart Area element, from which all other chart elements derive their inherent size and scale. The Chart Area element also controls the alt text of the chart, whether the chart moves and/or sizes with its underlying cells, and whether the chart is printable and locked.

Other chart elements can adjust their alignment from the Size & Properties options, which dictate vertical alignment, text direction and angle, and margins.

There are unique, element-specific options for axis, legends, and series:

Axis Options. Axis options allows you to adjust the axis bounds and units, the placement and interval of its labels, and other options including tick marks and number format.

Legend Options. Legend options allows you to specify the position of the legend, and whether or not it overlaps the chart.

Series Options. Series options allows you to specify whether the series should be plotted on the primary or secondary axis, the spacing and width of the columns on a bar or column chart, and the angle and explosion (separation) of the slices of a pie chart.

In addition to chart options, the Formatting Task pane allows you to fine-tune the visuals of each chart element by offering a multitude of line, fill, and effect styles. These styling options are available for shapes as well.

Line options allow you to adjust the styling of the lines and borders of each chart element. There are two line types: solid and gradient. A solid line uses one color throughout, while a gradient line changes smoothly from one color to another along its path. Regardless of the line type you choose, you can adjust a line's thickness, transparency, dash type, and endpoint settings.

Fill options allow you to choose how the inner portion of each chart element is filled. There are four fill types: solid, gradient, pattern, and picture. A solid fill uses only one color, while a gradient fill smoothly blends multiple colors along its filled region. A pattern fill tiles the inner fill region with preset imprints such as crosshatches and tiles, and a picture fill uses preset textures or a picture that you specify.

Please note that certain chart elements, such as the series of a Line Chart, do not have an inner fill region to color or tile. Fill options are disabled for these chart elements.

Effect options offer a powerful set of effects that add visual impact to your charts. Effects include shadows, glows, soft edges, and 3-D formatting.

Shadow. This effect adds a shadow either outside or inside the chart element.

3-D Formatting. This effect adds a bevel to the chart element to give it a 3-dimensional relief.

In addition to modifying the look and feel of charts and shapes, you can also style text inside these objects.

Text options are in the top right of the Formatting Task pane. Clicking Text Options toggles the task pane into text styling mode. You can toggle back to the former chart and shape styling mode at any time by clicking the element selector's title.

In text styling mode, you can modify the look and feel of your text using the same line, fill, and effect options that are available for charts and shapes.

The new Formatting Task pane is just one of many ways to customize and refine the look and feel of your charts. The on-object buttons featuring the new Chart Elements and Chart Styles provide quick access to the most common chart customizations, but when you need detail-level control, now you can get it with the Formatting Task pane. Please try it out, and give us your thoughts and feedback on the experience--we'd love to hear what you think!

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

Tip: How to cut and paste without messing up formatting

AppId is over the quota
AppId is over the quota

Why does formatting sometimes get messed up when you cut and paste text? And what is that thing that appears at the end of the last sentence every time you paste--like a fly returning to honey.

That thing--the Paste Options button--is your friend, a worker bee and not a fly whose only job is to follow your formatting instructions. Learning how it works keeps you from wasting time manually formatting pasted text.

Click the down-arrow on the Paste Options button and you'll see a menu with icons that lets you format copied text in different ways. The options you'll see depend on where you're cutting and pasting from and to, e.g., from within or between documents.  Roll your mouse over the icons and you can see how your pasted text will look before you click.

These are the four most common options:

Keep Source Formatting: Keeps the formatting of the text you copied Use Destination Styles:  Matches the formatting where you pasted your text Kept Text Only:  Discards both the text formatting AND the non-text elements you copied, such as pictures or table, and then matches the formatting where you pasted the text  Merge Formatting: Keeps the formatting of the text you copied without changing the formatting of the destination document, e.g., if you cut and paste a sentence from another document that had a different font type or size

Word gives you other options for copying and pasting things such as bulleted or numbered lists, or hyperlinks.  Plus, it lets you define how you want cutting and pasting to work most of the time (click Set Default Paste under the icons)--including getting rid of the Paste Options button if it still seems like a pesky fly.

View the original article here

Formatting vs. styles in Word (video)

Translate Request has too much data Parameter name: request Translate Request has too much data Parameter name: request a.stbar.chicklet img {border:0;height:16px;width:16px;margin-right:3px;vertical-align:middle;}a.stbar.chicklet {height:16px;line-height:16px;}Word Blog - Formatting vs. Styles in Word (video) Join Sign in Microsoft Office Office Blogs Word Blog Home All Blogs Products Word Excel PowerPoint Outlook Access OneNote Publisher Perspectives Office Casual Office Comics Crabby Office Lady Office in Education Office Exec Subscribe to our RSS feed
Send to friendRSS for post comments2007 Office system21st Century DocumentsBuilding Blocksco-authoringcollaborationcompatibilityCompatibility Packcontent controlscopy and pastecreativitydesigndeveloperdocument reviewimageslegal scenarionew default file formatsnewspersonalscenariostylesvideoWordWord 2010Word Tech DetailsWord tips and tricks

 

Unfiltered HTMLFormatting vs. Styles in Word (video) by Ron Owens on June 21

Styles may be the most important feature in Word. Learn to use Styles and not only will you save yourself a lot of time from having to click bold buttons, italicize, change the font, change it back again, adjust the paragraph spacing, and ... well, you get the idea. But it also allows you to access other features in Word that will make your work so much easier.

If you're not using Styles in Word, I hope to win you over in this video by demonstrating the advantage of the feature versus manually formatting text (aka "directly formatting text").

And here are a couple of the other benefits to using Styles:

You can automatically create and update a table of contents.You can use the new Navigation Pane -- my favorite new feature in Word 2010!

Jessica Reading recently demonstrated these two features at the Mom 2.0 conference.

Styles are helpful and a real time saver. (And they're kind of fun!)

-- Ron Owens

Tags: Word, styles ShareThis

 

You must sign in to leave a comment. Sign in or join now.

other Microsoft blogs Official Microsoft Blog Microsoft On The Issues The Windows Blog Bing Community Zune Insider Major Nelson other product blogs Office For Mac Office Web Apps InfoPath Project Visio other Office blogs Office IT Pro Office Updates Office Labs Legal | Trademarks | Privacy Statement © 2011 Microsoft Corporation. All rights reserved.

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

Let your fingers do the formatting, and more (video)

Before he after a recent trip, I sent a Tweet from our internal social network, as follows: "have you a favorite Office shortcut key combo?" "I'm a fan of SHIFT + F3." The answers came out of the woodwork and reminded me, what are the keyboard shortcuts in Word for a writer a great efficiency. If you flow the ideas want to keep, you want to take your hands off the keyboard. Here three are my favorite links when I'm writing in Word.

All the word 2010 shortcut keys are here. Word 2011 (Office: Mac) keyboard shortcuts are here.

Follow the Office blog at NOLA.

See the MOM 2.0 in New Orleans Roundup.

--Jessica reading

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