Difficulty: Easy
Issue: Autofill is an easy to fill patterns, create lists, copy formulas or complete large datasets.
Process: See below:
Example: Autofilling A Formula / Text Down Multiple Cells
You have a large dataset in columns A and B and a formula combining them in column You wish to drag this formula down all the way to the bottom of your spreadsheet so that the formula carries down.
Most people in this instance will manually drag the green handle all the way down to the bottom. This is time consuming (and impossible for spreadsheets with 10's of thousands of rows) and more than likely you will go too far or not far enough. Instead, try double clicking the corner of the green box. This will automatically fill all the way down to the bottom of the last cell in column B.
The dataset is now complete. This also works with copying numbers, not just applying the same formulas.
Advanced tip: Another advanced way of copying data to the end of the dataset is to use keyboard shortcuts. In column B, select the first data cell you wish to copy. Holding down CTRL + SHIFT + will select the entire column until the last number before a blank. Copy and paste 'special' the data to the adjacent column COR select cell C1, select all blank cells you wish to fill and press CRTL + D
Example 2: Auto filling a trend / pattern / list
Autofill can also fill in a pattern or trend for you, such as a list of months in a year, days in a week, or numbers in a list. Autofill will fill the list in the same text format as you have dictated, for example, typing JAN and dragging down, all following list items will be FEB, MAR, APR etc. To do this, simply type out the first item in your list (Below are some examples of types and formats).
Next DRAG DOWN the green box using the bottom right hand corner as your anchor. In this example, I have used months as an example
Note: If you have a list of data in the column next to the column you are trying to autofill - you can also double click in the bottom right corner. This will populate the autofill down to the same number of rows as you learned above. When you let go of the mouse button, the months have appeared in order
Other examples you can use include numbers (e.g. creating a list), days, Quarters, etc.
Helpful tip in case of error: Sometimes when creating a numerical list (issue is only for numerical lists) by typing 1 and using autofill, Excel will give you 1,1,1,1. This is because Excel isn't sure if you want to copy the data or create a list. To resolve this, add a few more numbers in the series and drag down OR even better HOLD DOWN CTRL when dragging and Excel will create a list for you
***
Example 3: Right-Click the Fill Handle For More Options
If you right-click and drag the Fill Handle, a menu appears with more options, like Weekdays, Months, and Years. This menu is great for dates.
For example, if you want to have a repeating pattern of a payroll on the 15th and last day of a month. Put in both dates, select both. Right click and drag the fill handle. When you finish dragging, choose FILL MONTHS.
You can also use fill handle on a mix of data types in the selection. For example, selecting this data below (including the blank)
And using the drag fill handle, you can create a series of four cell ranges that ALL HAVE BEEN uniquely and separately autofilled.I.e. Days increasing, Quarter increasing, and Item number increasing.
Excel Masterclass Course
If you want to learn more about Excel, regardless of your skill level, we offer free and premium Excel training courses on our website, www.meritexcel.com. Our Excel masterclass covers:
- all the key formulas you need (sumif, index, match, offset, eomonth, concatenate, lookup, if, or, sumproduct, date and time formulas, trim and much more)
- How navigate and use Excel effectively (viewing in 2 windows, paste special, locking cells, removing duplicates, autofill, freezing rows, fill blanks)
- Best practice techniques in Excel (formatting, checks, grouping, formula auditing, structure)
- How to present and manipulate data (Pivot tables, charts, editing charts, filtering and sorting)
- How to create engaging and dynamic spreadsheets (drop-downs, protect data, goal seek and data tables)
- And much, much more!
We offer a free, limited course which you can view here OR you can purchase the full, Excel masterclass here, which has over 20+ hours of amazing Excel content.
Comments