Locked lesson.

Upgrade

Quick reference

Apply Conditional Formatting

Apply Conditional Formatting to cells in a table using Copilot. 

When to use

We use Copilot to apply conditional formatting whenever we want to highlight cells in a table based on one or more pieces of criteria. 

Instructions

Copilot can apply conditional formatting to data. 

Highlight Cells

In this first example, we are going to use Conditional Formatting to highlight in yellow values greater than 1000 in the 'Units Sold' column. 

  • Type the prompt: 'Highlight in yellow all values in the Units Sold column that are greater than 1000'. 
  • Press Enter. 

It's worth remembering that conditional formatting is just rules and we can edit any rule in the normal way. For example, maybe I want to change the highlight color or change '1000' to '2000'. 

  • From the Home tab, click the drop-down underneath Conditional Formatting. 
  • Click Manage Rules. 
  • Select the rule and click Edit to change. 

Use a Suggested Prompt to Apply Conditional Formatting

Copilot will suggest prompts you might want to use. In this example, Copilot has suggested a prompt that applies bold formatting to the Top 10 values in the 'Operating Profit' column. 

  • Choose the suggested prompt. 
  • Press Enter. 

Add Data Bars

We can use Copilot to add data bars to a column in the table. Data bars are in-cell bars that help us visualize the value in the cell. 

  • Type the prompt: 'Apply green data bars to the Operating Margin column'. 
  • Press Enter. 

Format Cells in Bold and Red

Now, let's highlight in bold, red font the bottom 20 sales. 

  • Type the prompt: 'Apply bold formatting, red font to the bottom 20 values in the Total Sales column'.
  • Press Enter. 

The result is hard to see because the dataset is so large. We can check the formatting has been applied correctly by using a filter to filter by color. 

  • Click the Filter drop-down arrow at the top of the 'Total Sales' column. 
  • Select Filter by Color and choose Red. 

This will show us the cells where Copilot has applied the formatting. 

Login to download
  • 00:04 If you're a conditional formatting lover like me, then you will be thrilled to know
  • 00:09 that Copilot can also apply conditional formatting to datasets in a table.
  • 00:14 So let's take a look at a couple of examples.
  • 00:17 Now maybe I want to apply some conditional formatting to column I,
  • 00:22 the units sold column.
  • 00:23 And what I want to do here is I want to highlight in
  • 00:27 yellow all of the values that are greater than 1,000.
  • 00:32 Let's get Copilot to do it for us.
  • 00:35 I'm going to type in my prompt, highlight in yellow all values in the units sold
  • 00:39 column that are greater than 1,000.
  • 00:42 Notice I'm not saying including 1,000, greater than only.
  • 00:47 And also notice that I haven't specified that it needs to use conditional
  • 00:50 formatting, is going to need to work that out from the prompt that I've given it.
  • 00:55 So let's see if it can handle it.
  • 00:58 And boom, like magic, you can see those values highlighted in that column.
  • 01:04 And just remember that these are just conditional formatting rules.
  • 01:08 So if you need to edit this for whatever reason,
  • 01:10 maybe you want to change the color or change it to less than or equal to,
  • 01:14 you can simply go up to the Home tab, go into Conditional Formatting and
  • 01:18 manage rules, and there is the Conditional Formatting rule.
  • 01:22 You can see these cells that it's applied to, and
  • 01:24 we can edit the rule much like we would normally.
  • 01:27 So just bear that in mind.
  • 01:29 It is just simply applying the Conditional Formatting rule.
  • 01:32 What else can we do with Conditional Formatting in this dataset?
  • 01:36 Well, if I take a look over at these preset prompts, remember,
  • 01:39 you're going to get suggestions of prompts in this pane as well.
  • 01:43 Notice that the top one there says bought the top ten values in operating profit.
  • 01:48 So why not?
  • 01:49 Let's do that.
  • 01:50 I'm going to click to select, and it's just finishing up but
  • 01:54 if I scroll down you can see there we go.
  • 01:56 We can see a few of those values just they're showing in bold.
  • 02:01 And once again, this is just another conditional formatting rule,
  • 02:05 there is the top ten rule.
  • 02:07 Now this time, I want to add a slightly different piece of conditional formatting.
  • 02:11 I want it to add data bars to the operating margin column.
  • 02:16 I want this percentage shown as a bar in the cell as well as a value.
  • 02:21 So let's see if Copilot can handle this.
  • 02:24 Now I'm going to be very specific with my prompt.
  • 02:28 And I'm going to say, I want green data bars.
  • 02:32 I'm going to apply those to the operating margin column.
  • 02:37 And check it out.
  • 02:38 We have those data bars applied.
  • 02:40 Once again, just conditional formatting.
  • 02:43 Let's finish up simply by highlighting in bold and
  • 02:48 red font, the bottom 20 sales.
  • 02:52 Let's type in our prompt.
  • 02:53 Apply bold formatting,
  • 02:55 red font to the bottom 20 values in the total sales column.
  • 03:00 Let's see if it understands that.
  • 03:02 Now it's telling me that it's done.
  • 03:04 I can't immediately see those bottom 20.
  • 03:07 So what I'm going to do just to check to make sure it has applied is I'm going to
  • 03:11 click the filter in total sales.
  • 03:12 I'm going to say filter by color, and red.
  • 03:16 And there you go, there are my values.
  • 03:18 They were probably way down the table, I do have thousands of rows.
  • 03:22 So I can see that that has worked and it's applied it to the bottom 20 values.
  • 03:27 So those are some of the ways that you can utilize Copilot with conditional
  • 03:31 formatting.

Lesson notes are only available for subscribers.

Gift this course