How to use text to columns like an excel pro

Result: By clicking on the finish, you will see all the surnames in one column, and the name is another column. This is the process of Text to columns in Excel

We have solved another interesting query of excel users, in case if you wish to convert an invalid date format into a valid date format.

Split Cells into Multiple Columns.

Let’s see how to split the data into multiple columns. This is also part of data cleaning. Sometimes your data are in one single column, and you need to divide it into multiple adjacent columns for applying Sort, Filter or Pivot table.

All the information is in one single column, but you need to separate it. In our earlier example, we have applied the “Delimited” technique. However, this time, we will apply a “Fixed width” strategy of Text to Columns.

Observation:

From the above data, you can understand that there are four pieces of information in a single cell i.e. Account No., Item No., Check No., and Description.

Now you can convert or fix digits with trailing minus into negative digits. Check out how by clicking here:

Our aim is to separate that one column in four different columns. Let’s see how it’s done:

Step 1: Select your data to the range (from the first data cell). Go to the Data tab, and then go to Text to Columns. On the “Convert Text to Columns Wizard – Step 1 of 3” box, choose the Fixed Width option. Click Next.

Step 2: You will see the fixed-width divider vertical line marks (called Break line) in the Data Preview window. You may need to adjust it as per your data structure.

• On double click, the brake line will be deleted
• When you click once, a new brake line will be created at the point of click
• If you click an existing brake line and drag it, it can be moved to the desired position
• After placing appropriate break lines, click Next.

Step 3: As you click on next, you will reach Step 3 of 3 of Text to Columns wizard. You may change the destination cell so that your original data remains intact and output appears in adjoining columns’ cells.

Important Note:

If you click Finish, you will observe that the 3 rd column of the output has last the prefix zeroes i.e. 00816530 gets converted to 816530, thereby corrupting the data.

Step 4: To retain the prefix zeroes, you should have chosen the column from the Data Preview window of Step 3 of 3 of Text to Columns wizard. It will blacken out the column as shown in the picture below.

Step 5: Once the column is blackened out, choose the “Text” option from the list of options [General, Text, Date, and Skip]. Now if you click on Finish, you will see the zeroes are retained in the final output columns.

We have solved another interesting query of quite a few people, in case if you wish to convert an invalid date format into a valid date format. Click here:

How to do text to columns in Excel?

Please find the step below to apply the text to columns in excel

1. Click on home
2. Select Range of Cells
3. Click on Text to Columns
4. Choose the Delimited option & Click Next
5. Click on the Comma button then the next button.
6. Choose Destination cell & Finish

If you like this tutorial, then don’t forget to check our latest Excel Dashboard Course. This course provides excellent quality videos with certification & lifetime access.

Without doubt, an Excel spreadsheet is one of the most advanced tools for working with raw data—and one of the most feared. The application looks complicated, way too advanced, and like something that would take hours to figure out.

I wouldn’t be surprised if upon hearing that you had to start using MS Excel, your heart started to pound. Is there any way to make Microsoft Excel less scary and intimidating? Yes.

By learning a few spreadsheet tricks, you can bring Excel down to your level and start looking at the application in a different light. We rounded up some of the simplest yet powerful MS Excel spreadsheet tips you can start using on your data.

1. Use MS Excel Format Painter

To start you off, get yourself familiar with formatting your spreadsheet cells. A visually organized spreadsheet is highly appreciated by others as it can help them follow your data and calculations easily. To quickly apply your formatting across hundreds of cells, use the Format Painter:

1. Select the cell with the formatting you wish to replicate
2. Go to the Home menu and click on the Format Painter. Excel will display a paintbrush next to the cursor.
3. While that paintbrush is visible, click to apply all of the attributes from that cell to any other.

To format a range of cells, double-click the Format Painter during step 1. This will keep the formatting active indefinitely. Use the ESC button to deactivate it when you’re done.

2. Select Entire Spreadsheet Columns or Rows

Another quick tip– use the CTRL and SHIFT buttons to select entire rows and columns.

1. Click on the first cell of the data sequence you want to select.
2. Hold down CTRL + SHIFT
3. Then use the arrow keys to get all the data either above, below or adjacent to the cell you’re in.

You can also use CTRL + SHIFT + * to select your entire data set.

3. Import Data Into Excel Correctly

