Learn about advanced fill series options. Author Jen McBee demonstrates how to use the autofill series with text and trailing numbers, as well as how to fill a linear series.
- [Instructor] As I'm sure you're aware, you can use your auto fill options to fill in numbers, dates and the days, weeks, months, and quarters to complete a series. Did you know though that you can use the fill options to complete a list of text with a trailing number? What about creating a linear series? You can show Excel the pattern that you want to complete and show it how many cells you want to complete that series in, and apply the series options and it will complete all the information for you.
We're going to be using the 03_02 Advance Fills workbook so let's go ahead and complete some series. We're going to start with a simple auto fill series. We'll complete the list of quarters starting in C3 all the way over to F3. I'll click the auto fill handle, and as I'm holding down my mouse and coming across you can see the help text it's going to fill in Q1 through Q4, very simple. In column H I have a text entry with a number, product one.
Now I can use the auto fill handle and come on down and it will insert the series with the word product and then the sequential number following. It's pretty cool, isn't it? As long as you show Excel the pattern that you want to use you can use the auto fill handle to complete just about any series. In column I, I'm going to demonstrate how to complete a linear series. I'm starting with 123. I've added 111 to 123 and I get the result 234.
In the next cell I've add 111 to 234 and the result is 345. I want Excel to go ahead and complete the series for me so I'll begin by clicking in I4. I'll select as many cells as I want Excel to create the series in. On my home tab I'll go to Fill and down to Series, and this is where I can select a linear type series. Notice that Excel has picked up the step value that I created, 111.
I can also put in a stop value. So if I want it to stop at a greater number, such as 1,000 I can put 1,000 in and it will only fill down to that amount. In this case, I've already selected a range of cells so I'm telling Excel that I want it to stop at cell I17. Let's go ahead and click OK, and Excel has completed that series for me. So these are some advanced fill series options that you can use in your day to day work and go ahead and practice them so you'll be prepared in case you come across a task in the Excel 2013 MOS Expert exam regarding advanced fill series options.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc. In the managing workbooks chapter, learn how to work with templates, versions, and macros. In the formatting and layout chapter, see how to apply custom formats and styles. The advanced formula chapter covers additional functions including a nested IF. The charts and tables chapter shows how to create a new PivotChart. The course concludes with a sample exam you can use for practice.
- Using templates
- Managing versions
- Leveraging macros
- Linking to external data
- Protecting cells
- Hiding formulas
- Using advanced Fill Series options
- Creating form fields
- Using advanced functions: AND/OR, SUMIFS, AVERAGEIFS, and COUNTIFS
- Manipulating options in existing PivotCharts