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.

Wednesday, April 21, 2021

Use Descriptive Statistics Functions in Excel

Curious about statistics like mean, median or standard deviation? Want a feature that gives you some basic descriptive statistics in a couple clicks? This video will cover a basic overview of how to get those type of descriptive statistics with the Data Analysis command and the different functions you can use to get to the same for each individual statistic.