The benefit of using is Excel is that you can combine different types of data from all kinds of sources. The trick is importing that data properly so you can create Excel drop down lists or pivot tables from it.

Don’t copy-paste complex data sets. Instead, use the options from the Get External Data option under the Data tab. There are specific options for different sources. So use the appropriate option for your data:

4. Enter The Same Data Into Multiple Cells

At one point, you may find yourself needing to enter the same data into a number of different cells. Your natural instinct would be to copy-paste over and over again. But there’s a quicker way:

1. Select all the cells where you need the same data filled in (use CTRL + click to select individual cells that are spread across the worksheet)
2. In the very last cell you select, type in your data
3. Use CTRL+ENTER. The data will be filled in for each cell you selected.

5. Display Excel Spreadsheet Formulas

Jumping into a spreadsheet created by someone else? Don’t worry. You can easily orient yourself and find out which formulas were used. To do this, use the Show Formulas button. Or you can use CTRL + ` on your keyboard. This will give you a view of all formulas used in the workbook.

6. Freeze Excel Rows And Columns

This is a personal favourite of mine when it comes to viewing lengthy spreadsheets. Once you scroll past the first 20 rows, the first row with the column labels annoyingly disappear from view and you begin to lose track of how the data was organized.

To keep them visible, use the Freeze Panes feature under the View menu. You can opt to freeze the top row or, if you have a spreadsheet with numerous columns, you can opt to freeze the first column.

7. Enter Data Patterns Instantly

One great feature in Excel is that it can automatically recognize data patterns. But what’s even better is that Excel will let you enter those data patterns to other cells.

1. Simply enter your information in two cells to establish your pattern.
2. Highlight the cells. There will be a small square in the bottom right hand corner of the last cell.
3. Place your cursor over this square until it becomes a black cross.
4. Then click and drag it with your mouse down to populate the cells within a column

8. Hide Spreadsheet Rows and Columns

In some cases, you may have information in rows or columns that are for your eyes only and no one else’s. Isolate these cells from your work area (and prying eyes) by hiding them:

1. Select the first column or row in the range you want to hide.
2. Go to Format under the Home menu.
3. Select Hide & Unhide>Hide Rows or Hide Columns.

To unhide them, click on the first row or column that occur just before and after the hidden range. Repeat steps 2 and 3, but select Unhide Rows or Unhide Columns.

9. Copy Formulas Or Data Between Worksheets

Another helpful tip to know is how to copy formulas and data to a separate worksheet. This is handy when you’re dealing with data that’s spread across different worksheets and requires repetitive calculations.

1. With the worksheet containing the formula or data you wish to copy opened, CTRL + click on the tab of the worksheet you want to copy it to.
2. Click on or navigate to the cell with the formula or data you need (in the opened worksheet).
3. Press F2 to activate the cell.
4. Press Enter. This will re-enter the formula or data, and it will also enter it into the same corresponding cell in the other selected worksheet as well.

These general tips won’t turn you into an Excel guru overnight. But they can help you take that first step towards becoming one! Are you a well-seasoned Excel user? Which of your spreadsheet tricks would you add?

By John MacDougall

2019-01-13

DAX | Pivot Tables | Power Pivot

I got a rather interesting question from someone who attended one of my pivot table webinars. They were wondering if they could have text values in the Values area of a pivot table?

This is usually the area where we summarize fields by various different aggregation methods like taking the sum, average, minimum, maximum or standard deviation.

But the thing is, these aggregation methods require numeric data!

Is there any way to summarize text based data that will return text as the result?

The answer is yes, but we will need to use the data model and DAX formulas to do this. Traditional pivot tables do not have this functionality.

Also, we will need to be a PC user with Excel 2013 (or later) and Office 365. Sorry, but these modern features aren’t available in the Mac versions yet.

Table of Contents

Video Tutorial

The Problem

Here we’ve got a list of students along with the courses they are enrolled in. A student can have multiple rows of data when they are enrolled in multiple courses.

Can we summarize this data with a pivot table so that we just display each student once and then show a comma separated list of their courses?

Insert A Pivot Table

First, we will need to insert a pivot table. This is done in the usual manner.

Select a cell inside the data ➜ go to the Insert tab ➜ then press the Pivot Table button.

In order to use DAX formulas, we will need to select the Add this to the Data Model option.

Add A Measure

With traditional pivot tables, we don’t need to define any calculations. They come predefined with basic sum, count, average, minimum, maximum, standard deviation and variance calculations.

With the data model, we get access to a whole new world of possible calculations using DAX formulas. These are created by adding Measures. We can create just about any calculation we can imagine with these.

To add a Measure, select the pivot table ➜ right click on the table of data found in the PivotTable Fields window ➜ choose Add Measure from the menu.

This will open the Measure dialog box where we can create our DAX formulas.

ConcatenateX Function

In the Measure dialog box:

1. We need to select the table to which to attach our measure, give the measure a name and description.
2. We can explore the available DAX formulas using the Insert Function menu and also check the validity of any formula we write.
3. We can write our formula in the DAX formula editor.
4. We can assign formatting to the measure.

To create a measure that aggregates text into a comma separated list, we’re going to use the ConcatenateX DAX function.

We need to write the above formula into the DAX formula editor and then we can create the new measure by pressing the OK button.

This will take the Course field from the StudentData table and concatenate its values together with a comma and space character as a delimiter.

Using Our New Measure

This new measure will appear listed in the PivotTable Fields window with all the other fields and we can use this new measure just like any of the other fields.

Measures can easily be identified from the data fields by the fx icon in from of the measure name.

We can click and drag the Course List measure into the Values area of our pivot table and this will produce a comma separated list of a students courses.

Conclusions

Pivot tables have been a feature in Excel for a long time and they can do a lot of great useful calculations.

They are limited though, especially when it comes to displaying text values in the Values area.

With the data model we get many new calculation options that regular pivot tables just don’t have, including concatenating text values to display in the Values area. This is definitely a feature worth exploring when regular pivot tables just won’t cut it.

Wrap text in Excel if you want to display long text on multiple lines in a single cell. Wrap text automatically or enter a manual line break.

Wrap Text Automatically

1. For example, take a look at the long text string in cell A1 below. Cell B1 is empty.

2. On the Home tab, in the Alignment group, click Wrap Text.

3. Click on the right border of the column A header and drag the separator to increase the column width.

4. Double click the bottom border of the row 1 header to automatically adjust the row height.

Note: if you manually set a row height (by clicking on the bottom border of a row header and dragging the separator), Excel does not change the row height when you click the Wrap Text button. Simply double click the bottom border of a row header to fix this.

5. Enter an extra-long text string in cell B1 and wrap the text in this cell.

Note: by default, Excel aligns text to the bottom (see cell A1).

6. Select cell A1.

7. On the Home tab, in the Alignment group, click Top Align.

Manual Line Break

To insert a manual line break, execute the following steps.

1. For example, double click cell A1.

2. Place your cursor at the location where you want the line to break.

3. Press Alt + Enter.

Note: to remove a manual line break, double click a cell, place your cursor at the beginning of the line and press Backspace.

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel – 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I need to type some notes at the bottom of a report. How can I make the words fill each line as if I had typed them in Word?

This paragraph needs to fit in A:G.

Strategy: You can use Fill, Justify. Follow these steps:

1. To have the words fill columns A through G, select a range such as A70:G85. Include enough extra blank rows in the selection to handle the text after word wrapping.
2. Select Home, Fill dropdown, Justify. (The Fill dropdown now appears in the Editing group of the Home tab. It often appears as a blue down-arrow icon.)

Results: Excel will rearrange the text to fill each row.

Gotcha: If you have a few words in bold in one cell, this formatting will be lost. Gotcha: If you later change the widths of columns A:G, you will have to use the Justify command again to force the data to fit. Gotcha: Do not use this method if any of your cells contain more than 255 characters. Excel will silently truncate those cells to 255 characters without any notice!

Select Fill Justify.

Alternate Strategy: You can also use a text box to solve this problem. You simply click the Text box icon on the Insert tab, draw a text box to fill columns A through G, and paste your text into the text box. You can then format the text box to hide its border: Select the text box and on the Drawing Tools Format tab, select Shape Outline, None.

Additional Details: Starting in Excel 2007, you can give a text box multiple columns. To do so, you select the text box. On the Drawing Tools Format tab, you click the dialog launcher icon in the bottom-right corner of the Shape Styles group to display the Format Shape dialog. In the left pane, you choose Text Box. Then you click on Columns and specify two columns, with separation between them of 0.1″.