Applying a conditional format

From mtab wikisupport
Jump to: navigation, search

As an analyst, we often begin a new analysis by creating a crosstab comprising several row and column questions. The end result is too often a large grid of numbers that can take time to carefully analyze for insight. mTAB offers a powerful spreadsheet view tool called Conditional Formatting that considerably simplifies the analysis of a large spreadsheet by automatically highlighting cell values of interest.

There are two types of analysis available within mTAB's conditional formatting functionality, the Minimum/Maximum view, which is the default selection and the more powerful Threshold view. The Minimum/Maximum view colors either the minimum or maximum or both the minimum and maximum cell value within any spreadsheet view row or column.

The more powerful Threshold view can color cell values relative to a total's value, either in absolute terms (e.g. color cells +/- 10 units relative to the selected total's value) or in percentage terms (e.g. color cells +/- 10%) of a selected total's value.

The following examples illustrate the use of the Minimum/Maximum and Threshold Conditional Formatting analysis tool. To increase the understanding and readability of the screenshots, the examples below utilize a relatively small crosstab. As you view these examples, keep in mind that the Conditional Formatting tools are of greater benefit when applied to larger crosstabs.

The process of conditionally formatting cells starts from within the mTAB spreadsheet view. As with many mTAB spreadsheet view operations, you must first select the rows and columns that you wish to apply conditional formatting to. In addition, if your selection process includes multiple question formats (example: row questions include a question formatted as a Median and another question formatted as % Column Sample) then you must select the type of question format that you want to use for the analysis. Lastly, you need to decide if you want the cell values to be calculated and highlighted within (across) each row or within (down) each column.

Minimum/Maximum view

In the example below, we have selected the entire spreadsheet to illustrate how we will apply conditional formatting when a crosstab contains multiple question formats. Note that you can alternatively select an individual row question (or questions) to apply conditional formatting; the only requirement is that at least one question is selected. Under the Appearance ribbon, select Apply Cond Formatting.

Cond-format overview 1apply-cond-format.jpg

After selecting these menu items, the Conditional Formatting dialog pops up (shown below). Note the Row and Column question format data type selection list boxes at the top of the Minimum/Maximum panel; the list boxes operate in the same manner as the similar list boxes provided with the mTAB TopN report and graphic dialogs. The user is required to identify the question format that will be utilized for the analysis.

Conditional-formatting overview min-max-apply.jpg

The tabbed selection beneath the Row and Column question format list boxes facilitates the display of the Minimum/Maximum analysis or alternatively, the Threshold analysis. Note that the default tab is the more simplistic Minimum/Maximum analysis, which we will now explore.

In the previous spreadsheet view screenshot, our tab contained four row questions, with two questions formatted as medians and two as % Column Sample percentages. We can apply conditional formatting to either one of the two question formats in one operation, effectively formatting two of the four selected questions in one step. We will start with the Median row format as it is first in our Row Data Type list box.

As mentioned previously, after selecting the question type format, we must now decide if we want to apply minimum/maximum highlighting across the rows or down the column. With the Median format selected, there is really one choice - across the rows, as the Median format consists of a single row per question, so we select the Rows radio button option. Next we need to decide if we want to highlight the minimum value only, the maximum value only or both minimum and maximum median values concurrently within each Median row. We should also determine if we would like to change the default cell highlighting colors of red (representing the minimum value) and green (representing the maximum value).

Applying the minimum and maximum format

The Conditional Format dialog shows the Median row question format selection, selection of the calculation within rows (Rows radio button), red cell highlighting identifying the minimum values and green cell highlighting identifying the maximum values.

We select the minimum and maximum cell highlight criteria by clicking on the minimum and then maximum Apply buttons as called out within Conditional Formatting dialog show previously. Note that you can use the Change buttons to change the cell highlighting to an alternative background color and font if so desired prior to Applying the cell formatting criteria. Once the formatting criteria are applied, the underlying spreadsheet view is immediately highlighted as shown below. Note that both row questions formatted as Medians now display the minimum and maximum highlighted cell values.

Cond-format overview 2max-min-for-medians.jpg

Applying conditional format to other questions formats within the same tab

As mentioned previously, conditional formatting can only be applied to a single question format in a single operation. However, by repeating the conditional formatting steps for additional question format types, any number of questions within a spreadsheet view can be concurrently analyzed using conditional formatting.

