Customize How Toolbar Buttons Format


Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces.

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type indicates commands and instructions that users should type into the computer and the names of files.

Q. I put the Currency button on my toolbar so I can easily format a cell that contains a dollar figure. But the default format is for two decimal places and I’d rather it defaulted to no decimal places. I know I can reformat it each time, but is there a way to change the default style for that button?

A. There is, and it’s a good idea to go though the several steps to customize how the toolbar buttons format cells because once you invest the time, your work will go much faster.

Let’s take it from the beginning so readers who aren’t aware that they can customize toolbar buttons can get aboard. The buttons you’ll probably want to format are Currency, Percent and Comma . For this example, we’ll adjust the Currency button, whose icon is a dollar sign. The technique works for all buttons.

Excel’s default for currency is two decimal points. So if you type 3495 into a cell and click on the Currency button, it will transform the number to $3,495.00 . But if you want Excel to default to no decimals— $3,495 —you’ll have to tinker with the button, which is what we will now do.

Open a new workbook. Click on Format, Style. That will bring up this screen:
Choose the Style name from the menu—in this case Currency —and click on Modify . That will open the screen shown at right.

Format the style to your liking. When done, click on OK . It’s a good idea to test the new style to be sure you set up the format correctly.

Be aware that this style change affects only this workbook. If you want the default to apply to all new workbooks, you’ll have to take the following steps.

After you’ve made all the changes, and with the new file still open, click on File, Save As , and from the Save as type menu, choose Template (*.xlt) and name the file Book.xlt . Then navigate to your Xlstart folder. In Office 2000 you’ll probably find it at c:windowsapplication datamicrosoftexcelxlstart . Choose Save .

If you already have a Book.xlt file in that folder, open it, customize the style and then save it still as Book.xlt .

All subsequent Excel files now will have that custom style, but it will not affect old files. However, an easy way to change old files is to merge the styles from a newer workbook into an older one. To do that, open both files—the new, customized one and an old one—and then, from inside the older file, choose Format, Styles, Merge, and select the file with the styles to merge and click on OK .


Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.


How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.


News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.