Tag Archives: spreadsheet

Conditional Formatting for Clarity and Visual Impact in Excel

Excel, Office, , , , , , ,

Light Up the Cells with Conditional Formatting!

Conditional Formatting is a great tool for instant, visual results based on values, text or formulas in one or more cells. This is accomplished by creating rules for each desired result. It can be as simple as formatting all cells based on their values (the default), which could be applying a color in cell(s) that are above or below a certain value, contain specific text or fall within certain dates as well answer more complex questions.

Conditional Formatting is located on the Home tab | Styles group:

You can also choose to have the results displayed as Data Bars, Color Scales or Icon Sets (arrows or star ratings) instead of one solid color.

More than one condition can be applied to the same range of cells. This example below is returning two different results with two different cell colors based on two different questions (AND requires both conditions to be true but OR allows for either condition to be true to apply the rule).

The formulas in the last two columns have returned Yes or No based on True or False results. Range Names have been created from the header row text to make for easy identification of the cell references in the formulas. Then Conditional Formatting rules have been applied to designate the color(s) based on that answer.

=IF(AND(Years_Under_Contract<2,Number_of_Books_in_Print>4)=TRUE,”Yes”,”No”)

=IF(OR(Years_Under_Contract>5,Number_of_Books_in_Print>=10)=TRUE,”Yes”,”No”)

It is now very clear to see how many authors met none, one or both of the conditions.

Formatting Rules

There are four formatting rules applied here:

Rule Types

This is the Rule Type applied to the first rule for formatting only cells that contain “No” in the last two columns with Pink fill:


Each of the other three rules have their own Rule Description but all are based on Format only cells that contain (the second rule).

Note: You can include a formula directly in the condition by choosing the Rule Type: Use a formula to determine which cells to format and typing the formula in the Edit the Rule Description area.

Have you been using Conditional Formatting and, if so, which rule do you use the most? If not, I hope this post will encourage you to use this powerful feature. Thanks for reading!

Reasons to Format Excel Spreadsheets with Cell Styles

Excel, Office, , , , , , ,

Use Cell Styles to Format in Excel to save time and frustration and look smart doing it!

Here’s why you want to use Cell Styles to format Excel data:

  • Apply professional formatting to a worksheet in a flash.
  • Consistency across worksheets.
  • Built-in styles are labelled according to purpose for easy use.
  • Styles are customizable so you can edit existing styles or create your own to match your needs.
  • One click formatting.

Have you ever wondered how to format a spreadsheet in a hurry? Here’s a simple example of a worksheet with boring, unformatted data:

Name Region Q1 Q2 Q3 Q4 Total
Smith South 21223 17855 24855 21377 85310
Jones Southwest 23456 29550 24294 25335 102635
Wing North 19954 22600 19448 26450 88452
Baker South 17564 25439 32944 24000 99947
Folsom North 28543 28540 24400 28550 110033
Range Northwest 19534 30558 21844 19605 91541
McKnight West 20585 29667 27595 13605 91452
Crump South 24957 31322 15330 27550 99159
Rogers North 30332 27407 26440 24007 108186
Morris East 25395 27700 16500 27500 97095
Rollins South 35822 31854 24384 31005 123065
Jefferson Southwest 20949 23100 21774 27550 93373

But now you get a call to send it to a co-worker or the boss or it has to be ready for a presentation. Using individual formatting tools, it can take longer to format than it took to create it!

Format the Column headers and Data Content

Want to look smart really fast? Here’s the fix…First, I want to format the header row:

Select the header row in the worksheet

On the Home tab, in the Styles group, click Cell Styles icon:

If you have a larger screen and/or higher resolution, you may see several cell styles already displayed. See all styles by clicking the More arrow button at the bottom of the scroll bar for the group.

This box below will display with lots of options that are labelled for specific uses but you can use them for any purpose you choose. Roll your mouse over the sections and because they are live preview, you can see the results before you actually choose the option. I want Heading 1 for my header row, so I point and click to apply it.

I’ve selected Heading 1 under the Titles and Headings section:

Next select the body of the spreadsheet:

Click Cell Styles again, and choose whatever style you think would look best for your data. (If you are using Input Style, don’t include the Total column as you’ll probably want to use the Calculate Style for that to indicate formulas).

Total Row Style

If you have a total row, quickly make your totals stand out with the Total Style:

Data and Model Section

A way to alert users about which data they can edit is to use the Input and Calculation styles under the Data and Model section. It can’t stop them from overwriting formulas, etc., but it does signify the ranges that they should not edit. If you don’t like the default colors, you can change them. (See below)

Remove Styles

So, you’ve been playing and got carried away and now your data looks like a kindergarten project! Don’t worry if you goof and want to remove a style. Just select that range of cells; go back to the Cell Styles icon to the Style group and click on Normal to reset the cells to your default font style and size. (Located in the upper left of the first section, Good, Bad
and
Neutral).

Customize/Create Styles for Text

You can modify any style or leave the original styles and create a duplicate for your new style. I would recommend the latter. Here’s how to make changes in font, color, etc. for any style:

Example here is for Heading 1:

  • Click the Cell Styles icon (Home tab, Styles group).
  • Right click over the style you want to change.
  • Choose Duplicate (or Modify if you want to edit the original style) to display the Style box:

  • Change the name.
  • Click the Format… button and make desired changes. (Ensure Font tab is selected).
  • Click OK, OK.

Changes to Numerical Data Styles

If you want to change the Input or Calculation or any other numerical styles to add number formatting as well, the process is the same except you:

  • Right click over the style name.
  • Choose Duplicate.
  • Type in a new style name.
  • Click on the Number tab in the Format Cells dialog box.
  • Click on Currency in the Category pane.
  • Choose if you want the style to always display 2 decimal places or change to 0 if you want that for the style.
  • Make any other desired changes to Font, Alignment, etc., using the tabs at top.
  • Click OK, OK.

Apply Customized Styles

This can be a little confusing after modifying a style as it looks like nothing happened. The new style is not applied to your selected data. because you have created the style but not yet applied it.

When you customize or create new styles, Excel adds a Custom section to the top of the Cell Styles list. Just select desired data, click Cell Styles and click on your customized style. I added Currency formatting to the Input Style but left the background cell color; applied Currency formatting for the Calculate Style but changed to red color so it would be obvious not to enter data in those cells:

I selected the quarterly data to apply my customized Input Style, and then selected the Total column to apply my new Calculation Style. If there is also a Totals row, I would select them both first, and then go to Cell Styles and click my customized style:

Create your own styles and save a bundle of time while looking darn professional as well!

Let me know how it works for you…