Skip to main content

Flash Fill

A series of names appears in column A. The heading in column B indicates you want first initial and last name. Type the desired result in B2. Select the empty cell B3.

Type the first initial in B3. Excel sees what you are doing and “grays in” a suggested result.

As soon as you start to type the first initial in B3, Excel "greys in" the answers for the rest of the column using Flash Fill. Press Enter and the results will become permanent.

Press Enter to accept the suggestion. Bam! All of the data is filled in.

Look carefully through your data for exceptions to the rule. Two people here have middle initials listed. Do you want the middle initials to appear? If so, correct the suggestion for Dennis P. Jancsy in cell B4. Flash Fill will jump into action and fix Martha K. Wendel in B9 and any others that match the new pattern. The status bar will indicate how many changes were made.

In the above case, Excel gurus could figure out the formula. But Flash Fill is easier. In the example shown below, it would be harder to write a formula to get the last word from a phrase that has a different number of words and more than one hyphen.

In this case, column A contains three fields. An account number, the account name, a hypen, and then the industry in lower case. Type the first industry in B2, using Proper Case.

Flash Fill makes this easy. Go to cell B3 and press Ctrl+E to invoke Flash Fill.

Use Flash Fill and the rest of the industries are filled in.

Note

Flash Fill will not automatically fill in numbers. If you have numbers, you might see Flash Fill temporarily “gray in” a suggestion but then withdraw it. This is your signal to press Ctrl+E to give Flash Fill permission to fill in numbers.

Labels:

Comments

Post a Comment

Popular posts from this blog

DAX Language - Data Analysis Expression

The DAX language was created specifically for the handling of data models, through the use of formulas and expressions. DAX is used in several Microsoft Products such as Microsoft Power BI, Microsoft Analysis Services and Microsoft Power Pivot for Excel. Below are the types of Dax functions  1. Aggregate  2. Date and time 3. Filter 4. Financial  5. Information  6. Logical 7. Maty and trig 8. Other 9. Parent and child 10. Relationship Management  11. Statistical  12. Table manipulation  13. Text 14. Time intelligence  From the above list of functions 3 types of functions are basic and commonly used, those are Aggregate , Logical and Date and time. Other important entities which are used with the above function are as follows  1. Operators  Example -  ( ), + , Not, &, =, < >, || 2. Statements  Define , Evaluate,  Order by, Return, Var 3. Data Types Binary, boolean,  Currency,  date time, decimal, integer,...
Post a Comment
Read more

CAGR Dax Measure

CAGR stands for  C ompound  A nnual  G rowth  R ate.  It describes the rate at which an investment would have grown over several years if it had grown at the same rate every year on a compounding, rather than simple, basis.   The CAGR metric is calculated using the following formula: If we were to fit this entire formula into a single measure it may get messy and confusing for other users, so let’s step it out. We will need to create several measures to calculate the individual pieces the CAGR formula.  Of course, this is not the only way to calculate CAGR in Power Pivot, but this is the way we’ve decided to go about it.  So, let’s break it down, we will need the following measures to calculate CAGR: A measure to retrieve the First Year in the data set A measure to retrieve the Last Year in the data set A measure to calculate the Number of Years between the First and Last Years in the data set A measure to aggregate the total sales in the data set...
Post a Comment
Read more

40 Power Query Editor features in Power BI

40 Power Query Editor features in Power BI along with examples: 1. Filter Rows: Remove rows based on conditions. Example: Remove rows with a null value in the "CustomerName" column. 2. Remove Duplicates: Eliminate duplicate rows. Example: Remove duplicate entries based on the "OrderID" column. 3. Sort Rows: Arrange rows in ascending or descending order. Example: Sort data by "Date" column in descending order. 4. Replace Values: Substitute one value with another. Example: Replace "N/A" with "Unknown" in the "Status" column. 5. Split Columns: Divide a column into multiple columns. Example: Split "FullName" into "FirstName" and "LastName." 6. Merge Queries: Combine data from multiple sources. Example: Merge customer and order data based on the "CustomerID." 7. Group By: Aggregate data based on a specific column. Example: Group sales data by "ProductCategory" and calculate the sum ...
Post a Comment
Read more