Journal of Accountancy Large Logo
Reverse the negativity  
By J. Carlton Collins
March 2013

Q: Our company’s old accounting system produces financial reports with trailing minus signs, which we open in Excel and fix manually by retyping the data. Is there an automated way to remove trailing minus signs and convert those numbers to negative numbers with leading minus signs?

A: Presented below are three Excel-based solutions to converting numbers with trailing minus signs to negative values.

1. Using the IF, RIGHT, and SUBSTITUTE functions. The following formula checks for a trailing minus sign (using the IF and RIGHT functions), and if a trailing minus sign is present, the formula removes the trailing minus sign (using the SUBSTITUTE function) and changes the number to a negative number by multiplying the number by negative one: =IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)

2. Using the VALUE, IF, RIGHT, LEFT, and LEN functions. As an alternative approach, this following formula checks for a trailing minus sign (using the IF and RIGHT functions), and if a trailing minus sign is present, the formula inserts the trailing minus sign in front of the number (using the RIGHT function again), removes the trailing minus sign (using the LEFT and LEN functions), and then converts the rearranged results to a number (using the VALUE function): =VALUE(IF(RIGHT(A1,1)="-",RIGHT(A1,1)&LEFT(A1,LEN(A1)-1),A1))

(Both formulas described above accomplish the same result using different formula approaches; the first approach is a little less complicated.)

3. Using Text to Columns. You can also fix trailing minus signs using the Text to Columns command, as follows:

a. Select a range of data containing numbers with trailing negative signs (note that it is OK if some of the selected data does not contain trailing minus signs);

b. From the Excel Ribbon select Data, Text to Columns;

c. In the resulting Convert Text to Columns Wizard - Step 1 of 3 dialog box, click the Next button twice, then click the Advanced button;

d. Make sure the box labeled Trailing minus for negative numbers is checked;

e. Click OK, Finish.

f. This operation replaces all trailing negative signs in your data selection range with leading minus signs.

Missing toolbars  
By J. Carlton Collins, CPA
March 2013

Q: I typically surf the web using Internet Explorer’s InPrivate Browsing mode for better safety and privacy, but when I use this mode, some of my toolbar add-ins (such as the Norton toolbar add-in) are no longer displayed. Is there a way to display the Norton toolbar (and other toolbars) when browsing the web using the InPrivate mode?

A: As a default, Internet Explorer disables all toolbar add-ins when you select InPrivate Browsing, but you can change this default setting as follows: From the Internet Explorer menu, select Tools, Internet options. From the Internet options dialog box, select the Privacy tab and uncheck the box titled Disable toolbars and extensions when InPrivate Browsing starts. Thereafter your toolbar add-ins will display properly in an InPrivate Browsing window.

Make your point  
By J. Carlton Collins, CPA
March 2013

Q: I added a long numbered list to a Word document, and I want to format the numbered bullets with a different font and font size than the text (so the formatting matches the other lists already included in the document). I am able to select the entire numbered list (including the bullet numbers and text) and change the format, but I can’t figure out how to change the format of the text only without changing the numbered bullets’ font and font size. My only solution seems to be to format the text on each line individually, but there must be a better way. Can you tell me how?

A: To change the font and font size of bullet numbers (or bullet symbols) in Word, right-click on a bullet, then select Font from the pop-up menu. This action will select all of the bullets in the bullet list and display the Font dialog box. Select the font settings you want and click OK to apply those settings consistently to all bullets, but not to the text following each bullet.

By J. Carlton Collins, CPA
March 2013

Q: I have an Excel worksheet with two columns and more than 1,300 rows of data that I would like to print on legal-size paper in landscape orientation. Is there a way to get the two columns to flow automatically to adjacent columns (across the page) so it fits on one page, as the graphic image below suggests?

A: The effect you seek is sometimes referred to as “snaking columns onto a single page.” Unfortunately, Excel does not provide a “snaking” option; however, you might consider the following three options as possible work-around solutions:

Postscript Printer Method

Many postscript printers can print multiple pages onto a single sheet of paper, and if you have a postscript printer, the following steps may help you achieve the results you desire:
1. (Optional) Change the paper size to legal from Excel’s Page Layout tab by selecting Size, Legal.

2. (Optional) Change the orientation to landscape from Excel’s Page Layout tab by selecting Orientation, Landscape.
3. Increase the font size of the Excel data you want to print to 36 points (this step is important because this technique will ultimately compress the final size of the font).

4. Adjust the column widths as needed so the data with the larger font size fits in each column.

5. Highlight the data to be printed, and from the Page Layout tab, select Print Area, Set Print Area.

6. From the File tab, select Print, select the appropriate printer from the Printer dropdown box, and then select Printer Properties. From the Document Properties dialog box, select the Finishing tab, select 16 pages per sheet from the Pages per Sheet dropdown box, and select Down, then Right from the Page order dropdown box, and then click OK.

