How to Create '|' (Pipe) Delimited Files in Excel

Problem:

Microsoft Excel is a very useful tool for data analysis. It supports import of data from various sources and formats into it for analysis. But, though it supports export of data in various common formats, it does not natively support export of data in a delimited text format with the delimiter other than Comma and Tab characters. This post explains how to get the excel data exported into a "|" (pipe) delimited text file.

Solution:

Excel does not directly support export or saving the data delimited with a pipe or such other characters. It supports comma delimited files (.csv) or tab delimited files though. The csv file generator however uses the List Separator as set in the windows Regional Settings as the delimiter. With this you can change this value in the regional settings and the csv file option will now produce a delimited file with the delimiter of your choice as set in the regional settings. For those not familiar with the regional settings, here is how to get this accomplished:

If you are using Windows 8 or 8.1, you will find the Regional Settings option under the "Clock, Language and Region"  category.

Click on the Additional Settings button in the Formats tab of the Regional Settings dialogue box. In the Numbers tab of the resulting dialogue box, you will find the field List Separator (Highlighted in the image. By default, it displays ","(comma). Now set it to a character that you need the files to be delimited with. For instance, if you want export a pipe delimited text file from excel, enter the charcter "|" in this field and apply the change.

You are done. Close and re-open Excel with the data that you want to export. Now use the Save As option to save the sheet as a csv file (.csv). This will now produce a text file delimited with the pipe character. Unless you regularly use pipe as the delimiter, you may want to set it back to comma, so that it does not impact any other operations that dependent on the List Separator field.

Excel Pivot & Shortcut Keys

Question:

I understand Microsoft Excel is a good start for analytics on data. Please direct me as to how to create a basic Pivot Table. You may also share me any tutorial references to learn more on Pivot feature in Excel. Also share Short Cut keys in Excel

By: Vinay Kumar

Response:

Learning to create a Pivot is fairly easy but the tough part is to know how you want the data to be presented in the Pivot. For this you need to know a good understanding of the data on hand and what you want out of the data. For this question, I assume that you are already clear about your needs.

Here is how you can quickly get a Pivot of your Excel Data:

• Select the data range that you want to apply Pivot on and then click on the Pivot button, which is in the Toll bar ribbon under the Insert Menu. You may also use the short cut key Alt + NV
• This will open up the Pivot dialog window where you will have option to adjust or change your your data range (as you may observe, you can also use an external data source by choosing the appropriate option) and other options.
• Clicking ok on this dialog window will open up the Pivot table in a new work sheet (default option) and will show the Pivot Table Fields task pane on the right side. You can drag and drop the fields into Filters, Columns, Rows and Values. 
• FILTERS: This area contains the fields that enable you to page through the data summaries shown in the pivot table by filtering out sets of data — they act as the filters. For example, if you designate the Year field from a data list as a report filter, you can display data summaries in the pivot table for individual years or for all years represented in the data list.
• COLUMNS: This area contains the fields that determine the arrangement of data shown in the columns of the pivot table.
• ROWS: This area contains the fields that determine the arrangement of data shown in the rows of the pivot table.
• VALUES: This area contains the fields that determine which data are presented in the cells of the pivot table — they are the values that are summarized in its last column (totaled by default).
• Set these in the way you want and your Pivot table is ready.

To know more about other complex and advanced features of the Pivot Table, check here.

Here is a nice Cheat Sheet containing all the Short Cut keys for Excel. If you are a key board expert, then you will want to check out this as well.