More Excel Tips | AccountingWEB

More Excel Tips

In a recent workshop at AccountingWEB, author and AccountingWEB Managing Editor Gail Perry shared some of her favorite Excel tips.

Workshop sponsored by National Payment Corporation

Here are some more of the tips that were discussed in January's workshop event:

Placement of Toolbars

If the standard and formatting toolbars are appearing on the same line, which probably means that at least some of the toolbar buttons are disappearing off the right edge of your screen, choose Tools, Customize, and click the Options tab. The first checkbox reads, "Standard and Formatting toolbars share one row." Uncheck this box to separate the two bars into distinct rows.

If you open extra toolbars on your screen, such as the Drawing toolbar, the Picture toolbar, the Visual Basic Toolbar, etc., the screen can get pretty crowded. You may have little toolbar windows scattered everywhere. You can drag the title bar of a toolbar window to the top, bottom, or side of your screen, and the toolbar will attach itself to the edge of the screen. If another toolbar is present and you drag a toolbar window onto that toolbar, the two bars will be joined. This is called docking. If you want to prevent docking, and just have your little toolbar window appear on top of an existing toolbar, hold down the Ctrl key while dragging the toolbar window - the window won't dock, it will just be placed on top of the area to which you drag it.

Overcoming Excel's Color Limitations

Have you ever noticed that the color palette in Excel seems somewhat limited? There are some colors that don't seem to be available in Excel and at times this can be frustrating.

Excel comes with a choice of 56 colors, so even if your computer screen can display more colors than this, 256 is common for example, you will only see a choice of 56 colors in Excel. You can change the selection of 56 colors so that the colors you need are among the 56 available colors.

To do this, choose Tools, Options, and click the Color tab. You will see the 56 colors that are available. To replace one of these colors with the color of your choice, click once on a color you don't want, then click the Modify button. You can drag your mouse to the color of your choice on the color palette that appears, then click OK.

If you ever want to return to the default color scheme, return to this window and click the Reset button. Colors stay resident within a workbook, but you can copy a color scheme from another workbook by entering the location of the other workbook in the "Copy colors from" field of this window.

Create a Toolbar Button with Your Company Logo

Do you have a standard macro or set of commands that you use for company spreadsheets? Maybe you place the company name in the header, and set up a standard format on the worksheet including column width, font, number format, etc. You can turn all of these commands into a macro, then assign the macro to a toolbar button. For your finishing touch, you can place your company logo right on the toolbar button.

Create the macro first, making sure you save the macro to the Personal macro workbook so that it will be available to all workbooks. Then open an image of your company logo (or whatever other graphic image you want to use for the button face), click on the image to select it, and click the Copy button on your toolbar to place the image in your Clipboard.

To assign the macro to a toolbar button, right-click on the toolbars, choose Customize from the pop-up menu, then click the Commands tab. In the categories list, choose Macros, then drag the smiley-face macro button up to the toolbar, in the position where you want your company macro to appear.

Right-click on the button and choose Assign Macro, then select the name of your company macro and click OK. Right-click one more time on the button, and choose Paste Button Image from the menu. Ta-da! Your picture appears on the button face! Close the Customize window by clicking Close to finish the job.

Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.