Best Microsoft Excel Bloggers

Monday, December 31, 2012

Apps for 2013: Business, Travel, Fun

Happy Holidays!

I hope that you all had a great holiday season. We were quite busy. First to Boston for Christmas with my family and then to Smugglers Notch in Vermont. We tried snow shoeing for the first time which was a blast. It was the best skiing in quite a few years although driving to the Manchester airport in the blizzard conditions was not fun.

As you may have figured out, we have become an Apple family - even my husband! Last year I shared with you some recommended apps and I thought I would add some more that might be of interest if you do in fact have an Iphone, IPad or Ipad Mini. All the apps listed below are free although some may have upgraded versions that cost money. This is just my personal list of apps. If you have any you want to share please do- I love to check out new apps.

The Kindle is still my favorite app. Unfortunately Apple's Book app while pretty visually is worthless as its book offering is minimal. Once you set up the Kindle App, you can order E books from Amazon and they automatically transfer and update on your Ipad. It is so easy.

Business Apps- there are a few that might be of interest to you.
Dropbox, and GoToMy PC are still two of my favorites from last year.
DocuSignInk is an app that my husband uses quite a bit. It allows you to sign your documents anytime, anywhere. It can be quite handy.
OneNote- I am always jotting notes on small pieces of paper and my desk overflows with them and of course you can never find the note you need. One of my last years's New Years resolutions was to get more organized so I downloaded OneNote. This is a nice app because One Note is part of Office 2010 so you can synchronize between your PC and your phone. It is a very nice program - I just need to remember to use it more.

Cam Scan is an app that I have just downloaded and it looks good. To be honest, it is usually just easier to take a picture of a page - essentially what the scanner apps do although this app apparently will convert it into a PDF and allow you to annotate it.

Adobe Reader can be useful if you are trying to read PDFs. I have had mixed results. I have not been able to get it to work with the CPE E-books that I sell on www.cpaselfstudy.com. I have  figured out a work around though. I have found that if you email yourself the PDF that the iPad and iPhone will open it from within the mail message just fine.. go figure!

QR Reader - my daughter and husband use this one all the time. Just hold your iPhone over the bar code in a store or on a product and it will read it.

Best Utility Apps
I am not sure that Find iPhone is actually a utility but it needs to be on your phone somewhere. You can set it up so that if you lose your iPhone,iPad, Ipad Mini or  Mac etc it will locate it for you. You can also set it to delete all data on the iPhone or other Apple product as well.

Flashlight can really come in handy.

Best Navigation utilitiesThank goodness Google Maps now has an app although I have become attached to MapQuest since it is good at pointing out if you have overshot or missed a turn.
If you travel, a number of cities such as Boston have mobile taxis that will pick you up very quickly -usually in a nice upscale car. You can pre-arrange or call as needed. The only one I have personally used is Uber in Boston (Uber is also now  in other cities) and they were very quick and professional.  New York is in the process of approving e-hailing  taxis so you will be seeing a lot more of these apps. It's one of those apps you want installed for when you really need it.

Health Apps 
Walk Tracker is the best. It uses GPS to track how many miles you have gone and how many calories you have burned. It also integrates with Itunes so you can listen to music as you walk or jog. The only downside is it is a huge battery drain.  If your News Years Resolution entails calorie watching check out MyFitness Pal. It works on the PC as well as the I phone. It is the best one I have found and I have looked at quite a few.

Travel Apps
TripAdvisor is my absolute favorite although Yelp and UrbanSpoon come in handy particularly if you are in a major city.
OpenTable allows you to make reservations on the fly.
I always try to fly Southwest and their app is great for Check In and finding out about delays.
SeatGuru will tell you the best and worst airline seats by flight#- this is really the most useful on international flights. isn't it amazing that there is an app for this!

Games
There are so many and to be honest my daughter introduces me to a lot of them.
Bejeweled - my absolute favorite
Spider Solitaire - 2nd favorite

Fun Logic games: 

  • Flow Free 
  • Fling
  • Iassociate2- this is such a cool game....
  • Sudoku2
  • LineUp2


