Autofill Options
There is a way to exponentially increase your speed in Excel. If you use dates, days of the week, patterns or lots of formulas then AutoFill will transform the way you enter data into Excel.
This post is going to take a lot of pictures to explain the process thoroughly. It will be best demonstrated in this
VIDEO if you have 10 minutes to see it in action.
To start using
AutoFill, select the cell or cells you want to copy or use for the start of a pattern. Then put your cursor over the lower right-hand corner of those cells. The cursor will change shape to look like a bold plus symbol.
Then click and drag that corner down or across to fill those blank cells with your data. A preview of what Excel will put in the last cell is displayed just to the right of the cursor. Because Monday was selected, Excel knows we are looking at days of the week and it is filling the series through Sunday.
Excel filled the cells with each subsequent day of the week. However, I only want weekdays. Fortunately, this is an option that will be displayed by selecting the down arrow in the tan box to the right of our data.
By selecting
Fill Weekdays in the drop-down menu, cells A7 and A8 change from Saturday and Sunday to Monday and Tuesday. Now every subsequent weekday is displayed for 7 days without typing. This shortcut works over hundreds and thousands of cells, making it one of the most powerful shortcuts in Excel.
If you want to display every day of the week, this option is called
Fill Days.
When you use AutoFill, it will default to
Fill Series or
Copy Cells depending on your data type. In this case
Fill Days is the same as
Fill Series.
To fill only the formatting and no data, select
Fill Formatting Only.
By using AutoFill, it is easy to copy C2 to D2. By default copying brings the formatting as well.
To fill the series with just data and not the formatting select
Fill Without Formatting.
AutoFill can be used to more then one row at a time. Select both of the cells you want to use as the beginning of your series. Select the lower right-hand corner and drag to the last cell you want filled.
Again Excel shows a preview of what the last cell will contain. This is nice to know, so you know how far into the pattern you have gone.
Not only did Excel fill the days of the week, it also filled in the dates as well using
Fill Days.
Fill Weekdays works on both days of the week and dates.
Fill Months shows the same date of each subsequent month and the names of the months filled in series.
Fill Years shows the same date, but changes the year to the next year for every cell selected.
Fun Fact: Excel limits its calculations in relation to time to the year 9999.
Excel can recognize patterns and will repeat them using AutoFill. To tell Excel to do this enter enough data to represent the pattern and select all of that data.
Click and drag the AutoFill handle over to where you want the data to stop. If Excel copied the cells, use the AutoFill Options drop-down menu to change to
Fill Series.
Introduction to Absolute Referencing
Here a simple formula tells Excel to add cells B1 through B5 in cell B8. When this formula is AutoFilled to the 9 cells to the right, the formula changes in each cell to add the cells in the same column as the new formula. This means that the formula in K8 is now
=SUM(K1:K5)
In a formula, the $ symbol locks that portion of the cell reference to keep it from changing. When both the column and the row has a $ symbol, this is called an
Absolute Reference. The example above is $A$10. This cell is highlighted yellow and contains the number 10.
The formula in cell B11 is multiplying B8 and A10. When AutoFilled to the 9 cells to the right, the formula is now multiplying K8 and A10. A10 didn't change because it was an absolute reference. Now every cell that has a formula looking at A10 can be changed by changing the data in cell A10. Here it has changed to 15 and all the formulas updated automatically.
Absolute referencing can be toggled through the options using the F4 key while entering a formula. More detail to come on this in a later tutorial.
Bonus: Customize Status Bar
At the bottom of the screen in Excel, just below the horizontal slider and just above the taskbar is the
Status Bar. Right-click on the status bar to customize it. I recommend turning on all of the quick functions highlighted in red above.
This will give you the ability to analyze your data very quickly without entering formulas. Simply select the data you want to analyze and look at the status bar.
If you have learned something today and want to learn more, follow me by email to receive future posts right to your inbox. I only post once a week, so you won't be flooded with unwanted emails.
Enjoy your week and God Bless!
Nathan
Every cell = a calculator!
No comments:
Post a Comment