Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams (2023 Update)
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11 (2023 Update)
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Data Analytics
        • Introduction to Analytics and Artificial Intelligence
        • Data Analytics in Excel
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Auto Fill Data in Microsoft Excel 2016

How to Auto Fill Data in Microsoft Excel 2016

During this Microsoft Excel 2016 training tutorial video, we will demonstrate the different ways you can use the Fill feature in Excel. You will learn how to work with the fill handle, fill dropdown and series dialog.

Facebook Linkedin Twitter

Like what you see? Get our complete Microsoft Excel 2016 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

Video Transcript

We’ve already looked at filling a series of cells a couple times already in the course. We looked particularly at filling a series with the months of the year. But in fact, many times when you’re working with Excel you’ll find that the data follows one or more distinct patterns.

Now let me just quickly do again something we’ve done a couple of times already. I’m just going to type January in that cell and this little block, this little icon, the bottom right hand corner of the board here it’s called the Fill Handle. And if I pull the Fill Handle across, let’s just go to there say. You’ve already seen that you get the months of the year. Let me just Undo that, go back to just having January. But this time instead of pulling across with that Fill Handle what I’m going to do is to select the cells up to R4. So I’m going to hold the Shift key down, press R4 and all the cells between G4 and R4 are selected. Now what I’m going to do is I’m going to go the Fill dropdown in the Editing Group on the Home Tab and I’m going to select Fill Right. And what happens this time instead of getting the months of the year is that I get the same cell content, in this case the word January, in each of those cells. So that isn’t a Series Fill that we refer to. It’s really just a Fill, a Fill Right. Now let me Undo that yet again.

There is a Keyboard Shortcut for Fill Right that you’ll probably find yourself using quite a bit. It’s Control-R. Let me do Control-R here and that gives you a straightforward way of doing a right fill. But in that case what you’re doing is effectively copying the contents of one cell into a number of other cells.

Let me just go back. Let me Undo that yet again and I’m going to type February in H4. Again I’m going to select G4, put the Shift key down. Now I’m going to do my right fill again, Control-R. What do you think is going to happen? That’s right. You still get January in every cell because basically with my left most cell selected I select the range of cells I want to fill and basically it goes left to right filling those cells.

Now similarly if I were to hold the Shift key down and click in G14 I could do a Fill Down which is Control-D or let me just Undo that, click in G4 again, use the Fill Handle to drag down and I get my sequence of months again.

Now it’s important to realize that you can’t only fill right and down. You can also do up and left. Let’s suppose I put the word Hello here. And if I wanted to basically have Hello going up the sheet. Say I’m starting here in U23. If I select a cell above that, go to the Fill dropdown and do a Fill Up that works the same. And in fact if I click in M10 with the Shift key held down, so I’ve got a rectangular selection, I could do a Fill Left and so on. So you can basically work in any direction on the sheet. And of course you can do Series in any of those directions as well. So if instead of Hello in this first cell here I’d put January I could use the Fill Handle to go left and then I could even use the Fill Handle to go up. So as you can see you can build some pretty sophisticated patterns.

Now the examples that I’ve used here have been relatively straightforward. And now I want to look at something a little bit more complex and we’re going to use a very important Series Dialogue for this example.

Want More? Get Started With a Free Excel 2016 Course! Click Here

So in cell H30 I’m going to type a date. I’m going to type a date of the 3rd of January 2015. Let’s suppose that I want to do a Series, a sequence of cells in row 30 staring on the 3rd of January 2015 but I want to have the step as 17 days, a very strange amount. Now to work out what those individual cell values will be, well 17+3, the next one would be the 20th of January and then another 17. That sounds like the 6th of February and so on. But it becomes a little bit complicated to keep going up in steps of 17 days. Well one way that you can do this is to use the Series Dialogue. And if I click on that Fill dropdown again there’s an option, Series and then it’s got a dot-dot-dot. Now whenever you get a dot-dot-dot on an option on one of these menus from the Ribbon it means you’re going to see a dialogue if you click on that. And in this case you have the Series Dialogue which lets you build very sophisticated series on your worksheets.

So, first of all do we want to go in rows or columns? Well I’m going to go in rows. I’m going to go across the row. What type of series is it going to be? Is it going to be a linear series? Is it going to be a growth series, something that’s getting bigger? Is it going to be a date series? Or is it some sort of AutoFill? Now what I’m going to do is to make this a date series and the date units I measure are going to be days. But the step value here is going to be 17 days. So I’m going to build a series that goes up 17 days at a time.

Now the step value basically says, “Where do I want this series to stop?” Now I’m going to randomly choose a date. I’m going to say I want it to stop on the 31st of October 2015 and there it is. Notice as I pointed out earlier in the course that where the columns aren’t wide enough to accommodate those dates I get the hashes. Let’s select all of those cells. Let’s AutoFit the column width so you can see them all properly. And there you are. Starting at the 3rd of January, 20th of January, 6th of February, 23rd of February, and so on.

Now it is true that pretty much any series that you can do with a Series Dialogue you could actually setup to do using the Fill Handle type approach. But it can get quite complicated and you’ll quite often find the Series Dialogue is a more convenient way of doing things. You can very simply, for example, just do a series of numbers. It doesn’t have to be dates or months or something. If I had a two in this cell and a five in this cell, let me now extend those with the Fill Handle, then I still get a series of numbers increasing three at a time. So I can do that without using the Series Dialogue but the Series Dialogue often makes it much more straightforward thing to setup.

Want More? Get Started With a Free Excel 2016 Course! Click Here

Another point to make here is that, let’s go back to that left hand one. Let’s put 100 in there. If you’re using Series Dialogue, let’s say that you’re doing in this case rows linear and your step value instead of being three as it was just now you made minus six and said stop when you get to minus-100 that’s fine as well. And you can of course count down dates as well.

So that’s it on Fill and Series Fill. In the next section we’re going to take a look at Flash Fill. So please join me for that.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

START LEARNING How to Auto Fill Data in Microsoft Excel 2016 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

How to Stretch and Straighten a Photo in Photoshop Elements 2019

Take Your Database Online with New Features for Access 2013

Customizing Tables in Microsoft Project 2013

How to Use Photoshop CS6 ? 5 Best Tips for First Time Users

How to Protect Cells in Excel Workbooks —5 Easy Ways

Free QuickBooks Tutorial – Getting Started

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)