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

Send Bulk Email from Excel for Outlook

  Download the File from below Link https://drive.google.com/file/d/1tcb4lzNFgEfDKsvQqCW05sgoiGFEhqcK/view?usp=sharing Instructions are given in the image below. Save the File as Excel Macro - Enabled workbook (.xlsm) Use this file to send bulk emails at a time i personally have sent more than 2000 bulk emails at a time. Error may occur if email id typed contains space etc. Only one email id one cell.
Post a Comment
Read more

Charts - Make your data presentable

One-click charts are easy: Select the data and press  Alt+F1 . What if you would rather create bar charts instead of the default clustered column chart? To make your life easier, you can change the default chart type. Store your favorite chart settings in a template and then teach Excel to produce your favorite chart in response to  Alt+F1 . Say that you want to clean up the chart above. All of those zeros on the left axis take up a lot of space without adding value. Double-click those numbers and change Display Units from None to Millions. To move the legend to the top, click the + sign next to the chart, choose the arrow to the right of Legend, and choose Top. Change the color scheme to something that works with your company colors. Right-click the chart and choose Save As Template. Then, give the template a name. (I called mine ClusteredColumn.) Select a chart. In the Design tab of the Ribbon, choose Change Chart Type. Click on the Templates folder to see the template that ...
Post a Comment
Read more

Trace Precedents which cells forform into a formula

If you need to see which cells flow into a formula, you can use the Trace Precedents command in the Formula Auditing group on the Formulas tab. In the following figure, select D6. Choose Trace Precedents. Blue lines will draw to each cell referenced by the formula in D6. The dotted line leading to a symbol in B4 means there is at least one precedent on another worksheet. If you double-click the dotted line, Excel shows you a list of the off-sheet precedents. If you stay in cell D6 and choose Trace Precedents a few more times, you will see the second-level precedents, then the third-level precedents, and so on. When you are done, click Remove Arrows.
Post a Comment
Read more