Chatting about Excel and More

Excel Hero Academy - Starts Feb 1

2012-01-30T13:35:00.001-05:00

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

2012-01-12T11:27:00.000-05:00

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

2011-12-28T14:48:00.000-05:00

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

2011-12-14T12:24:00.000-05:00

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

2011-11-21T23:34:00.000-05:00

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.

LEN

2011-11-17T10:06:00.000-05:00

I was getting my U.S. Christmas mailing list in order and noticed a problem on some of my zipcodes. Many of New England's zip codes begin with a zero so when I pulled my addresses into Excel to sort and filter them, Excel dropped the 0 from the beginning of the zipcode so for example, instead of 02186, Excel displayed 2186.
(It did this because it assumed the ZipCode was a number instead of text.)

The fix is easy.

In this case, I clicked in cell I2 and entered =IF(LEN(H2)=5,H2,"0"&H2)

LEN() counts the number of characters. So, I told Excel -if the number of characters in cell H2 total 5 then just display the contents of cell H2. However, if the number of characters in H2 do not total 5 then include a 0 at the beginning of the H2 cell contents. I used an ampersand (&) to tell Excel to join the 0 and the zipcode together however you could also have used the Concatenate() function- I just think the & is faster. Both LEN() and Concatenate() are Text functions.

Once I copied the formula down, I then selected all the zipcodes in Column I, copied them and then did Paste Values so that the formulas were overwritten with the actual numbers. I did that so that when I deleted Column H, my new zipcodes would display.

An alternative way would be to just hide Column H.

This IF assumed residential US zipcodes. If you had business zip codes which would have more than 5 characters or were including European postal codes then you would have to change the IF a bit.

Freezing Columns In an Access Table

2011-11-01T13:32:00.000-04:00

You can FREEZE one or more of the columns in an Access table so that they become the leftmost columns and are visible at all times no matter where you scroll.

Open a table in Datasheet view.
Select the columns you want to freeze by clicking the field selector (top of the column) for that column

To select more than one column, click the column field selector and then, without releasing the mouse button, drag to extend the selection.

Right-click and select Freeze Columns

Access handles this a bit differently from Excel - what it does is literally move that column so that it becomes the left most column in the table.

To unfreeze a column, Right-click on the frozen column heading and select Unfreeze Columns

And of course, Access does NOT move the column back to its original position - you have to move it manually.

Moving Individual Fields (Controls & Labels) in Access 2007

2011-10-28T12:02:00.000-04:00

Moving Individual Fields in an Access 2007 Form

Moving fields in Access used to be pretty simple - NOT ANYMORE. Microsoft has added a Control Layout feature so if you want to move controls and /or labels separately then you need to become friends with the Remove icon located on the contextual Arrange tab in the Control Layout group! The default is that all the controls and labels move together as a unit.
Oh and make sure you are in Design View- not the new Layout View they added.

In Access 2007, if you want to move an individual field it requires an additional step as the default is for all of the fields to move together.

In Design view, select the control and/or label you wish to move
Click on the Remove icon - found on the contextual Arrange tab on the Control Layout group
Now you can move the individual control and/or label.

Spreadsheet Controls under Sarbanes-Oxley Section 404

2011-10-19T21:42:00.000-04:00

This is based on an EBook Joe Helstrom, CPA wrote for CPASelfstudy.com entitled Spreadsheet Controls Under Sarbanes-Oxley Section 404

Spreadsheet Controls

Spreadsheets have become pervasive in most companies and have many uses. Those that are used in the financial reporting process are of most concern to the assessment of the effectiveness of internal controls over financial reporting mandated by Sarbanes-Oxley. Several steps are recommended to accomplish the needed assessment related to spreadsheets. The first would be to get a handle on the population of spreadsheets that are used in the company. Secondly, determine whether the spreadsheet is used in the financial reporting process. Next, identify risk factors of the spreadsheet and grade the overall risk. Next, identify any compensating controls that reduce or mitigate the identified risks. Lastly, determine what remediation steps are necessary, if any, for the identified spreadsheets.

