ShareThis
|
TECHNOLOGY Q&A
An aggregate answer to two Excel questions
By J. Carlton Collins
January 2012

Q: Two reader questions:

1. Is there a way to subtotal large volumes of numbers in Excel 2010, while ignoring the values contained in hidden rows?

2. What is the best way to subtotal data in Excel 2010 that contain divide-by-zero errors?

A: The answer to both questions is to use Excel 2010’s new AGGREGATE function, which works just like the SUBTOTAL function, but includes options to ignore hidden rows, error values, or both. The trick to using AGGREGATE is the function’s second option, which dictates the data to be ignored. As pictured below, options 5, 6 and 7 are used to ignore hidden rows, error values, or both.

In the screenshot below, the same data has been summed three ways using the SUM, SUBTOTAL and AGGREGATE functions in cells A7, B7 and C7, respectively. (The formulas in row 7 are spelled out on row 8 so you can see both the formula and its results.)

Notice above that row 4 (which contains the value “5” in all three columns) is hidden. In this case, the SUM and SUBTOTAL functions include the hidden data in the results (25 and 25), but the AGGREGATE function ignores the hidden data when calculating the results (20).

Instead of hidden rows, the next example contains data with error values, but the solution is similar. This time the AGGREGATE function is used with the “Ignore Error Values” option to subtotal only the error-free data, while the SUM and SUBTOTAL solutions return errors.

More from the JofA:

TECHNOLOGY Q&A
A solution for dummies
By J. Carlton Collins
January 2012

Q: I frequently prepare document templates (such as newsletters and brochures) in Word 2010, and I often need to insert dummy text to help form the document layout. I usually find some old text and copy it into the document template, but I’ve found out the hard way that using this method without taking time to read the old text can be dangerous. Is there a fast way to insert generic dummy text into Word?

A: A simple Word function called “RAND” can be used to insert dummy text into Word as a placeholder. To use this function, position your cursor in the left-most position of your Word document, type =RAND(p) (where p is the total number of dummy paragraphs you would like inserted) and press Enter. Word will insert the dummy text using English phrasing, and the resulting text will repeat every three paragraphs. (Note: Excel users might recall that, when used in Excel, the RAND function generates a random number between 0 and 1.)

More from the JofA:

TECHNOLOGY Q&A
What 11-letter word do all CPAs spell incorrectly?
By J. Carlton Collins
January 2012

Q: Help! I accidently added several incorrectly spelled words to my Word 2010 dictionary, and now they are causing problems. Can you tell me how to remove those incorrect words from my dictionary?

A: The words you add to your dictionary are saved in your Custom Dictionaries word list, which you can edit from the Word 2010 File tab (or Word 2007 Office Start button) by selecting Options, Proofing, and then clicking the Custom Dictionaries button. Next, click the Edit Word List button to display a list of all the words you have added to your dictionary. Then to delete unwanted words, scroll the list, select each unwanted word and press the Delete button. When you are done, click OK three times.

Note: You could also use this method to add a large volume of words to your dictionary. For example, if you have a company directory of several hundred names, you could copy and paste them into your dictionary so that the spell checker can recognize the correct spelling. Keep in mind that you should paste the first names and last names on separate lines so the spell-check feature can recognize each name individually. (By the way, the answer to the riddle is i-n-c-o-r-r-e-c-t-l-y).

More from the JofA:

TECHNOLOGY Q&A
A bolder folder
By J. Carlton Collins
January 2012

Q: I want all of my Windows Vista folders to display the Details view, with my own custom column headings and column widths. Unfortunately, Vista does not seem to consistently remember these settings after I customize the top-level folder’s view using these steps:

1. I select Details from the Views dropdown menu.

2. I right-click on the folder’s column headings and place check marks next to the column headings I want to display.

3. I click and drag the folder’s column headings to rearrange their order.

4. I click and drag the right edge of each column heading to adjust the width.

5. I navigate up one level, then right-click on the folder and select Properties, Customize, and place a check in the box labeled Also apply this template to all subfolders, and click OK.

Unfortunately, this action does not apply my settings to subfolders as expected, and many of the subfolders keep reverting to their previous view settings. Is there a trick to making these settings stick?

A: The procedure you describe is perfect, except that Windows Vista includes a default View setting that prevents this procedure from working properly. To correct the problem, disable the offending setting by opening an Explorer window, and from the Tools menu, select the Folder Options, View tab, uncheck the box labeled Remember each folder’s view settings, and click OK. Next, repeat the procedure you described above, and the Detailed View settings you make will apply properly to all subfolders. Presented below is the default folder view I prefer for folders containing Word, Excel and PowerPoint data files.

Notes: Windows XP has the same default setting as Vista, and the solution is similar to the remedy described above. In Windows 7, Microsoft removed the default view setting mentioned above; hence, this is no longer an issue in that operating system.

More from the JofA:

TECHNOLOGY Q&A
The Thomas database
By J. Carlton Collins
January 2012

Q: Is there a good source for following proposed tax law changes pending in Congress?

A: Since January 1995, the Library of Congress has posted virtually every word uttered in Congress to the thomas.gov website; this information is usually posted by the following morning. Named for Thomas Jefferson, the Thomas database is the official repository for the following information:

1. Bills & Resolutions (including the texts of bills and resolutions,
summaries and status, and voting results, including how
individual members voted);
2. Activity in Congress;
3. The Congressional Record, including the daily digest (Note: Congressional members and their staff are allowed to edit their remarks in the Congressional Record prior to or after publishing. Therefore, the final published record may not exactly match what was said on the floor.);
4. Schedules and calendars;
5. Committee information;
6. Presidential nominations;
7. Treaties and more.

To follow the bills that address your specific topic(s), search by clicking Bills, Resolutions from the menu in the left column, then select the Search Bill Text option (see above). Select the congressional records to be searched (such as the 112th Congress for 2011 bills), then enter the desired search phrases, such as “capital gains tax,” bill numbers and other search parameters, as shown below. (You may notice the JofA cites bill numbers when reporting on tax and other legislation in Congress.)

This action will return a listing of all bills that meet your search parameters. In the example search for “capital gains tax,” the Thomas database returned the top 1,000 results.

More from the JofA:

TECHNOLOGY Q&A
The right sort
By J. Carlton Collins
January 2012

Q: I receive large amounts of data that need to be sorted both vertically and horizontally in Excel. I typically accomplish this task by transposing the data onto a separate worksheet (using the Paste Special, Transpose command), where I sort the data vertically (which is actually a horizontal sort because the data has been transposed). I then transpose the sorted data back to the original worksheet and sort the data vertically to complete the double sort task. The data we receive is never the same size and, often, extra columns are included—ruling out an easy macro solution. Is there a way to make this task easier?

A: Excel 2010, 2007 and 2003 all provide the ability to sort top to bottom and left to right, with Sort top to bottom as the default setting. To use the Sort left to right option, select the data to be sorted, then select Sort from the Data tab (or menu). In the Sort dialog box, click the Options button, click the Sort left to right radio button, and then click OK.

Define the sort criteria as you normally would, then click OK to complete the left-to-right sort. Once you have completed this task, sort the data again, this time changing the option back to Sort top to bottom. Using this approach, you will avoid the extra tasks of copying, pasting and transposing data twice between two worksheets.

More from the JofA:

TECHNOLOGY Q&A
A super footnote reference
By J. Carlton Collins
January 2012

Q: We’d like to print our financial statements from Excel, but we can’t get the footnote references to appear the way we would like. We want to display the footnote references in the superscript format, but to do this we have to enter the footnote reference in a different column, and this results in a wide gap between the row description and footnote reference. As far as I can tell, below are the two options we have when printing from Excel, neither of which is suitable.

Because of this problem, we take the extra steps of copying and pasting the financial statements into Word, then edit the financial report further before printing. Is there a way to use superscripting and eliminate the gap between the row description and the footnote reference at the same time, without having to copy and paste the financial statement into Word?

A: Excel allows you to format each character in a given cell individually, and this trick can be used to achieve the result you desire. To accomplish this, enter the row description and footnote reference into the same cell. Highlight the cell and activate edit mode (by pressing the F2 key or by double-clicking the cell). Use the mouse (or the arrow keys while holding down the Shift key) to highlight the footnote reference portion of the text (see below).

Next, right-click on the cell to display the pop-up menu and select Format Cells. Click the Superscript checkbox, and click OK. As pictured below, this action will display the footnote reference in superscript format, like Word does.

More from the JofA: