Organization Tip #7 - Using Spreadsheet Shortcuts to Auto-Fill Data

If you have never used a spreadsheet program, Organization Tip #1 discusses a few of the basics for using spreadsheets and why you would want to.  This post discusses some of the useful built in auto-fill features spreadsheet programs have.  These tools make using spreadsheets more efficient and user friendly.  Some of the auto-fill features copy data, while others actually enter in the next variable of a list.

If you have worked with spreadsheets before you know there are a few different types of pointers.  The type of pointer depends upon where you are relative to a cell, column, or row.  Listed below are the different types of pointers and a brief description of what each one does:

1. White Plus - the default pointer, allows you to select a cell or group of cells.  Note: Left-click and hold to select multiple cells from contiguous rows and columns with this pointer.
2. Down or Right pointing black arrow - appear when you hover over row or column headings and allow you to select an entire row or column to apply changes to at one time.
3. Left / Right and Up / Down double arrow black pointer -  appear when your pointer hovers on the dividing lines between individual rows and columns.  Allow you to adjust the row width and column height.
4. Black four-arrow pointer - appears when you hover over the edges of a cell but not the corners.  Use this pointer to move data in a cell or group of cells to another place in the spreadsheet, another sheet, or another spreadsheet file.    Note:  You can also select a cell or group of cells, right-click and select cut, then click the cell where you want to move the cells to, right-click and select paste to move the same data.  There are multiple ways to manipulate data in a spreadsheet.  Use the way that seems most intuitive and effective for you.
5. Black plus - appears when you hover at the bottom right corner of any cell or selection of cells.

These may seem confusing at first, but after using a spreadsheet program a few times the differences between the pointers will be easy to differentiate.  To access the auto-fill functions, we will use the black plus pointer, which appears when you hover at the bottom right of any cell or selection of cells.

A common way to use the auto-fill feature to reduce the typing is to use it to fill in the remainder of a list.  If you type January into any cell, then use the black plus pointer, left click and hold while dragging to other cells, auto-fill will fill in the remaining cells for you.  The months of the year following January will appear in order in the cells you selected below January.  As you drag down notice the auto-fill data for each cell will pop up so you know when to stop dragging.  This will also work with days of the week and dates of the month.  If you keep track of something daily you can start with a number representing the day of the month and copy until you have all the days of the month or start with a day of the week and copy until you have each day.  The beauty of the auto-fill feature lies in its flexibility.  You can input any month, date, or day of the week and the auto-fill feature will still work.  If you keep track of things monthly, weekly, or daily, this is a great way to create headings in a snap.

Another way to use the auto-fill feature is to copy functions.  If you have a spreadsheet with rows or columns of numbers that need mathematical equations applied to them, you can input the equation just once and then copy the equation for all the remaining cells.  For example, if you had a 2 in cell A1 and a 4 in cell B1 and needed to know what the growth between the two were, you could enter this function into cell C1 next to them:  =B1-A1.  Then, with the black plus hovering over the bottom right of cell C1, click and drag until you hit the last row containing data.  Once you do this, the function you entered in C1 will be copied to each cell you selected.  More importantly, the function will be automatically adjusted for the row it is in.  In other words, the function in cell C24 is going to be:  B24-A24, not B1-A1 because the formula automatically adjusted for the row the function is copied to.  In reality, it would not make much sense to copy a function to multiple cells that is being applied to the same two cells over and over again.  This is why when you copy a function, the function itself adjusts to the rows and columns it belongs in while still copying the format of the original function equation.  Copying a function in this way prevents you from having to type the same function over and over and also prevents typos.  The more complicated a function is, the easier it is to make a typo, but if you copy the function, the possibility of having a typo is removed as long as your first equation is correct.

Regardless of how familiar you are with spreadsheet programs, these tips can help you reduce the time you spend entering data.  Work at your own pace as nothing says you have to use all of these features right away.  Try a few of the tips to see which ones really are helpful for what you do with spreadsheets.  The information here is provided to help you be more efficient in your usage, not to be overwhelming or frustrating.  Add the tips that make sense to your usage and let the others go until you need them.

To watch a video describing the information in this post, visit our YouTube channel and watch this video.

Enjoy this post? Subscribe to our Blog