Archive for December, 2010

Excel: Captain Excel discusses word processing options

In this post on the Captain Excel blog on, the author runs through several options for composing and displaying text in Excel spreadsheets. This is a common issue for accountants, especially auditors. We use Excel so much for displaying tables of numbers and performing complex calculations, but a significant part of reporting is the explanatory text. This is precisely why a standard set of financial statements includes more note disclosure than actual numbers.

From the article:

Callouts are Text Boxes with arrows to help direct the discussion within the Text Box to the object.   Create a Callout by clicking on the Shapes button in the Illustrations group on the Inset tab, then click on one of the callout buttons at the bottom of the selections.

As a preparer and reviewer of millions (okay, maybe thousands) of Excel-based audit workpapers, I see all manner of text display methods. Even after five years in the profession, I have yet to decide on a preferred method. This article helps put it all in perspective, and I think I agree with the author on a favorite method. You’ll have to read the article to find out!

Captain Excel: Word Processing Options in Excel


Leave a comment

Word: Easily clear a table of numbers and preserve formatting

We compose our note disclosures, including number tables, in Word. This has its advantages and disadvantages, but that’s another topic entirely. One process, though, that gets tedious after a while is rolling forward a set of note disclosures and clearing the numbers out of a table. Luckily, I found a great way to speed up the process.

Take, for example, a table for a property, plant, and equipment note disclosure. In the one shown below, we can easily copy and paste the 2009 column over the 2008. But for the 2010 column we would have to manually remove each number, unless we wanted to lose the tabs and spacing set for each row.


To roll this note forward for 2010, I first copy the 2009 numbers and paste them over the 2008 values and update the heading. I then change the 2009 heading to 2010. Now, using this method, I can quickly clear the 2009 values, leaving the tabs and spacing in each row.

  1. Using the mouse, select the column of numbers.
  2. On the Home ribbon, click the Replace button.
  3. In the Find and Replace dialog box, click the More >> button.
  4. Click the Special button near the bottom and select “Any Digit.”
    There should now be a “^#” in the Find What field.
  5. Do not enter anything in the Replace With field.
  6. Click Replace All. You will see a notice asking if you want to search the rest of the document. Click No.
  7. You will notice that the commas are still in the column. Before closing the Find and Replace dialog box, enter a comma in the Find What field.
  8. Click Replace All. You will see a notice asking if you want to search the rest of the document. Click No.
  9. Close the Find and Replace dialog box.

The table should now look as shown below. It is now ready for fresh numbers!


1 Comment

Word: Styles, part 2 – more on customizing and clearing styles

This is a continuation of my short series on using styles in Word. Last time I went over applying styles and creating an automatic table of contents. This week I will go into a little more depth on modifying the built-in styles and how to clear styles from existing content.

Modifying built-in styles

Word has some very good built-in style sets which provide us with a good bit of flexibility, but I often feel the need to tweak things a little. To change style settings on a broad basis, the Style section of the Home ribbon gives us some simple solutions. The Change Styles menu allows us to change Style Set, Colors, and Fonts.


Experiment with these options before tweaking individual styles, as you may find exactly what you’re looking for. Once you have a style set, fonts, and colors selected, there are two primary ways to modify the built-in styles individually: 1. the easy way, and 2. the involved way.

The easy way:

  1. Format some text to suit your needs. In the example below, I’ve tailored a subheading with a particular font, weight (bold), color, size, border, alignment, and vertical spacing (paragraph settings).
  2. Once the text looks the way I want, select the text. For a heading or subheading style, I like to select the entire line:
  3. Choose a style category (e.g., Heading 3) from the Styles section of the Home ribbon and right-click on it.
  4. Select “Update [style name] to Match Selection”
  5. Now the style should match your selection and can be applied throughout the document.

The involved way:

  1. Choose a style from the Styles section of the Home ribbon. Alternatively, you can click on the lower-right corner of the Styles section to display the Styles pane.
  2. Right-click on the style and select “Modify.”
  3. This will display a dialog box which gives you a very large number of options for customizing a style.
  4. Many of the options are on the face of the dialog box, but the Format button at the bottom-left gives a comprehensive set of options:
  5. Explore the options and experiment, if you dare.

Clear existing styles

Sometimes we paste in text from other documents, and often by default Word keeps the source style. To start fresh and apply your own styles, the solution is easy yet hard to find.

  1. Open the Styles pane as shown earlier.
  2. Select the text from which you want to remove formatting.
  3. Click “Clear All” on the Styles pane.
    The text should now look normal:
  4. Alternatively, you can selectively clear formatting by opening the Style Inspector for the selected text.
  5. With the text selected, click the Style Inspector button at the bottom of the Styles pane.
  6. The Style Inspector window will open, detailing all of the bad choices made:
  7. Click any of the buttons along the right side to clear the applicable formatting. This window can also be used to modify the style or clear all formatting from the selected text.

Leave a comment

Excel: New workbooks with just one worksheet

SS-2010.12.14-08.28.22I have always found it a little annoying that Excel thinks it needs to give us three worksheets in a new workbook by default, especially since it is so easy to add a new one. Also, there have been discussions several times over the past several years about not leaving unused worksheets in audit workpapers. The remedy to this tragic annoyance is quite simple.

  1. Click the Office button and go to Excel Options.
  2. Under “When creating new workbooks” change the number next to “Include this many sheets” to 1.
  3. Click OK.

Problem solved.

Leave a comment

Excel: “The Column Name Game” yesterday shared a wonderful step-by-step solution to a more-common-than-you’d-think spreadsheet problem. A reader asked this question:

If you have a list of employee names in one column showing last name, first name (Smith, John), how do you convert the list to show first name last name (John Smith)?

I get this sort of question all of the time from my colleagues, and the solution presented in the article is precisely the method I use. I recommend that all Excel users give this solution a try, as it teaches some very good fundamentals of text manipulation and data processing.

An example of the methodology:

Solving the Problem One Step at a Time
Say that the names start in A2 and run down column A. Although you can solve the problem with a single formula as shown in Figure 5 (below), it will be less intimidating to break this problem down into smaller problems.

Where Is the Comma?
In Figure 1 (left), the formula in cell B2 uses the FIND command to locate the first comma within cell A2. =FIND(“,”,A2) returns the value 6 because the comma is the sixth character in SMITH, JOHN.

Spreadsheets: The Column Name Game

Leave a comment

Word: Styles, part 1 – the basics & easy table of contents

I have spent a good bit of time recently composing training materials for an upcoming IDEA training session. With over 70 pages so far with many more to go, I assumed that one of the most daunting and annoying tasks would be creating the table of contents. I was aware the built-in feature for creating a TOC, but I assumed it would be too complicated. I’m glad I was wrong. The first in a series on styles, today’s tip is and will show how to set a few basic styles and use them to create a table of contents automatically.

Microsoft Office programs have included a feature for managing “Styles” for a few versions now. With 2007, applying and modifying styles is really simple. For this tip, I created a simple document with headings, subheadings, and paragraphs of content. Until I apply styles, Word assumes each section of text is a paragraph. That is why you almost always see the “Normal” style selected in the Styles section of the ribbon.


The benefits of using styles are many:

  • Easily update the format of your document. Modifying “Heading 1” updates all of the sections of your document that use that style.
  • Using the built-in styles can make your document look more professional and consistent.
  • Improved conversion to PDF and other document formats.
  • Easily create tables of contents and cross-references within your document.

Apply styles

When I apply a heading style to a heading in my document, Word now understands that it is not just another paragraph. To apply a heading style:

  1. Place the cursor anywhere on the heading line.
  2. Click one of the heading styles on the ribbon. The first line of my document is a main heading, so I will select Heading 1.

Here is my heading before applying the style:


Here is the heading after:


I can do the same thing with my subheadings. For those, I will choose Heading 2 as the style.

Subheadings before styles:


Subheadings after styles:


Modify styles

The easiest way to modify a style is to right-click on the style on the ribbon and select Modify.


From there, you can customize any aspect of the style, like font, size, color, spacing, borders, tabs, etc. The modified styles can either be saved just to the current document or to all future documents based on the normal template.

Easy table of contents

If, at a minimum, you have headings set using styles, you can very easily create a table of contents. I have one “Heading 1” and five “Heading 2” set.

  1. Insert a new page at the beginning of the document. With your cursor at the very beginning of the document, click the Page Break icon on the Insert ribbon.
  2. With cursor on the new page (very beginning of the document), go to the References ribbon, click on Tables of Contents, and select one of the automatic options. I selected the first option.
  3. The result shows up immediately. You will notice the Heading 1 is the first item, and the Heading 2s are sub-items below it.

Page numbers are entered automatically and can be updated periodically as the document develops. To update the table of contents for new headings and changes in page numbers:

  1. Click anywhere in the table of contents.
  2. Click the Update Table button that appears above the table.
  3. Choose whether to update the entire table or just the numbers and click OK.

1 Comment

%d bloggers like this: