[Libs-Or] Tech-Talk: EXCEL/G-Sheets - Speed Up Your Work with Flash Fill

Fri Sep 15 10:51:03 PDT 2023

Welcome to the latest issue of Tech-Talk!

Having trouble reading this email?
You can view the tech tip<https://www.tech-talk.com/excel-flash-fill-feature.html> and the communication tip<https://www.tech-talk.com/wherever-vs-where-ever.html> online instead at:
https://www.tech-talk.com/login/oregon

When prompted for a username and password, use ORLIBTECH for both.

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.
Quick trick to fill in data

[Tech-Talk 27 years]

[video]<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cHE1t8e3cpAW04x7iZg5erctlRHN4cP_WXA64vLfmfTq4-98_poIonPBMgFj2zgCr3MC1HRuI20aiD8HvTtsERRygtCVF1Y6KB6QLATKwzhXPY5GBxwR7I_jRQRXG5MBxQKUv_mzKTxn&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>

Photo by Pascal Debrunner<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cHE1t8e3cpAWXWzBNdeOaWanqr9Ex7m3m_PWVPKIyfUnrghvUsbY93-OzVgUIADJQ9wW5HyxEvkHzl1Rn2uwXrQGyphhc2szJBpWxzlUG5sLhFz1vimQ_inm4UvkfUznt3-JKa0ANticADmnlFEsbomoSRYY9TeO796j6doeMNA7jcM7u-lpy86KLh2F1e5W9kw7SWhGV4H-7Yk0Fd0j48w=&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==> on Unsplash<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cHE1t8e3cpAWZ4wLVqJ9MKFYKYe7wvXPrOKY3bUlc9saPo66GdDO9mMvIPm2ThIS0BMi8jRHKwoes9EHnTqW32PZtZgmk82gqcYSRCK0xo0j0ggE4_cFI8kOhl_DqC17WMdNg1AwcqlLiC0ao3IZfIaJ-Fu2YuFKBsj4GIXjj_fzZbNK1Tzb9SBl9Qy9zAqletG7MQCVjW4AgWaHTD8rEM0hBDE2tYSiYg==&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>

