This tips-based course will show Excel users productivity-boosting tricks, cool hidden features, need-to-know functions, and advanced content on subjects such as using PivotTables for data analysis. Tune in every Tuesday for a new tip from expert Dennis Taylor. Each tutorial is a short, self-contained lesson guaranteed to give you new insights into Excel
LEVEL – Appropriate for all
COURSE TOPICS:
READ MORE
LEARN THIS COURSE FOR FREE *10 days of free unlimited access to “Excel Tips”
Instructor’s Welcome Note:
– In this worksheet, we’re setting up a data entry vehicle for someone who doesn’t necessarily know a lot about Excel. Ultimately, all this person or persons will be doing is selecting a month in cell A2, and as I click there, you’ll see a drop arrow, it’s a picklist, I’ll show you how to do that by way of data validation. Same thing in column B, in cell B2, we pick a year, and depending upon that choice, we want our display here to reflect how many days there are in that month. Now, I’ve hidden some of the rows ahead of time because, as we look at this model, we want to be viewing some of the early days along with the other data here, and then the later days toward the end of the month.
So you’ll notice that rows 14, and then we see row 29. The other rows have been hidden. So, let’s talk about how this is set up. And a lot of different functions are in use in this model, including the ones we see over here. These are simply here for reference purposes; eventually, we’d get rid of them. The list that we see in columns J, K and L ultimately will be hidden. We can move these to a different worksheet, or hide the columns, as we choose. The user of this worksheet will simply put data, by way of choice, in cells A2 and B2 and then fill in the information here in the relevant green cells.
So, how is this all set up? Let’s begin with the idea of the picklist. Instead of having somebody type a month or possibly mistype it, we simply have made cell A2 be dependent upon this list over in column J. The list is there ahead of time. How so we set this up: we click on cell A2 and then go on the Data tab to Data Validation to the right, and if we had not made the choice earlier, we would choose List, right here, under Allow, and then, in the source panel, right here, (selects the cells populated with month names) and click OK; all set up.
And then you would test it out, of course, scrolling up and down, make sure they’re all selectable. Select this one, select that one, and so on. Year is done the same way, I won’t go through the details, but there, the list is over in column L. And of course, that could be a longer or shorter list. So we’re only allowing the years 2015 through 2025 to appear in our model. The key to making this all work is, once we have chosen the month and the year, we want to make sure that the day of the month that’s being displayed here doesn’t exceed the number of days actually in the month. And of course, some months have 31, never any more than that, but occasionally it’s 30 and, worse, occasionally, 29, once every four years for February.
The green box here is simply what the user will see. The user will enter data in cells A2 and B2 by way of those picklists, and then type in the numbers over here. The user will never see these formulas, although, he or she could by clicking, and so on, but we want to talk about these formulas one by one as they’re set up. First of all, in cell A4, the date function allows us to create a date by gathering a year and a month and a day from different locations. The year is gathered here, from cell B2, that’s the year that you would have put in with the picklist.
The month is going to need to be gathered by using a VLOOKUP function, which we’ll get to in a minute, and the day that we want here, for the beginning day of the month is simply the first, so that’s why the one is there. Let’s focus on the VLOOKUP function. The VLOOKUP function allows us to take information, for example, cell A2, right now contains the word “August”, we want to be looking in columns J, K, off to the right together, and when we find a match, we’ll go into the second column to pick up the associated number. So, for August, it’s eight, January is one, February is two, and so on.
And zero here simply means we want to have an exact match. Those of you familiar with VLOOKUP know that we could be using the word “FALSE” here; you wouldn’t know that intuitively, but zero, or “FALSE” means exact match, meaning w
Get 10 days of FREE unlimited access to Lynda.com.
Excel Tips
Business Tutorials
Dennis Taylor tutorials
Lynda review
from Course reviews, freebies, coupons http://www.coursetag.com/free-excel-tips-business-excel/
No comments:
Post a Comment