Do you manage Projects? Do you create Project work schedules? Do you need to know how many days were actually worked on your Project?
If so, this Excel video lesson is for you. A viewer asked for my help. He did not want to use MS Project for his work schedules. Rather, he wants to know which Excel Functions to use for his Project work schedules.
In this lesson, I demonstrate how to use two functions included in the Excel “Analysis Tool-Pak”:
Here is another response to a viewer request. The letter asks for my help in identifying, counting and totaling the amount of “Past Due” invoices. In the viewer’s letter, she wanted me to use the =NOW() Function. This function returns the current date and time (Hour, Minute, Second) from your computer’s system clock. The =TODAY() Function is similar, but it returns only the current date. Both the =NOW() and =TODAY() Functions are “volatile.” This means that the value that they return will automatically update according to your computer’s system clock. THis makes them excellent reference points in formulas that identify “Past Due” invoices.
In addition to using the =IF() Function to identify the invoices that are “Past Due,” I also demonstrate two other functions: =COUNTIF() to total the number of “Past Due” invoices and =SUMIF() to give me the total dollar amount that is “Past Due.” I recreate these formulas, this time, using “named cell ranges” in the formulas.
Finally, I show you a great new Filtering Feature in Excel 2007 – the ability to filter by time period e.g. “Next Week!”
Check out my new DVD, “The 50 Best Tips, Tricks, and Techniques for Excel 2007.”It contains over 5 1/2 hours of training for Excel 2007. You can locate the specific tip that you want to learn – and in @ 6 minutes, you will have received all of the information that you need to become more productive in this area.
I am working through the special requests that viewers have sent me. In this lesson, I show you how to:
Use Data – Get External Data – to import a simple delimited text file.
Use Copy – Paste Special – Transpose – to make a copy of the imported data that is now “vertically oriented.” i.e. The field entries run down the column rather than across the rows.
Use the Data – Text-to-Columns feature to break an “address block” (City, State, Zip Code) that is one cell into three distinct cells. This is a terrific time saver!
Now available for purchase: My DVD “The 50 Best Tips, Tricks, and Techniques for Excel 2007″
Over 5 1/2 hours of training. Each video lesson focuses on one tip and averages 6 minutes.
A viewer wants to know how many badges have been claimed and how many have not yet been claimed. In Excel, use the =COUNTIF() Function to get the answer(s) to this question.
If you have ever struggled with using “Nested =IF() functions” you will welcome the =COUNTIF() Function – it is so much easier to use because the arguments for the function follow a logical progression.
My viewer also wanted to see the names of the guests who have claimed or who have not yet claimed their badges. To answer this request, I use a Data Filter – very easy to use and change.
This video is in response to a viewer who wants to join two columns containing Data Validation “Pick-from” lists. She knew that there was a Formula that could perform this, but she didn’t know how to accomplish this.
I promised to create this Excel video lesson to show her how – using the =INDIRECT() Function.
Here are the steps to follow in this lesson:
Create lists for each group you want to be able to select from. Division and then in Departments (Sales, Marketing, etc.)
Name each of these lists. Use either the NAME BOX or “Insert – Name – Create” (Select the Label and the Values).
For the 1st Column – Division – use “Data – Validation” and in the “Allow” drop-down, choose “List.”
Use the F3 shortcut to bring up the Named Range Dialog Box. Select “Division.” Type in a message for the Screen Tip and click OK.
For the 2nd Column – the one whose “Pick-from” list will be based on the Values from the 1st Column (Division), we start with “Data – Validation” and in “Allow” choose “List.”
Here is where we use the =INDIRECT() Function in the “Refers To” box. Click to select the cell in the Same Row in the 1st Column. Note: Be sure to make the Cell Reference Relative so that the formula can be copied down the column.
I created the Validations in the Top Cell in each Column. Use Copy, Paste Special, Validation to apply the “Pick-from” validation to the remaining cells.
While I was training a class to use Pivot Tables this week, I showed them how to Group Dates by Month, Year & Quarter. They liked that a lot – it’s a great feature! However, one student said, “My company is on a Fiscal Year. Is it possible to change the grouping of the Quarters from a Calendar Year to a Fiscal Year?”
Yes! There are several ways to do so. In this Excel Video Lesson I demonstrate the easiest way.
Here are the steps to follow in this Excel Training Video:
Create the Pivot Table. In this example we drag the “Date Field” to the “Row Area” and drop the “Sales Field” into the “Data Area.”
Select one cell in the Date field and from the Pivot Table drop=down menu choose Group & Show Detail – Group. Highlight Month & Quarter. Click OK.
We now have a new “Outer Row” filed – Quarter – in our Pivot Table. However, the grouping reflects a Calendar Year. We need to change this to a Fiscal Year.
Select each label, in turn, and edit the name in the Formula Bar.
To change the Order of the Quarters: Right-click the 4th Quarter label and choose Order – Move to End.
Edit the Field Settings for the Quarters Field. Add in the SUM Subtotal.
Make the Quarterly Subtotals & Grand Total rows Bold.
Double-click any Quarterly Label to Hide the Details. Use the Icons on the Toolbar to Show / Hide details for each group to suit your needs.
A viewer wrote to me, asking for help in preparing his company’s payroll statement. I found an Excel Template using the =VLOOKUP() function to help him. In this Excel Video Training lesson, I demonstrate how to effectively use Lookup functions from 2 different worksheets.
Here are the steps to follow in this Excel Video Training Lesson:
