Google Sheets - Separate or Split Text on Comma, Space or Other Delimiter

When you get a list of values that are separated by comma, semi-colons or other delimiters, you might want to separate the values into it's own cell whether it's across columns or down the rows. In Google Sheets this video will cover two ways to do this; one with the text to column command in the file menu and the second with the SPLIT function.

Numbering Groups or Categories of Data Records

If you had data in a group or category, you might want to number them for counts or ordering. In Excel this can be done with a COUNTIF function or if it's a small table you can manually do this. However if this is something you do on a recurring basis, have to do with large tables or it's a step in part of your data cleaning process you might want to use Power Query. To number rows by grouping in Power Query uses the Index function (but it's not that complicated). It's an exercise that will partition out the categories by using the group function and then applying an index number to each of the groups. This video is short so it must not be too hard to do, so check it out.

Google Sheets - Create a QR Code

Creating a QR (Quick Response) code may sound hard, but it's actually easy to do with Google Sheets. You'd have to use the link from the Google developer site (https://developers.google.com/chart/infographics/docs/qr_codes) and use the required parameters in addition to using the IMAGE function in Sheets. There are two type of QR codes: static & dynamic. The difference is that a static QR code will not change. For example, once the static QR code is created for a the destination link will always be the same. However for a dynamic QR code, the destination link can change. This would be useful if you didn't want to change the QR code, but once scanned it can take you to a different link in the future. The use case would be restaurant menus or some events on the web. You'd want your customers to scan the same QR code now to go to a site, but in the future that same QR code could go to an updated menu or future event on a website. In this video, I'll show you how to create a static and dynamic QR code.

Find the Difference between Two Lists

If you've got two lists or table to compare, most of the time you can use the equal sign to compare the cell values. But sometimes there are complications like having more than one value, values (text or string values) are separated by delimiters (e.g., commas, semicolons, etc.) or the values are not in the "correct" order. Doing the simple cell to cell comparison is not applicable and that's when you can use Power Query to help compare values.

Google Sheets - Finding Duplicate Values

When you get a table or list from others or some systems, you might get duplicate records. If you wanted to find those duplicates whether it be in a one column table or a multiple column table, it's fairly easy to do within Google sheets. I'll show a couple of ways this can be done with formulas and with a Pivot Table. Also if you wanted to just delete duplicate records ( and didn't want to bother with the analysis of which records is a duplicate), closer to the end of the video I'll show the command to do this quickly.

Extract Values from Column based on Another Column

There are times when you get data from someone that's not in a spreadsheet but looks like it came from one. The file you get may be a text file, but it looks like it's almost in some table format and had some calculations. You'd want to take the data or content from the file and do further analysis, so that would involve putting it into Excel. If it was a small file, it's a simple copy and paste, but if it was a large file it would take a long time to copy/paste all that data. You can actually bring it into a spreadsheet but you'd first need to see if there were some patterns. Hopefully if someone gave you a text file that seems to have calculations in it, the file would have come from some application that did the calculation and uniformly had the output in some repeating format (like some dash marker to indicate a total row was next) then you could extract data based on a previous row. This video will show how to use values from one row to find a pattern, use some conditions to compare and extract it to make a new column to help make a proper table to support further analysis

Google Sheets - Create a Gauge or Speedometer Chart

Gauge charts aka Speedometer charts are one of those fancy looking charts that try to represent a metric with the car dashboard analogy. This dashboard analogy is good in small doses because it's preferable to have something similar to a basic car dashboard as opposed to airplane cockpit dashboard. Thankfully creating a gauge chart in Google Sheets is fairly straight forward and easy to do. So buckle up and check out the video.