Excel - Grabbing the Last 5 Values and Joining into a Single Cell | Dueling Excel - Episode 1364
2,064 views · Published 18 March 2011 · 7:13 · Indexed 29 October 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial - Grabbing the Last 5 Values and Joining into a Single Cell | Dueling Excel. Welcome back to another live dueling Excel podcast from MrExcel. In this episode, we are joined by Mike Girvin from Excel Is Fun at Highline Community College. We will be tackling a formula request from OBLUKMT on YouTube, who wants to know how to grab the last five values from each row and concatenate them into a single cell. To solve this problem, we will be using the INDEX function. By using INDEX, we can specify which row and column we want to retrieve data from. In this case, we will use the COUNTA function to determine the number of entries in each row and then go back four cells to get the last five values. We can also use the COLUMN function to automatically subtract the desired number of cells from the last cell in the row. However, we also wish that the CONCATENATE function would allow us to concatenate a whole range of cells together. Unfortunately, this is not currently possible, but we will be sure to suggest it to Microsoft as a Christmas present for all Excel users. Moving on, we will also explore another method using the INDEX function. This time, we will highlight the desired range of cells and use the COUNT function to determine the number of columns. By locking the last cell in the range, we can create a dynamic range that will automatically adjust as we copy the formula down. We will also use the ampersand symbol to add a space between each value in the concatenated cell. As we continue to discuss and debate the best way to solve this problem, we realize that there are countless Excel videos on YouTube, including non-Excel related ones. However, we are proud to be among the first Excel experts on the platform and we will continue to share our knowledge and expertise with our viewers. Thank you for tuning in to this dueling Excel podcast. Be sure to check out our other videos and subscribe to our channels for more Excel tips and tricks. Until next time, this is Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun, signing off. 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/ Table of Contents: (00:00) Introduction (00:17) Formula for grabbing last five values (00:27) Using INDEX function (00:40) Using COUNTA function (01:56) Concatenating the values (02:51) Using CONCATENATE function (03:01) Using INDEX and COUNT functions (04:29) Fixing the order of values (07:00) 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: Automating subtraction in Excel formulas Concatenating a range of cells in Excel Concatenating cells in Excel using ampersand Empirical evidence for the effectiveness of Excel formulas Excel add-in for concatenation Excel formula for grabbing last five values from each row and concatenating Locking cells in Excel using the $ symbol Using COUNTA function to count entries in a row Using INDEX function in Excel Using the COLUMN function in Excel Using the CONCATENATE function in Excel Using the INDEX function to return values in reverse order Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153044/ Today's Dueling Excel, with Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen [Episode #1364]looks at selecting specific entries in a row and then concatenating those entries in one cell.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920