Clean Excel data

- Do you ever feel like your Excel spreadsheet is a jungle of messy data, duplicates, inconsistencies, and errors lurking at every corner? Now imagine having a personal assistant who not only finds and fixes these issues, but does it all in seconds. That's what Copilot can do for you. Throughout this section, we'll be using a sample data set related to sportswear sales. This data set contains several columns showing different metrics, and each row relates to a transaction. Now, currently this data set has a few issues that I want to tidy up. For example, if you take a look at the table, we have banded rows turned on, and that is that each alternate row is shaded in gray. Now, some people really like this. I personally want to turn this off, because it gives my table a cleaner look. If we look in column D, I can also see that we have some inconsistencies in this column. Some of the text is in uppercase and some is in lowercase. You'll also notice that in some cells we have some erroneous spacing issues, so we have big gaps at the beginning, and we might even have trailing spaces at the end that we can't see. So I want to get Copilot to give me a formula that's going to tidy up this column. Another thing I want to do is in column H, I want to replace every occurrence of "In-store" with the word "Store". I can get Copilot to do that for me as well. And in columns I to L where I have these values. These values are currently unformatted, so I'm going to get Copilot to apply formatting. The final thing I'll do to tidy up this data set is simply run a quick check for any duplicate records. Now all of this we're going to do utilizing Copilot. Now, the data that we're using here is already formatted as an Excel table. If we go to the Table Design ribbon, notice in the properties group, we have the table name MegaSales. From the Home ribbon, we're going to click on the icon. Now at the top of this pane, you can see the different tasks that you can perform with Copilot. We can create formulas, we can summarize with PivotTables or charts. We can apply conditional formatting, and even ask Copilot questions. We are going to use the prompt area at the bottom and type in prompts to get Copilot to perform some data cleansing tasks. So let's start really simple. Let's get Copilot to turn off banded rows. (keyboard clacking) So our first prompt is really simple. "Remove banded rows from the dataset." Let's press Enter to send this through, and see if Copilot can do it. And check it out, like magic, it has toggled off those banded rows, which is brilliant. The next thing I'm going to get Copilot to do is help me clean up column D. That's where we have the inconsistent case and those erroneous spaces. So I'm going to ask Copilot to generate me a formula that's going to fix both of these issues. Let's type in our prompt. (keyboard clacking) So this is my prompt. "Tell me what formula I need" "to make the text in the 'Region' column consistent." "I want to change the text to proper case" "and trim any spaces." So when I send this through, I would expect Copilot to come back with a formula that's going to accomplish both of those tasks. And I can see that it has, so it's given me the Proper and the Trim formula, and it's going to apply it to the Region column. So that looks pretty good to me. What we're going to do is we're going to insert a blank column into our table Control + Shift + Plus or Control + Plus depending on your keyboard. And we're going to paste the formula into this column. So back over to the Copilot pane, let's click on Copy. And then over in this cell, we're going to press Control + V to copy it in, and notice what it's done. It's looking at the values in column D, and it's applying the Proper and the Trim. So now everything looks nice and consistent. Now because this formula is referring to the region column, it means that if we try and delete out this column, we're going to get reference errors in column D. So let's just Undo, the way that we can combat that is simply to copy and paste the formula column over the top. So let's select the entire column Control + C to copy, and then we're going to paste the values only. Now if you're using Microsoft 365, you can use the keyboard shortcut, Control + Shift + V. If you are not, then you can go up to the Paste button and choose Paste Values. That will remove the underlying formulas, and it means that we can basically delete out the column that we no longer need and just rename the new column. The next thing I want to do is replace all occurrences of "In-store" with the word "Store". So let's ask Copilot. So this is our prompt. "Replace all occurrences of In-store" "with Store in column H." Let's send it through. And check it out, if we now look at column H, you can see we have Store as opposed to In-store. The final couple of things I'm going to do here is I'm going to get Copilot to apply some formatting to columns I to L. So let's type in our prompt. (keyboard clacking) So this is our prompt. "Change the number formatting of columns I to L" "to comma style, 2 decimal places." Let's send it through, check it out, how quick was that? And the very final thing I'd probably do here is just get Copilot to check to see if we have any duplicates in the data set. So let's type in our prompt. (keyboard clacking) "Remove any duplicate records from the data," "each column must contain the same information" "as another row to be considered a duplicate." Let's send that through. Now, notice here I'm getting something different, and this is good to note, because Copilot at this stage can't execute everything that you ask it to do. So here, it can't remove any duplicates from my data set, and it's giving me the steps where I need to go in order to do that. So it's telling me to go to the Data tab, and then click on the Remove Duplicates button, which is this button just here. So this is still pretty useful as it does guide you in the correct direction if it can't actually execute the task itself. So I'm going to click on, OK, and you can see it found one duplicate and that's now been removed. And just like that, our wild jungle of data is now like a neat, cared for, garden. Don't take my word for it, try it for yourself.