Excel Hero Academy - Starts Feb 1

I wanted to tell you about Daniel Ferry's Excel School. Daniel is a Microsoft Excel MVP and runs a really interesting Excel enthusiast blog. If you have not discovered it yet, definitely check it out. It’s called Excel Hero . What makes Daniel's blog interesting is that he does all sorts of things with Excel that no one has ever seen before. Some really fun things to be sure, but very important and useful business things as well.

Daniel started an Excel training course a few years back at the request of his readers It is called  Excel Hero Academy . The Excel Hero Academy has been extremely popular and is now in its third year. Registration for Excel Hero Academy (EHA3) opens on February 1.

Excel Hero  teaches you how Excel “thinks” and how to leverage that to be drastically more productive at work. This makes you a valuable commodity in today’s competitive workforce. I sat in for a week or two and was impressed with the caliber of the teaching as well as of the students.  I plan to sit in this session too now that I have some "free time".

The Excel Hero Academy is an extremely in depth, 16-week, video training program. If you can dedicate three to four hours a week for the next four months, the course will revolutionize the way you solve business problems. You will be transformed, literally, into an Excel Hero.  There is homework and I want to be clear -you need to have the time for this course - you just can't squeeze it in and think you can do it in ten minutes.   I will also state that I personally would only recommend this for high intermediate to advanced Excel users and those having an interest in learning more about VBA.


The entire course can now be accessed from iPhone and Android devices in addition to the normal web based academy access. EHA Mobile is distributed as a free app (for paid students of the academy) in both the iTunes App Store and the Android Market. And the academy forums now support Tapatalk as well.


Daniel is committed to continually improving the Excel Hero Academy and the results speak for themselves. Just take a moment to peruse the many dozens of student reviews on his LinkedIn Profile.

If you enroll in the class,  you will be glad that you did. And here is the icing on the cake. I have worked with Daniel and have secured a special $50 discount for you. To learn more and enroll in the course click here
Your $50 discount code: CPASELFSTUDY.

If you sign up let me know. I always appreciate feedback particularly when I am recommending something. Daniel and I are talking about ways to set up this course for CPE credit down the road so all comments etc are appreciated. I also want to let you know that I do receive a small commission for anyone who signs up.
Have a good week.

Custom Lists

Customizing a List
I spent a little time hunting around for Custom Lists in Excel 2010 so I thought you might be experiencing the same problem.

A custom list allows you to create a list of names or data in whatever order you want. You can then sort on this list and Excel will sort in the order that you created it in. You can also use autofill to fill in the list.
People typically use it to create lists such as  product lines or sales regions.  You can sort products or sales regions in alphabetical order but perhaps you want to sort by highest revenue generator? - that is where the custom list comes in.



Click on the File tab
Click on Option
Click on Advanced on the left side of the dialog box
Scroll down until you see Edit>Custom Lists

Make sure that your cursor is on New List  and then click in the right column entitled "List Entries" and type a name and then press Enter. Continue until all the names are entered. In the example to the below I typed different sales regions in the order I want them to sort.

Once you complete the list, click Add and it adds it to Microsoft's existing custom list of days and months.

If you already have a list, you can simply import it instead of typing them in.


Click OK.

Go to an Excel worksheet and type in the word West and then use the autofill handle to copy it down and you will see that West, Mid Atlantic, New England etc display in the order specified in the custom list.
If you go to the Data tab and click Sort and  click on Custom List.. under Order you will see the sort list there also.

Click OK and the Custom Lists dialog box opens. 

Best Wishes over the Holiday Season

I hope everyone had a great Christmas, Hanukkah or whatever holiday you celebrate. It is a great time to get together with your family and other people who are important to you and to remember the reason why we are all here.

I had a wonderful time getting together with my family in Boston for the holidays. The only minor disappointment was we spent a few days in Vermont and there was literally no snow - so no skiing unfortunately.

I am pretty excited. I got a MAC laptop for Christmas and feel like I am back in kindergarten learning how to scroll and select data. I also got the Mac version of Office so that should be interesting. I will let you know how this goes.
The Mac laptop has some great features that I am looking forward to playing with. Luckily I have my daughter as the resident Mac guru. It seems that with this I am reverting back to shortcut keys to do everything. The command key is constantly in use. When I tweeted about my laptop and commented about learning how to right-click, I had a couple of people tweet me back to see how to do it. 
To right-click on a Mac laptop, put two fingers - your index and forefinger -slightly apart on the track pad area and then press down with your forefinger.

