Look Who it Was Random video

Excel - Excel Data Formatting: Splitting a 2-Field Column for Pivot Table Analysis - Episode 702

565 views · Published 18 February 2009 · 3:04 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel Data Formatting: Splitting a 2-Field Column for Pivot Table Analysis.

Welcome back to the MrExcel netcast where we explore all things Excel. In today's episode, we'll be tackling a common data formatting issue - the 2-field column. This can be a major headache when trying to perform data analysis, but fear not, we'll show you how to clean up your data and make it pivot table ready.

As we dive into our data set, we can see that there are numerous problems that need to be addressed. Blank rows and columns, inconsistent data in column A, and a mix of region and model information all make it difficult to work with. But don't worry, we'll walk you through the steps to fix these issues and get your data in tip-top shape.

The first step in cleaning up our data is to address column A. We want to split it into two columns - one for models and one for regions. To do this, we'll use the "Insert" function and add two new columns. Then, we'll use a formula to determine which data belongs in the model column and which belongs in the region column. This will make it much easier to work with our data in the future.

Once we have our data split into two columns, we'll need to fill in all the blank cells in column B. This can be a tedious task, but we have a trick up our sleeve to make it quick and easy. We'll show you how to use the "Paste Values" function to quickly fill in all the blank cells with the correct data.

And just like that, we've fixed the problem with our 2-field column and our data is now ready for analysis. Don't forget to join us for our next netcast where we'll continue to explore the wonderful world of Excel. Thanks for watching and we'll see you next time on the MrExcel netcast.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


In today's podcast, we start with a data set where column A contains both Region and Model information. In Episode 702, I'll use formulas to split that data into two columns.

Table of Contents:
(00:00) Issues with data set
(00:28) Blank rows and columns
(00:52) First step: fixing column A by creating two columns
(01:06) Identifying models and regions
(01:33) Fill blanks with data from above
(02:47) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Cleaning and formatting data in Excel
Converting one column into two columns in Excel
Copying values in Excel
Excel tips and tricks for data manipulation
Filling blank cells in Excel
Formatting data for pivot tables in Excel
Freezing rows in Excel
Identifying models and regions in a data set
MrExcel netcast episodes on data cleaning and analysis
Paste Values feature in Excel 2003
Using the LEFT function in Excel
YouTube video on data analysis with pivot tables


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152196/

More from this channel