7. Click Print to print the results. (Note: Since the print job is compressed by the printer and not the computer, selecting Print Preview will not display the compressed results described; you must print the page to view the results.)

This approach will print 16 condensed pages on one sheet of paper. Usually, these results would be unreadable. However, by increasing the font size to 36 points, the compressed results end up displaying a font size of approximately 12 points, so that the results are readable, and the effect (as pictured below) is close to the outcome you seek.

Paste to Word Method

Because Microsoft Word does provide an option for “snaking” (or “wrapping”) columns, a second approach is to copy and paste your data from Excel to Word, and then use Word’s Columns tool to achieve the effect you seek, as follows:

1. Copy and paste the data from Excel to Word by highlighting the data in Excel and pressing Ctrl+C, then paste the data into Word by switching to a new blank Word document and pressing Ctrl+V. (Note: It may be helpful to first adjust the column widths in Excel so that the pasted data fits properly in Word, with minimal wasted white space and no word wrapping within the pasted table cells.)

2. (Optional) In Word, adjust the paper size and orientation as follows: Change the paper size to legal from Word’s Page Layout tab by selecting Size, Legal. Change the orientation to landscape from Word’s Page Layout tab by selecting Orientation, Landscape.

3. From the Word Page Layout tab, select Columns, More Columns and, in the Number of columns spinner, select the number of columns that best fits your data (I’ve selected 4 columns in the example pictured below).

4. If needed, adjust the data’s font size and column widths in Word; then print the results.

Rearrange the Data in Excel Method

The third method involves a more labor-intensive approach in which you reorganize the data in Excel prior to printing, as follows:

1. (Optional) Change the paper size to legal from Excel’s Page Layout tab by selecting Size, Legal.

2. (Optional) Change the orientation to landscape from Excel’s Page Layout tab by selecting Orientation, Landscape.

3. Select File, Print; and then press the Esc key. This action displays Print Lines indicating where the print range will break if printed on multiple pages. These Print Lines can then be used as a guide for selecting and rearranging your data.

4. Select all of the data below the first page’s bottom Print Line (1,300+ rows, or cells A33 to B1358 in the reader's example), cut the data by pressing Ctrl+X, move the cursor to row 2 in the next available column, and then paste the data by pressing Ctrl+V.

5. Continue moving all data positioned below the worksheet’s first horizontal Print Line to the next available blank column until all data have been repositioned horizontally across the page, as suggested by the image above.

6. Print the resulting worksheet.

(Note: While this approach is more labor-intensive, a macro could be created to repeat these steps fairly rapidly).

A bit disappointed  
By J. Carlton Collins, CPA
March 2013

Q: Embarrassingly, I purchased a new computer last year that was advertised as being pre-installed with the 64-bit version of Windows 7 Professional operating system, and I then spent about three weeks (off and on) installing applications, configuring the system, setting up email, setting up network permissions, setting up printers, and copying my data over. Once the new computer was ready for use, I switched computers, and, a few weeks later, I de-commissioned my old 64-bit Vista computer.

I soon noticed that my new computer needed regular rebooting, and upon closer inspection I discovered that my new computer had actually been configured with the 32-bit version of Windows 7 Professional (not the 64-bit version as advertised)—and I failed to notice this problem before investing time to install my applications and data, and configure the system. As a result, the computer uses only three of the system’s eight gigabytes of RAM—so the performance is worse than that of my old Vista computer. My understanding is that there is no way to upgrade my 32-bit system to 64-bit without starting over and reinstalling everything all over again. Is this true?

The store manager where I purchased the computer apologized and offered to provide a free consultant to help me reinstall the computer, but a year later I still have not found sufficient time to shut my computer system down for several days in order to reinstall and reconfigure everything. In hindsight, I should have checked the system to make sure the 64-bit version of the operating system had been correctly installed before proceeding with the remaining setup, but it never occurred to me to do so.

A: Unfortunately, there is little you can do to recover from this type of error—short of wiping the hard drive clean and reinstalling a new operating system, applications, and system settings from scratch. If it is any consolation, perhaps sharing your misfortune may alert our readers to avoid this potential problem the next time they purchase a new computer.

To ensure that the operating system has been correctly installed on your computer, from the Windows XP, Windows Vista, Windows 7, or Windows 8 Control Panel, select System and check to see that the Installed memory (RAM) and System type screen confirms that the 64-bit version of your operating system has been installed and that other settings match your system’s advertised capabilities. 

To view your computer’s System Profiler screen on an Apple computer (running Mac OS X Snow Leopard), from the Apple menu, click About This Mac and then click the More Info button.

View CommentsView Comments   |  
Add CommentsAdd Comment   |   ShareThis

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