By repeating our selection of the entire spreadsheet, we now select the row question format % Column Sample from the Conditional Format dialog in keeping with the remaining two row question's formatting. We now must decide if we want to display the minimum and maximum values across the rows or down the columns. For this illustration, we will choose to analyze minimum and maximum values within each brand column, so we select the Column radio button.

Selecting the Apply buttons for the minimum and maximum values as in the previous step yields our completed minimum/maximum analysis as shown in the image below. Note that the analysis retains the minimum and maximum median values across (i.e. selecting across rows) brands we selected in the previous step, and now adds the minimum and maximum Age Category and Education Definitions values (i.e. % Column Sample formatted row questions) within each brand.

Cond-format overview 3max-min-columns.jpg
Conditional-formatting overview threshold-apply.jpg

Taking it to the next level - the Threshold view

The threshold conditional formatting view borrows many of the Conditional Formatting dialog selections from the minimum/maximum view.

Selecting a row or column question format for applying conditional formatting is the same as in the minimum/maximum view. Determining the "across rows" vs. "down columns" orientation is identical. However, in the threshold view, cell values can be highlighted if they are under (lower), within a range, or over (upper) any threshold value that you designate. All three criteria - lower, range and upper, can be displayed concurrently, in a manner similar to the concurrently display of the minimum and maximum cell criteria. In addition, you can associated a unique threshold value with each of the three (lower, upper, range) criteria.

To illustrate, we will use our previous example tab. In the threshold panel the dialog illustrates the selection criteria for displaying % Column Sample row formatted cells as red when their values are less than 10%, green when their values are greater than 90% and yellow when their values are between 10% and 90% (inclusively - note the inclusive checkbox selection). As each individual criteria is applied, the cumulative criteria is displayed at the bottom of the Conditional Formatting dialog within the Show Applied Formatting: selection bin.

The spreadsheet view below illustrates the highlighting achieved from our Conditional Formatting dialog as shown in the previous illustration.

Cond-format overview 4threshold.jpg
Conditional-formatting overview threshold-benchmark-apply.jpg

Even more powerful - highlighting based upon a relative cell value

In addition to setting absolute threshold values such as the < 10% or > 90% values selected in our previous example, mTAB supports highlighting cells based upon the cell's relative value to a total value within a row or column.

This feature permits the identification of cells that are, for example, 10 percentage points above and 10 percentage points below the sample total; which may identify outlier values of interest. Note that you need to include totals within your selected questions in order to apply the relative threshold, and we have now added the Formatted Sample Total to our example tab's column question to accommodate this additional analysis.

In addition, by applying the percentage check box within the Benchmark Conditional Formatting Within: dialog area as called out in the screenshot above, the selected criteria is calculated in terms of a relative percentage to the selected total. By using the relative percentage checkbox option, we can identify outlier values that are, for example, less than 50% of the total value and values that are greater than 150% (i.e. total value + 50%) of the total value. The percentage check box provides greater flexibility for identifying outliers than would be possible with a fixed relative value such as 10 percentage points above or 10 percentage points below the comparison total - for example, in cases in which the total percentage is less than 10%.

The spreadsheet view below reflects the criteria settings applied from the dialog settings as shown above.

Cond-format overview threshold-format-total.jpg

In this illustration we elected to highlight cells with a background color of red if the cell values were less than 50% of the sample total value for the same row, and similarly, highlight cells with a background color of green if the cell values were 50% greater than (i.e 150% of) the sample total value for each given row.

In conclusion, mTAB's Conditional Formatting provides a powerful means to quickly identify cell values of potential interest, enhancing mTAB's ability to quickly find insight from a large quantity of survey data results.

Note: the additional spreadsheet view menu items Clear Conditional Formatting and Clear All Conditional Formatting can be used to remove the conditional formatting from the spreadsheet view. Clear Conditional Formatting also appears at the bottom of the Conditional Formatting dialog.

Also note that conditional formatting is automatically saved within mTAB's saved tabs and is automatically recalculated when the spreadsheet view is modified by deleting rows or columns using mTAB's zero suppression or threshold spreadsheet view operations. However, changing column questions and/or question responses by subsetting questions from within mTAB's question selection view will automatically remove all conditional formatting from the spreadsheet view.