Microsoft Excel Tutorial: Expanding two cells with a year range into one row for each year in Excel.
In episode 195 of the Dueling Excel podcast, Bill Jelen and Mike Girvin tackle a question from Erno V on YouTube. The challenge is to expand a column of data containing ranges of years, such as "2000-2004," into individual years. Bill approaches this using VBA, while Mike opts for Power Query.
Bill's VBA solution involves looping through the data, checking for single years or ranges, and then creating separate rows for each year within the range. He demonstrates the code to achieve this expansion.
Mike, on the other hand, employs Power Query to create a dynamic list of years within the cell itself. He starts by converting the data into an Excel table and then uses Power Query to create a list from the start and end years. Mike explains the Power Query steps in detail, emphasizing how Power Query allows lists within cells, unlike the Excel worksheet.
Additionally, Bill briefly showcases an alternative solution using the LET formula in Excel. This formula extracts the start and end years, calculates the count of years, generates a sequence of years, and combines them with the name to achieve the desired result.
Both approaches—VBA, Power Query, and the LET formula—offer viable methods to expand the range of years into individual years, allowing users to choose the one that suits their preferences and requirements.
You have a column of cells with a year range, such as 2016-2020. How can you break those years out to new rows, so you have 2016, 2017, 2018, 2019, 2020. This Dueling Excel video uses VBA, Power Query, and the LET function to solve the problem.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#excelhacks
#excelchallenge
#microsoft365
#walkthrough
#excelformula
#excelformulasandfunctions
#powerquery
This video answers these common search terms:
how to expand multiple cells in excel
how do you expand in excel
how do you expand a list in excel power query
Table of Contents
(0:00) Problem Statement: Create sequence of years from starting and ending date
(0:20) Cell contains a range of years like 2001-2004
(1:00) Bill: VBA solution to expand the data
(2:39) Mike: Power Query solution to create a list for each year range
(3:56) Creating a List in Power Query
(4:40) Excel LEFT is Text.Start in Power Query
(5:24) Excel RIGHT is Text.End in Power Query
(5:45) Power Query Expand to New Rows
(6:40) LET formula to expand the data
(7:59) Clicking Like really helps with the algorithm
In episode 195 of the Dueling Excel podcast, Bill Jelen and Mike Girvin tackle a question from Erno V on YouTube. The challenge is to expand a column of data containing ranges of years, such as "2000-2004," into individual years. Bill approaches this using VBA, while Mike opts for Power Query.
Bill's VBA solution involves looping through the data, checking for single years or ranges, and then creating separate rows for each year within the range. He demonstrates the code to achieve this expansion.
Mike, on the other hand, employs Power Query to create a dynamic list of years within the cell itself. He starts by converting the data into an Excel table and then uses Power Query to create a list from the start and end years. Mike explains the Power Query steps in detail, emphasizing how Power Query allows lists within cells, unlike the Excel worksheet.
Additionally, Bill briefly showcases an alternative solution using the LET formula in Excel. This formula extracts the start and end years, calculates the count of years, generates a sequence of years, and combines them with the name to achieve the desired result.
Both approaches—VBA, Power Query, and the LET formula—offer viable methods to expand the range of years into individual years, allowing users to choose the one that suits their preferences and requirements.
You have a column of cells with a year range, such as 2016-2020. How can you break those years out to new rows, so you have 2016, 2017, 2018, 2019, 2020. This Dueling Excel video uses VBA, Power Query, and the LET function to solve the problem.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#excelhacks
#excelchallenge
#microsoft365
#walkthrough
#excelformula
#excelformulasandfunctions
#powerquery
This video answers these common search terms:
how to expand multiple cells in excel
how do you expand in excel
how do you expand a list in excel power query
Table of Contents
(0:00) Problem Statement: Create sequence of years from starting and ending date
(0:20) Cell contains a range of years like 2001-2004
(1:00) Bill: VBA solution to expand the data
(2:39) Mike: Power Query solution to create a list for each year range
(3:56) Creating a List in Power Query
(4:40) Excel LEFT is Text.Start in Power Query
(5:24) Excel RIGHT is Text.End in Power Query
(5:45) Power Query Expand to New Rows
(6:40) LET formula to expand the data
(7:59) Clicking Like really helps with the algorithm
Transcript of the video:
Hey, it's time for another Dueling Excel podcast.
I am Bill Jelen from MrExcel.
I will be joined by Mike Girvin from ExcelisFun.
This is our episode 195.
Expand something like 2016-2020 to five rows.
Alright Mike. Hey, today's question, and I actually have several different ways that I can solve this this, is from Erno V on YouTube.
he has a column of data that has a range of years.
Like 2000-2004.
Although some of them are just a single year.
He wants to be able to convert that to multiple years.
For example it was 2000-2004, you would get 2000, 2001, 2002, 2003, and 2004.
The data set that I am using here is name in column A and then the years in column B.
I want to expand that.
We want to have three years for Andy.
Andy Andy Andy 2013 2014 2015.
Although there are many fun ways to do this.
I can think of LET and Power Query.
My knee-jerk reaction every single time is just straight to VBA.
In VBA we separate this out.
We loop from this to that.
If there is nothing then we just grab Barb 2015 and copy it over.
So Alt+F11.
Here's the code.
Select the VBA worksheet.
Figure out the last row of data we have today.
The next row is where I am going to write the answers to.
So I start in row 2 because I have headings in row 1.
Then loop for i equals 2 to FinalRow.
So we are looping through each individual row.
Grab the name from column one.
Grab the range from column two.
If the range is a length of four then it's just simple.
Write the data to column D and column E. D is the fourth column.
E is the fifth column.
This name and zero plus this Range to convert it to a number.
Then increment the Next Row.
But in the case where it's not a four character year, that means we are going to grab the first year.
The LEFT of this range comma four.
The last year is the MID of this range comma six length of four.
Add zero to both of those to convert them from text.
And then a second loop For j equals first year to last year.
And then write that out to column D the name.
To column E the year.
Tthen increment next row.
Alright so it's amazing how fast this is to run.
We have this data here.
I click expand by year.
And bam!
There we are.
I know there's some folks out on my YouTube channel who hate when I use VBA.
But, for me it's just the fastest and easiest way to go.
Alright Mike, let's see what you have.
[ Mike Girvin ] Thanks MrExcel.
Don't like VBA?
A simple click and Bam. That is lightning fast.
Alright, so for me when I see this I immediately think of Power Query and here's why.
Inside of Power Query I can take that start year and end year and create a list that actually lives in the cell.
And then I simply expand and I get the name repeated for every one of the years.
Well in the Excel spreadsheet we're not allowed to have objects like lists or tables but in Power Query it's no problem.
So I first need to convert this to an Excel table so I use Ctrl+T and Enter.
I immediately go up to Table Design and give it a smart name.
Something like Names Year Start and Enter.
Now I come up to Data, Get and Transform.
And there it is, From Table or Range.
That opens up the Power Query editor.
I am going to rename this and Enter.
I don't need Change Type.
But now I want to create an extra column and from the start and end create a list.
So I come up to Add Column.
Choose Custom Column.
We'll call the new column Year.
Here is what's so beautiful about Power Query.
Right in that cell. Right there.
I can create a list.
Now we can use list syntax.
Open curly bracket.
And if I had some number like two thousand and thirteen.
If I wanted to increment I use the dot dot operator.
That says, hey, I am starting at some number and I want you to increment all the way to the end number.
Close curly bracket.
Now this is hard-coding this list in.
So every single cell will get the same years.
But when we click OK, we can see, sure enough, we are allowed to have a list in a cell.
Which is very different than the Excel worksheet.
If that was the same year for all of these then we'd simply click Expand.
But now we need to make this dynamic.
So up in the Formula Bar, I am going to double-click that number.
And we want to use the Excel worksheet function LEFT.
But that doesn't exist in Power Query.
So we use Text.Start.
Open parentheses.
The text is actually going to be the field name here.
We access that field name by using the square brackets.
Years.
And then the second argument, comma.
Well, the years are always four.
Now the problem with that is it's text so we simply wrap on the outside Number.From.
Remember Power Query is data-type specific.
So we have to create a number from text.
So for each row in this table.
Now that will get the begin year as the first part of our list.
Now I am going to copy that.
And for the end number, Ctrl+V.
And now I simply instead of Start use End. And that will work when I hit Enter.
Now, I have my dynamic list including for the single year 2015 to 2015.
It is a list of one year.
Now I can use Expand, Expand to New Rows.
There's Andy and all of the years.
I am going to hold Ctrl.
Click Name.
Right-click.
Remove Other Columns.
We'll add a data type.
I am going to say Whole Number.
Sometimes you want it as text but I am going to say whole number.
And text.
There's our steps including that formula.
Now I can come to Home, Close and Load.
Close and Load To.
I am going to say the Existing Sheet.
Right there.
Click OK.
Man, that is so cool to be able to have a list in a cell.
Alright I will throw back to MrExcel.
[ Bill Jelen ] Wow, Mike that is so cool.
I love the way that you explain that Power Query lets us have a list right in the cell.
And it's almost like an array constant there that you created.
Although the dot dot is different than Excel.
And then how you were able to create a formula for the start year and end year.
So we have VBA.
We have Power Query.
And I know that some of our viewers who watch this will point out that there's a LET formula that we could use.
So what I have here is =LET.
The start year is equal to the left of B2 comma four.
That creates text so I add zero to it.
End year is the right of B2 comma four.
Again add zero.
And then your count is end year minus start year plus one.
And then a SEQUENCE of one row. YearCount columns.
Starting at the start year.
And what that will do?
That will generate three numbers 2013, 2014, 2015.
Concatenate the A2 with that and we get Andy 2013, 2014, 2015.
If this would change.Let's say to 2019. You see that it automatically expands out like that.
Alright so now I get this weird range here.
That's not formatted correctly.
But then using the INDEX function.
Setting it in the UNIQUE and SORT.
That gives me a single column with all of these values.
And then simply the left of J2 hash.
We take everything the length of J2 hash minus 5.
That gets us the name because the year is always the last four plus the space.
And then the right of J2 hash.
This is a ridiculous way to go.
I am just putting it out there for completeness.
Because you never know if ten years from now someone will come along and do a reaction video on this dual number 195.
And say, well I wonder why they didn't include LET?
Alright well hey I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and ExcelisFun.
I am Bill Jelen from MrExcel.
I will be joined by Mike Girvin from ExcelisFun.
This is our episode 195.
Expand something like 2016-2020 to five rows.
Alright Mike. Hey, today's question, and I actually have several different ways that I can solve this this, is from Erno V on YouTube.
he has a column of data that has a range of years.
Like 2000-2004.
Although some of them are just a single year.
He wants to be able to convert that to multiple years.
For example it was 2000-2004, you would get 2000, 2001, 2002, 2003, and 2004.
The data set that I am using here is name in column A and then the years in column B.
I want to expand that.
We want to have three years for Andy.
Andy Andy Andy 2013 2014 2015.
Although there are many fun ways to do this.
I can think of LET and Power Query.
My knee-jerk reaction every single time is just straight to VBA.
In VBA we separate this out.
We loop from this to that.
If there is nothing then we just grab Barb 2015 and copy it over.
So Alt+F11.
Here's the code.
Select the VBA worksheet.
Figure out the last row of data we have today.
The next row is where I am going to write the answers to.
So I start in row 2 because I have headings in row 1.
Then loop for i equals 2 to FinalRow.
So we are looping through each individual row.
Grab the name from column one.
Grab the range from column two.
If the range is a length of four then it's just simple.
Write the data to column D and column E. D is the fourth column.
E is the fifth column.
This name and zero plus this Range to convert it to a number.
Then increment the Next Row.
But in the case where it's not a four character year, that means we are going to grab the first year.
The LEFT of this range comma four.
The last year is the MID of this range comma six length of four.
Add zero to both of those to convert them from text.
And then a second loop For j equals first year to last year.
And then write that out to column D the name.
To column E the year.
Tthen increment next row.
Alright so it's amazing how fast this is to run.
We have this data here.
I click expand by year.
And bam!
There we are.
I know there's some folks out on my YouTube channel who hate when I use VBA.
But, for me it's just the fastest and easiest way to go.
Alright Mike, let's see what you have.
[ Mike Girvin ] Thanks MrExcel.
Don't like VBA?
A simple click and Bam. That is lightning fast.
Alright, so for me when I see this I immediately think of Power Query and here's why.
Inside of Power Query I can take that start year and end year and create a list that actually lives in the cell.
And then I simply expand and I get the name repeated for every one of the years.
Well in the Excel spreadsheet we're not allowed to have objects like lists or tables but in Power Query it's no problem.
So I first need to convert this to an Excel table so I use Ctrl+T and Enter.
I immediately go up to Table Design and give it a smart name.
Something like Names Year Start and Enter.
Now I come up to Data, Get and Transform.
And there it is, From Table or Range.
That opens up the Power Query editor.
I am going to rename this and Enter.
I don't need Change Type.
But now I want to create an extra column and from the start and end create a list.
So I come up to Add Column.
Choose Custom Column.
We'll call the new column Year.
Here is what's so beautiful about Power Query.
Right in that cell. Right there.
I can create a list.
Now we can use list syntax.
Open curly bracket.
And if I had some number like two thousand and thirteen.
If I wanted to increment I use the dot dot operator.
That says, hey, I am starting at some number and I want you to increment all the way to the end number.
Close curly bracket.
Now this is hard-coding this list in.
So every single cell will get the same years.
But when we click OK, we can see, sure enough, we are allowed to have a list in a cell.
Which is very different than the Excel worksheet.
If that was the same year for all of these then we'd simply click Expand.
But now we need to make this dynamic.
So up in the Formula Bar, I am going to double-click that number.
And we want to use the Excel worksheet function LEFT.
But that doesn't exist in Power Query.
So we use Text.Start.
Open parentheses.
The text is actually going to be the field name here.
We access that field name by using the square brackets.
Years.
And then the second argument, comma.
Well, the years are always four.
Now the problem with that is it's text so we simply wrap on the outside Number.From.
Remember Power Query is data-type specific.
So we have to create a number from text.
So for each row in this table.
Now that will get the begin year as the first part of our list.
Now I am going to copy that.
And for the end number, Ctrl+V.
And now I simply instead of Start use End. And that will work when I hit Enter.
Now, I have my dynamic list including for the single year 2015 to 2015.
It is a list of one year.
Now I can use Expand, Expand to New Rows.
There's Andy and all of the years.
I am going to hold Ctrl.
Click Name.
Right-click.
Remove Other Columns.
We'll add a data type.
I am going to say Whole Number.
Sometimes you want it as text but I am going to say whole number.
And text.
There's our steps including that formula.
Now I can come to Home, Close and Load.
Close and Load To.
I am going to say the Existing Sheet.
Right there.
Click OK.
Man, that is so cool to be able to have a list in a cell.
Alright I will throw back to MrExcel.
[ Bill Jelen ] Wow, Mike that is so cool.
I love the way that you explain that Power Query lets us have a list right in the cell.
And it's almost like an array constant there that you created.
Although the dot dot is different than Excel.
And then how you were able to create a formula for the start year and end year.
So we have VBA.
We have Power Query.
And I know that some of our viewers who watch this will point out that there's a LET formula that we could use.
So what I have here is =LET.
The start year is equal to the left of B2 comma four.
That creates text so I add zero to it.
End year is the right of B2 comma four.
Again add zero.
And then your count is end year minus start year plus one.
And then a SEQUENCE of one row. YearCount columns.
Starting at the start year.
And what that will do?
That will generate three numbers 2013, 2014, 2015.
Concatenate the A2 with that and we get Andy 2013, 2014, 2015.
If this would change.Let's say to 2019. You see that it automatically expands out like that.
Alright so now I get this weird range here.
That's not formatted correctly.
But then using the INDEX function.
Setting it in the UNIQUE and SORT.
That gives me a single column with all of these values.
And then simply the left of J2 hash.
We take everything the length of J2 hash minus 5.
That gets us the name because the year is always the last four plus the space.
And then the right of J2 hash.
This is a ridiculous way to go.
I am just putting it out there for completeness.
Because you never know if ten years from now someone will come along and do a reaction video on this dual number 195.
And say, well I wonder why they didn't include LET?
Alright well hey I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and ExcelisFun.
