Resources

How can I add brackets e.g. (£50) instead of -£50 in an Excel spreadsheet?

Microsoft gave Excel a major facelift with its introduction of Excel 2007. Depending on which version you have, there are different options available for formatting numbers in Excel. I’ll go over the universal step first and then cover some of the other ways to do this.

Format Numbers in Excel

In order for Excel to show negative numbers in parentheses, you have to instruct Excel to do so. This is done in the “Format Cells” dialog. No matter which version of Excel you have, you should be able to quickly access this dialog box by selecting the cell or range of cells that you want to format and then right-click on Format Cells. This will open a dialog box with the following tabs: Number, Alignment, Font, Border, Fill, and Protection. Obviously, you will want to use the Number tab.

In the Category section, click on the type of number you are working with such as number or currency. Since you mentioned currency in your question, let’s look at currency as an example. Once you select currency, you’ll see the main part of the pane now displays several options. It shows you a sample and provides you with three editable fields: decimal places, symbol, and negative numbers.

Select the number of decimal places you want to display. If you select 2, then the numbers will display two decimal places such as £50.00 whereas if you select five decimal places, the numbers will look like this: £50.0000000.  If you want to change the currency symbol from £ to $ or even ¥, do so in the Symbol field.

Finally, the answer to your question lies in the next field, negative numbers. You have four choices for displaying negative numbers:

-£50

£50 (red text)

(£50)

(£50) (red text)

Choose the third or fourth option to place your negative numbers in parentheses. If you want your negative numbers to be displayed in red text, choose the fourth option.

Format Numbers in Excel 2003

While it’s easy to simply right-click a set of numbers, Excel offers other ways to access the Format Cells dialog. If you have Excel 2003, select the cells you want to format, go to the menu bar, and click on Format > Cells. From there, follow the above directions to format your numbers.

Format Numbers in Excel 2007

Excel 2007 does not have the handy menu bar. In its place is the infamous “ribbon.” You must have the Home tab active and then find the area of the ribbon labeled “Number.” A drop-down box allows you to quickly change the basic number type such as from General to Currency. However, if you want to change the way negative numbers display, you’ll need to click on the tiny square found in the lower right corner of the Number section. This will open the Format Cells dialog box.

My Excel spreadsheet prints strange. Can I fit the spreadsheet on one page?

One of my biggest pet peeves with Excel involves printing large spreadsheets. The very nature of Excel makes for wide printouts as each record stretches across the screen. While I might have a spreadsheet that looks good on my wide screen monitor, the printout is an impossible mess! Short of taping a bunch of pages together, what’s an Excel user to do?

Fortunately, you can control the way Excel prints. First, let’s take a look at the Print Area command before digging into the various print settings. You can control which areas print on a page by setting the Print Area. This helps to avoid printing blank pages or pages with information that you may need in the spreadsheet for calculation purposes but not necessarily for general viewing. To do this, highlight the text that you want to print and then go to File >Print Area > Set Print Area. In Excel 2007, highlight the text, go to the Page Layout tab, and select the Print Area icon. Choose Set Print Area.

Once the print area has been set, you will see a dotted line indicating the page boundaries. As your document evolves, you may need to clear and reset the print area. For example, you may need to add columns outside of the boundary. Keep this in mind as you edit your documents and adjust the print area as necessary. Otherwise, your new data won’t print.

While setting the print area is a good first step, it doesn’t solve the problem of wide records fitting on the page properly. While in the Page Layout area (either through File >Page Setup or the Page Layout tab depending on your version of Excel), you can further fine tune your spreadsheet. For example, you can adjust the margins, orientation, size, page breaks, and titles.

Adjusting the margins and orienting the page to landscape mode rather than portrait mode often does the trick. A few tweaks of column widths may also be needed. This option is ideal for those spreadsheets that “almost” fit across a page.

In addition, you can also scale the image down to less than 100% or even choose the option to fit the entire spreadsheet on a single page. Of course, doing so depends on the size of the spreadsheet. If you have a huge spreadsheet, shrinking it to fit on a single page will make it microscopic. A better idea is to scale it down to a legible size.

One problem when you print Excel spreadsheets on multiple pages is that you lose the column headings on the pages that follow. You can set Excel to print the title rows across the top and the columns on the left side. This helps to clarify the data on each page.

You can also set the printed page order to either print down then over or over then down.

As you adjust the print settings, use the Print Preview feature to see how your changes affect the printed document. Print Preview provides shortcuts to these various setting options as well.