This exercise was originally posted at: http://maritimeorca0.blogspot.com/2014/08/create-simple-checklist-in-excel.html
The Task
Create a simple checklist with daily, weekly, and monthly tasks in a spreadsheet program such as Excel. Columns should reflect the Frequency, Task, Date Completed, and Status (a provided formula to quickly display whether or not the item has been completed). Among the features that you should use are copying and pasting, filling, and find and replace. If you are already familiar with Excel, you may want to skip this beginner exercise.
My Solution
Obviously, I started with the headers. In row 1, I entered “Frequency,” “Task,” “Date Completed,” and “Status.” I also made the headers bold so that they stand out against the data.
I copied the tasks from the exercise’s explanation. In cell B2 I right-clicked and clicked the icon for “Match Destination Formatting.” If you are using an older version of Excel, the equivalent of this would be to right-click, select Paste Special, then select text. For beginners, pasting normally is perfectly fine. I just thought that I should point out that you don’t have to carry formatting from the web that doesn’t match the formatting of a spreadsheet.
Since the clipboard’s contents remained, I did the same thing for cell B14. This segment is a little different because we want PM instead of AM. With the second set of pasted values still selected (or after reselecting them), you can use find and replace (Ctrl+H is the shortcut). For “Find what:” you want “AM.” “PM” goes in the “Replace with:” section. Clicking on Replace All will fix this section for you.
In cell A2, I typed, ”Daily.” I then dragged down the bottom-right corner (the fill handle) until I had selected through A25. I could have also copied Daily, selected the range, then pasted.
In Cell B26, I entered, “Sunday.” While I could have entered each day of the week separately (there are only seven total), I did some more filling. Excel can recognize days of the week and create a series rather than just copy. I dragged the fill handle through cell B32. I typed “Weekly” in cell A26 and filled to A32.
The monthly task was similar, but 31 days would be worse to enter manually than 7 weekdays. I entered “Get through day 1” in cell B33 then filled through B63. “Monthly” was entered in A33 and filled through A63.
When designing spreadsheets, you should realize that Excel can’t read minds. Additionally, developers haven’t discovered every single type of fill that you could ever possibly want. If I said “Finish Sunday” with the weekly tasks, Excel doesn’t know how to finish the series. It wouldn’t know how to fill “Get through the 1st day.” In both of these cases, Excel would just copy the text. There are ways around these problems, but they are beyond the scope of this exercise.
Finally, I pasted the formula (matching destination formatting) to cell D2 and filled to D63. This is essentially a pre-beginner display of how formulas work. I’m not asking anyone to make sense of anything right now, but playing around with dates shows that changing one cell can have a calculated result in another. The reason I’m bringing it up now is because I’m showing the basics of filling. Excel adjusts the formula when you fill to make them relevant to each row. If you look at the formula bar and move between the cells in column D, you can see the formula changing. You don’t even have to understand formulas to see this.
I should also mention that the formulas adjust if you copy and paste. If you delete D3 through D63, you can copy D2 and paste to D2 (or D3 since the formula is already in D2) through D63 to get the same results.
I should point out one more thing. What if you want to fill with the same thing when Excel thinks you are looking at a series? In recent versions of Excel, it’s actually pretty easy. After filling, a little icon will pop up. Click on it and change to Copy Cells. You can also copy and paste.
I have uploaded my solution to SkyDrive. It can be found at: https://onedrive.live.com/redir?resid=2E6EFB80915ED5BD!7259&authkey=!AN3R257k-a2NTxQ&ithint=file%2cxlsx
Feel Free to Comment
Now that this exercise has concluded, feel free to share any thoughts that you had regarding results, your approach, and any other ideas that popped in your head regarding this exercise.
I do not provide space to those who visit my blog. If you would like to share any files, you are going to have to find your own online storage. At that point, you can provide links with your comments. This particular exercise is intended to help people understand the basics of spreadsheets. If you are already familiar with this type of software, this task might be too easy for you. Please don't resort to macros since they could be confusing to those who are still learning.
About Sharing
Nobody should copy my work and then take credit. The nature of this exercise, however, could produce very similar results for different people. For this reason, I am unlikely to push too hard for credit. You can also add to my solution and pursue variations. Unless otherwise specified, similar guidelines should apply to solutions from other contributors.
No comments:
Post a Comment