How To

In this article you'll learn nifty tips & tricks that will help you edit your data in Excel quicker and more easily.

 

Find and Replace


Find and Replace allows you to search for data in the spreadsheet and replace it with other data without having to search for it one by one. Commonly used to find unnecessary information from your product descriptions and replace it with nothing . You can do this by using the Find & Select function in the Home tab’s Editing section of your toolbar:

 

Autofilter


Autofilter will allow you to sort your data by a specified range via a dropdown. This can be helpful, as an example, to sort your data by moving any rows that contain blanks to the bottom of the list.

Note how the columns in the image below have some blank spaces in their rows. Select the Data tab in your toolbar, then click the Filter button to apply an auto-filter:

Now you can click the drop-downs to filter or sort by the criteria you need. For example, you can choose to sort number values Largest to Smallest or Smallest to Largest, to filter by value or color, and so on. The options will change depending on the kind of data in your column, ie: text columns will have Sort A - Z and Sort Z - A instead of Largest to Smallest or Smallest to Largest.

 

Highlight Duplicates


You can use the Conditional Formatting option in the Styles section of the Home tab in your toolbar to easily find duplicate values in a column, and then use the Auto Filter described above to isolate them.

Select the column of data where you suspect you have duplicate values, then go to the Home tab to the Styles section and choose Conditional Formatting > Highlight Cells Rules > Duplicate Values…

You’ll get a pop-up of formatting options that allows you to choose how the duplicates are highlighted:

Choose OK to apply the conditional formatting.

You can use the Auto Filter to show only duplicates or only non-duplicates by using the Filter By > Color option:

 

Shade alternate rows


This handy tip will allow you to shade every other row to make your spreadsheet easier to read.

Here's how to do it:

1. Highlight the range of cells or rows or columns that you want to format.

2. Choose the Conditional Formatting option in the Styles section of the Home tab in your toolbar, and go to Highlight Cells Rules > More Rules....

3. Select Use a formula to determine which sells to format and enter =MOD(ROW(),2)=0 in the Format values where this formula is true box:

4. Click the Format button to bring up the Format Cells dialog box:

5. Select the Fill tab to specify a color for the shaded rows, in the Font tab of the Format Cells box to format the color or style of the font, and so on. For example, you might select a light green background as I've done in the image below.

6. Click OK twice to return to your worksheet. Your results will look like this:

 

Split text into columns


Separate delimited or fixed width text from one cell into separate columns. In the image below, the data I want to separate is in a comma-separated format, and I want to get it into columns:

To separate this into 3 columns, I'll use the Data > Text to Columns option from the menu:

The result will look like this:

 

Copy / Paste Special


Sometimes you need to do a bit of work to get data to display properly in Excel.... and that work usually involves using a function or two to get what you need. Prior to uploading that data via Bulk Upload / Update, you'll want to make sure you remove the formulas and paste it in the appropriate cell as text-only.

In the image below, all of column 'B' is displaying data through the use of the Concatenate function. I want to replace the data AND formulas in that column with JUST data.

To do this I'll need to copy the data in that column. Select that column by pressing the 'B' column header and Ctrl+C to copy that data. With that column still selected, right-click and choose Paste Special...

Select Values and hit OK. This will overwrite the formula with just the text value of that data.

This will ensure proper display in our system (and inevitably, your listings) upon upload. You should also use this when copying from another program like Word or Dreamweaver directly into Excel.

If you have data across rows that you need in columns, or data in columns that you need in rows, you can use the Copy + Paste Special > Transpose function. In this example we’ll use it to create a column of data out of a row. First select the data you want to transpose and copy it. Then right-click in a new cell and choose Paste Special… and select the Transpose option:

This results in:

 

Create comma-separated list


You’ll sometimes need to create a comma-separated list of values for an Advanced Filter. There are many ways to do this, but if your data is in a column in Excel it can be done easily with a simple formula using the Merge Cell function noted above. Here’s the basic method; we’ll use SKUs as an example here:

1. Click into a blank cell next to the first SKU in your list.

2. Enter the formula =A2&”,”&B1 where A2 is the first SKU in your list and B1 is the cell above your formula. The formula will capture the first SKU, and as you drag down, will capture the next SKU and concatenate it with a comma and previous one into a list:

 

Fill in the blanks


If you have a spreadsheet that has the a data point on the first row of a set of data and you need it on each row, you can write a formula that checks to see if the column with that data point is blank. If it is, it inserts the value just above but otherwise it inserts the value in the column with your data point. So as you drag down, when it reaches your data point it inserts that value, and when the next cell is blank it inserts the data point that you just inserted in the cell above with your formula.

For this example, we'll say that you have Style on the Parent SKU row of your SKU data, and you need it on each row so that the Child SKUs will send the Style to the marketplace:

You can see that our formula checks the first cell and finds a value there, so it uses that value. As you drag down, it checks each row for a value; when it finds the next Parent SKU row with a Style populated, it inserts the new Style value and continues on.

You can do the same thing by checking for Parent in the Parent SKU column rather than for blanks in the Style column:

When the formula finds a value of Parent in the Parent SKU column, it inserts the value in the Style column. When it finds any other value, it inserts the value in the cell above itself. So as you drag down, it inserts the Parent Style for the Parent SKU as well as for each Child SKU below it.

