In Excel, you can streamline that process by using AutoFill. In this video, learn how to enter data quickly and efficiently.
- [Instructor] Excel 2019 provides many different ways for you to enter data. In most cases, you can click a cell, type the value you want, and press Return. However, there will be times when you need to enter a lot of data by hand. For example, if you track sales by month, you might need to enter a long string of month names by hand when you're setting up your workbook. In Excel, you can streamline that process in several ways. In this movie, I will show you how to enter data quickly and efficiently. My sample file is the Enter Data workbook and you can find it in the chapter three folder of your exercise files collection.
The most basic way to enter data into a worksheet is to click the cell and start typing. For example, in cell B2, which I already have selected, I might want to type in calendar year 2017 so in cell B2, I'll start typing CY2017 and then I might realize, oh, I have data from 2016 so I can either backspace over CY2017 to change the seven to a six or, if I want to stop editing entirely, I can press the Escape key and the cell is reset to its original value.
So if it already had a value or a formula, pressing Escape would leave it. Since I know that I have data for 2016, I'll type CY, which is short for Calendar Year 2016 and Enter. Now I can enter my data into cell B3 so let's say that I know I have revenue of $25,000. So I'll just type $25,000, press Return, and I get my value $25,000 and I've already applied formatting to a bunch of cells in this worksheet so don't worry if it doesn't appear the way you want it the first time when you do it.
If I want to enter values into multiple cells at the same time, I can select the cells. So let's say that I know that I have sales of $30,000 for the months of February, March, and April, which is unlikely, but let's say that that happened. I will select cells B4, B5, and B6 and then I'll type $30,000 and with all those cells still selected, I'll press Command Return and Excel enters the same value into all those cells.
You can also extend series so for example, I have the month of January in cell A3. The months of the year are a series that Excel recognizes so I can drag the fill handle to extend that series. The fill handle is the green square that appears at the bottom right corner of a selected cell range and if I move my mouse pointer over it, you'll see it changes to a black crosshair. If I drag down, you'll see from the tool shift that appears that the series is extending.
So I have February, March, and April. I can do the same thing for CY2016 and because there's a number at the end, that number will increment. So if I drag the fill handle to the right, you'll see it goes CY2017, CY2018. However, if I were to hold down the Option button and then drag the fill handle, you can see that the mouse pointer is now not just a black crosshair, but it has a small black crosshair at its top right corner.
So if I drag to the right now, you'll see that, in fact, it's repeating instead of extending. If I release the Option button and then increase the series extension, you can see that my tool tip now says 2018. And when I release, those are the values that I get. Now let's say that I want to assume constant growth for each month over the next couple of years, so for January of 2017, I'll put $30,000 and then for C4, I'll put $32,000.
So I'm assuming $2,000 growth. For March, I'll say $33,000 and for April, I'll say $34,000 and Return, okay. If I want to extend the series, I can do so by selecting the cells and then dragging the fill handle to the right and you can see that I get 35, 34, 36, and 38. And the reason I got those values is because $25,000 to 30 is a $5,000 increase so I see that here, $2,000 from 32 to 34, three from 33 to 36 and four from 34 to 38.
So you can see, if you want to build in a series with a standard progression, you can do so. Now I'm gonna switch over to Sheet two and show you a very interesting and very useful capability. I'll click the Sheet two sheet tab to go there and you can see that I have data for city and state. And I'd like to combine them. The way that I'll combine them will set a rule for the rest of the cell values. So in cell C2, for Portland, Oregon, I will type Portland comma Oregon, make sure that's right, okay, and then press Return.
Now if I use the fill handle, like I did before, so I'll click cell C2 and use the fill handle to drag down, you notice I get repeats of the previous values. However, because I established a pattern, we can apply that pattern, in many cases, it doesn't always work, for the values in C3 through C8. So with the cell still selected, I will click the Auto Fill Options button and you notice that the last item is Flash Fill.
If I click Flash Fill then I get Portland, Oregon; Seattle, Washington; Mt. Crawford, Virginia, my home town, Syracuse, New York; Sacramento, California; Phoenix, Arizona; and Denver, Colorado. The reason it worked is that there was a built in capability called Flash Fill, which looks at data, does its best to determine a rule, and if it can, it extends the data for you. So as you can see, entering data into a worksheet is never fun, but the techniques I've shown you in this movie will help you enter large data series quickly and using Flash Fill lets you combine them without using formulas.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks