Download Excel File: https://ift.tt/2lWBWDr
Learn how to create a report that shows each student by quarter with a list of classes in a single cell. See many of the new Excel 365 Functions like: SORTBY, UNIQUE, FILTER, TEXTJOIN and the ROWS Function. See how we can combine Spilled Arrays formulas and Old School Arrays Formulas together to make a report that automatically updates when new data arrives.

View on YouTube

In this video, learn how to use different methods to Split names in Excel (i.e., separate first, middle and last name in Excel).

There are different ways you can manipulate text data in Excel. And one of the common things people do in Excel is to manage the names datasets.

Here are the methods covered in this video to split names into first and last names:
— Text to Columns
— Find and Replace
— Flash Fill
— Formulas

All these methods work great and are quick and easy.

With the first three methods, the result you get is static. This means that if your names data changes, then the resulting data wouldn’t change.

The last method in this video shows how to split names using formulas. With formulas, you get a result that is dynamic. This means that if you change the original dataset, the resulting data automatically updates.

Text to Columns method uses a delimiter to separate the first and last name. The delimited could be a space character or any other delimiter – such as a comma.

Find and Replace method uses wildcard characters to find and replace part of the name. For example, if you want to remove the last name and only keep the first name, you can do that with Find and Replace.

Flash Fill is one of the easiest methods to split names in Excel. All you have to do si type in a few cells and it automatically identifies a pattern and gives you the same result for all the cells. Note that Flash Fill is available only in Excel 2013 and later versions.

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel. It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #ExcelTutorial

View on YouTube

Vote for the improvement to Filtering:
https://ift.tt/2l17Fms

While thinking about the Filter problem, I decided to check to see if the data profile improvements had been added to Power Query in Excel, and they are! You can now see how many distinct and unique items there are in each column.

View on YouTube

Download Zipped Folder with all files: https://ift.tt/2Va4whq
Learn how to import a text file with student data, then create a report that shows each student by quarter with a list of classes in a single cell. See how to edit the query when a new Text file arrives in two different ways: 1) Edit Source Step in the Query Editor, 2) Build a Parameter Query so we can list the file path in the Excel Sheet.
Learn:
1. (00:01) Introduction
2. (00:52) Build Query to import text file and list student classes
3. (05:12) Edit Source Step in the Query Editor
4. (06:13) Build a Parameter Query so we can list the file path in the Excel Sheet
5. (10:25) Summary

View on YouTube