• by David Grow , Op-Ed Contributor, July 8, 2016

Microsoft Excel is one of the most common tools that marketers rely on for sorting and analyzing data, and although exporting and saving would seem to be basic skills that everyone would already have, there are certain situations that cause problems for many Excel users in general, and marketers specifically. Here are a few of those potential hazards and how to fix or avoid them.

Issue 1: Delimiting

For new marketers, one of the first exporting issues they will probably encounter will be a lack of delimiting. Most commonly, this occurs when opening a file in Excel that is not formatted to be opened by the program. For instance, a file saved as a .txt or .csv may require that the data be delimited in order to separate the data into the correct columns within Excel.

In order to properly export the information with the data separated correctly, marketers must understand what is used to separate the data (i.e., commas, tabs, semicolons, etc.). As they move through the process of delimiting the information in Excel, they can choose which delimiter is being used.

After choosing the correct delimiter, Excel will show the user what the output will look like when finished and they can select “Finish” if the data is aligning with the column headers.

Issue 2: Excel Reformatting Data - Dates

Another potential issue to look out for relates to dates. Instead of recognizing the dates for what they are, Excel may import them formatted as numbers. This most commonly occurs when marketers attempt to copy and paste information from one sheet to another, or when they import dates into a sheet formatted for numbers. Errors can be corrected by changing the formatting from “Numbers” to “Date” within the Excel sheet (go to “Home,” then “Format”).

Issue 3: Excel Reformatting Data – Numbers

Two very common issues are both formatting errors that can occur with numbers. The first occurs when longer numbers are put in Excel with “General” as the format rather than “Number.” Once the format is changed back to “Number,” the full number will appear.

The second, and often unnoticed, change occurs when there are preceding zeros in a number. If the data is formatted as “General” or “Number,” Excel will drop the preceding zeros and represent the number incorrectly.

If marketers are copying and pasting the information from one document to another, they can change the formatting of the cells where the information will be pasted to “Text.”

Most often, preceding zeros appear within a merchant center feed. If that is the case, the best way to pull the information correctly is to download as a Notepad and save as a .txt. From there, open an Excel sheet, go to the “Data” tab, select “From Text” (located on the far left side) and chose the .txt file that was just saved from the merchant center. Lastly, choose the method of delimiter, click “Next” and just before selecting “Finish,” scroll over to the column that has the preceding zeros. Highlight the column and change the formatting from “General” to “Text” and click “Finish.”

Issue 4: Using Plus Or Minus

Marketers use plus (“+”) signs more often than minus (“-“) signs due to modified broad keywords. Because Excel sees plus signs as part of a mathematical formula, an equal sign will appear at the beginning of whatever is in the cell. To fix this, simply highlight the affected cells and do a find and replace (control + H). Find the equals (“=”) sign and replace it with nothing.

Issue 5: Saving As .CSV File

Most people learn the limitations that .csv files have.the hard way. First, .csv only will save the information on the tab that is selected when the file is saved. So if a user has developed multiple tabs, they will lose all of the information on all of the other tabs. In addition, .csv files do not save certain formatting changes such as highlighting, underlining, bolding and italicizing. So if a user was to send a file with notable highlighting and multiple tabs to another marketer, the file would arrive with no highlights and only the one tab.