Fun Word Games

  • Iassociate2 fits into both logic and word game 
  • SpellTower - my husband is now addicted to this one
  • Moxie
  • WhirlyWord
  • Words for Friends


I just downloaded a bunch of new ones over the holidays - can't wait to try them out.


Best wishes to you and your family over this holiday season.
I hope 2013 is a great year for you.







Monday, November 26, 2012

No Grandma!

I saw this photo today and it cracked me up - how many of you can identify with it?


Monday, November 19, 2012

Excel Arrays - Published by Texas CPA Society


We were really excited that the Texas CPA Society published our article "Incorporating Excel Arrays into Your Audit Plan". Click here to access a copy the Array article.

If it is of interest we have a more detailed version of that course for which you can earn CPE credit -
Using Arrays to become a Power Excel user.

We offer a lot of different Excel CPE courses. so if you are a CPA check them out when you get a chance.


Here is a link to other articles that we have written over the last few years.



Best wishes to you all and your families. I hope that you have a wonderful Thanksgiving.


Friday, November 9, 2012

Surviving the File Restore Virus

My Survival of the File Restore Virus

Well yesterday I was sitting around and getting ready to update this blog but first I made a serious error and went to YouTube. Out of nowhere, a virus called File Restore appeared with dialog box after dialog box telling me I had critical hard disk errors! Essentially it tells you that you have all these critical problems and low and behold just click the Buy Button and for $85 they will fix the problems. (There really are no problems except what they created. The Virus locks down your system and hides all your files.) Nice huh?
Looks really official - doesn't it? Just trying to instill fear !  

But hey, no problem, right - this wasn't the first virus I have experienced. After all,  I had a spyware program installed on my computer. Ha Ha. No, it is never that easy. I couldn't access the program since all the files were hidden. So, first thing I did was try to download Malwarebytes Anti-Malaware which always works. Not this time! Access Denied every time I tried to install it. Then I tried pulling it in off a flash drive - No! Access Denied again. Aha, you are all thinking hey go into Safe Mode and restore your system to a prior date.  Well, if not, that is what I thought - as I said I have been through this before.
(To get into safe mode you can just press and hold the F8 key as you restart your computer. You can also press the Window key and R to access Run and then type msconfig)

