Join Dennis Taylor for an in-depth discussion in this video Solution: Complete a payday chart using Auto Fill and year-to-date formulas, part of The 5-Day Excel Challenge.
(musical tones) - The first objective in our Day 1 challenge is to fill in column A with all the paydays for the year. Imagine you get paid every other Friday. Here's the first Friday of the year. In order to fill in this list without a formula, all we need to do is to put in our next payday right here. When you're typing dates, use a slash or a dash, so I can type 1-16-15, or 1/16/15.
If I press enter the active cell will move down, nothing wrong with that, but I'll press ctrl + enter so the active cell doesn't move. We have to highlight these two cells right here, together. Before we do that though, pound signs, what does that mean? The column isn't wide enough. We can either double-click the AB boundary, or click and drag. I'll just double-click the boundary between A and B, up where the column letters are. Highlight both of these. That interval is 14 days. We can drag this fill handle down to the bottom of our list.
Previously, column B was filled in with the gross pay for each pay period, but it was just filled in all the way down, sort of guessing how many there are for the year. Both of these are highlighted, we'll double-click the lower right-hand corner. If you want to get down to the bottom quickly, you can press ctrl + period. Looks like we'll have to make our column even wider. And looks as if it's just one row deeper than we need, so we'll simply get rid of the data here, that takes us into 2016, which we don't want. And we'll readjust the column width again.
Double-click. There we are. Our list is filled in, these are not formulas. And again, repeating the basic concept, we had one date here, I added another date, highlighted both of them, and double-clicked to copy the formula down the column. Cumulative year total, there are two ways to do this. One way is to say every time I get paid, I wanna add that onto my previous cumulative. So, very first pay period, this is what I got paid. The formula here is simply its equal to this.
We don't wanna be copying that formula down the column, because we'll have the same entry. How much have I made by the end of my second pay period? Formula equal whatever I have previously, so far this year, plus my latest gross pay. Now, maybe by mid-year or somewhere later in the year, I might get an increase, so whatever the value is in column B, it's going to be added to the previous cumulative. And here I will not press enter, I'll press ctrl + enter, and here too, to copy this down the column, I'll double-click.
When you double-click the lower right-hand corner, that means copy the formula or the text or the value down the column, based on what's in the adjacent column to the left or to the right. Excel also looks farther to the left to look at your entire list, so when you double-click the lower right-hand corner, nearly always it does what you want it to. In other words, it takes the formula and copies it down the column to match up with the other data. Ctrl + period gets us down there fast, just to verify. And again, if somewhere along the line your total for the pay period changes, perhaps it would go up at some point in here, these cumulative formulas, as we look at just one of them, certainly takes that into account.
Another way to do this, not necessarily better, is, in the very second entry here, instead of this kind of a formula, and by the way, the order of these is not critical. This could've been B3 + C2. Another way to do this, a little more cumbersome, and not necessarily better, is to write a sum function here that says, in effect, "I want to add these two." Now, if we copy that down the column, it's not gonna work so well, but if I make B2 absolute, and click right here or behind the 2, in front of the B, press the function key F4, that's a formula that can be copied down the column.
I'll press ctrl + enter, double-click the lower right-hand corner, we'll get the same answers. I think this formula isn't nearly as clear as the other one, so I'll press escape and then ctrl + z to undo and undo. And this kind of a formula here, C2 + B3, is direct and straightforward. Probably the best formula for most people to use. So those are the solutions to our Day 1 Challenge.
The challenges will ask you to:
- Generate a list of future dates
- Clean up a list of employee data
- Create a dynamic chart
- Create a pivot table to analyze sales data
- Match data across multiple lists
Starter and solution files for Excel 2010, 2013, 365, and Excel for Mac 2011 are provided in the exercise files, so you can follow along with your version of Excel.