Join Dennis Taylor for an in-depth discussion in this video Challenge: Complete a payday chart using Auto Fill and year-to-date formulas, part of The 5-Day Excel Challenge.
- Let's begin our Day 1 Objectives by opening the file, the Excel Challenge 1 workbook. It's not too complex. In fact, it's got very little information in it. We need to create a list in Column A showing every other Friday, let's imagine that's when you get paid, for the remainder of the year. Use AutoFill techniques. Do not use formulas. And think of what is the fastest way to do this. We're talking about the list in Column A. We've entered the first payday. That's the first Friday of 2015.
You want the other Fridays listed below here, all the way down to the bottom. And the next part of the challenge is to fill in the cells in Column C with a formula that tabulates your year-to-date pay. We already have the cumulative total for the very first payday, which is just the same as what your first pay is, but we want a formula right here, and once the formula is in place, copy it down the column. There are two objectives then, filling in the paydays in Column A with AutoFill techniques and then creating a formula in C3 and copying it down the column.
After solving this challenge, or perhaps you got stuck along the way, check out the video solution, where I discuss one or more ways to meet the 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.