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.

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.

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.

Create a Dynamic Chart Title Sourced from Pivot Table & Slicer

Pivot Tables are one of the best analytical tools in Excel. To enhance and better present the data / information to your audience, you'd need to have a chart to visualizes the data. You may also have a meaningful title, but what if you want to change the Pivot Table? You'd think that you might need to manually change the title to reflect the pivot table chart. Nope! You can use slicers and an additional table to create dynamic text to put into your chart title.

Create Four Column Layout in SharePoint

The available section layouts in modern SharePoint is a three column layout. But what if you what to have more columns, like a 4 column layout? There's actually an option to do this by using the the vertical section option when adding a section to a page. There's some limitations to the view or design of the whole page, but if you want a four column layout on your Share Point page, this is a nice workaround for your design.

Create Custom Sort Order Pivot Table

Did you know you could have a custom sort order for pivot tables? You can customize a sort order when using the drop down sort features to enable a sort like bronze, silver and gold. Or in this example green, blue or diamond; which corresponds with ski slope difficulty in some resorts. You'll have to customize a sort order but it's easy.

Quickly Calculate Investment Return

If you're given an scenario where you have the present value of an investment and a future value of that investment with not periodic investments for five years, could you figure out a rate of return per year for that investment? There's a common business concept of Compounded Annual Growth Rate (CAGR) and and there's an Excel function called RRI that will help you calculate it