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.

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

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

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






Pin Your Spreadsheet

GET PINNED!!!!

I actually got this tip idea from Francis Hays, "The Excel Addict" but I really liked it so I thought I would share it.  I learned how to pin my files awhile ago and of course promptly forgot it since I did not use it. (Let that be a lesson to you.)

Anyway, I have a couple of spreadsheets that I open almost on a daily basis so usually I can just go to File>Open or look in Recent Documents and easily find them. Sometimes, though I have opened a lot of other files that day and of course, then I have to go search for these files- which can be a pain.
What you can do is PIN the file and although it will move down the most recently used list, it will not disappear until you unpin it.

Click on the Quick Office Button.

Click on the gray pin (located to the right of the file name)

The pin will turn green to indicate that the file is 'pinned' to the list.

Click on it again and it will turn back to gray and be unpinned. 
How easy is that? Now that I have been reminded of this trick, I plan to use it.
Have a great weekend.

Subtotal Example with Filtering

Using the Subtotal Function
I wanted to spend a minute and show a subtotal example because of some emails I received. A lot of people seem to be only familiar with the Subtotal command on the Data tab or Data menu that automatically creates the subtotal for you . Obviously that is the fastest way but typically it requires sorting your data and sometimes you don't want to do that.

In those cases, you would want to use the SUBTOTAL function .
=SUBTOTAL(function_num, ref1, ref2, ...).

The beauty of the Subtotal feature is that it does not have to be at the bottom of the data- it can be anywhere on the sheet. In addition, you do not have to manipulate your data.
Below is a simple example, where I filtered sales by State and I wanted only the total for Massachusetts' sales. When I use =SUM(C6:C9) and select the filtered range, it added up all the sales of all states which was 1800.
When I used the Subtotal function on the filtered data =SUBTOTAL(109,c6:69), Excel returnd the total sales of the filtered data which was 900.

Enjoy the week. Fall may actually be here - crisp weather and the leaves are just starting to turn.

Excel Arrays - Audit Plan Uses

I hope that everyone had a great Labor Day.
We went up to Chicago and had a great time at the Field Museum and shopping as well as eating at some great places. The weather was perfect.
I've been busy redesigning and reformatting CPASelfstudy.com to make it easier to navigate so I have not put a lot of time into this blog lately however that should change shortly.

I did want to mention that the Missouri CPA Society published our article on Incorporating Excel Arrays into Your Audit Plan as that may be of interest to you. They have an online version of  The Asset that can now be accessed  -  http://theasset.dirxion.com/WebProject.asp?BookCode=jul10flx&from=2