Journal of Accountancy Large Logo
Sum highlights for you  
By J. Carlton Collins, CPA
December 2012

Q: I was disappointed to find that Excel’s AutoSum tool produced an error in my workbook, which I did not catch until it was too late. The problem resulted when I highlighted row 15 (as pictured below), and then clicked the AutoSum tool to insert formula totals at the bottom of each column.

Because cell B3 was empty, the AutoSum tool detected data only in rows 4 through 14 and inserted erroneous total formulas, which did not include the data from rows 2 and 3. This error was embarrassing. Is there a way to use the AutoSum tool so that it doesn’t make this kind of error?

A: In your example, you left it up to Excel’s AutoSum tool to guess the range you wanted to sum, and as you pointed out, the results contained formula errors. In the future, you can force the AutoSum tool to select the correct sum range every time by highlighting the data range to be summed and then clicking the AutoSum tool located in the Editing group on the Home tab.


Small keys create big problem  
By J. Carlton Collins, CPA
December 2012

Q: Help! My laptop has half-size keys, and this keyboard is almost impossible to use with my large fingers. What can I do? (The image at right shows two normal size left and right arrow keys, and two half-size up and down arrow keys.)

A: The same thing happened to me earlier this year, and I found this frustrating, too. I purchased a laptop computer for use in delivering CPE courses, but I was unaware how difficult it is to use half-size keys. The problem became very noticeable the first time I taught an Excel course in front of a live audience. I was unable to smoothly use the smaller up and down arrow keys without accidentally pressing adjacent keys. This made me look more like an amateur than a seasoned presenter. Luckily, I always carry an older laptop as a backup, and I quickly switched to my older system to complete the presentation.

Later, I practiced for several hours in an effort to get used to the smaller keys, but I found that no amount of practice would make my fingers smaller. At that point, I purchased an external keyboard. (Actually, I bought two of them because the first one was too noisy.) Unfortunately, these external keyboards were problematic because they do not always fit well on lecterns (or in my luggage).

