Author: Karrie McLellan

Getting Started

Learning new software can be intimidating. It’s hard to know where to start if you don’t have guidance from a tutorial or class. When you have a basic understanding of that software, you naturally start to wonder about what shortcuts or features may be available that you haven’t used yet. In that case, it is impossible to search for information on things you don’t know exist. How do you take it to the next level without skipping steps in between?

Another thing to consider when looking for instruction is how you prefer to learn. Some folks will check out a “for dummies” book from the library and read it cover to cover. Others prefer to stop by the library and get instruction in person or connect to a web training so they can ask questions. Yet another subset of folks would rather watch videos or read articles online to get their information without involving another person.

The Library Has You Covered

The library offers drop-in tech help and virtual classes, books, e-books, and DVDs about Excel and Google Sheets, and a fantastic resource full of articles, videos, and interactive learning opportunities called Tech-Talk. Let’s take a closer look at the kinds of Excel/Google Sheets help Tech-Talk offers. These links should log you in automatically. If not, or if you are signing up for a webinar, use the username eglibrary and password eglibrary.

This quick reference guide is handy for users of all levels. Here is a collection that includes all of their Excel content, with the most recent articles and videos first. When possible, their information about Excel also includes a section for achieving comparable results on Google Sheets.

For Beginners

• Become Spreadsheet Savvy in Very Basic Excel and G-Sheets is a recorded webinar for folks who are just getting started.
• Should I Use a Table or Spreadsheet? is an article and short video on how to decide whether to use a table in Microsoft Word or an Excel spreadsheet to maintain and display your information.
• Articles about the top 5 basic Excel techniques

For Intermediate and Advanced Users

• The First Steps collection contains articles, videos, and webinars. Many of these are for targeted tasks, though you’ll want to note the skill level in the description. There are items for all levels of users in this collection.
• Top 5 Excel formatting and formula tips
• Formatting essentials collection

Major Cool Factor

I have always found Excel useful for organizing and sorting basic information, but I never got fancy with it. Recently, I attended a Tech-Talk webinar about creating dashboards using Excel and I was really impressed. If you want a quick peek at what dashboards are and how they can enhance your data, check out this short video and article.

We Can All Use a Little Help

Whether you enjoy playing around in spreadsheets, use spreadsheets because you have to, or are forced to use spreadsheets under duress at work, everyone can benefit from the tips and tricks offered at Tech-Talk. Which sort of Excel user are you? Let us know in the comments.

I have long been a fan of using autofill shortcuts to save time in Excel. I recently learned about a smart feature in Excel that takes autofill to the next level. The Flash Fill feature appears to be able to read the mind of the user, allowing them to make bulk changes in an instant.

Auto-filling Cells

Flash fill is part of a larger set of autofill features that allow a user to click and drag a “fill handle” across a number of cells and choose how the selected cells fill. To find the fill handle on a cell, click the cell once so it is outlined, but make sure the cursor does not appear in the cell. Then look at the lower-right corner of the cell. There is a tiny box there that is your target to click and drag.

If I click and drag down on the fill handle, cells in the column below will highlight. When I release the mouse, the highlighted cells will fill with copies of the first cell.

If you just drag and let go, it copies the first cell by default, but you can see in the second image above that an icon has appeared just below the fill handle. This icon leads to the fill menu, which will allow you to select a different way to autofill the cells besides copying.

For the text example “Dave” we are given the option to copy only the formatting without the text, fill the text without formatting, or flash fill. If you are performing the same option on a cell with a number, you are presented with an option to fill series, which allows you to fill with consecutive numbers.

Both of these menus include the flash fill option, but we will need a new example to show how flash fill can shine.

Flash Fill

Flash fill is like autofill on steroids, and it is most helpful if you are working with a batch of raw data that needs to be reformatted. Some examples would include turning 10-digit numbers into phone numbers or splitting a full name column into first name and last name columns. Just show Excel the pattern you are looking for, and it will learn how to fill in the area the way you want.

For an in-depth look at flash fill, check out this Tech-Talk article. Tech-Talk is a technology training service provided to patrons by the library. Learn more about Tech-Talk in our Tech Tips article about finding answers to common tech questions. If you would prefer to watch a video demonstrating flash fill, this video from My Online Training Hub is quick and easy to follow.

Summary

Autofill and flash fill are two features that save Excel users valuable time in creating and formatting spreadsheets. Do you use autofill or flash fill? Do you have a different time-saving Excel feature that you’d like to share? Tell us about it in the comments.