Journal of Accountancy Large Logo
ShareThis
|
TECHNOLOGY Q&A
Seeing green  
By J. Carlton Collins, CPA
October 2012

Q: I keep seeing green triangles in my Excel spreadsheet (pictured below); can you please tell me how to get rid of them? Thank you.

A: The small green triangles displayed in the top-left corner of a cell are indicators of a possible error. By default, Excel 2010, 2007, and 2003 display this indicator whenever the program detects one of the following nine common spreadsheet errors:

1. The cell contains a formula error.

2. The cell’s column total calculation is inconsistent with adjacent column total calculations (this rule is not included in Excel 2003).

3. The cell contains a date where the year is represented with only two digits.

4. The cell contains numbers formatted as text or numbers preceded by an apostrophe.

5. The cell contains a formula inconsistent with other formulas in the region.

6. The cell contains a formula that omits certain data—for example, a formula that refers to only a portion of a solid range of data.

7. The cell contains a formula but is unlocked in a protected worksheet.

8. The cell contains a formula that refers to an empty cell.

9. A restricted cell (restricted via data validation) contains invalid data.

If you are one of those amazing CPAs who never makes an error, you can disable Excel’s error-checking indicators as follows. In Excel 2010 or 2007, select File, Options (or Excel Options), Formulas, and under the Error Checking section, uncheck the box labeled Enable background error checking. Or, if you prefer, uncheck any of the boxes in the next section (labeled Error checking rules) to individually disable any of the nine rules listed there (see box below).

Excel 2003’s error-checking settings are accessible by selecting Tools, Options, and then clicking the Error Checking tab.

 

 

 

 

 


TECHNOLOGY Q&A
Excel PivotTable questions  
By J. Carlton Collins, CPA
October 2012

Several readers have submitted questions about Excel 2010 and 2007 PivotTables, as follows:

Q1: Why does Excel insert the GETPIVOTDATA function into a formula when you use the mouse pointer to refer to a cell in a PivotTable, and is there a way to prevent this action?

A1: As discussed in the October 2011 Tech Q&A item “Make a Difference With PivotTables” (page 76), Excel by default inserts the GETPIVOTTABLE function when you point to PivotTable cells while writing a formula. In that article, I explained the benefits of the GETPIVOTDATA function, but I also should have mentioned that you can disable the GETPIVOTTABLE functionality in Excel 2010 and 2007 as follows:

a. Click a PivotTable to display the PivotTable Tools tab.

b. From the PivotTable Tools tab, select Options, PivotTable.

c. Click the Options dropdown arrow and uncheck the Generate Get PivotData item, as pictured below.

Q2: Why do my Excel PivotTables sometimes lose their formatting upon refresh, even after I check the PivotTable Options box labeled Preserve cell formatting on update?

A2: Often, when a CPA thinks his or her PivotTable is reformatting upon refresh, what actually is happening is the column widths are adjusting on each refresh. To prevent this from happening in Excel 2010 and 2007, disable Excel’s Autofit functionality as follows. Right-click on your PivotTable, then select PivotTable Options from the pop-up menu. From the Layout & Format tab, uncheck the box labeled Autofit column widths on update, then click OK. In addition, make sure the box labeled Preserve cell formatting on update is checked. Thereafter, your PivotTable’s number formats, color formats, and column widths will remain the same whenever you change the PivotTable settings or refresh its data.

Q3: I can sort my PivotTable in ascending or descending using the numeric data columns just fine; however, my PivotTables do not sort alphabetically. Why is this, and how do I fix this problem?

A3: By default, Excel 2010 and 2007 PivotTables sort row label data according to the order of the source data, and you have two options for solving this issue. First, you can sort your source data in the order you would like it to appear on the PivotTable. Second, you can set your PivotTable to ignore the source data order by right-clicking on your PivotTable, selecting PivotTable Options from the pop-up menu, then from the Display tab, under the Field List section, selecting Sort A to Z.

Also, be aware that Excel’s sort functionality is somewhat different for PivotTables than for regular worksheets. When you click on a text-based column in a PivotTable and select Sort from the Data tab, the resulting dialog box (pictured at the top of the next column) provides additional sort options for sorting data manually (by dragging and rearranging data), or for sorting the data in ascending or descending order according to a PivotTable’s specific field names. Consequently, you should also check these settings to ensure the desired results are achieved.

 

 

 

 

Further, be aware that as a default, Excel’s PivotTable sort function sorts by custom lists, which explains why a PivotTable will sort months (January, February, March, etc.) in month order instead of alphabetical order. To disable custom list sorting in a PivotTable, right-click your PivotTable, select PivotTable Options from the pop-up menu, then from the Totals and Filters tab, under the Sorting section, uncheck the box labeled Use Custom Lists when sorting.