Year End is almost upon us. Yikes! Enjoy the rest of the week.

Apps to Add to your Holiday Shopping List

IPAD/IPHONE APPS - Must haves! So, do you have all your Christmas and holiday shopping done?  Of course you do! So, now it is time to consider some stuff for yourself!

If you have an IPAD  or IPhone already or asked for one for Christmas - these are recommended apps for you.

iBooks is nice but the Kindle app is the best book app out there as far as I am concerned - simply because it has a huge selection to choose from which IBooks does not.  I have both the iBooks app and the Kindle app on my IPAD and find that I rarely use the ibook app. If you download the Kindle app - it will  automatically take you to Amazon.com where you can look and then  click to buy the Ebook. It will immediatlely download the Ebook to your ipad. If you purchase an Ebook on a desktop or laptop, the Ebook will automatically download next time you open your IPAD. Best of all both apps are free. The one thing that I love about the ibook app is that it will tell you if you have already purchased the Ebook  from ibook so it tracks your buying history -just as it does on ITunes - if the Kindle app did that then it would be perfect.

FlipBoard is a social magazine app.  This app is set up as a magazine and when you flip it open it displays the different feeds that you have selected. If you want to see that particular newsfeed - for example, ABC News, then you  touch it and the app will then show you a screen of articles from that periodical.If a headline catches your interest you can touch the headline and the rest of the story appears.  My Flipboard opens and displays my favorites: USA Today, Bloomberg, Business Insider, Huffington Post, Twitter, Facebook and a couple of others.

Apple offers Iwork which is an office suite including a wordprocessing, spreadsheet  and presentations however all 3 are disappointing and at $9.99 each the only one I would even recommend you consider is Pages, the word processing program. Pages is a very poor man's wordprocessing program and only does the very very basics. I think you are better off going over to Google docs.

Dropbox has an app. It offers a free service or you can pay for additional features and space. Dropbox allows you to store documents, photos etc on the cloud. This is great if you travel so if you lose that flashdrive you are not out of luck. It can also be a great way to share documents.

GoTo Meeting is a free app that allows you to attend online meetings on your IPAD or Iphone. Very easy to use if you are familiar with GoToMeeting.

GoToMYPC - you can try it for free . It allows you to access your pc remotely. Some of my friends have had great luck with this app. I unfortunately have not gotten it to work consistently - partly because we have a lot of quick power outages and when my pc reboots the program doesn't restart.  You can try it for free so take a look but always have a backup plan just in case it doesn't work when you need it.

Text+ and Textfree allow you to text if you are in a wireless environment. Useful if you don't have texting on your phone - particularly if your phone's battery is dead.  You are assigned a phone number that you text from. It can be useful but I added texting to my phone I tend not to use it anymore.

Evernote is one of the top apps . It is designed for note taking and archiving. You can save images to find later, you can view PDFs, you can click on New Note to jot down some thoughts and you can even clip and save a webpage using a Web Clipper. This is one I just downloaded for myself.

Now, for  you procrastinators, while you wait in line to make purchases and are looking for a mindless holiday game that becomes addictive - my daughter recommends Cut the Rope and I recommend LineUp.

If you want to exercise your mind check out these fun word games. Fling, iAssociate2, Moxie, 7 Words and WhirlyWord. They are all free although on some of these I have finished all the levels so I will have to buy the next levels - guess I need to add that to my Christmas list!

If you have any favorite apps that I have not mentioned, please let me know and I will be glad to include them.

Best wishes for the coming holidays.

Converting Text Dates to a Numeric Dates

Converting Text Dates to Numeric Dates

Sometimes when you import data, particularly dates,  Excel treats the data as text. You can use the DATEVALUE() function to convert text dates to real numeric dates.
 DATEVALUE() returns the serial number of the selected date. Once you have the serial date, you can format it to display as a date.

In this example, the data in Column A are dates that Excel treats as text. (A good way to tell if Excel is treating something as a number or text is to check the alignment. Text is left aligned whereas numbers are left aligned.)  In column B2, I used the formula =DATEVALUE(A2) to change the text in cell A2 into a serial number which displays as 39894.

39894 is the serial number of the date in cell A2.



To convert it to something meaningful, you just need to go the Number group  on the Home Tab

and click on the drop-down arrow beside General



In this particular example, I then selected the short date

The result is shown below:

DATEVALUE() only works on text dates. If you try it on a column of text and numeric dates, you will get a #VALUE error on the entries that are numeric.