It should be noted that a high risk spreadsheet used in financial reporting, even with compensating controls, may not be able to achieve an adequate level of control in a spreadsheet environment. It may be necessary to migrate the process to an application where the information technology controls are developed and maintained by the information technology function.

Inventory all spreadsheets
The beginning of the “top down” approach would be to identify all spreadsheets used by the organization in the financial reporting process. This would include financial reporting, plant accounting, treasury, tax and operations. This can be done at the department level by asking each department head or supervisor to create a list of all spreadsheets used with the following information:

• Spreadsheet name
• Location of the spreadsheet file
• Department using the spreadsheet
• Description of spreadsheet purpose
• Spreadsheet users that have access

In addition to the above, the IT staff can be enlisted to query the company’s networks for spreadsheet files. This will help ensure that the inventory is complete.

Determine how current spreadsheets are being used

Once the spreadsheet inventory has been completed, an assessment of spreadsheet use must be performed. The first step is to segregate the spreadsheets into categories. These categories may include financial, operational and analytical.

The spreadsheets that fall into the “financial” category will carry the most risk potential. These will include spreadsheets that:

• Validate account balances

• Support transactions or journal entries

• Compute financial statement disclosures

• Perform financial reporting controls

Operational and analytical spreadsheets may also be important depending on the organization. However, these generally are used for operational decisions rather than in the financial reporting process.

Determine the risk factors of the spreadsheet

The financial spreadsheets (as well as any others that are significant to the financial reporting process), must be assessed for risk. Risk factors will include:

• The use of the spreadsheet and the use of the spreadsheet output

• Materiality of the affected account balance or disclosure

• Potential errors in downloaded data such as an incomplete download or a download of incorrect data.

• Whether the spreadsheet uses complex calculations, formulas or macros.

• Number of individuals using the spreadsheet

• Size of the spreadsheet

• How well the spreadsheet is documented

These risk factors must be assessed along with the use of the spreadsheet and a risk rating should be assigned. As an example, a spreadsheet that is used for financial reporting disclosure, uses downloaded data, contains complex calculations and is used by a number of people would have a high risk rating. A similar spreadsheet used solely for analytical purposes would likely carry a moderate to low risk. Additionally, a spreadsheet that is used for a key financial control would likely carry a higher risk rating than one that is used to provide a list of documents. This is a subjective determination. It must be well documented so that a reviewer can assess the conclusions drawn by the company.

Evaluate compensating controls for risk factors

Certain organizations have already put controls in place to reduce the risk of material financial reporting error related to spreadsheets. These controls must be evaluated in light of the risk factors noted above and, once again, a determination must be made as to the effectiveness of the compensating controls. Compensating controls may include:

• Downloaded data has control totals that are compared the source data and validated by the user.

• If applicable, control totals or logic controls are used to validate user input.

• A logic inspection of the spreadsheet by an independent party is performed and documented prior to spreadsheet use.

• Spreadsheets are protected against unauthorized changes.

• Spreadsheet versions are used and, before a new version is utilized, it is tested and approved.

• Access to the spreadsheet is limited to authorized users via network access limitations and/or use of spreadsheet passwords.

• Spreadsheet documentation is adequate and up to date.

Once again, a determination must be made as to the adequacy of compensating controls. This can be a grade of either “Good”, “Moderate” or “Ineffective”. It is also a subjective determination.

Documentation of procedures

The spreadsheet inventory, description of use, risks and compensating controls should be summarized in a spreadsheet or workpaper. The documentation should also include your risk and control grades as well as a testing strategy for those spreadsheets that are deemed to have adequate compensating controls. Keep in mind that once a control has been identified, it still must be tested.

Remediation

For those spreadsheets whose compensating controls are moderate to ineffective, there should be changes made to enhance the compensating controls. Excel supports many compensating controls.

Keep in mind that a spreadsheet may not be appropriate for high risk accounts. In cases where the risk is high and the balance is material, migration to an application supported by information technology staff and control environment may be warranted.

Converting a Date to a Calendar Quarter

