How to Highlight Specific Rows and Cells in Power BI

Highlighting specific rows in Power BI is a necessity for reporting. Because Power BI lacks a built-in change color button for highlighting columns, Power developers have to use conditional formatting. We’ll explain several different approaches that can help whether you’re trying to highlight a cell based on a value or an entire row.

Using Conditional Formatting to Highlight Cells and Rows

One of the biggest differences between Power BI and other tools is that everything is rules and logic based, formatting is no exception. This is done because many Power BI dashboard are setup to automatically refresh data on a schedule or connect directly to a live data source. Because of the dynamic nature of changing data, you would seldom want to hard code a specific column, row or cell in a Matrix or other visual.

While it can be frustrating that Power BI does not have a native function to select a cell or row to change its color, we can do so by using Conditional Formatting rules where the condition is always true.

Highlighting Individual Rows

The first example we are going to look at is a Matrix visual that is configured to show values as rows. While not a necessity, we generally recommend placing values as rows in a Matrix when building out financial reports. It makes it easier to move rows around, change the order, and create subtotal calculations.

It also makes it easier to apply conditional formatting to highlight full rows.

Example of a Power BI Matrix prior to applying conditional formatting to highlight a row

To get started, follow these steps:

Step 1.) Set Conditional Formatting Background Color

Click the down arrow next to the name of the measure or field name that you want to highlight and navigate to: Conditional Formatting > Background Color.

Launch the conditional formatting editor for a background color on a matrix visual

In the next screen, select Rules, from the Format Style Dropdown Box. It is located in the top right of the page.

Use the rules format style to highlight based on a specific value

Step 2.) Setup a Conditional Formatting Rule

Modify the Conditional Formatting Rules so that the values are always going to be selected. This can vary based on your specific dataset, but often times we will set it to if values are >= 0% and <= 100% which ensures that numbers will be highlighted in almost all instances.

The exact conditions will vary based on your dataset, you could also set it to bet between negative and positive trillion or some other unlikely to reach range.

To highlight values in Power BI use conditional formatting to change the background color

Step 3.) Adjust Apply to Settings

To Highlight the Total column along with the values update the “Apply To” selection in the conditional formatting window from “Values Only” to “Values and Totals” There is also an option where you can highlight “Totals Only” This controls how much of the row is highlighted.

Step 4.) Review the Results

The result of the selections above are a visual where the values of all columns in the Gross Profit row are highlighted. We recommend checking the conditional formatting with different slicer selections and different date periods to make sure that the logic works for multiple scenarios.

Example of a matrix visual with a full row highlighted

If you’re curious about the other conditional formatting options available, Microsoft has an in-depth guide explaining all of the different use cases for it.

Highlight Individual Rows in Power BI with an Index Column

Another workaround that you can use to tell Power BI to highlight specific cells in a Matrix visual is to use an index column. The method still utilizes Conditional Formatting, but assigns a static value to each row that becomes the basis for the highlighting.

Take a look at the example below.

The columns to the right, Cookie Category and Total Sales are standard Matrix visual columns.

Step 1.) Add an Index Column

In our example, we added a column called Item ID# on the left side of the Matrix. This gives us a specific identifier to base conditional formatting off of.

This column could be setup by manually entering data into a table, or by importing a separate Excel spreadsheet. The idea is that you have to assign the aggregated category in the Matrix to a specific number that you’ll be able to reference with conditional formatting.

An example of a matrix visual with an index column used to more control over highlighting

Note: We need to have an index column in the Matrix visual for it to be referenced in conditional formatting. Don’t worry, we will remove it in a later step so users won’t be able to see it.

Step 2.) Assign a Conditional Formatting Rule

Once an index column exists in a visual, you can specifically identify that value to apply conditional formatting based off of. In the example below we based it on being greater than or equal to 4.5 and less than or equal to 5 ensuring that only the number 5 row will be highlighted.

Highlight a row based on the value in a different column

The result looks like the following, with only a single cell highlighted based on a row and column designation.

Example of a single cell highlighted in a Power BI Matrix with a row index column

Step 3.) Hide the Item ID # Column from our Matrix Visual

To hide a column in Power BI take the following steps.

  • Select the Matrix Visual
  • Go to “Format Your Visual” on the Visualizations Pane
  • Disable Text Wrap under Column Headers
  • Adjust the width of a column by selecting the edge of the column header in the Matrix visual. Drag it to the left to make it smaller to the point that it cannot be seen.
Disable text wrap on a matrix visual under format visual options to avoid column headers getting larger when you a hide an index column

Once Column Headers text wrap has been disabled, you can drag a column to the left to make it smaller. Because text wrap is off it will not make the column headers themselves taller.

This doesn’t fully hide a column from existence, but it will hide it so end users will be unable to see it.

Screenshot of a matrix column with an index column being hidden from end users

Highlighting Individual Cells in Power BI Matrix Visuals

While there is no direct or easy way to highlight an individual cell within a Power BI Matrix, you can use the technique above to highlight based on an index column and place the column being conditionally highlighted into the middle of a Matrix visual.

A matrix visual with a single cell highlighted in the middle of it

While it’s not the most dynamic use of conditional formatting, it may be just good enough to get the job done!

Additional Highlight and Conditional Format Options

For even more information about how to set conditional formatting for a column based on values in another column, Patrick from Guy in a Cube does a great job explaining some of the additional considerations of using this feature.

Removing Conditional Formatting

Our final note is how to remove conditional formatting. Once you’ve added conditional formatting to a field in Power BI, you can use the same button to edit it. However, you need to use the drop down menu option below Conditional Formatting to Remove Conditional Formatting.

Screenshot of how to remove conditional formatting in case you add a highlight to a row or column and later need to remove it.

We don’t know why Microsoft chose to set this up as a separate section, but you can’t delete conditional formatting from the edit and add screen.

Scroll to Top