My final attempt to fix this problem involved gluing a piece of plastic across the two smaller keys, and then using ATNSOFT’s Key Remapper tool ($14.95) ( to remap the Shift key and half-size up arrow key. As a result, the large Shift key is now my new up arrow key, and the old half-size up arrow key no longer has a function. This solution provides me with full-size up and down arrow keys, before and after pictures of which are shown below.

Before the fix:

After the fix:

The new keys took a little practice, but this solution is working fine.

Note: I considered returning the computer to the place of purchase, but I’d already spent more than 40 hours loading and configuring the device—so a laptop swap would have set me back even further. I also contacted the manufacturer to order a replacement keyboard with full-size arrow keys, but none was available for my laptop model. In summary, I classify my experience as a lesson learned in the school of hard knocks, and I advise everyone to avoid half-size keys, especially if you use Excel heavily.

Run a tab for this table  
By J. Carlton Collins, CPA
December 2012

Q: I use a lot of tables in Word, and it would be nice if the Tab key would actually indent my table data, but that functionality does not work—the Tab key simply moves my cursor to the next cell in the table. Is there a setting that makes the Tab key work properly in a table?

A: To indent text in a table cell, press Ctrl+Tab.

I've got you pegged  
By J. Carlton Collins, CPA
December 2012

Q: We use a wall map with pushpins to keep track of the location of our service technicians, but this manual method requires continual phone calls and pushpin adjustments. Can you recommend a cheap and easy technology solution that would enable us to see where our service technicians are and possibly keep a record of their travel routes?

A: Install the Google Latitude app (free) on each of your service technicians’ smartphones. (Latitude runs on the AppleiOS, Android, BlackBerry OS, Windows Mobile, and Symbian S60 platforms.) Thereafter, Google Latitude will track and record each service technician’s movement and position, allowing you to view his or her location on a map in real time. Steps for deploying Google Latitude are as follows:

1. Sign up for a free Google account at Have each service technician do the same.

2. On each service technician’s smartphone, visit the smartphone’s app store and search for, download, and install Google Latitude. (If Google Latitude is not available at your app store, then download, install, and launch Google Maps, press the Menu button, and then select Join Latitude). Read and accept the agreement, and launch Latitude. From the Latitudemain screen, click the Mini button, select Location settings, and make sure the following two settings are enabled:

a. Detect your location

b. Enable automatic check-ins (this setting may not be available for all smartphones)

3. Next, on your desktop computer, from the Latitude main screen on the website, click the Add Friends button, then to request tracking approval, select either Choose from your Google contacts or Invite a friend by email address, select each service technician from your contacts list (or enter his or her email address), and click OK.

You can also add friends from the smartphone app, but the menu options are slightly different. To add friends to your tracking list in the app, from the Latitude screen click the Invite Friends button (the icon consisting of a plus sign and the silhouette of a person), choose either Select from Contacts or Add via email address, select each service technician from your contacts list (or enter his or her email address), and click OK.

4. Your service technicians will receive a request asking them to approve your tracking of their movements via Latitude, and they should accept your request.

5. Upon the technicians’ approval and after applying the two setting adjustments described in step 2, you can then view their locations by launching and logging into Latitude on your desktop computer or smartphone. Examples of Latitude’s mapping screens on the desktop and the smartphone are pictured below.

6. Important notes about Latitude:

a. Keep in mind that Latitude does not track the person; it tracks the movement of the smartphone on which the app is installed.

b. The map locations pinpointed and displayed in Latitude are based on predetermined check-in intervals you specify, or if you so desire, you can ping the person whose phone is being tracked to determine the phone’s current location in real time.

c. Latitude can be set to record a history of each service technician’s travel route, including the approximate amount of time spent at each location.

d. By enabling Latitude’s Location History feature, you can produce map-based reports by day, week, month, or year, pinpointing locations where the phone being tracked has stopped, including approximately how much time it spent at each location.

e. The Latitude app provides a quick button for disabling the tracking feature, so the person whose phone is being tracked can maintain privacy during personal hours.

There are many GPS-based tracking solutions on the market, including those designed to track people, vehicles, inventory shipments, laptop computers, and even pets. Some tracking systems not only record locations but also track other information, such as a commercial truck’s speed and how hard the driver presses his or her brakes. Some accounting and enterprise resource planning systems offer service modules that include advanced service technician tracking and scheduling. There also are highly sophisticated solutions designed to track people using facial recognition and other methods. You should consider this brief description of Google Latitude an introduction to these types of solutions; investigate your options according to your tracking needs.

Table rows are a drag  
By J. Carlton Collins, CPA
December 2012

Q: I often rearrange rows in Word tables, and I find it cumbersome to insert a new blank row, copy and paste row data, and then delete the original row. Is there a faster way to accomplish this task?

A: To rearrange rows in a Word table, you need not insert a blank row. Simply click to the left of the row (in the margin area) to select the row, then click in the row and drag the row to the desired position. If your rows have multiple cells, make sure that your mouse arrow is positioned to the left of the table (in the margin area) when you unclick the mouse.

The games people play  
By J. Carlton Collins, CPA
December 2012

Q: I have an employee who plays social media games on his lunch break. Will playing these games slow down his computer or cause other harm?

A: The vast majority of computer performance issues and crashes occur when a computer runs out of available memory. Because many computer games involve colorful graphics and animations that consume large amounts of memory, playing certain games potentially can undermine a computer’s performance. (This explains why computers designed for avid gamers are usually equipped with larger amounts of high-speed RAM.) Performance issues alone are reason enough for me to recommend that CPAs refrain from playing online games, but there are other good reasons to avoid games.

As a rule, I never play any social media games because doing so grants permission to the game’s publisher to harvest my social media data, including my friends list, contact information, IP address, computer device ID number, and, often, much more. You may recall episode 106 of Star Trek: The Next Generation titled “The Game” in which the Ktarians attempted to take over the Enterprise by introducing the crew to an addictive video game with mind-controlling capabilities. In real life, the publishers of free social media games sometimes have dubious motives as well, and harvesting your private data may be their primary goal.

In addition to protecting my privacy, I also want to protect my productivity. (We all have more important things to do, both personally and professionally.) I’ve seen some of my own employees waste far too much time playing games on their computer, which is confirmed when I find their high scores exceed my own. ;-).

Five tips using Excel's Fill Handle  
By J. Carlton Collins, CPA
December 2012

1. Drag the Fill Handle of a single cell to copy that cell. The Fill Handle is at the bottom right of any selected cell or group of cells.

2. Drag the Fill Handle of a cell containing January, Jan, JAN, Monday, Mon, or MON, Quarter 1, or Q1 to have Excel autofill those lists with the subsequent time periods. (This tip works with any month or day of the week.)

3. Double-clicking the Fill Handle copies your selection downward when there is data in an adjacent column (users sometimes refer to this technique as piggybacking the data in the adjacent column). This is especially useful when you are working with larger amounts of data containing thousands of rows—in this case a simple double-click can copy a formula downward thousands of rows in an instant.

Note: Excel 2010 users will find that this double-click trick works even if the adjacent columns are empty, as long as column headings connect the highlighted data to a distant column that can be piggybacked.

4. Drag the Fill Handle of two cells containing the numbers 1 and 2, respectively, to quickly number a range. (Make sure to highlight the two cells before dragging)

5. Holding down the Ctrl key when dragging the Fill Handle will disable the AutoFill effect.

View CommentsView Comments   |  
Add CommentsAdd Comment   |   ShareThis

AICPA Logo Copyright © 2013 American Institute of Certified Public Accountants. All rights reserved.
Reliable. Resourceful. Respected. (Tagline)