Look Who it Was Random video

Excel - Transforming Headers into Functional Data Set in Excel - Episode 826

295 views · Published 9 January 2009 · 4:33 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Transforming Headers into Functional Data Set in Excel.

Welcome back to the MrExcel netcast where we tackle interesting Excel problems. Today, we have a spreadsheet with beautiful headers that give us department names, account numbers, and headings for FTE, NAME, and TOTAL. However, this makes it impossible to sort or create pivot tables. So, in this video, we will learn how to transform this spreadsheet into a functional data set.

To start, we will insert two new columns, one for POSITION and one for ACCOUNT NUMBER. We will then use the IF function to extract the account number from column D. This formula checks if the 5th character in column D is a "-", and if it is, it will return the value from column D. If not, it will return the value from the cell above. This will give us the same account number for each row until it reaches the next "-" in column D.

Next, we will use the same logic to extract the position from column D. This time, we will check if the 5th character is a "-", and if it is, we will return the value from column C. Otherwise, we will return the value from the cell above. We will copy these formulas down to the end of our data set and then convert them to values.

Now, we can sort the data set by column D, which contains the account numbers. This will group all the account numbers together, allowing us to easily delete them. We can also delete the rows with the word "NAME" and the rows with totals. Finally, we can delete the blank rows between the data and the totals. This will leave us with a clean data set that can be sorted and used to create pivot tables.

In tomorrow's netcast, we will learn how to calculate totals for each account using a formula instead of simple links. This will eliminate the need for another sheet to grab the totals. Thank you for watching and be sure to join us for more Excel tips and tricks 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/


It is frustrating when section headers contain data that applies to all records in that section. You really want to get that header data down onto every row in the section. Episode 826 shows you one method for doing this.

Table of Contents:
(00:00) Introduction
(00:24) Headers and Data
(00:39) Difficulty with Sorting and Pivot Tables
(00:54) Solution: Inserting New Columns
(01:07) Attacking the Account Number
(01:33) Copying Formulas
(02:03) Sorting the Data Set
(02:32) Changing Formulas to Values
(02:44) Sorting by Column D
(03:34) Creating a Clean Data Set
(03:51) Formatting Issues
(04:12) 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:
Automatic custom list item selection in Excel tables
Boosting Excel with MrExcel 2022 book
Center across selection in Excel tables
Excel table merge cells
Extend data range formats and formulas in Excel
Formula automatically copies in Excel
Insert a row in Excel
Merged cells in Excel
Table growth with custom list headings in Excel
Troubleshooting formula copying in Excel
Vertical merged cells in Excel
Workaround for merged cells in Excel

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

More from this channel