Format Columns, Rows, and Cells

Quick reference

• 00:04 So, now that we're all set up and ready to work with Copilot,
• 00:08 it's time to stretch its legs and see what it can do.
• 00:11 And in this lesson, we're going to run through some of the ways that we can use
• 00:15 Copilot to format cells, columns, and rows.
• 00:18 Now, I will say at the beginning here that a lot of the stuff that we
• 00:21 can get Copilot to do, at the moment, it's probably quicker to do it manually.
• 00:26 And I will say that Copilot is in its infancy.
• 00:29 This is a product that's going to change and
• 00:32 go through a whole string of improvements over time.
• 00:35 So at the moment, we're just trying out some of the basic things that it can do.
• 00:39 So let's use use Copilot to completely reformat this dataset.
• 00:43 Now, what might I want to do with the formatting in this table?
• 00:47 Well, I can see that when I convert it to a table, it applied a default table style.
• 00:52 You can see we have this blue color just here.
• 00:55 Now, Table Styles live on the Table Design ribbon underneath the Table Styles
• 01:00 gallery.
• 01:00 And we can see that it applied this style just here, Dark Teal,
• 01:04 Table Style Medium 2.
• 01:05 Now, maybe I want to use Copilot to apply a completely different table style.
• 01:10 So maybe I want this one instead, Dark Green, Table Style Medium 18.
• 01:14 So let's see if Copilot can change it for me.
• 01:18 Now, I'm going to go straight down to this prompts area at the bottom and
• 01:21 I'm going to type in my prompt.
• 01:24 So this is my prompt, apply the table style, Dark Green, Table Style Medium 18.
• 01:29 Let's press Enter to send it through and see if Copilot can do it.
• 01:33 And check it out, how cool is that?
• 01:35 It's applied exactly the table style that I want.
• 01:39 What else might I want to do here.
• 01:41 Well, I can see that this table style has banded rows turned on, meaning that,
• 01:45 each alternating row is formatted with this grey background fill.
• 01:50 Now, I want to turn off banded rows, so let's get Copilot to do it for us.
• 01:54 I'm just going to type in remove banded rows, send it through.
• 01:59 And there we go, it's turned those off.
• 02:01 And it's worth noting that banded rows, if we go to the Table Design ribbon, all
• 02:06 Copilot's done here is, it's toggled off this checkbox where we have Banded Rows.
• 02:12 What else can I do in terms of formatting?
• 02:14 Well, let's try and autofit all of the column widths.
• 02:17 Let's go down to the prompt area.
• 02:21 I'm going to type in autofit all columns in table.
• 02:25 And there we go, it's applied autofit.
• 02:27 Now, just for the sake of this demonstration, I'm just going to decrease
• 02:32 the width of column A, so we can just see a little bit more of our data.
• 02:36 What else do I need to do to this dataset in terms of formatting?
• 02:40 Well, if we look at column C,
• 02:41 this is where we're housing the invoice date details.
• 02:45 Now, currently, I have the incorrect formatting applied to this column.
• 02:50 If I click on a value in the Invoice Date column and go to the Home tab,
• 02:53 you can see that I've got general formatting applied.
• 02:56 Now, this should be showing a date.
• 02:59 So I'm going to get Copilot to convert this column to short date format.
• 03:04 And I will say what I've noticed is that when you're working with Copilot,
• 03:08 it tends to work a lot better if you give it the column name as opposed to
• 03:12 the column letter.
• 03:14 So the column name would be Invoice Date as opposed to column C.
• 03:18 So what we're going to say here in our prompt is,
• 03:21 change the Invoice Date column to short date format.
• 03:25 And of course, if you wanted the longer version,
• 03:28 you could specify to change it to long date format.
• 03:31 And there we go, it's changed all of the dates in column C.
• 03:36 Now, if I look a little bit further across my dataset,
• 03:39 I can see that in the Price Per Unit column and the Operating Profit column,
• 03:44 currently, I have currency format applied to both of these columns.
• 03:49 How do we know that we have currency formatting applied?
• 03:51 Well, we can see it up here in the number formatting area.
• 03:55 And we can also see that that dollar symbol is pushed right up
• 03:58 against the number, which means that it's using currency formatting.
• 04:03 Now, what I want to do, is I want to change these two columns,
• 04:07 column H and column J, to accounting format.
• 04:10 Which means that currency symbol is going to be pushed all the way over to
• 04:13 the left-hand side of the cell, and the decimal places are going to be in line.
• 04:17 And I want to take the decimal places down to 0.
• 04:21 So we should be able to do all of this within one prompt,
• 04:24 and we want to apply this to two different columns.
• 04:28 So let's type in our prompt.
• 04:30 Change the Price per Unit and
• 04:32 the Operating Profit columns to Accounting format with 0 decimal places.
• 04:37 Let's send it through and see if it can do it.
• 04:41 And there we go.
• 04:41 You can see that the dollar symbols have been moved to the left-hand side of
• 04:45 the cell, and those decimal places have also been taken down to 0.
• 04:48 So that is working perfectly.
• 04:51 Now, if I look at column I, where we have Units Sold,
• 04:55 I want to add a comma separator in here, and I want 0 decimal places.
• 05:00 So let's type in our prompt.
• 05:01 There we go.
• 05:02 There is our prompt, let's send it through.
• 05:06 And there we go, like magic, we have our comma separator.
• 05:10 So those are some of the ways that you can use Copilot to format columns, rows, and
• 05:15 cells.
• 05:15 Of course, there are many other ways that we haven't discussed in this tutorial,
• 05:20 but hopefully that gives you an idea as to how you need to word your prompts in order
• 05:25 to get Copilot to execute specific commands.

Lesson notes are only available for subscribers.