Sorry- I just realized that I told a lot of different people about the free Excel CPE we are offering at CPASelfstudy.com but I forgot to blog about it here! I know, I couldn't believe I did that either!
Anway, we are offering a FREE... totally FREE... 1 hour Excel CPE course. Everyone always seems to be short that one odd hour.
The course is entitled, "Audit and Analytical Uses for Excel 2007 Filters and Tables. It is a brief overview course that focuses on Filtering data, Conditional Formatting and using Tables. It also briefly touches on Pivot Tables. So far, the reviews have been good. So, if you are a CPA needing CPE credit and want to learn something that you can immediately start using in your job, definitely check it out.
And of course, feel free to tell your friends and associates about it.
The link to the course is http://cpaselfstudy.com/item_185/Audit-Analytical-Uses-for-Excel-Filters-Tables-Free.htm
Monday, October 18, 2010
Thursday, October 14, 2010
Evaluating Formulas
In the past, whenever I have talked about debugging or evaluating a formula, I have always talked about the Evaluate Formula icon found on the Formula ribbon
Two ways to do the same thing... and yes, if we look around, we will probably find a 3rd method too. Have a good day.
It is very useful as it allows you to break apart complex formulas step by step. Click Evaluate to examine the value of the reference that is underlined. The result of that evaluation is shown in italics.
If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula. And then just keep going until you have worked your way through the entire formula.
If it is a long complex formula, I still think this is the best method but I learned a quicker method the other day on Chandoo's site and I think it is really useful if you are in a hurry or the formula is relatively short.
( If it is a long complex formula, I think it is easier to see the breakdown in the Evaluate Formula dialog box. - just my personal preference.)
All you need to do is double-click on the formula, select the component that you want to evaluate and press F9. If you have additional formulas, select them and press F9 again. When you are done, press ESC to get out.
Two ways to do the same thing... and yes, if we look around, we will probably find a 3rd method too. Have a good day.
Wednesday, October 13, 2010
Using Data Ribbon to Find the Top 5 Items
Below is an excerpt from the new course we are offering entitled "Audit and Analytical Uses for Excel Tables and Filters. It is a free course and has 1 Hour of CPE associated with it. Check it out at http://cpaselfstudy.com/item_185/Audit-Analytical-Uses-for-Excel-Filters-Tables.htm
Finding the Top 5 Invoices
For audit purposes, assume that you wanted to manually select the 5 largest invoices for testing. In this case, you would sort the Sales_Amt. column by clicking the Sort icon on the Data tab and selecting “Sort Largest to Smallest”. The top portion of the result is displayed below. Notice that all the related data is also moved.
That was the quick and dirty way and works if the data list is relatively small. If the list was larger, you would want to use a Number Filter to identify only the Top 5 invoices. In this case, you would click the drop down box next to Sales_Amt and choose “Number Filters”, then select “Top 10…” as shown below.
Using the arrow keys next to the number “10”, click on the down arrow until the number “5” appears. This will filter the list to the Top 5 items. It will now look like the figure below:
Finding the Top 5 Invoices
For audit purposes, assume that you wanted to manually select the 5 largest invoices for testing. In this case, you would sort the Sales_Amt. column by clicking the Sort icon on the Data tab and selecting “Sort Largest to Smallest”. The top portion of the result is displayed below. Notice that all the related data is also moved.
That was the quick and dirty way and works if the data list is relatively small. If the list was larger, you would want to use a Number Filter to identify only the Top 5 invoices. In this case, you would click the drop down box next to Sales_Amt and choose “Number Filters”, then select “Top 10…” as shown below.
Using the arrow keys next to the number “10”, click on the down arrow until the number “5” appears. This will filter the list to the Top 5 items. It will now look like the figure below:
Thursday, September 30, 2010
Migrating from Excel 2003 To Excel 2010
One of my CPE pilots sent me a link to a Microsoft PDF file that discusses migrating from Excel 2003 to Excel 2010. It is a short but nicely laid out article with lots of pictures!
I have discussed a couple of differences between Excel 2010 and Excel 2007 in earlier posts so I won't repeat myself but thank goodness File is back and Print Preview has moved back where it belongs under the File ribbon tab. It does not contain all the changes - just the basic ones that most people would be interested in.
http://officeimg.vo.msecnd.net/en-us/files/918/662/AF101882555.pdf
Thanks for the link Amy.
I have discussed a couple of differences between Excel 2010 and Excel 2007 in earlier posts so I won't repeat myself but thank goodness File is back and Print Preview has moved back where it belongs under the File ribbon tab. It does not contain all the changes - just the basic ones that most people would be interested in.
http://officeimg.vo.msecnd.net/en-us/files/918/662/AF101882555.pdf
Thanks for the link Amy.
Tuesday, September 28, 2010
2 Way Lookup using Index Match
I taught an Excel functions class at the Indiana CPA Society last week and thought I would share one of the examples we went over. We used the INDEX MATCH function to create a 2 way lookup.
It was a great class. If you missed it, look for it next year- It is called Functions Every CPA Needs To Know.
If you don't have time for the class, I am putting together the course and will be offering a self study version shortly so check at http://cpaselfstudy.com/category_14/Excel.htm end of October.
My Dashboards self study course is almost done. It should be avaible in another week or so as the CPE piloting is basically done.
2 Way Lookup
If I asked you to look at A1.E13 and to find the unit sales for TVs for the month of July, you would look down Column A until you found July and then you would look across the columns until you found TV and you would find the intersection point of 763 which is the answer. This is that a 2way lookup does.
1. Click in cell I3
2. Select the Index function from the Lookup category
3. Click OK
4. Enter A1:E13 as the Array
5. Click in the Row_number section of the dialog box
6. Click in the Name box and click on the Match function
7. Enter I1 as the Lookup Value
8. Enter A1:A13 as the Lookup_array
9. Enter 0 as the Match_Type
10. Click OK
11. You will have an error message – but don’t panic
12. Go up to the formula bar and click on the fx button to bring the Index dialog box back
13. Click OK
14.Click in Col_num in the Index dialog box
15.Click in the Name box and select the Match function
16.Enter I2 as the Lookup_Value
17.Enter A1.E1 as the Lookup_Array
18.Enter 0 as the Match Type
19.Click OK
Notice the 8 and the 4 in the dialog box (over on the right)- the 8th row and the 4th column intersect at cell D8. If you use a Data Validation drop-down for I1 and I2 so much the better.
For other software tips and articles, go to http://cpaselfstudy.com/pages/SoftwareTips.php
It was a great class. If you missed it, look for it next year- It is called Functions Every CPA Needs To Know.
If you don't have time for the class, I am putting together the course and will be offering a self study version shortly so check at http://cpaselfstudy.com/category_14/Excel.htm end of October.
My Dashboards self study course is almost done. It should be avaible in another week or so as the CPE piloting is basically done.
2 Way Lookup
In this example, we wanted to lookup Unit Sales for a specified month (I1) and for a specified Product Line (I2).
We need Excel to match the contents of I1 which is the month of July and the contents of I2 which is TV product line with the table in cells A2.E13 and find the intersection point and return the answer.
If I asked you to look at A1.E13 and to find the unit sales for TVs for the month of July, you would look down Column A until you found July and then you would look across the columns until you found TV and you would find the intersection point of 763 which is the answer. This is that a 2way lookup does.
1. Click in cell I3
2. Select the Index function from the Lookup category
3. Click OK
4. Enter A1:E13 as the Array
5. Click in the Row_number section of the dialog box
6. Click in the Name box and click on the Match function
7. Enter I1 as the Lookup Value
8. Enter A1:A13 as the Lookup_array
9. Enter 0 as the Match_Type
10. Click OK
11. You will have an error message – but don’t panic
12. Go up to the formula bar and click on the fx button to bring the Index dialog box back
13. Click OK
14.Click in Col_num in the Index dialog box
15.Click in the Name box and select the Match function
16.Enter I2 as the Lookup_Value
17.Enter A1.E1 as the Lookup_Array
18.Enter 0 as the Match Type
19.Click OK
For other software tips and articles, go to http://cpaselfstudy.com/pages/SoftwareTips.php
Subscribe to:
Posts (Atom)







