Excel Tutorials

If you use Excel a lot, you probably know how to merge cells. However, something you may not use or think much about is merging columns. At some point, you will have a need to merge two or more columns, and you’ll wonder how to do it but figure it’s probably too hard and you find another way to solve your dilema. But, merging columns in Excel is really easy, and in this tutorial, I will show you how to merge columns in a couple of quick steps.

1. First, click on the blank cell where you want to put the new merged data from the different columns. Go to Formulas>Function Library>Insert Function and the “Insert Function” pop up window will appear. Under “Or select a category” select “text” from the drop down menu. And under “Select a function” select “CONCATENATE.” Next, click OK.
2. The “Function Arguments” pop up window box appears. Next, type the cell address of the first column you want to merge (use the first cell of the first column you want to merge) in “Text1” and enter the second column address in “Text2” – again use the first cell address. And click OK.
3. Now you will see the new column with the merged data from different columns created in the location where you had originally specified.

Good Job! So the next time you wonder about merging columns, you’ll remember how easy it really is.

Posted in Columns | Comments (0)

You know how some times you receive raw data in word and you really need to analyze it but you can’t do it in the format that was emailed to you. You have the option of taking this raw data and coping and pasting it onto an Excel spreadsheet, but it will probably dump the pasted data on one column. And you can’t analyze it like that. The solution is to split the raw data into columns. Many people don’t know about this handy trick that lets you split data from column into several other columns. In this short tutorial, I will show you the easy steps to get this done.

1. Lets pretend that you have copied a massive bulk of information from a website. You copied cities, states and university names. But when you copied and pasted the data, all the information got dumped in one column in an Excel spreadsheet. And you want to separate the cities, states and university names into three separate columns. To accomplish this, first, select all the raw data, in this case, the column, and go to Data>Data Tools>Text to Columns, and now you will work with the “Convert Text to Columns” wizard.
2. Next, you need to click on either “Delimited” or “Fixed With” under the heading “Choose the file type that best describes your data” – if you’re dealing with text data with similar formatting, then “Delimited” works better but if you’re dealing with numbers “Fixed With” may work better. And click Next.
3. Now, you need to tell the wizard how to split the data and this will depend on whether you selected “Delimited” or “Fixed With.” If you clicked on “Delimited” you need to specify the symbol that separates the data, in most cases dealing with formatted text is a comma. But if you picked “Fixed With” then you can use the ruler that is provided to split the data by clicking and dropping the check point in the specific areas where you wan the data to be split. Also, you can see a preview of how the data will look like in “Data preview.”
4. Finally, hit the Finish button and click OK. Now you will see the data nicely organized into separate columns. Now you can add headings to each column and format your new table accordingly.

Great Job! Using this highly ignored but powerful function in Excel, you’ll be able to organzie and analyze raw data in no time and look really smart in front of your coworkers.

Posted in Columns | Comments (0)