Show All

Hide All

Financial reporters regularly export data from accounting programs like Peachtree, Microsoft Business Solutions-Great Plains®, and QuickBooks Pro into Microsoft Office Excel 2003 for analysis and reporting. This often leaves the task of hand-formatting data. However, the right Excel features for the job can help make formatting a more automatic process.

This article covers three time-saving tips for formatting imported data in Excel. You'll learn how to:

• Group similar worksheets and apply formats to them all at once.
• Customize a toolbar so that the formats you need are at your fingertips.
• Use formulas to make instant corrections to data entry errors.

Format multiple worksheets at once

If you've imported data into a number of worksheets, and those worksheets share the same layout, you can format all sheets at once by grouping them.

Let's say that you have imported profit and loss data for three locations of Fourth Coffee from your accounting software into Excel. Each region has its own worksheet within your workbook, and each worksheet has the same layout as the others. With worksheet grouping, you can save time by applying formatting to all sheets at once.

Remember, it's important that the data in each grouped sheet have the same layout across the same cells for this to work. Note that the following sheet for Sacramento shares the same layout as the preceding sheet for Mountain View, and the data resides in the same cells between sheets.

In this example, you'll apply a particular format to the title of all three sheets: The font should be bold, blue, and 14 point.

1. First, group your sheets. To group nonconsecutive sheets, press and hold CTRL, and then click each sheet tab to be grouped. To group them all, click the first sheet tab, press and hold SHIFT, and then click the last sheet tab in the series. Grouped sheet tabs will turn white, and any worksheet that is part of a group will display [Group] in the title bar.
2. In any of the grouped worksheets, select the cell (in this example, A1) containing the label "Profit and Loss Statement: Fourth Coffee (Region Name)."
3. Apply the formatting to that cell: bold, blue, and 14 point.
4. Click to other sheets in the group. You'll see that you have simultaneously applied this format to cell A1 in all the grouped sheets.

Use this tip to eliminate repeat formatting tasks.

Format it fast with a custom toolbar

Let's say that you format and distribute monthly asset-tracking reports that you import into Excel from your firm's accounting software program. When first imported, of course, the data needs work.

Each month, you usually have to change imported asset reports in the following ways:

• Make column headers bold, and change their background color.
• Apply the currency cell format to Cost and other currency columns.
• Left-align certain data columns.
• Use AutoFormat to apply an existing style to your data.
• Generate column charts.

You can make some of these changes with the existing Formatting toolbar; however, the Column Chart button isn't on the Formatting toolbar by default (and many buttons that you don't use are). By creating a custom toolbar, you can put all the tools you need in one place.

Make an Asset Formats custom toolbar

1. On the View menu, point to Toolbars, and then click Customize.
2. On the Toolbars tab, click New, name your toolbar in the New Toolbar box, and then click OK.

   Your new toolbar appears.

3. On the Commands tab, click the categories you would like to view buttons in, and then drag the buttons you want from the Commands box and place them on your new toolbar. For this example, choose the following categories and commands:

   Categories	Commands
   Format	Bold
   Format	Fill Color
   Format	AutoFormat
   Format	Currency Style
   Format	Align Left
   Charting	Column Chart

4. When you're finished, click Close.

Your toolbar should look like this:

Now, you can quickly customize the worksheet.

How?

Custom toolbars don't stop with formats. You can create custom toolbars to add speed and convenience to all of your work in Excel.

Use functions to fix data formatting errors

Functions can do more than just return interest, yield, and depreciation. They can also automatically format text. Your worksheets may contain names or data labels that are inconsistently capitalized or contain extra spaces. You may have corrected errors like this by hand before. Thankfully, formulas can do the work for you.

Let's say that you have imported customer information from your accounting program into a spreadsheet to prepare a report. However, data was entered into the accounting program by a number of different colleagues, which means your worksheet is in disarray. For one thing, some of the entries in the list of company names contain extra spaces. For another, they haven't all been capitalized correctly.

Functions to the rescue

The TRIM function will help you remove extra spaces in data, leaving only one space between words. The PROPER function will change your data to capital case. Try both in this example of an imported customer list. When you are done, the Company Name column will be correctly formatted, without retyping the corrections. Here's how:

1. Select the Contact Name column (C). On the Insert menu, click Columns. This adds a column to the right of the Company Name column.
2. In cell C2, type =PROPER(TRIM()).
3. Place your cursor inside the set of parentheses just after TRIM.
4. Click in the first cell to be trimmed, B2, to fill in the cell reference.
5. Press ENTER to complete the function. This enters the function into cell C2, but now you need to copy it through cell C10.

   

6. Select cell C2, and place your cursor over the lower-right corner of the cell. A fill handle appears (it looks like a black cross). Drag the cursor down to cell C10, and release.
7. The Company Name entries now appear in column C flush left, with no extra spaces and correctly capitalized.

   

However, now you have two Company Name lists — in columns B and C. To correct this, paste the new list (column C) back over your old list (column B), but just paste the values, not the formulas. Here's how:

1. Copy the names in column C (cells C2-C10).
2. Select cell B2, and then click the arrow to the right of the Paste button on the Standard toolbar.
3. Click Values to paste the text but not the underlying formulas.
4. Delete column C.

Done! Column B now contains a correctly formatted list of Company Names.

Check the See Also section of this article for a training course on using functions to correct formatting in your worksheets, including how to spread data (like first and last names) imported to a single cell into more than one cell.

Make Excel work harder, so you don't have to

This article covers a few ways you can take the time and hassle out of getting your exported finance data to look the way you want, but there are dozens of shortcuts left to explore. Check Office Online for training, articles, and Help topics to see how Excel can help you work more efficiently.