Tuesday, June 15, 2021

Google Sheets - Track Stocks with GOOGLEFINANCE

If you like to track your stock investments, Google Sheets has a function called GOOGLEFINANCE that helps you get delayed (~20 minute) stock price (or volume) information on numerous stocks in your portfolio. It can event track mutual funds and currency. You also have the option of getting historical information on those various stocks so you can do some anaylsis or comparisons of your portfolio. In this video I'll show you how to use the many ways of GOOGLEFINANCE to get this information and even show you how to create a small in cell chart to visualize the stock prices from 30 days or even 365 days to the current date.

Sunday, June 13, 2021

Convert Numbers to Roman Numerals

Converting numbers to Roman numeral is quite easy with the ROMAN function. But I'll spice this up with some other tricks to get a series of numbers. If you have Office 365, you can use the SEQUENCE function to get a matrix of numbers in a 10x10 grid. If you don't have Microsoft Office 365, I'll so another method to get this matrix. Also if you're interested in how to convert from Roman numerals to number, it's also just as easy with the ARABIC function.

Tuesday, June 8, 2021

Lookup Values from Different Workbook with IMPORTRANGE() - Google Sheets

Lookups in Google Sheets are fairly easy with the standard functions like LOOKUP, VLOOKUP or INDEX / MATCH. Within the same worksheet or different worksheet in the same workbook is straight forward. However if your looked up table or range is in a different workbook in Google Sheets, it take an additional function (IMPORTRANGE) to work. It's not so bad, so check out this video to see how it could work.

Sunday, June 6, 2021

Create a Pivot Table without Using the Pivot Table Feature (SORT & UNIQUE)

Pivot Tables are one of those great data analysis tools. But what if you wanted a simple cross tab (aka pivot table) without using the Pivot Table command in the ribbon. You could have done this before with various combinations of commands and use the CTRL+SHIFT+ENTER to create arrays. But in Office 365 there are two dynamic array functions (SORT & UNIQUE) that let you create a cross tab very easily. The UNIQUE function in it self is totally useful and I'll cover it in detail near the end of the vid.

Thursday, June 3, 2021

Split Comma Separated Value Ignore Comma In Brackets Excel (2 Excel Exam...

If you've tried to split or separate text string that's delimited by commas ( or semicolons) it's a straight forward process using the text to columns feature. However if you have some values that are enclosed in parenthesis or brackets and you don't want that text to be separated, it wouldn't work. There is a workaround that this video goes through. One is a VBA solutions provided from iliace at the mrexcel forum (https://www.mrexcel.com/board/threads...) and the other is an adjustment to the text with find/replace before using the text to column feature.

Sunday, May 30, 2021

Filtering Tables on Multiple Criteria in Excel

To filter a range of data just got a bit more interesting with the new Dynamic Array functions in Office 365. This video will cover the FILTER function which is currently only available in O365 Excel, but I'll also give an alternative way to do range / table filtering if you don't have Microsoft Office 365.

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.