[https://files.constantcontact.com/ee1208b4001/e25f6bcf-26a8-4e59-91f2-cc248962042d.png]<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cO_LYqwVHC6FfkEBddOibheChmzMoU44VrM9niDIQtzyLdC5yseIkoq3Tep6jXXd9m_g6npc70fKTTGzef2lLdnujMWZQhIfdljzjdS6zIBE&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>

Webinars for You

NOTE: Register for one of the "Favorites" below and get both.

·    Sep 13: [FAVORITES -PART 1] Graphics, Windows, Email: Supercharge Your Productivity [3 pm ET for 1 hour]

·    Sep 27: [FAVORITES -PART 2] Formatting Docs, Spreadsheets, Quirky Challenges: Productivity Tips [3 pm ET for 1 hour]

·    Oct 11: [VIDEO] Creating Simple Tool Kits Using Three-Minute Video Tutorials [3 pm ET for 1 hour]

·    Oct 25: [GRAPHICS] From Still to Thrill: Create Moving Images That Will Bring Your Content to Life [3 pm ET for 1 hour]

View Webinars and Register Here<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cDkzr2cdfoBCJiBFCpL1tBQbVoB07c-IDqo-hg1yTlL8_CP-6VqoiIir36gXhVMDhJeVkp_zGmNMJNVWhV4JZWap94-8Ut_haG8FyVhtjLs9&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>

EXCEL/G-Sheets - Speed Up Your Work with Flash Fill

Intermediate

[spreadsheet]

Do you ever get an Excel spreadsheet that has long lists of names, email addresses, and other info? Maybe you want to upload the data into an email program to send a note to everyone. You would like to personalize the message, but the problem is, the first and last names are in the same column. You just need the first.

I had that issue! I wanted to separate the names to be in individual columns. I could manually re-type the entries, but with over 100 lines of data, that would take a long time!

Or the Text to Columns option is an option… but there's an even easier way!

[flash fill]

The Flash Fill feature is designed to automatically detect a pattern in the first few rows… and then it will complete the new content in the lower rows for you… magically! If you use Google Sheets, it has a similar option called Smart Fill (see how to use this at the end of the article).

You can use this feature to separate all kinds of text, but using the first and last name as an example is easy. So Flash Fill not only works for names, but you can split address fields, change the case of text, or combine fields together. Or, perhaps there is a list of phone numbers, but they are an unrecognizable string of seven numbers instead of formatted like a regular phone number with dashes.

NOTE: This is different from Autofill -- which is about Excel automatically completing lists (like months of the year or numbers) by dragging a cell's Autofill feature down the column to create consecutive data. For more on Autofill, see this article<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cHE1t8e3cpAWOEhLBBhA4Yuq4LUo0fDHVxftdybOlv7bzuEcbUvT-KFfHJ6kaYwuOP8UEHtdq6mmk4759Z3F4C60nRaKZ2XOsTNxEzgmBWBAyrEShMiDeq00kVkDKRxJ0zYMa0nGVDxr&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>.

Flash (or Smart) Fill Basics

When Excel (or Google Sheets) senses a pattern in the data that you are entering, Flash Fill (or Smart Fill) will complete the column with data based on the example you entered. This feature is great for repetitive information - and it will apply to every row in your list - no matter if it is 10 or 10,000 rows long.

A few notes to make this process work smoothly:

·    The content must have some degree of consistency.
·    Make sure you have a Header row.
·    The column you want Flash/Smart Fill to complete must be adjacent to the columns of data.
·    Make sure you add the desired punctuation (like if you want to format a name with a comma after the last name and a period after the middle initial: Durham, David S.)

TIP: If you try the example below and the Flash Fill does not kick in, see the manual option you can use to turn it on at the end of this article.

EXCEL: Let's Take a Look at a Few Examples

From One Column to Two

If you have data in one cell, and you want to split it into multiple cells, create a new column with the heading you want the un-merged data to go into. In this case, we added one for First Name and one for Last Name. Then just start typing the First Name in the first row, and continue to a second row until the Flash Fill detects the pattern and gives you a preview window. All good? Just hit Enter to accept the update.

[flash fill]

From Multiple Columns to One

Spreadsheets with names are very common. We get lists set up in multiple cells (or one cell), and sometimes we need them in a different format.

In the example below you'll see a separate column for First, Middle and Last Names. But we want them in one column.

Once we start typing the format in the adjacent cell, Flash Fill will detect it and then Excel provides a "preview" of the data. If it looks correct, we just push the Enter key and the remaining data will be filled in.

In the image below you see the "preview" of content (grayed-out text) that Flash Fill suggests based on the first row ... and the first few characters of the second that were typed.

[https://files.constantcontact.com/ee1208b4001/2ae33027-6931-45f2-8724-5e176f7abab3.png]

Formatting Number Series

[numbers]

When you have a string of numbers, like a date, telephone number, social security number, etc… and you need to change the way it is formatted, there's an automated way to do this as well.

As an example, we have a spreadsheet with digits listed in a format that isn't recognizable as a phone number. To easily update the formatting, in an adjacent cell, we started typing in the first few cells the way that we want it to appear so that Flash Fill could complete the list.

Change the Case of Text

You may inevitably receive a spreadsheet that has case inconsistencies or the wrong structure. That often happens with forms that different individuals fill out. Unlike Word, Excel doesn't have a Change Case button. But, if you want all of your data to be in the name/title case (each word capitalized), you can use Flash Fill to change it.

[all caps]

In the example to the right, we see several names in uppercase (in red). We want to use Flash Fill to extract the First and Last names into new columns and convert the upper to title case. The trick is to type at least one row that has an uppercase name in it ... to give Flash Fill a clue. In fact, you may want to change the first row to ALL CAPS (if it is not already) so that you can give it as an example.

There are tons of other ways you can use the Flash Fill!

How to Give Flash Fill a Manual “Kick”

If Flash Fill doesn't understand the pattern when you fill in the first cell and you don't get any "preview" content:

·    Fill in a few more rows until it sees what you are trying to accomplish by giving it several more examples.

·    If it still doesn't engage, to manually employ this tool, either:

·    On the Home menu, in the Editing section, go to Fill, Flash Fill ... or

·    Go to the Data tab and in the Data Tools section click the Flash Fill button (it has a lightning bolt in the icon) to give it a kick.

[https://files.constantcontact.com/ee1208b4001/7295a22f-a369-4e0e-a4a8-06daecc720cf.png]

Google Sheets – Smart Fill

As with Flash Fill in Excel, you can use Google Sheets's Smart Fill to detect patterns and fill in columns of data with text or numbers instantly for you as in the scenarios above.

When getting your data ready, the content must have some consistency and the column you want Smart Fill to complete must be adjacent to the columns of data.

·    Make sure you have a Header row.
·    Start typing the data you want Smart Fill to detect a pattern. It can take 2-6 rows to trigger this feature.
·    A Smart Fill window will open with formula suggestions. Press the checkmark to approve the changes.
·    Or, to give it a "kick," if Smart Fill doesn't detect a pattern, type the first few lines of data and then press Ctrl+Shift+Y. If a pattern is detected, Smart Fill will complete the column of content.

[https://files.constantcontact.com/ee1208b4001/5406476e-0e0c-4149-86c0-3e186e6269fd.png]

[https://files.constantcontact.com/ee1208b4001/d8034ac0-6ed3-45d2-a779-97654f34cba7.png]

Want to Learn MORE Productivity Tips
Like This One?

Join us on Wednesday, September 13th and 27th for a two-part webinar ...

Supercharge Your Productivity

Using Tech-Talk's Best 24 Tips

Register Here<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cEc5ue6CMN-GrQl1W424P5aF-a3JwanlKDj4IVjP2Lv8F1mwpDupqeLDG_UcZfmh3WYnS03p9hoxqxab1dYu8_n8xtPt3Gsa6nLCtqREBkHmbqWsfh37Wavb2BGlm1F0xedGgtfO1tIwYN9adOcJy1PWaER2UpwgnQpLQ3mi8xZJeFmV2SMtKAk=&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>

to attend one or both!

Can't attend? Register and you'll receive an email with the recording links.

[https://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communications: Writing

Is it wherever or where ever?

Have you ever stumbled when writing the word "whereever"? If you think there are too many "e's" ... you're right! Drop that extra "e" in the middle.

It's wherever!

And don't split the word into where ever. Look at the examples of using this tricky word and you'll notice that separating the two words would be clunky at best.

·    Wherever you go, always remember to be kind to others.
·    Place flowers wherever you see an empty spot in the garden of life.
·    Wherever there's music and dancing, you'll find happiness in the middle of it.
·    What about traveling without a plan and stop wherever your heart desires?
·    Wherever there's a will, there's a way.

[https://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cJc41tA5EDWlrvWB3rcQZefv41h-wQHL_okCJc-S06r_HzILeFJq9Jge0t50VFPCYe4IvPIZpo3kMpX2UtU2l1csFoPPmC8DaaDwgan_FeCs&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>
[Ask a question]<https://r20.rs6.net/tn.jsp?f=001GmZja005FchU1jvzgsVZBBywWT0GR3qqPYJTq3715mJb1A9bcpG-cHJnm6QRz-IPiK6rgf1OR6kFyxjHL7DVw2ZtlkesJlC0O8lYAWVjlpLtLZ0B5ti6ajHWgQxRQdYESKclZ2kQQa0-XanY5UfLqvLLoLi2zuGnKldZDKzfRI8=&c=mk6YlYQ5aRgXDFl-27xZuRbKlyLCvFjTNMBy1yT1Hu7agPnjFog00A==&ch=NS4-aV7rTMjYs98V3cGulekEPtL-vXVkDo8lCYRrGMq6rESE8Y4eyg==>

Copyright 1996-2023 Shared Results International. Published weekly. Distribution is limited by license. For information on how to include additional recipients, contact support at tech-talk.com<mailto:support at tech-talk.com> 941-355-2092.

Cheers,
Darci Hanning, MLIS (she/her/hers)
Public Library Consultant / CE Coordinator
Continuing Education Resources: https://libguides.osl.state.or.us/conted
State Library of Oregon | Library Support and Development Services
971-375-3491 | darci.hanning at slo.oregon.gov<mailto:darci.hanning at slo.oregon.gov> | www.oregon.gov/library<http://www.oregon.gov/library>
Follow @StateLibraryOR: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Instagram<https://www.instagram.com/statelibraryor/> | Pinterest<https://pinterest.com/statelibraryor/> | YouTube<https://www.youtube.com/channel/UC0-kU8Gu0jS_YcnXg-b_TRA/featured>

[cid:image001.png at 01D9E7C2.36813790]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20230915/ae4ba024/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20230915/ae4ba024/attachment.png>