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

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

What if Analysis

Sometimes, you want to see many different results from various combinations of inputs. Provided that you have only two input cells to change, the Data Table feature will do a sensitivity analysis. Using the loan payment example, say that you want to calculate the price for a variety of principal balances and for a variety of terms. Make sure that the formula you want to model is in the top-left corner of a range. Put various values for one variable down the left column and various values for another variable across the top. From the Data tab, select What-If Analysis, Data Table.... You have values along the top row of the input table. You want Excel to plug those values into a certain input cell. Specify that input cell for Row Input Cell. You have values along the left column. You want those plugged into another input cell. Specify that cell for the Column Input Cell. When you click OK, Excel repeats the formula in the top-left column for all combinations of the top row and left colum...
Post a Comment
Read more

Sum a cell through several Worksheets

You have 12 identical worksheets, one for each month. You would like to summarize each worksheet. Is there a better way than using =Jan ! B4 + Feb! B4+Mar! B4+Apr! B4…? Solution:  You can use a 3-D formula such as  =SUM(Jan:Dec!B4) , as shown in  Figure 41 . Figure 41. A 3-D formula adds up all instances of B4 on the 12 sheets from Jan through Dec. Late-breaking Tip : To add up cell B4 on all the worksheets with Sales in the sheet name, type  =SUM(‘*Sales’!B4)  and press Enter. If the first or last worksheet contains a space in the name, you have to use apostrophes around the pair of worksheet names:  =SUM(‘Jan 2009: Dec 2009’!B4) . You can easily copy this formula to other cells on the summary worksheet.  Gotcha:  Do not drag the summary worksheet to appear after the Jan worksheet, or you will set up a circular reference. Additional Details:  It is possible to set up a named range that refers to a 3-D range. Here is an interesting way to set...
Post a Comment
Read more