(So if you have only selected one date, the same date will be repeated in every cell. Copy Cells will again copy exactly whatever cells you have selected.With a series of dates, you have a few extra options: Flash Fill is not relevant in this case but we’ve talked about Flash Fill here.Fill Without Formatting will copy the content of the cells but leave the cells with no color fill.Fill Formatting Only will copy the sequence of colors, but without the content.Copy Cells will copy both the format and the content of the cells.In the example above, if we click the Auto Fill Options menu, we see a number of options for what data should be used to fill the cells. You can also choose different options for auto-fill by clicking the Auto Fill Options menu that appears after you drag the cursor. We want this pattern to repeat for the rest of the dates listed, so again we highlight them all, and click and drag from the bottom right corner. In the example below, we want each week to be represented by a different color, so we have entered the six colors we want and filled in the cells with the appropriate color. While for numbers and dates it predicts the data that is coming up, if you want to just repeat an existing pattern, enter the pattern once, select all cells, and click and drag. Repeating FormattingĪuto-fill can also be used to repeat any pattern of data, including the formatting. It’s also possible to fill weekdays only or fill by month or year, see below. This works for numbers, dates, days of the week, and anything else that has a numerical pattern to it. Really any pattern is acceptable even an unusual one like this, jumping 9 days at a time. For example, if you want a list of weekly dates, you would start by putting two dates a week apart into the first two cells, and ensuring you have both cells selected when you click and drag from the bottom right corner.Īuto Fill will detect and continue repeat patterns such a fortnightly or monthly. Just start typing the pattern into the cells, and when you have established the pattern, select all the cells comprising the pattern, and again click and drag from the bottom right corner. You can auto fill with other patterns as well. Excel will automatically fill the cells with all the following dates. Dates – day by dayĪnother simple example of Auto Fill is filling a column with dates, you can type in the first date in the format you want, click the bottom-right hand corner of the cell, and drag down for as many rows as you want to fill. That’s the basic Auto Fill, from there Excel has a lot of hidden tricks available. These examples jump by two, three and seven.Īs you can see, the starting value can be anything you like. Excel looks at the difference between the two selected cells and repeats it. We’ll explain that a little later.Īuto Fill doesn’t stop at simple number list. As you drag the tooltip show the last value you’ll get if you complete the drag there.Īt the end of the completed fill is a little icon, click on that to see a fill menu of options. The simplest example is a list of integers, just drag down to make a list as long as you need. Enter two or more values, select them then drag down/across to fill more cells according to the repeat pattern in the selected cells. Dates are well supported with good options under a little-used menu as well as copying formatting choices. Auto Fill can be used for any type of data that fits a regular pattern. We thought we knew Auto Fill but it has some surprising and useful tricks.Īuto Fill goes beyond simple incrementing numbers or dates which is what most of us do. Typing or formatting cells of repetitive data can be very boring, but Excel has an Auto Fill feature to make it easier by automatically filling in data for you.
Thanks for joining us! You'll get a welcome message in a few moments.
0 kommentar(er)