In this article you'll learn nifty tips & tricks that will help you edit your data in Excel quicker and more easily.

 

Find and Replace


Find and Replace allows you to search for data in the spreadsheet and replace it with other data without having to search for it one by one. Commonly used to find unnecessary information from your product descriptions and replace it with nothing . You can do this by using the Find & Select function in the Home tab’s Editing section of your toolbar:

 

Autofilter


Autofilter will allow you to sort your data by a specified range via a dropdown. This can be helpful, as an example, to sort your data by moving any rows that contain blanks to the bottom of the list.

Note how the columns in the image below have some blank spaces in their rows. Select the Data tab in your toolbar, then click the Filter button to apply an auto-filter:

Now you can click the drop-downs to filter or sort by the criteria you need. For example, you can choose to sort number values Largest to Smallest or Smallest to Largest, to filter by value or color, and so on. The options will change depending on the kind of data in your column, ie: text columns will have Sort A - Z and Sort Z - A instead of Largest to Smallest or Smallest to Largest.

 

Highlight Duplicates


You can use the Conditional Formatting option in the Styles section of the Home tab in your toolbar to easily find duplicate values in a column, and then use the Auto Filter described above to isolate them.

Select the column of data where you suspect you have duplicate values, then go to the Home tab to the Styles section and choose Conditional Formatting > Highlight Cells Rules > Duplicate Values…

You’ll get a pop-up of formatting options that allows you to choose how the duplicates are highlighted:

Choose OK to apply the conditional formatting.

You can use the Auto Filter to show only duplicates or only non-duplicates by using the Filter By > Color option:

 

Shade alternate rows


This handy tip will allow you to shade every other row to make your spreadsheet easier to read.

Here's how to do it:

1. Highlight the range of cells or rows or columns that you want to format.

2. Choose the Conditional Formatting option in the Styles section of the Home tab in your toolbar, and go to Highlight Cells Rules > More Rules....

3. Select Use a formula to determine which sells to format and enter =MOD(ROW(),2)=0 in the Format values where this formula is true box:

4. Click the Format button to bring up the Format Cells dialog box:

5. Select the Fill tab to specify a color for the shaded rows, in the Font tab of the Format Cells box to format the color or style of the font, and so on. For example, you might select a light green background as I've done in the image below.

6. Click OK twice to return to your worksheet. Your results will look like this:

 

Split text into columns


Separate delimited or fixed width text from one cell into separate columns. In the image below, the data I want to separate is in a comma-separated format, and I want to get it into columns:

To separate this into 3 columns, I'll use the Data > Text to Columns option from the menu:

The result will look like this:

 

Copy / Paste Special


Sometimes you need to do a bit of work to get data to display properly in Excel.... and that work usually involves using a function or two to get what you need. Prior to uploading that data via Bulk Upload / Update, you'll want to make sure you remove the formulas and paste it in the appropriate cell as text-only.

In the image below, all of column 'B' is displaying data through the use of the Concatenate function. I want to replace the data AND formulas in that column with JUST data.

To do this I'll need to copy the data in that column. Select that column by pressing the 'B' column header and Ctrl+C to copy that data. With that column still selected, right-click and choose Paste Special...

Select Values and hit OK. This will overwrite the formula with just the text value of that data.

This will ensure proper display in our system (and inevitably, your listings) upon upload. You should also use this when copying from another program like Word or Dreamweaver directly into Excel.

If you have data across rows that you need in columns, or data in columns that you need in rows, you can use the Copy + Paste Special > Transpose function. In this example we’ll use it to create a column of data out of a row. First select the data you want to transpose and copy it. Then right-click in a new cell and choose Paste Special… and select the Transpose option:

This results in:

 

Create comma-separated list


You’ll sometimes need to create a comma-separated list of values for an Advanced Filter. There are many ways to do this, but if your data is in a column in Excel it can be done easily with a simple formula using the Merge Cell function noted above. Here’s the basic method; we’ll use SKUs as an example here:

1. Click into a blank cell next to the first SKU in your list.

2. Enter the formula =A2&”,”&B1 where A2 is the first SKU in your list and B1 is the cell above your formula. The formula will capture the first SKU, and as you drag down, will capture the next SKU and concatenate it with a comma and previous one into a list:

 

Fill in the blanks


If you have a spreadsheet that has the a data point on the first row of a set of data and you need it on each row, you can write a formula that checks to see if the column with that data point is blank. If it is, it inserts the value just above but otherwise it inserts the value in the column with your data point. So as you drag down, when it reaches your data point it inserts that value, and when the next cell is blank it inserts the data point that you just inserted in the cell above with your formula.

For this example, we'll say that you have Style on the Parent SKU row of your SKU data, and you need it on each row so that the Child SKUs will send the Style to the marketplace:

You can see that our formula checks the first cell and finds a value there, so it uses that value. As you drag down, it checks each row for a value; when it finds the next Parent SKU row with a Style populated, it inserts the new Style value and continues on.

You can do the same thing by checking for Parent in the Parent SKU column rather than for blanks in the Style column:

When the formula finds a value of Parent in the Parent SKU column, it inserts the value in the Style column. When it finds any other value, it inserts the value in the cell above itself. So as you drag down, it inserts the Parent Style for the Parent SKU as well as for each Child SKU below it.

0
Your rating: None Average: 3 (1 vote)
0
Your rating: None Average: 3 (1 vote)