Use the SORT Function

New and currently only available in Office 365 is the SORT function. It's part of the dynamic array functions in Microsoft 365 Excel. Prior to the SORT function you might have had to use a combination of other functions to perform sorting, turn your range of data into a table for sorting or use the custom sort functions. However using this SORT function does give flexibility in the use of other dynamic array functions.

Use a Cell or Filename Value for Column Values on Combined Files

If you wanted to get or extract a cell value and use it for input into values for a table column or category, it's an easy copy and paste. However if this is something you do on a recurring basis or have lots of worksheets to do this with, it would be a prime candidate for some automation to combine the files. VBA would be one way of doing this but if you're a VBA novice like me, it'll take some time. Enter Power Query. With a few clicks and less than 5 minutes in set up, you can have an automation on what was a manual task before. This video will cover two examples: (1) where the value is coming from the actual filename (2) where the value is coming from a common area in the worksheet that is consistent amongst the files.

Extract a Cell Value for Column Values

If you wanted to get or extract a cell value and use it for input into values for a table column or category, it's an easy copy and paste. However if this is something you do on a recurring basis or have lots of worksheets to do this with, it would be a prime candidate for some automation. VBA would be one way of doing this but if you're a VBA novice like me, it'll take some time. Enter Power Query. With a few clicks and less than 5 minutes in set up, you can have an automation on what was a manual task before.