Overview

ChannelAdvisor uses text files and comma-separated value files in multiple areas of the program in order to work with and display data in bulk.  One of the most common tools to modify and view these files is Microsoft Excel. This article is designed to provide you with some useful tips using Excel with ChannelAdvisor applications. Below are some of the more common formulas and tips we use to get our data to display properly and prepare our spreadsheets more efficiently. If you would like additional information on the use of these functions, view the Help function in Excel. 

Concatenate:

The Concatenate function allows you to merge values from multiple cells into a single cell. This is beneficial if you have 2 columns of data that you would like to bring together with a 'seperator'.

Example:

Note how the function is merging Cell A1 with a "space" and Cell A2. The "space" needs to be surrounded by double quotes.

Back to list of formulas

Length:

The Length (Len) function will count the number of characters from a cell and return the value. Helpful if you want to count your 'Auction Title' column length to make sure it will conform to eBay's limit.

Example:

Back to list of formulas

Clean:

The Clean function removes all nonprintable characters from a string. This will help you remove uncessary 'carriage returns' or other nonprintable characters that may display improperly after Bulk Uploading / Updating.

Example:

Back to list of formulas

Merge data from two columns to one:

Similar to Concatenate. Take data from 2 columns and merge them together into one cell. You can also use a seperator in between the merged data.

To use a seperator, surround it with quotes, then again with ampersands.

Back to list of formulas

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 unecessary information from your product descriptions and replace it with nothing.

Back to list of formulas

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 Data > Filter > AutoFilter

A drop-down should appear in the first cell of your range. Select NonBlanks from the drop-down.

This will sort the NonBlank rows to the top.

Back to list of formulas

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 Format > Conditional Formatting to display the Conditional Formatting dialog box.

3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.

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

5. Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). 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:

Back to list of formulas

Split text into columns:

Seperate delimited or fixed width text from one cell into seperate columns. In the image below, the data I want to seperate is seperated by a 'space' in one cell.

To seperate this into 3 columns, I'll use the Data > Text to Columns option from the menu. Excel will automatically determine if the data is delimited or fixed width:

Select Next and now you're able to change where the data is seperated by moving the vertical lines. Hit Next and decide the specific data format and cell destination of the data:

Hit Finish for your results:

Back to list of formulas

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 'AI' 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 'AI' column header and 'Ctrl+C' to copy that data. With that column still selected, go to Edit > 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.

Back to list of formulas

Control + c / Control + v:

Copies contents of your selection to the clipboard / Pastes the contents of your selection from the clipboard

Back to list of formulas

Control + Shift + Down/Up arrow::

Select a cell and press Control + Shift + Down arrow to select everything in the column until it reaches an empty cell.

Back to list of formulas