Thursday, May 27, 2021

3 Ways to Swap Columns in Excel


Swapping or moving columns around in your data set can be a fairly simple exercise.  It's a simple step of cut and paste. But if you were to move or re-arranging a data set that has a lot of columns or you're working with someone that wants to see how it looks like this or that (multiple times) it becomes a burden.  This video covers three ways to move columns around.  The first two are using the mouse and cursor to move it around.  The last example uses the newer dynamic array function called SORTBY and gives you an effective and dynamic way to swap columns around and change it effortless through multiple times.

Sunday, May 23, 2021

Random List from Another Table - RAND() or RANDARRAY

If you wanted to sort a list in random order there a many ways this can be done in excel. If you have Office 365 the new dynamic array function of RANDARRAY combined with SORTBY provide a quick way to do this. If you don't have O365, then using the RAND() function along with the sort capability will give you this output. This video will also cover the detail on how the SORTBY and RANARRAY functions works later on in the video.

Thursday, May 20, 2021

Use the Forecast Sheet in Excel

Forecasting in Excel has become must easier with the Forecast Sheet feature (which became available in Excel 2016). If you wanted to do a quick forecast of sales or population data, Forecast Sheet will create a table and chart for you all in a few clicks of the mouse. It uses the Exponential Smoothing (ETS) algorithm and the note here is that it requires the timeline to be organized with a constant step between the different points (i.e., a monthly timeline with the values on the 1st of the month); so if you've got different dates, it's advised to aggregate raw detailed data to a common time series before you apply the forecast.

Sunday, May 16, 2021

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.

Sunday, May 9, 2021

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.

Sunday, May 2, 2021

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.

Sunday, April 25, 2021

Combine Worksheets & Keep Worksheet Names in Column in Power Query

Combining small tables in worksheets that have the same column structure and names is a simple task. But if you had to combine or append the tables from a workbook that had a LOT of worksheets (or tabs), that would be a mind numbing exercise. Also how would you know what worksheet name it came from? With the small worksheet example you could create a helper column and manually enter the worksheet name for those respective table. If there's 10 or more worksheets (30? 40?), that'll be painful and you might think about learning VBA to do this. Fear not because with a few clicks in Power Query, it can be done in less than 3 minutes.