How to Format a Spilled Array in Excel

Use formula conditional formatting to handle overflow arrays in Excel

Direct formatting of overflow arrays in Excel can cause problems, especially when the data shape or size changes. Formula-based conditional formatting rules allow automatic formatting when data parameters change. Adding a dollar sign ($) before a column reference applies a rule to all rows in the data.

In Excel, you can apply direct formatting to the values ​​or background of a cell to make the spreadsheet easier to read. However, when an Excel formula returns a set of values ​​(called overflow arrays), applying direct formatting will cause problems if the size or shape of the data changes.

Suppose you have this spreadsheet with spillover results from the PIVOTBY formula that shows the number of viewers per sport in each region over the past four years.

Since the PIVOTBY function does not contain parameters that allow you to format the results, it is difficult to distinguish between title rows, data rows, subtotal rows, and total rows.

At this point, you might want to apply direct formatting through the Fonts group on the Start tab of the ribbon to intuitively distinguish different types of rows in your data.

However, if you later modify the parameters in the PIVOTBY formula, or the results are enlarged or reduced due to changes in the original data, the direct formatting you applied will not be adjusted accordingly. This is because direct formatting in Excel is associated with cells, not with the data they contain. See the screenshot below where the data has been reduced, but the direct formatting is still applied to the same row, which can cause confusion.

Instead, you should use conditional formatting, which allows you to format them based on the values ​​of the cells and rows.

Select all the data (and some extra rows at the bottom to allow vertical growth), and on the Start tab of the ribbon, click Conditional Format > Manage Rules.

Next, in the Conditional Format Rule Manager dialog box, click New Rule.

For every rule you will create to format overflow arrays, you need to use formulas. Therefore, in the Select Rule Type area of ​​the New Format Rule dialog box, select the last option Use formula to determine which cell to format.

The first rule you want to create is related to the title line. Specifically, you want these cells to be on a gray background.

To do this, take a moment to determine what makes the title row different from the other rows in the table. In this example, the title row is the only row in column G that does not contain numbers. So, in the Formula field, type:

 <code>=ISTEXT($G1)</code>

Since the ISTEXT function treats empty cells and cells containing text as text values, the conditional formatting rules will consider cells G1 to G3 to contain text, while the rest of the cells in the G column contain numeric values.

Importantly, adding a dollar sign ($) before a column reference repairs the conditional formatting to this column while allowing Excel to apply rules to the rest of the rows.

Finally, because you initially selected the data from columns A to G, the conditional format will be applied to the entire row that satisfies the condition.

Now, click Format to select the format of the title row. In this case, you want them to be gray. Then, click OK in the Format Cell and Edit Format Rules dialog box.

When you click Apply in the Conditional Format Rule Manager dialog box, you will see that only the G columns contain empty cells or text (in other words, the title row) are filled with gray.

Next, you want to format the subtotal rows so they are filled with light green.

Take a closer look at the data again and see which conditions you can use to apply the format to these rows only. In this example, the subtotal row contains text in column A, but nothing in column B. Additionally, since the total row also meets these conditions, you need to exclude any cells in column A that contain the word "total".

With the Conditional Format Rule Manager dialog box still open, click New Rule and select the option that allows you to format cells using formulas. This time, in the formula field, type:

 <code>=AND($A1"",$B1="",$A1"Grand Total")</code>

in:

• The AND function allows you to specify multiple conditions in parentheses.
• $A1" ""Tell Excel to find cells that do not contain () empty cells ("") in column A.
• $B1="" Tell Excel to find cells containing (=) empty cells ("") in column B, and
• $A1"Grand Total" tells Excel to exclude () any cells in column A that contain the text "Grand Total".

As with the previous rules, remember to insert the $ symbol before the column reference to allow Excel to apply the same rule to all selected rows.

Now, click Format to select a light green fill color, and after closing the Format Cells and Edit Format Rules dialog boxes, click Apply to see that the subtotal rows are filled with light green.

Finally, you want the cells in the total row to be filled with darker green.

Since the total row is the only row in column A that contains the word "total", this is the condition you can use for conditional formatting. In the Conditional Format Rule Manager dialog box, click New Rule, and then select the last option in the Select Rule Type list. Now, in the Formula field, type:

 <code>=$A1="Grand Total"</code>

Next, click Format and select the bold green fill color you want to apply to the cell that matches this condition. Now when you close the Format Cells and Edit Format Rules dialogs and click Apply in the Conditional Format Rule Manager dialog, you will see that the total rows are in this format.

Now that you have applied all the rules, click Close in the Conditional Format Rule Manager dialog box. Then, adjust some data in the original table and see if the overflow results and their formats are updated accordingly.

In this example, even if I delete 12 rows from the original data table, the overflowing PIVOTBY results are still formatted correctly, the title behavior is gray, the subtotal behavior is light green, and the total behavior is bold green.

If you need to change the rules you have created and applied, simply select any cell in the data and click Conditional Format > Manage Rules to restart Conditional Format Rules Manager. Then, double-click the rule to change its conditions.

The above is the detailed content of How to Format a Spilled Array in Excel. For more information, please follow other related articles on the PHP Chinese website!