Excel Text to Column and Transpose Commands

Tips on Excel Text to Column and Transpose Commands

Excel Text to Column Command
The Text to Column command allows you to break text in one column into several columns. For example, if you have LastName, FirstName in a column, you can break the text into two columns—one containing LastName and the other containing FirstName.
1. Insert as many columns to the left of the column to be split as needed.
2. Select the cells to be separated.
3. Pull down the Data menu and choose Text to Columns
4. Choose Delimited and click on Next
5. Choose the Delimiter that is used in the column, such as comma and click on Next
6. To set the column data format, click on the column and then click on the format desired under column data format (this is optional). Repeat this for each column and click on Finish.

Excel Transpose Command
Use the transpose feature to switch data from vertical to horizontal.
1. Select the cells to switch
2. Click on the Copy tool
3. Click where you want the data to be transposed to
4. Pull down the Edit menu and choose Paste Special
5. Click on Transpose and click on OK

______________________________________________________________________________

Comments

Post a Comment

Popular posts from this blog

The MID Function

The MID Function The syntax for the MID function is: = MID ( Number , Start_num , Num_chars ) Number - the piece of data you want to change. This can be a cell reference indicating where the data is stored. Start_num - specifies the starting character from the left of the data to be kept. Num_chars - specifies the number of characters to the right of the Start_num to be retained. Example Using Excel's MID Function: Note: For help with this example, see the image to the right. 1. Enter the following data into cell C1: #687 miles 2. Click on cell D1 in the spreadsheet - this is where the function will be located. 3. Click on the Formulas tab of the ribbon menu. 4. Choose Text from the ribbon to open the function drop down list. 5. Click on MID in the list to bring up the function's dialog box. 6. In the dialog box, click on the Number line. 7. Click on cell C1 in the spreadsheet. 8. Click on the Start_num line. 9. Enter the number 2 on this line s...
Read more

How to find blank cells in a range

Here is the step by step solution to the question: How to find blank cells in a range and hightlight them. 1. Highlight the range where you want to find the blanks cells. 2. Go to to menu bar, select edit, goto. Alternatively, you can press the function key F5. 3. When a dialog box pops up, find a button labeled “Special” located at the bottom left hand corner of the dialog box. 4. Another dialog box will appear. Look for the option labeled as “blanks”. Select the option. 5. Click on the “OK” button. 6. Click on the OK” Button to exit the dialog box. 7. Notice that all the blank cells are now highlighted. 8. Go to the menu bar, select format, cells. Alternatively, you could right click over any of the highlighted blank cells and select format cells. 9. Go to the pattern tab and choose yellow or any of your favorite color. Alternatively, you can click on the paint icon to format the cells 10. Click OK. Now all the blank cells are highlight
Read more

Excel Tips

SumIF Another useful function is SumIF. This function is like CountIf, except it adds one more argument: SUMIF(range, criteria, sum_range) Range and criteria are the same as with CountIF - the range of cells to search, and what you want Excel to look for. The Sum_Range is like range, but it searches a new range of cells. To clarify all that, here's what we'll use SumIF for. (Start a new spreadsheet for this.) Five people have ordered goods from us. Some have paid us, but some haven't. The five people are Elisa, Kelly, Steven, Euan, and Holly. We'll use SumIF to calculate how much in total has been paid to us, and how much is still owed. So in Column A, enter the names: Create the following Excel 2007 Spreadsheet In Column B enter how much each person owes: In Column C, enter TRUE or FALSE values. TRUE means they have paid up, and FALSE means they haven't: Add two more labels: Total Paid, and Still Owed. Your spreadsheet should look something like this one: In cells...
Read more