Google Sheets - Create a Static Timestamp

If you're doing some project management and don't have a large budget, you can use Google Sheets as a poor man's project management tool. One concept in most project management timelines is the concept of estimated dates and actual dates. Usually you'd input the dates manually for these dates, but it would be nice to have the actual date entered "auto-magically" based on the value changes of another cell (i.e., when a percent complete is entered). But you don't want the actual dates to change after the initial value is triggered in that cell; you want the time stamp that updates only if data is entered in some cell. This video covers a workaround using iterative calculations and sort of breaks a rule in spreadsheet design where you don't want circular references. In this case, we'll have to change the Google Sheet setting to allow for it. See the video to learn how to do this.

Append or Combine Tables | Same File | Many Worksheets

Excel can be used to to append tables in multiple different worksheet tabs in the same workbook file. But there may also be a value that you'd want to preserve in those worksheets to use for a new column value in the combined output. The tables are all structured the same and have the same column headings but the worksheet tabs have the generic name (i.e, Sheet1 or Sheet2). The common field in each worksheet can be used as a value that categorizes the tables. One worksheet will be California data and another worksheet would be New York data. If these were small data sets, then copy/paste and some column movements would suffice. However if these are large tables, you do this on a recurring basis or you had 10 or 20 worksheets in the file, it would be a chore. This is a great opportunity to use Power Query and this video will show how. This is a tip I learned from Gil Raviv's book. Get the book at https://amzn.to/2Zj9ADY

Google Sheets - Create a Candlestick Chart

A candlestick chart is a multiple purpose graph that can provide information about stock history (i.e., open, close, high and low prices) in one consolidated view. Google Sheets give you a way to view a stock history via the ticker symbol in a table and then put it into a candlestick chart. In addition, you can set it up to dynamically pull in different stocks as you enter the new ticker symbol in. In this video, I'll cover how to cover how to create a basic candlestick chart and how to create a box plot (or box and whiskers) with the candlestick chart feature. Caveat: It's not a full fledge box plot, more of a pseudo box plot because it doesn't include a median statistic. Lastly I'll cover how to create a dynamic stock history table and the candlestick chart that pulls it's data from that table.

Combine or Append Tables | Same Workbook | Multiple Worksheets

You want to use Excel to combine tables in different worksheet tabs in the same workbook file. You also want to preserve the title in those worksheets to use for a new column value in the combined output. Luckily the tables are all structured the same and have the same column headings. The catch is that the worksheet tabs are the generic name (i.e, Sheet1 or Sheet2) and there is a common field that houses the value that categorizes the tables. One worksheet will be California data and another worksheet would be New York data. If these were small data sets, then copy/paste and some column movements would suffice. However if these are large tables, you do this on a recurring basis or this is something you get from someone else on a recurring basis, it would be a chore. This is a great opportunity to use Power Query and this video will show how. This is a tip I learned from Gil Raviv's book. Get the book at https://amzn.to/2Zj9ADY

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.