Time-Saving tips for Excel Dynamic Formatting

May 21, 2014 Excel  No comments

Excel formatting features like Conditional Formatting and Whole Line Formatting save you time and make your data look good by formatting certain data in a certain way. With Excel dynamic formatting features like this, formatting options will be applied to your data as you enter and change it. Here are some tips for using dynamic formats in Excel.

Conditional Formatting

Excel’s conditional formatting allows you to apply different formatting options like background colors, borders, and font formatting to cells that meet certain conditions. To set a formatting condition, first select the cell(s) that you want to format. Next, on the “Home” tab, click on the “Conditional Formatting” button in the “Style” section.

Next, choose “New Rule.” From there you can select from the following rule types:

  • Format all cells based on their values: With this rule you can choose a 2 or 3 color scale to show which category that value fits closest to (lowest value, percentage, formula, etc). You can also choose from a data bar or icon sets that you can specify the rules of.
  • Format only cells that contain: With this rule, you can pick a certain format for cells that have a certain value between, greater than, equal to, ect to a certain value.
  • Format only top or bottom ranked values: With this rule, you can format values that are in the top or bottom percentage of the range.
  • Format only values that are above or below average: With this rule, you can format the cells that are above or below the average of the range.
  • Format only unique or duplicate values: With this rule, you can format the cells that contain a duplicate or unique value.
  • Use formula to determine which cells to format: With this rule, you can format cells that contain a certain formula.

After creating the rule, it will apply immediately. Any cell that fits the rule will take on the specified format. If none of the cells changed, that means that your rule didn’t apply to any of them. But, as soon as you enter a value that meets the rule, it will take on the specified format.

Whole Line Formatting

This feature allows you to format an entire line the same way. To use it, click on the “Conditional Formatting” button on the “Home” tab. Select “New Rule” from the drop-down menu. In the “New Formatting Rule” dialog box, select the option “Use a formulas to determine which cells to format.” In the “Edit the Rule Description” box, click on the first cell of the line. Remove the absolute cell reference symbols ($) that appear, then choose the formatting that you want to be applied. Click “Ok” and your data will be formatted in the way you wanted. After that, each time you add more data, it will be formatted in the same way if it’s on the same line.

We here at DTA hope you now feel confident in your ability to use Excel dynamic formatting. It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work. Subscribe to our blog to receive weekly Desktop tips. If you’re looking for more in-depth training check out our upcoming instructor-led, live online trainings.

Liked these Excel Database tips? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Desktop Training Academy

email

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>