Hello dear subscribers. This is Oppa Do Excel. What functions do we use most often in practice? Pivot table? table? No It is automatic filling. In this lecture, we prepared everything about auto-filling. Are you ready? Let's go together! The example files used in this lecture can be downloaded for free from the Opado homepage. Click the link below to download the example file and proceed with it. I will go to the next page. First, let’s see what auto-filling is. Auto-filling literally means formulas entered into cells, dates, numbers, text, etc. We talk about the ability to type automatically. So, if you are a practitioner dealing with a large amount of data, It is an essential function that you must be familiar with. It's very simple to use. When you select a cell, there is a small dot at the bottom right of the cell. This point is called the autofill handle. When you hover the mouse cursor over the auto-fill handle, it was originally a white cross It will change shape to a black cross. Click the mouse in this state, If you drag in the downward direction, it will automatically fill downward. Dragging to the right will automatically fill in the right direction. There is also a way to drag with the mouse, You can also auto fill with keyboard shortcuts. Ctrl + D for down direction auto fill, Ctrl + R for right direction. However, when using auto-fill as a shortcut Without recognizing the pattern of letters and numbers in the first cell Note that the first value will be auto-filled. The difference can be easily understood by looking at the example next to it. I'll try auto-filling with the mouse. After selecting 1 and 2, if you auto-fill downwards, It is automatically filled in increments of 1. After selecting the range next to it, auto-fill in the downward direction It is automatically filled in increments of 2. The range that also refers to the SUM function entered last They are referenced moving down one space. This time, I will auto-fill with keyboard shortcuts. Select the range from 1 to the last, then press Ctrl + D on the keyboard The first value, 1, is copied/pasted downwards. In the second range, the value of the first cell is pasted down the same. Finally, let's look at the formula. Unlike numbers before, formulas It moves by space and automatically fills in correctly. I'll go to the next page. Next is the autofill rule. It is automatically filled in with specific rules according to the type of data entered in the cell. The first is character data. Regardless of the entered value, the same value is repeatedly output. If you select the cell with apple and fill it in the downward direction automatically The same value of apple is output repeatedly. If you select apples or pears and fill them downwards automatically, it will be printed repeatedly. Apples, pears, and tangerines are printed repeatedly like this. Next is the numeric data. When there is only one input cell, the number will be repeatedly output the same as the text data. If you select a cell with only 1 input and fill it in the downward direction, 1 is output repeatedly. If you hold down the Ctrl key on the keyboard and auto-fill here It is automatically filled in increments of 1. If two or more cells are selected for the second number, The pattern is recognized and automatically filled. Select 1 and 3, and then auto-fill downwards It is automatically filled in increments of 2. If you select 1 and 11 and auto-fill, It is automatically filled in increments of 10. 1,3,10 are entered. This value has no uniform pattern. After selecting the range, auto-filling will result in an incorrect form Please note that it can be auto-filled. The third is date data. Date data increases by month if the entered value is month. If it is daily, it will increase by day. When there are two or more cells with the same date as numeric data In this way, the pattern is recognized and automatically filled in increments of 3 days. In the case of 1, 4, 10, irregular patterns on the same principle This value is recognized as a text rather than a date. The 1st, 4th, and 10th days are automatically filled repeatedly. Click the-button above to hide the range and then view the data. The fourth is time data. The basic principle is to increase by time. If you select the cell where 10 o'clock is entered and fill it automatically, it increases by time. When 10:01 is entered, auto-filling increases by time. When two or more cells with time input are selected The pattern is recognized and automatically filled in the same way as the number. It will be filled automatically in 2 minute increments. Last is the cell reference. Cell references are automatically filled in the form of moving one space at a time according to the auto fill direction. For details on the cell reference method, Everything about the cell reference method was covered in detail in the video lecture. And there are other rules. When numbers and letters are mixed, The letters are printed repeatedly and the number increases by 1 or according to the pattern. The box is kept and only the number increases. If you select box 1, box 11, and box 21 and fill in automatically Keep the box and recognize the number pattern It is automatically filled in increments of 10. When numbers and letters are mixed, but two or more numbers are mixed, Only the last number is incremented. Keep the first grader in front and increase the number by one in the back. Only the values in the back are automatically filled in by recognizing the pattern. Next, let's take a quick look at the auto-fill options. Unit price and quantity are entered for each product. Let’s calculate the amount next to it. Please enter unit price * quantity. Auto-fill the amount down. There were cases where the previously entered form was automatically filled and broken. In this case, after auto-filling, If you look at the bottom right, there is an auto-fill option. Click the button and select [Fill without formatting] Only the formula entered inside is automatically filled. In addition to filling without formatting, you can also fill in just the form. It's very easy to apply stripe formatting. Select only the row where grapes are entered, and change the groove-fill color. Choose grapes and oranges. After auto-filling, in the auto-fill option If you fill in the format, the striped format is applied at once. In addition to this method, there are various ways to apply striped rows Please refer to the link I wrote below. Finally, if you use the auto fill option, if the data in the entered cell is a date, You can auto-fill only on weekdays, excluding weekends. After auto-filling in the cell where the date is entered, In addition to the existing items in the auto fill option Other options include daily fill, weekday fill, and more. If you choose to fill in weekdays, weekends are excluded and only weekdays are automatically filled. How to display the day of the week in parentheses on the right as the date is entered in this way. After selecting the range, right-click-go to Format Cell, or Use the shortcut Ctrl + 1 to go to Format Cells. Display Format-I have put the cell format in advance in custom. If you apply this cell format, you can display the month/day and day of the week at the same time. For more information on cell formatting, refer to the basic lecture at the top right. Finally, let's look at how to create your own autofill pattern. Intern, employee, assistant manager, team leader, Or in the case of repetitive business processes such as purchase request, purchase review, and company survey You can enter more conveniently by adding it to the custom list. Go to File-Options. If you scroll down from Advanced, you can edit the custom list. Enter the required value directly for the list item on the right or Or, if you select the range to import the list below and click the'Import' button, The selected range is automatically added as a list item. Please add it to the second import to work process. When you finish with confirmation, registration is complete. After entering the intern, auto-fill the intern, employee, and representative. It is automatically filled according to the set work system. However, there is one precaution when using a custom list. When entering a value directly into a list item, the input is limited to a maximum of 255 characters. When adding a list of 255 characters or more, select Range-Import. Please note that you will need to enter a list. If you have any questions about this lecture, please leave a comment below. If you find this lecture helpful, please like and subscribe.
