No more COMMA DRAMA! In this video I will show you how to change the default comma delimiter in Excel when making .csv (comma separated value) files. Sometimes text fields in your file have comments that you need to preserve in a flat file. This means you have to change the delimiter. Watch the video for a demonstration and keep reading for a quick reference.
I should note that as of the time of this post I am using Windows 10 Home.
Search for “Set Regional Format”
Select Additional date, time & regional settings
Select Additional Settings
Then in the List Separator box, change the comma to a pipe.
When you save your .csv file in Excel, it will now be pipe delimited instead of comma delimited.
In this video I help you solve the dual problems of selecting a random value from an Excel list and selecting a number of random rows from a range of data in Excel. At times when I am generating a data-set to use in my video tutorials, I want to select a random selection of rows. Typically, because my data values are clumped together and are too similar to the data on preceding and subsequent rows.
SELECTING A RANDOM VALUE FROM A LIST
Enter three Excel formulas to save the day for selecting a random value from a list:
The INDEX function returns a value or the reference to a value from within a table or range.
In the screenshot above notice that the ROWS() function returns the value of 20, which corresponds to the number of names listed in the cell range of A6 to A25.
The RANDBETWEEN() function generated a random number between 1 and the value returned from ROWS() (i.e., 20). In this case, RANDBETWEEN() combined with ROWS() returned a value of 3.
By combining the results from the first 2 functions, the INDEX() function searches our list and returns the value of the 3rd cell in the list (i.e., Flor McCard) because the RANDBETWEEN() function returned a value of 3.
When we put it all together it looks like the following:
Use the unpivot functionality in Power Query (a free Excel add-in) to easily turn your cross-tabbed data into a more normalized structure. The normalized data structure will grant you the flexibility to create additional analyses in a more efficient manner.
Most likely you have a love/hate attitude towards spreadsheets. This highly useful tip will make you fall back in love with Excel. Microsoft does a great job at providing a solid add-in that makes fuzzy lookups relatively easy to perform.
Are there more powerful approximate string matching tools out there? Of course. But if you’re using Excel, this tool should be used before applying more complicated methods.
Everyone loves visualizations but sometimes you have to roll up your sleeves and clean up the data.
If you’ve ever had to put together a quick timeline to share with someone without the need to resort to full blown Microsoft Project then you will find this video helpful. I will show you how to create a very simple but effective Gantt chart that will satisfy your inner project manager. Definitely keep this tip in your Excel toolkit.
If you’re interested in Business Intelligence & Tableau please subscribe and check out my videos either here on this site or on my Youtube channel.