Apply Conditional Formatting

Overview

Qvinci's Report Editor allows for conditional formatting rules that are very similar to those in Excel. If you combine Qvinci's automated data collection with Excel's formatting rules, you can have cells that are dynamically formatted differently, based on their value or other conditions set forth in the rules. 

NOTE: Be sure to check every tab on the Format pane when using Conditional Formats. Conditional formatting affects all aspects of cell formatting, such as number format, font size/color, background fill, and cell borders. Additionally, a conditional format, when applied, will override those types of cell settings. For example, if you have a cell with borders, but a conditional format that only sets the font color is applied to that cell, it will remove the border as well as change the font color. 

To Begin, Open Up a Report Template

Step 1. From the main menu on the Qvinci website, go to Reports & Libraries > Libraries.

Step 2. Go to the report you wish to edit. This can be any report from the Pinned, Mine, Shared With Me, or Qvinci Gallery tabs.

Step 3. Press the blue Edit  button from the right-hand columns on the screen.

Add Conditional Formatting Rule Based on Single Condition

Add Conditional Formatting Rule Based on Formula

View, Manage, & Delete Existing Rules

Add Conditional Formatting Based on Single Condition

You can apply rules based on a condition so that a financial cell displays in red if it is negative or green if it is positive, or vice versa. This can be helpful when you are comparing performance between two different periods, like a month-over-month or year-over-year report.

In the example year-over-year report below, we'll apply formats based on whether Total Income has increased (text will be green) or decreased (text will be red). Since we want two different formats, we'll need to create two rules.

Step 1. Once inside the template, select the cell(s) for which the first formatting rule should be applied.

For our example, we'll select the cell that will contain the difference in Total Income.

Step 2. From the HOME menu, click on the Condition Format  icon, then New Rule.

Step 3. In the Select a Rule Type section, choose Format Only Cells That Contain.

Step 4. In the Format Only Cells With section, choose the first condition that must be met for the first formatting rule.

• • Since we want Income to increase, we'll choose Cell Value Greater Than 0 for our arguments.

Step 5. In the Preview section, click the Format button and set the formatting to be applied when the above rule is true for the cell, then click OK.

Step 6. Select a font color of green for a positive change in Income.

Step 7. Ensure your rule settings are correct, then click OK.

Step 8. Repeat the steps above to add another rule, except:

• • Format Only Cells With will be Cell Value Less Than 0.
  • After clicking on Format in the Preview section, select a font color of red.

Step 9. From the left menu, click on Save , then Preview to see your changes



Note: The background fills for Total Income was overwritten because we did not set a background fill color when we clicked on Format above.

Add Conditional Formatting Rule Based on Formulas

Formatting rules can also be built on formulas. This can be helpful for any reports that are comparing multiple date ranges, such as month-over-month or year-over-year reports. The example report has a column indicating whether the margin percentage has improved. We'll format the cell text to be green for sections that have improved and red for those that have not.

Step 1. Select the cell(s) for which the first formatting rule should be applied.

In our example, that will be the cells indicating if there has been an improvement.

Step 2. From the HOME menu, click on the Condition Format icon, then New Rule.

• • Since we have two conditional formats, we'll need to set two rules, one for green text and one for red.

Step 3. In the Select a Rule Type section, choose Use a formula to determine which cells to format.

Step 4. In the Format values where this formula is true section, type in the formula that must be true for the first rule.

• • Our first rule will highlight the font in green if the number has improved (ie, the percentage of income has decreased)
  • Therefore, our formula will be "C12<E12"

Step 5. In the Preview section, click the Format button and set the formatting to be applied when the above rule is true for the cell, then click OK.

• • We'll select a font color of green for an improvement

Step 6. Ensure your rule settings are correct, then click OK.

Step 7. Repeat the steps above to add another rule, except:

• • Format only cells with will be "C12>E12"
  • After clicking on Format in the Preview section, select a font color of red

Step 8. From the left menu, click on Save , then Preview to see your changes.

View, Manage, & Delete Existing Rules

Step 1. From the HOME menu, click on the Condition Format icon, then Manage Rules.

Step 2. The Conditional Formatting Rules Manager box will appear and display all current rules for the workbook.

Step 3. To delete a rule, click on the rule and click the Delete Rule button.

Step 4. To edit which cell or range of cells for which rule applies, click on the rule and change the settings in the Applies to column.

Step 5. To edit the rule settings or the formatting for a rule, select the rule and click the Edit Rule button.

Step 6. Once you've made all desired changes, click the OK button.

Step 7. From the left menu, click on Save , then Preview to see your changes.

Have Questions?
We're More Than Happy to Help

Schedule a call with Customer Success below, email us at support@qvinci.com or call us at 1-512-637-7337 Ext. 1 Available M-F, 7:30am-6:30pm CT and it is always FREE!

Schedule an Appointment with Customer Success