2011-10-14T10:44:00.000-04:00

This is a guest post from Joe Helstrom, CPA.
I thought you would be interested in this as Excel is not that user-friendly when it comes to dates.

How to Convert a Date to a Calendar Quarter

This tip involves some math. We're going to use just the numeric month portion of the date and divide by three. So, if the month is 3 (March), 3 divided by 3 = 1 (First quarter). If the month is 6 (June), 6 divided by 3 = 2 (Second quarter). If the month is 9 (September), 9 divided by 3 = 3 (Third quarter).

What happens when the math is a little messier? If the month is 2 (February), 2 divided by 3 = .66666. We know that this should be in the 1st quarter. If the month is May, 5 divided by 3 = 1.6666 and we know that this should be in the second quarter. The Roundup function needs to be used.

The Excel Roundup function "rounds up" a number. The syntax for Roundup is =Roundup(cell reference, Number of digits). If the number of digits argument is zero, it rounds up to the nearest integer.

So, if we have a date of 2/12/2011 in cell A3, we first need to know the month. The Month function will provide that. In cell B3, type =Month(A3). The result is 2.

We'll combine the Month function into the Roundup function. Using the same cell, B3, type =Roundup(Month(A3)/3,0).

This function takes the numeric month, divides it by 3 and rounds it up to the nearest integer. In this case 2 divided by 3 = .66666 which is rounded up to 1 (First quarter).

