Locked lesson.
About this lesson
Use Copilot to reformat columns, rows, and cells in an Excel table.
Exercise files
Download this lesson’s related exercise files.
2.02 format-columns-rows-cells - Exercise.docx131.8 KB 2.02 format-columns-rows-cells - Exercise solution.docx
236.5 KB 02-02-Format Columns Rows and Cells-Start.xlsx
550.2 KB 02-02-Format Columns Rows and Cells-Finish.xlsx
550.3 KB 02-02-Sales Data Exercise-Start.xlsx
18.1 KB 02-02-Sales Data Exercise-Finish.xlsx
18.1 KB
Quick reference
Format Columns, Rows and Cells
Use Copilot to format columns, rows, and cells in an Excel table.
When to use
We use Copilot to format columns, rows, and cells whenever we want a quick way of applying different types of formatting to our table data.
Instructions
We can use Copilot to format columns, rows, and cells in our Excel table. In this lesson, we are going to explore a few examples.
It's worth noting that Copilot is in its infancy and will undergo changes and improvements as time goes on. Currently, many of these tasks are quicker to do manually but this will change. For Excel beginners who are unfamiliar with formatting, Copilot is a great tool to get this task done.
Let's use Copilot to reformat our dataset.
- Click anywhere in the data.
- From the Home tab, click the Copilot icon.
Apply a Table Style
When we format a dataset as a table, Excel applies the default table style. The colors used will be the default colors depending on the theme you have applied.
We can use Copilot to change the table style for us.
- Click in the prompt area at the bottom of the Copilot pane.
- Type the prompt: 'Apply the table style Dark Green, Table Style Medium 18'.
- Press Enter.
Remove Banded Rows
When we apply a table style, the default is to use banded rows. Banded rows ensure our spreadsheets are easy to read by adding an alternating background fill color for each row. While some people love this table style option, others do not. We can use Copilot to turn off the banded rows setting.
- Click in the prompt area at the bottom of the Copilot pane.
- Type the prompt: 'Remove banded rows'.
- Press Enter.
AutoFit Column Widths
If we have inconsistent widths applied to our columns or we cannot see information in our columns because the column is too narrow, we can use Autofit Column Widths to adjust all columns at the same time. Copilot can help us with this.
- Click in the prompt area at the bottom of the Copilot pane.
- Type the prompt: 'Autofit all columns in the table'.
Change Date Format
We can use Copilot to change the formatting applied to each column in our table. Copilot can format one or multiple columns at the same time as specified in the prompt.
In this first example, we need to change the format of the 'Invoice Date' column from General to Short date format.
- Click in the prompt area at the bottom of the Copilot pane.
- Type the prompt: 'Change the invoice date column to short date format'.
- Press Enter.
Change Number Format
Next, we need to change the formatting applied to columns H and J. Currently, these two columns have Currency formatting applied and we want to change it to Accounting format with zero decimal places.
Accounting format ensures that the currency symbol is pushed over to the left of the cell and the decimal places are always lined up.
- Click in the prompt area at the bottom of the Copilot pane.
- Type the prompt: 'Change the Price per Unit and the Operating Profit columns to Accounting format with 0 decimal places'.
- Press Enter.
Add a Comma Separator
The last thing to do here is apply a comma separator to the 'Units Sold' column and take the decimal places down to zero.
- Click in the prompt area at the bottom of the Copilot pane.
- Type the prompt: 'Add a comma separator to the Units Sold column with 0 decimal places'.
- Press Enter.
In just a few minutes, we used Copilot to completely reformat our dataset.
Hints & tips
- As a general rule, Copilot tends to be more accurate when using column names.
- We can send a prompt through to Copilot by pressing Enter or by clicking the paper plane icon next to the prompt area.
- 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.