Monday, October 3, 2022

Split Text between Upper and Lower Case in Excel

Separating text that should have been in two columns instead of one is easy to do if there is some kind of delimiter (i.e., comma or colon). But if it was two words like first name and last name that was combined; and the only way to make out the beginning vs. end is the case (Upper case vs. Lower case; like Bob Smith). There's actually a really easy way to do this and then there's the super nerdy formula combination way. I'll show both ways but show the formula way first.

Monday, September 19, 2022

Rent Split Calculator in Excel

Splitting the rent between roommates is not an easy task, especially if you all have got different preferences about what's important. In this video, we'll take a hard facts approach to apartment rent splitting by using square footage. One model will use the square footage of the whole unit but give specific consideration to the bedrooms and other model will pay attention only to the bedroom. There's not right approach, just the approach you all agree on. The beauty of this model is that it's done in Excel and when you look at different room sizes or the number of room mates sharing, you can adjust the numbers and the cost per person will recalculate.

Monday, September 12, 2022

Basic Stock Forecasting in Excel Warren Buffet Would Love

Investing in the stock market means trying to forecast or do some prediction on where the market will go. One way to do predictions is with regression, more specifically basic linear regression or simple linear regression. Applying simple linear regression to an individual stock may not work out, but for an index of stocks, that might work. This video will use the S&P500 index as an example of how we can use regression to forecast the index value in the future. As a bonus we can see what the Compounded Annual Growth Rate (CAGR) of the return is to get an idea of what an "annual" return could be based on almost a decade of holding the investment.

Tuesday, September 6, 2022

Are You Presenting Excel Data Wrong? See Why...

If you're looking for a way to make your data more meaningful, then you need to understand how to use charts in Excel. Whether you're trying to show your data in a more visually appealing way, or simply trying to spot trends that are hidden in numbers, charts can help. For mid size or large tables, data visuals should be a given, but even when you have a small table it's often a good idea to chart the data to see patterns. Charting data is not only an effective way to visualize the data but it can also help you spot patterns and trends that you may not have otherwise noticed. You'll find the saying that a picture says a thousand words will ring true once you finished viewing this video.

Monday, August 29, 2022

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.

Monday, August 22, 2022

Split Comma Separated Value Ignore Comma In Brackets Excel (2 Examples)

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/separating-commas-and-parenthesis.403833/) and the other is an adjustment to the text with find/replace before using the text to column feature.

Monday, August 15, 2022

2 Ways to Quickly Filter Unique Records From Table

When you get data from others, sometimes you find that you'll have to do some clean up. A common clean up activity is removing duplicate data or records from a table or range of data. Small tables are easy to do, just find the row and right click delete row. But if it's a lot of data, or this is something you do on a recurring basis, there's an easier way to do this. In this video, I'll show you two examples of how this can be done. One with the newer dynamic array function (available for M365) called UNIQUE and the other with the old school Advanced Filter feature.