Try it on other dates. If, in cell A4, the date is 5/17/2011, in cell B4 type, =Roundup(Month(A4/3,0). Your answer is 2 (Second quarter).

If you have a series of dates that you want to convert to quarters, just copy the formula. You have a quick, easy way to convert dates to calendar quarters.
For courses that address these and other Excel tips and provide CPE to CPAs, visit http://www.cpaselfstudy.com/ or excel-diva.com for non-CPE Excel courses.

Sorting Pivot Data

2011-10-11T11:06:00.001-04:00

Sorting Pivot Data

Pivot tables are very flexible and it is easy to sort data.

In this example, I want to sort the Ship Via carriers in descending order.

1. Click and select the Ship Via labels in the pivot table (cells C5.H5)

2. Click on the Data Ribbon and select the descending Sort icon

You should see that the Ship Via carriers are now sorted with UPS first (see below) and note that there is a small arrow showing a descending sort order beside Ship Via in cell C4.

An alternative way to sort your data is to click the drop-down arrow beside the field in the pivot table

1. Click on the drop-down arrow beside Ship Via –(the Column Label at C4)

2. Click Sort A to Z and you will see the Products reorder themselves again.

3. Click the Undo icon so that the data is sorted in descending order again.

You can also use a custom sort that you have created however custom sorts are lost when you refresh a pivot table.

Print Macro

2011-09-29T19:02:00.000-04:00

I don't know about you but I am tired of always having to change my spreadsheet to a landscape orientation before printing. Personally, I think Microsoft should have made the default oreientation landscape. Anyway, I have been trying to play around and use macros so I decided to just write a macro to do it for me. This is a simple macro - all it does is change the print orientation to Landscape.

The green is just information for the user and is not part of the actual macro.
To access the Developer tab where all the macro features exist, in Excel 2010, you need to:

Click on File>Options
Select Customize Ribbon
Select Developer
Click OK

Once you have the Developer tab visible,

Copy the code below
Click on the Visual Basic icon (or press Alt+F11)
Click Insert>Module
A blank window will display
Click Edit>Paste
You can run it to test it or debug it if you wish
Click File>Close and Return to Excel
Click the Macros icon
From there you can edit it, run it, or click on Options to create a shortcut key.

If you record the steps yoursefl, your code will be a lot longer but it will still work. You could also add in whatever other print features you change all the time. I gave mine a shortcut key of Control+Shift+P but you can give it a key combination that makes sense to you.

Sub Landscape_Printing()

'
' Landscape_Printing Macro
' This macro changes the print setting to landscape.
'
' Application.PrintCommunication = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With
Application.PrintCommunication = True
End Sub

Pick From A List

2011-09-13T09:45:00.000-04:00

If you are constantly typing something such as a list of employee names or a list of cost center codes consider using the Pick From Drop-Down List. It is very easy to use.

Type your data. In the example below, I typed a series of names...

When I wanted to repeat one that I had previously typed, all I had to do was right-click and select Pick From Drop-Down List. The Drop-Down List displays all the names that I had typed above.

Excel then displays the list of names I had previously typed in alphabetical order. Now I can just click on the one I want and Excel will enter it for me. So, it saves a bit of typing.

And no, I'm sorry- I know what you are thinking but unfortunately it only works with text.

Data Validation, another feature in Excel, also allows you create a list and is a bit more flexible although this Pick From Drop-Down list is a bit quicker.

2011-09-02T11:31:00.000-04:00

USE THE OFFSET FUNCTION TO CREATE A HORIZONTAL CHART THAT AUTOMATICALLY UPDATES

In most cases people tend to add data to a chart vertically however there are instances where you need to add columns of data to your chart. To have the chart automatically pick up additional columns as they are added you should use the OFFSET function.
In the example below I have widget sales for January through March.

I wanted to create a chart so that as I added April through December sales into the chart, the chart would automatically include the new data without me having to do anything to the chart.

First, I need to create my chart as I normally would. I am using a column chart as i think it is a little easier to see.

Then I need to identify each of the 2 rows to Excel by creating defined names.
We are going to create a name and formula for the months and then for the widget sales.
Go to the Formula tab and select Define Name

In the Name box type Month_Labels
In the Refers To box: type =OFFSET(Sheet1!$A$1,,1,,COUNTA(Sheet1!$B$1:$ZZ$1))
Click OK

This is telling Excel to go to cell A1 and then move over 1 column and to start selecting data all the way to Column ZZ. Now currently there is only data Columns A through D but as we add additional columns of data, Excel will be looking for it.
Now we need to define the widget sales

Go to the Formula tab and select Define Name
In the Name box type Widget_Data
In the Refers To box: type =OFFSET(Sheet1!$A$2,,1,1,COUNTA(Sheet1!$B$2:$ZZ$2))
Click OK

This is telling Excel to go to cell A2 and to move over 1 column and select data from B2 through Column ZZ.

Click in the chart and select the data series and replace the cell references to the formula names of Month_Labels and Widget_Data

Now as you add April and May's data, Excel will automatically include it in the chart.

Click here to download the Excel file.

Apps and More

2011-08-30T11:45:00.000-04:00

I have really gotten attached to my IPAD. It has some really neat apps and I thought I would share a few before I get back to chatting about Excel.

If you are buying the IPAD as a reader, I have to tell you that in my opinion, ibooks is terrible. The book selection is poor. The good news is that you can download for free the Kindle App. You can buy your books from Amazon and with a click it downloads it to your IPAD. Amazon as I am sure everyone knows has a broad spectrum of EBooks.

If you are looking for news- there is tons of stuff. NPR News, CNBC, CNN, NYTimes, Teh New Yorker and the WSJ all have apps so you can read their material when you are in a wifi area.

Flipboard is an absoutely cool app as it set up in a magazine format and you can flip through the news, facebook updates, magazines that it allows you to select from. You can mix your social media, images and news all in one place. And it is FREE.

I am not a huge game player but a couple of the word games have me hooked. These are all free by the way.
My absolute favorite is Iassociate.
iAssociate is a word association game, where the goal of the game is to guess which words, or phrases, are associated to the other words in the game. Every word is associated to at least two other words, and the associations are meant to be things that you come to think of upon hearing the word. The first level is easy and fun and then it just gets harder and more frustrating as the levels go up but I am hooked- I have made it through almost all of the free levels and have actually bought the next level. This is totally addicting!

Fling is an absolutely cool game that my daughter introduced me to awhile back and I used to play on my ipod touch. You fling colored fur balls around the screen. You have to get them off the screen by bouncing them off one another. The first level or two is a yawn and then it gets interesting. You really have to strategize.

Three more traditional games are:
Whirly World presents you with a a wheel containing 6 letters. It is your job to come up with as many word combinations as possible in a minute.

Scrambe CE is similar to boggle. You have 16 letters and you have to drag or tap the letters to form words. You can play it solo or online.

Words with Friends is playing scrabble with a friend. You can play with someone you know or pick someone randomly that is online searching for a partner. This game can be frustrating as you have to wait sometimes for a couple of hours or a day to finish a game depending on how quick your opponent is. This game requires wi-fi to play.

Now you know what I do with my free time!

There are a lot of games and other apps out there so check them out if you have an IPAD or Iphone. They can be particularly handy if you sitting in an airport as they all work offline -except for Words with Friends.
These are just my opinions but since everything is free - it is easy to check them out for yourselves.
Have a great Labor Day and next next week I will get back to blogging about Excel.

Patricia

IPAD Tip- Organizing Your Apps

2011-08-19T07:28:00.000-04:00

Hi All
I was at the Maroon 5 &Train concert last night. It took place at Conseco Fieldhouse instead of at the State Fair grounds due to the Fair stage collapse earlier this week. It was an absolutely fantastic show but what was truly impressive was the fact that every single thing was donated- the FieldHouse, the band's performances and most impressive - all the workers. Every person working there including the ushers, stage hands and concession workers donated their time so that all funds could go to the Indiana State Fair Remembrance Fund. Hoosiers are a truly phenomal group of people.

If you are like me and have screen after screen of IPAD apps, consider organizing them. It is very easy-simply select an app
(press on it until it starts to shimmy)and then drag it on top of another app and the IPAD will create and put the apps in a box that you can name. Then just keep dragging apps into it. (To get it to stop shimmying- press the Power button once).
I now have a nice box of Card Games and another nice box of Word Strategy Games. Next week I will share 2 or 3 of my favorite game apps that you might want to consider downloading for your "free time" and then I will get back to Excel tips.
I hope that you have all had time to check out my new site Excel-Diva.com. We will be adding more materials to it and hopefully a few more videos shortly.

Have a great Friday and an even better weekend.

Introducing Our New Sister Site- Excel-Diva.Com

2011-08-09T11:31:00.000-04:00

Check out our new site at Excel-Diva.com.

We had quite a few requests from Non-CPA business people for the same high quality Excel EBooks that we were selling on CPASelfstudy.com so we decided to create a site for them.

The quality of the Excel EBook sand the step by step instructions are the same; however, since these courses do not need review questions, exams, certificates and state accountancy board approvals etc, we can offer them a significantly lower price.

So, if you are a CPA and have been using our courses, you might want to consider these for some of your employees. If you are in numbers oriented business position, you should also consider taking a look at these EBooks. They offer step-by step instructions and most include exercise files to work through and answer files. Best of all, if you don't understand something you can email me with your questions or comments. If you are looking for a group rate, just email me at either patricia@excel-diva.com or patricia@cpaselfstudy.com

We will be adding additional EBooks and videos over the next couple of weeks.

More Useful Programs - DropBox and SnagIt

2011-08-05T09:23:00.000-04:00

Below are two other programs that I have found very useful. Again, these are my personal opinions and I have no affiliation with either company and I am NOT receiving any referral fee etc. If you have found some other useful programs let me know. I would love to try them out.

DropBox
Another program that I have been using for about a year is Dropbox.  You can use drop box a couple of different ways. I can take files on my computer and put a copy in the Dropbox application on my computer. Once the file has been put in the DropBox on your computer - they are then available to you on the web.
How cool is that?   Dropbox also says that it works with mobile devices such as the Blackberry however I have not yet tried that.

You can also upload files to Dropbox.com and give share privileges so that others can access files. This is a lot more useful than emailing multiple people a file or files. You can even upload a folder.
People with share privileges can view the files, download the file or the entire folder. It is much easier to keep track of files this way - particularly if you are updating them all the time. Also, if the files you are sending are too big to email this is an alternative.
I really like this program because I was getting tired of sorting through my flashdrives to find a file- of course if you don't have an Internet connection then this would not be too helpful - I learned this the hard way when on my last vacation I ended up being without internet service for a few days (always have a Plan B!).
I tend to go the low cost way when looking for programs so dropbox met that criteria since it is free if you are storing 2G or less. Of course, if you want more storage than that, they have a monthly pricing structure. If this sounds interesting to you check out http://www.dropbox.com/

SnagIt
I have used SnagIt for years. It is a screen capture program. You can capture an entire window or just a piece of it. You can add text and arrows if you want.You can then save the image as a file or to the clipboard.   You can get a 30 day free trial but you have to buy it after that. I have tried other screen capture programs and have always come back to this one so even though it costs about $50, I personally think it is worth it if you doing a lot of screen captures. http://www.techsmith.com/snagit/features/win/

Useful Software Programs

2011-08-02T21:50:00.000-04:00

Do you like the new design of the blog? If you didn't notice, don't feel bad- it is not that different.
I have been working away on my new Excel Site called Excel-Diva.com however it is not quite ready yet. I will tell you more about it later in the week when everything on the site is actually working. There is nothing more irritating then clicking on a button that takes you nowhere.

Right now, I want to talk to you about some different software programs that I have been using. These are my personal opinions. I have not accepted any fee etc for these recommendations. Between traveling and getting an IPAD, I have been using a lot of different software and Apps so I just throught I would share a few with you that I have found useful.

These two software programs relate to sending/uploading files.

First, if you are sending a lot of files - particularly large ones consider a zip file. The person receiving the file will certainly appreciate it. Also, a lot of mail systems now will not send large files.
I have used WinZip for years and have never had any problems with it. Winzip compresses your files and you can send a group of files or even folders in a compressed .zip format. You can also turn the zip file into an executable file so that the receiver only has to double click on it and the files all extract. You can password protect the .zip file and .exe files. You can also extract or add files into a compressed file.
So, if you have not used a zip file check it out. They offer a 30 day free trial which is always nice.
http://www.winzip.com/win/en/index.htm

The downside to the .zip and .exe file is that some firewalls will not accept them. In those cases or in cases where my .zip files is still too big for my mail system, I use YouSendIt.
I just started using this program about 2 months ago and have been very pleased with it. They have a totally free subscription and then of course they have the enhanced versions. I started out with the free subscription but I did move up to a paid subscription simply because I wanted to send multiple files at one time. You can send files to people and see when they have downloaded the files. You can upload files and share them with others. If you are interested check them out. http://www.yousendit.com/compare-plans?incid=topNavPlansSignUp_txt

That's it for now. Later, I will tell you about some cool IPAD apps.

Excel Hero Academy- Starts August 1st!

2011-07-28T01:09:00.000-04:00

I am still tunneling through the mountains of paperwork that resulted after a 2 week vacation however I wanted to make sure and mention Daniel Ferry's Excel Hero Academy to you all.
The Excel Hero Academy starts on August 1st so I obviously need to tell you about it now! There is not much time left to sign up.

Daniel Ferry, an Excel MVP, runs the Excel Hero Blog and the Excel Hero Academy. If you have a chance check out his blog and this course he is offering.
I will tell you that I am only recommending advanced Excel users take a look at it - that is strictly my opinion, of course.
There is no CPE currently being offered although Daniel and I are going to talk about that and see if we can offer CPE in the future on Excel Hero Academy or some portion of it.

The content, recommendations of others and testimonials from prior students is impressive and I would recommend this course anyway however I do want to let you know that I will receive a small fee if you register for the course.
Here is the list of the 12 (very in-depth, to the point of advanced) weekly modules included in the academy (remember that each module is strongly peppered with VBA):
Click here to visit excelhero.com.

Module 1: Named Formulas

Module 2: Array Formulas and Named Formulas Continued

Module 3: Charting 1

Module 4: Dashboards 1

Module 5: Database Techniques

Module 6: Advanced and Creative Formulas

Module 7: Charting 2 with Animation

Module 8: Excel and Access

Module 9: Dashboards 2

Module 10: Classes (VBA on Steroids)

Module 11: Optimization (Speeding up large/slow workbooks)

Module 12: Extreme Excel

It looks extremely interesting and I know a few of you are licking your lips over it. I know that I am. So, check it out and even if you don't have the time to take this particular session-(there will be others), check out Daniel's blog as you may find that useful too.
Click here to visit excelhero.com.

Creating Multi-Tiered List with Data Validation and INDIRECT

2011-06-24T14:28:00.000-04:00

I found a great tip on one of my LinkedIn Excel Discussion groups. It was how to create a multi-tiered list.

The example used was state and city. Using a data validation in Column A to list states, the person wanted the associated cities to display in Column B. In other words, if Cell A1 displayed Indiana then B1 should display a list of related cities. If A1 changed to Massachusetts then the list of related cities should change to reflect that.

I thought I would share this tip with you but I wanted to take it a step further so that you can see how to do this using separate sheets and not have all the data in one sheet. I also decided to do a different example so that you would see some other uses for it.

We are going to create a Product List and as different products are selected, the associated model numbers will display.

Let's go through this:

I have 2 sheets - a Summary sheet and then a Model Information sheet.
Here is the sheet named Model Info. It contains the data we are going to work with.

Here is the Summary Sheet:

Put your cursor in cell A2 and then go to the Data tab and select Data Validation.

Select List from the Allow: dropdown
Put your cursor in the Source: dialog box and select A1:A4 on the Model Info sheet.
Click OK.

The Summary sheet now has a drop-down arrow at A2.

Click in A2 and copy that blank cell with the drop-down arrow down to A5 or A6.
Click on A2 and select the drop-down arrow and select VCR from the list we just created.

What we want to do now is set up Column B so that it will display all the models associated with whatever is displaying in Column A.

Let's go to the Model Info sheet and create a range name for all of that data.
Select A1.I14 on the Model Info sheet.
Click on the Formulas Tab and select Create from Selection.

Make sure that there is only a checkmark in left column and click OK.
This is an easy way to create multiple range names all at once. We are telling Excel to select the Left Column which in this case are the Product Line names in Column A and create that as the range name for all of the data to the right – the model numbers. This is a lot faster than individually naming each of the ranges.

Now, let’s go back to the Summary sheet for the final steps.
Select all of Column B (select Column header).
Select Data Validation again.

In the Allow: dialog box select List again
In the Source: dialog box, type =indirect(A1)
(make sure that it is a relative reference - not an absolute cell reference. If it an absolute, every cell will display the same contents).

Click OK.

Since we are referencing A1 that contains the word Product, Excel is going to give us an error message. When you see it just click Yes that we want to continue.

To get around the error message, you could have selected specific cells in Column B but I took the lazy way of selecting the entire column.

Click on the drop down arrow at cell B2 and you should see all the model numbers associated with the VCR product line.
Click on A3 and select another product and when you click on cell B3, you will see model numbers that are associated with that product. In my example, I selected CAM for camcorders and B3 shows all the associated model numbers.

Using Weekday() function in Fraud Audits

2011-06-16T08:36:00.000-04:00

I just looked at the last date I blogged and realized that I am woefully behind!
I have been very busy. I just finished writing a step-by-step EBook on Pivot Tables.
This time I wrote the EBook both for CPE credit and then created a separate EBook for people looking to purchase the EBook but who don't need CPE credit for it. Go to http://cpaselfstudy.com/category_14/Excel.htm if you are looking to purchase the CPE course (It is a 5 Hour course). If you just want to purchase the Ebook, you can find the information on that over on the top right-side of the blog under EBooks and Videos for Purchase.

In addition to that, I just finished reviewing an Excel course entitled "Excel Time Value of Money Functions for CPAs " which was written by Joe Helstrom and which can also be found at http://www.cpaselfstudy.com/.

Finally, I just finished my first 1 hour video course on Excel Tools and Tips. I am very excited about that and hope to add a few more soon.

Plus, of course it is summer and you know how that goes!

So, I am taking the lazy approach here and picking an excerpt from a course Joe and I wrote recently entitled " Fraud Audit Techniques Using Excel" that I thought you might find useful. If you are interested in this course go to
http://cpaselfstudy.com/item_188/Fraud-Audit-Techniques-Using-Excel.htm

Using the Weekday() Function in a Fraud Audit

You can use date functions to identify potential fraud such as checking for activity on weekends.
One quick test is to see if journal entries are being processed on a weekend.

The Weekday function returns the numeric value of the day. So, for example, if you test September 1, 2010, =Weekday(A1) will return a value of 4 since 9/1/2010 was Wednesday the 4th . You could then use conditional formatting to review all the values of 6 and 7 to see if there is anything unusual about the date or you could just sort it.

• Select cell E2 and type =Weekday(A2,1)

• Press Enter

• Copy it down the column

Now that we have the weekday values, we could sort by number or we could use conditional formatting to identify Saturdays and Sundays (6 and 7)

• Select your data E2.E23

• Click on the Conditional Formatting icon on the Home Tab

• Select Highlight Cell Rules

• Select Greater Than….

• Enter 5 in the dialog box and click OK

Your data should look like the screenshot below:

You can easily see what transactions were occurring on Saturday and Sunday (6=Saturday and 7=Sunday)

Organize, Summarize and Analyze Data with Pivot Tables

2011-05-20T12:55:00.000-04:00

As many of you have noticed, I have been blogging about Pivot Tables for awhile and the good news is that I have finally finished writing my new updated Pivot Table EBook. It covers everthing - from creating a pivot table, to drilling down for data, to creating multiple reports from a single pivot table. It concludes by showing you how to update your data automatically. The EBook uses exercise files so that you can walk through and follow along.
If you want to buy the 5 Hour CPE Course on pivot tables, click here and go to cpaselfstudy.com
If you just want to purchase the EBook and files (with NO EXAM or CPE Credit) click here.

I have now added a column on the right with EBooks to Purchase in case you are interested. I will be adding more to it shortly.

Enjoy the weekend.

Creating Multiple Pivot Tables All At Once

2011-05-20T11:36:00.000-04:00

If you are using Pivot Tables, it is extremely easy to create multiple pivot tables from your original pivot table by using the Report Filter.
The Report Filter which displays at the very top of the pivot table is the best feature within the pivot table as it controls all the information seen in the pivot table. It also allows you to easily generate multiple pivot reports.

In the example below, Ship Via is showing as the Report Filter. Currently, I am seeing the total orders for all the ships but I can easily generate a separate pivot table for each of the individual carriers.

1. Click on a cell in the pivot table

2. Go the Options contextual ribbon

3. Select the little down arrow next to Options (in the first group on the ribbon),

4. Select Show Report Filter Pages…..

4. Select the Report Filter that you want to view in detail. We only have one report filter here so you would select
5. Click OK

It may seem as though nothing happened however if you look down at the sheet names in your file, you will see that Excel has created worksheets for all the individual carriers. In my example, I now have a worksheets named UPS, Parcel Post, FedEx etc and when I select the sheet named FedEx I have a pivot table just showing the orders FedEx. If I click on the UPS sheet, then the pivot table displays just the order information for UPS.

Here is another quick example: If the report filter allowed you to select between 4 Salespeople, Smith, Jones, Brown and White and the Report Filter instead of displaying All was displaying Smith, you would see 3 sheets at the bottom that Excel created – Jones, Brown and White. (Jones would not display as a new separate sheet as that is already displayed on the “original worksheet”.

This can be a big timesaver.

Video: Pivot Tables Make Everything Just Right

2011-05-11T08:25:00.000-04:00

Someone posted a comment and sent me a link to a video on Pivot Tables.
For a class, some students at Ball State put together this musical video which is now on Youtube of course.
A for effort and creativity.
Who says Excel is dull?
Pivot Tables Rock!
Click the link if you are interested in watching it.
http://www.youtube.com/watch?v=GwbSJFtRmDw&feature=player_embedded