How to Organize Raw Data Into a Spreadsheet in Excel

By Laura Gittins

Microsoft Excel lets you take raw business data and make some sense of it in a spreadsheet. You can organize data alphabetically, by size, color or some other filter. However, to do that, you first need to get the data into your spreadsheet. When collecting your data, you probably used some sort of identifying character to separate unique values. This character is called a delimiter. Excel uses this character to put each piece of data into its own cell. Having your raw data properly formatted makes it quick and easy to organize in Excel.

Open your raw text file. Make sure unique entries are separated by a delimiter, such as a space, tab, comma or semicolon. This delimiter tells Excel where one data entry ends and the next begins. If the entries are not separated by delimiters, type them in manually. For example, if you choose a semicolon, your file may look like this: "first; second; third; ..."

Save and close the data file. Open Excel and start a new spreadsheet.

Click the "Data" tab, and then click the "From Text" button in the Get External Data section on this tab.

Navigate through your system files in the dialog box to the folder that contains your raw data file, and then double-click it.

Click the "Delimited" radio button to have Excel separate data entries using a delimiter. Click "Next."

Click the check box with the delimiter you used to separate data entries in your text file. For example, if your text file contained "first; second; third; ...", then click the "semi-colon" check box. Click "Next" on this screen, and then click "Finish."

Click the cell on this current worksheet that you want to begin copying data onto. Do not select the first row to paste the data. Click on cell "A2" -- you can add more rows and columns ahead of the data as needed later. Click "OK" to close the dialog box and import the data.

Click on cell "A1," type in a heading for the data in column "A," and then type in headings for the rest of the columns.

Click and drag to highlight all of the headings on the first row. Click the "Filter" button. This adds drop-down arrows to each heading, and you can use these to organize your data alphabetically or by colored cells, if you add fill colors. You can also filter for data that fit certain criteria.


Information in this article applies to Excel 2007 and Excel 2010. It may vary slightly or significantly with other versions or products.