Again, I was blocked - it would not restore it to a prior date. Luckily I  realized that I had a backup CD of Spyware Doctor. I always pay for the CD as a preventative measure and boy did it pay off this time.(Maybe now my husband won't laugh at me for being so cautious.) After about 3 tries, in safe mode I was able to get Spyware Doctor to identify and remove a couple of the threats. Notice I said couple! Once those threats had been removed I was able to get back into the system. I could tell I still had problems but luckily File Restore does not shut off access to the internet and I found this handy dandy thread of how to remove the File Restore Virus and I wanted to share it with you.
http://malwaretips.com/Thread-How-to-remove-File-Restore-virus.

This thread walked me through all the steps including downloading Malwarebytes' Anti-Malware (which I was able to finally download and install ) and downloading links to two of my new favorite products HitmanPro, which removed File Restore's  Rootkit, and  RogueKiller which restored my shortcuts in the temporary internet folder and removed some malicious  registry keys (don't you love the names of these programs.)
Now, at that point, everything was supposed to be fine but I re-ran Malwarebytes and it found another high-level threat. After I removed that I re-ran Spyware Doctor which found another high-level threat.
So, definitely double-check. I will be running both of these and possibly a third again - just to make sure everything is as it should be.

Malawarebytes, Rogue Killer and HitmanPro were all free. HitmanPro gives you a one time free trial, Malawarebytes offers a free and an upgrade version and RogueKiller appears to be written by an individual who asks you to donate if you wish.

So, lessons to be learned.
Have active spyware on your system to prevent a virus from getting in. I thought mine was on but apparently I was incorrect so check it periodically and remember Malwarebytes and Spydoctor in case you ever need them.  I personally plan to upgrade to the pro version of Malwareebytes and I need to donate to the RogueKiller cause.  Without these my 3 or 4 hours of frustration would have increased tenfold and possibly ended with me reformatting the drive.
Oh yes... last lesson to be learned... BACKUP your files periodically. Backing up is one thing I try to do religiously and which is what allowed me to remain relatively calm as I removed the virus!

It's Friday - Have a great day and wonderful weekend. I just had 3 deer and  big buck walking through my backyard. It's time to move away from the computer and go and enjoy the beautiful day.



Thursday, November 1, 2012

Day of the Week

Day of the Week and Weekday()

I was working with dates and had to remind myself on how to display a date as a day of the week so I thought perhaps some of you might be interested in it too.

I was double-checking items and I wanted to verify that nothing was occurring on the weekend so the easiest way to do that would be for Excel to tell what day of the week that date was. 

In Cell A4 I had my date so all I needed to do was enter =Text(A4,"ddd") and it displayed Thu for November 1st.  If I had entered =Text(A4,"dddd") it would display Thursday.
Pretty easy - eh?


If you wanted to incorporate this into an IF or some other formula then you might prefer to have the actual number of the day as opposed to the text. In that case, you can use the Weekday function. The resulting answer for November 1,2012 is 5 which is Thursday.

Friday, October 26, 2012

LinkedIn- Privacy/Email Settings

LinkedIn - Privacy / Email Settings

I just finished writing a CPE EBook entitled LinkedIn for Accountants- What is it and What Do I Do with It?
Everyone talks about Privacy Settings on Facebook but no one ever mentions LinkedIn. It may not be as worrisome as it is not "personal information per se "however you should be aware of how and to whom your information is displayed.  I also thought I would help you understand how to change the frequency of emails you might be receiving if you belong to a number of different groups.


If you start joining a lot of groups and start getting a lot of emails, you can change the frequency of communication by clicking on Groups, Companies and Applications and making some changes

Thursday, September 27, 2012

Become an Excel VBA Master!

I wanted to tell you about Daniel Ferry's Excel Academy.
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 as well as some very important and useful business things as well.

The Excel Hero Academy that he runs  has been extremely popular and is now in its fourth year. Excel Hero Academy (EHA4) opens on October 1.  This program 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.  You will be an expert by the time you finish it.

This 16-week course will teach you to craft proper VBA, including truly understanding and leveraging the Excel object model, modularization, functions and UDFs, classes, branching, looping, and interfacing with external applications such as Access. But this is not a theory course, it’s hands-on, applied learning focused on business solutions including extraordinary Dashboards and other methods of reporting. Heavy emphasis is on database functionality both by using Excel exclusively, and with using Excel as the front-end to a real database.

Daniel says “We will push Excel hard, seeing just what is possible without any VBA coding at all (a fun example of this is my Excel Chess Game Viewer), but then Discovering the Next Level of power and the universe of possibilities that VBA brings to the table.”

The Excel Hero Academy is extremely in depth and is video based. 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.

 

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 tried it and that does not work :(
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. You jump right in – if you have been looking to move to the next level in Excel and VBA this is it. I will be attending this program again and hope to see you there.

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. Here is just one: “Daniel is an Excel genius who does extraordinary things with Excel, and uses his considerable skills to create exceptional training opportunities. ...inspiring!” – Doug Kim, Senior Editor, Office.com, Microsoft”

If you enroll in the class, you will be glad that you did. I have worked with Daniel and have secured a $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 still 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.



Tuesday, August 28, 2012

Page Orientation - 2 Ways to Change It.

The other day, I was sitting there changing the page orientation in Excel to LANDSCAPE AGAIN and thought AGAIN that there has to be a better way. I was also doing a lot of muttering about Microsoft and why the heck they have the default as portrait instead of LANDSCAPE anyway. Seriously, how often do you change your page orientation and think why am I doing this AGAIN?

Anyway.. I digress... of course I am thinking that I should stop being so lazy and complaining and just change it. So, I am sitting here thinking about changing something in Options or creating a macro because even though I use templates and create them from time to time - I totally spaced it and did not consider the obvious solution.  I was discussing this with some members of the Excel BlackBelts group on LinkedIN and of course a number of people immediately pointed out that I should just create a template or edit the existing  template.
(Great group by the way- if you are looking for an intermediate to advanced Excel group).

James Travers, a member of ExcelBlackBelts, gave me such as great example that I knew a lot of you would find it useful. His comment is below:

The default workbook is a template file called book (with the appropriate template extension .xlt, .xltx or .xltm) .
The default worksheet is a template file called sheet (again with the appropriate extension). These are in your xlstart folder which by default is in (on an XP machine) c:\Windows\Application Data\Microsoft\Excel\XLStart or c:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART but you can specify a different one in Excel Options.

Any other workbooks that are in that folder are automatically loaded on Excel start up.
I have my defaults set up with the company standard header & footer, print settings, corporate theme, cell styles, pivot, slicer & table styles. I also have an add-in to apply these settings to any other workbook on 1 click of a custom ribbon command button.

We've rolled the same settings out to the entire company through a log-on script meaning that all workbooks that get created now automatically have the correct format and any legacy workbooks can be updated with 1 click.

If you are interested in the add-in, feel free to contact James Travers, on LinkedIN.


If you can't change your template due to network restrictions etc, another option that was pointed out is to add the Orientation button to the Ribbon. To do this, go to File>Options and select Customize Ribbon. From the Choose Command from drop-down arrow, select All Commands. Scroll down and click on the first Orientation icon. Click Add and specify where on the Ribbon you want it to display. When you go back to Excel you will see the Orientation icon on the ribbon and when you click on it, you have the option of Landscape or Portrait.
Be still my heart..........look how much clicking this is going to save you!

Monday, August 13, 2012

Excel 2013 is coming!

Yes- I know that many of you are still using Excel 2003, Excel 2007 and Excel 2010 and quite happily however apparently Microsoft cannot wait until 2013 to release Office 2013. It is on its way.

At this point, I have not tried it simply because I still use XP and did not want to to upgrade after witnessing the debacle of Windows Vista however now I will be forced to since Office 2013 requires Windows 7 or better.  It looks to have several interesting things - one of which is that PowerPivot is now incorporated into Excel instead of just being an add-on.   The big thing though is that it will be cloud based which I know offers some security concerns for businesses.

As soon as I get it and play around with it, I will update you.

If any of you reading this blog, have tried the beta version, I would be interested in your comments.

http://www.microsoft.com/office/preview/en/try-office-preview

http://betanews.com/2012/07/22/a-preview-of-microsoft-office-2013/

Mobile Apps for Finance



If you are into stocks and investing I thought you might be interested in this link.
It discusses the top 17 Must Have Mobile Apps for Finance - some of the apps are free and some are pricey but a lot of interesting ones.

http://www.businessinsider.com/17-must-have-mobile-apps-for-investors-2012-8

Wednesday, July 11, 2012

NPER - Computing Number of Periods

This is a guest post from Joe Helstrom, CPA and is an excerpt from his Ebook Excel Time Values of Money Function for the CPA


Computing Number of Periods


One of the biggest questions asked in retirement planning relates to how long funds will last. Excel can compute that, as seen in the following example:
Your client has saved $1 million and wants to retire. He intends to withdraw $70,000 per year at the end of each year. He expects his investments to earn 6% per year. How long will his retirement assets last?

We know that the client has $1 million today (PV). He intends to withdraw $70,000 (PMT) per year and his Rate is 6%. As the PMT is on an annual basis, there is no need to adjust the Rate.
Our inputs follow:


The sign convention is at work again. The PV is negative as this represents funds invested (cash outflow). The PMT is positive as this represents a cash inflow.


Click on cell B5. Click the Fx button on the top left of the Formula tab.
Choose the “Financial” category and scroll down the function list until you see NPER.



Complete the inputs with Rate as cell B3, PMT as cell B2 and PV as cell B1.

Click OK.

Your client’s investments will last 33 years in this scenario.
==============================================================
This computation can also be monthly.



Your client has saved $1 million and wants to retire. He intends to withdraw $5,500 per month . He expects his investments to earn 6% per year. How long will his retirement assets last?


Note that our compounding is now monthly. Therefore, we must adjust the interest rate to a monthly rate by dividing by 12. We must also expect our answer to be denominated in months now, rather than years.
Our inputs follow:

Click in cell B5.  Type =Nper(B3,B2,B1),  The answer is below.
Your client’s retirement assets will last 480 months.  Dividing the 480 by 12, you get approximately 40 years.



Tuesday, June 19, 2012

PV of Annuities using Excel

This is a guest post from Joe Helstrom, CPA and is an excerpt from his Ebook Excel Time Values of Money Function for the CPA

Present Value of Annuities


An annuity is a periodic payment of a fixed amount. The most prevalent examples are car loans and mortgages. They add the payment (Pmt) variable. The present value of an annuity deals with the value today of a future stream of fixed payments at a specific earnings rate.

Example.
Your client has just won the lottery. He must choose between $1 million paid to him immediately or $150,000 paid to him at the end of the next 10 years. He can earn 5% annually on his investments. Which option should he choose?

We have two scenarios to evaluate. The first involves $1 million today (the PV). The $1 million involves no computation. It’s already a present value. The second involves a series of periodic payments of $150,000 (Pmt) over the next 10 years (Nper) to be evaluated at an annual rate of 5%. We want to know the present value (Pv) of the annuity in the second scenario to compare to the first scenario. First, we prepare our input spreadsheet.

Click on the Fx button on the left side of the Formulas tab.  It will ask you to select a category.  Select “Financial”.  On the bottom, scroll down and choose PV.  Click OK.  The function arguments wizard will appear.  Complete the function arguments wizard as shown below.
Click OK. 
Once again, ignoring the sign convention, the present value of $150,000 per year over the next 10 years at a rate of 5% is $1,158,260.  Which is better – $1 million today or $1,158,260?  In this case, the better deal is to take the $150,000 per year periodic payments.

If you want to make the answer a positive number, place a minus sign in front of the payment (Pmt) input.  The answer is negative as Excel assumes that you would need to pay (cash outflow) this amount to obtain (cash inflow) $150,000 per year payments at a 5% rate.

Monday, April 30, 2012

AGGREGATE()

Excel 2010 introduced a couple of new functions. If you currently use the Subtotal() feature, you might be interested in a new function that was introduced in Excel 2010 - AGGREGATE().


The AGGREGATE() function is similar to the SUBTOTAL() function; however, it gives you greater control over what to ignore or what to include. It is particularly handy if you routinely have error messages in data that you are trying to manipulate. You can specify that you want the calculation to ignore hidden rows, subtotals, eror cells and or any combination of the the 3.

The syntax is =AGGREGATE(function_num,options, array, [k])

The function number, like Subtotal() 's , is a number that refers to the function you want to perform. Aggregate's function number has been expanded to 19.


Options allows you to specify what you want to ignore or exclude:
Array are the cells that you are trying to aggregate/subtotal.

Below is a simple example. For Java Joe's I have filtered all the data to just see customer "Cup of Joe"'s sales.

Now, I just want to add up Cup of Joe's Total Sales in Column F.

If I tried to use the SUBTOTAL() function I would get an error message since F13 has an error value.
 

Instead, I can use AGGREGATE() and tell it to ignore any error values as well as hidden rows. The answer of 1053.2 is returned.


Friday, March 16, 2012

New Keyboard Shortcut

I have been woefully behind on this blog due to family illness and upgrading CPASelfstudy.com so I apologize to people who have left me questions however I will get them all answered shortly.
CPAselfstudy.com will be down March 17and March 18 as the site is upgraded and shopping cart changed. I can't wait for this to be finished :)

Meanwhile - A quick tip.
Display Formulas with Keyboard Shortcut
This one was new to me and I have to thank Francis, the ExcelAddict, for it.
If you have a large spreadsheet with a lot of formulas, a really quick way to display them is to simply Press the Control key and the tilde button. The tilde button is the upper left of the keyboard and looks like this: ~
Press Ctrl and ~ together and your formulas will display. It works as a toggle.
If you have long formulas, you will need to widen the columns to see the formulas and then manually return them to their previous width.

Have a great St. Patrick's Day!



Monday, January 30, 2012

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.

Thursday, January 12, 2012

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. 



Ms. Excel- Resident Excel Geek