Q4: What’s the best way to update a PivotTable’s source data range when the data source grows larger?

A4: Before creating a PivotTable in Excel 2010 or 2007, first convert the source data to a Table by selecting the source data (or by selecting a single cell in your source data, assuming that your source data contains no blank rows or columns and has a blank row above the column headings). Then from the Insert tab, select Table, OK. Thereafter, refreshing PivotTables created from this source data will automatically include any new column or row data you add to your source data table. (The Table feature is not available in Excel 2003.)

 

 


TECHNOLOGY Q&A
Take a swipe at this  
By J. Carlton Collins, CPA
October 2012

Q: My friend’s phone has a cool feature that allows her to type text messages by sliding her finger across the virtual keyboard rather than typing each key individually. She says this method of typing is much faster once you get used to it. I cannot seem to find this functionality on my smartphone. Is there a way for me to do this on my iPhone?

A: The feature you are referring to is called Swype, and many Android smartphones have this new functionality built in. The procedure for activating Swype varies slightly from one phone to the next, but as an example, I can activate Swype on my Android-based Motorola Razr Maxx by holding my finger on any compose message text box until the Edit text menu pops up, then I select Input method, Swype. Thereafter, the keyboard senses my finger movement and spells out words as I slide my finger across the keyboard, pause, or change finger direction (as suggested in the picture below). Further, Swype’s built-in spell checking does a good job of correcting most of my finger-sliding errors. Swype is currently only available for the Android, Symbian, Windows 7, MeeGo, and Windows Phone platforms, but other platforms are likely to offer this type of functionality in the future.

 

 

 

 

 

 

 

 

 

 

 


TECHNOLOGY Q&A
Facebook tips  
By J. Carlton Collins, CPA
October 2012

Who you know matters. Accordingly, many social media websites provide proven solutions for expanding and cultivating one’s circle of contacts, both personally and professionally. With this in mind, presented below are tips for using Facebook, the world’s largest social media website with more than 800 million valid users, half of whom reportedly are active on a daily basis.

1. Map your friends’ locations. Visit and install My Friend Map (for free at tinyurl.com/7yrj5et) to view a zoomable map displaying where all of your Facebook friends live; that way you can quickly identify friends that live near your next travel destination and make plans to meet.

Once you have installed the applet, to use My Friend Map, select the My Friend Map icon from the Home screen’s left pane menu to launch the application. Next, click the View Friend Map button, then click the Google Map View button to display a world map containing locator pins that pinpoint your friends’ locations. Use the zoom slider to zoom in on a specific destination, then click a locator pin to display profile pictures of friends residing in that location. Finally, click each picture to display their names (as pictured below).

2. Alias name. Facebook allows you to use an alias name (such as your nickname or a company name) instead of your full name. As examples, some people use this feature to display their company name to make their Facebook page easier for customers to find, while some recent college graduates seeking jobs use this feature to display nicknames, making it harder for potential employers to find them. To use this feature:

a. Click the dropdown arrow located on the top-right menu and select Account Settings.

b. Click the edit button titled Username, then enter a new name in the Username box.

c. Enter your password in the Password box (required to complete this change).

d. Click the Save Changes button.

3. Create a poll. Facebook provides the option to create a poll for soliciting feedback on your Wall. To use this feature, click the Ask Question option located above the What’s on your mind? status box on your Facebook home page, then click Add Poll Options to create a list of responses.

4. Multipost. When writing a wall post, type the @ symbol and your friend’s name, then select his or her name from the popup list. This action will cause the post to appear on your wall and your friend’s wall.

5. Album download. Instead of saving Facebook photos to your computer one at a time, you can download an entire album (from your Facebook page or a friend’s Facebook page) using fotobounce (free) at fotobounce.com.

Note: Facebook is constantly evolving, and the menus change frequently; therefore, be aware that the specific instructions related to these tips are subject to change.

 

 

 

 

 



TECHNOLOGY Q&A
Complete advice  
By J. Carlton Collins, CPA
October 2012

Q: My colleague runs the same version of Microsoft Office 2003 as I do, but my version is missing some of the functionality that hers has. Why would this happen, and what can I do about it?

A: When installing Microsoft Office 2003, the installation routine provides the option to perform either a “typical” or “complete” installation of the product. I am guessing that whoever installed Office on your computer selected the typical option.

To remedy this problem, reinstall Office using the Complete Install option. Because hard drive space is more abundant these days, most software applications (including Microsoft Office 2010 and 2007) no longer provide options to partially install the product.


View CommentsView Comments   |  
Add CommentsAdd Comment   |   ShareThis

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