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.

You may like these other stories...

Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several...
On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I...
By David Ringstrom, CPA In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation....

Upcoming CPE Webinars

Jul 16
Hand off work to others with finesse and success. Kristen Rampe, CPA will share how to ensure delegated work is properly handled from start to finish in this content-rich one hour webinar.
Jul 17
This webcast will cover the preparation of the statement of cash flows and focus on accounting and disclosure policies for other important issues described below.
Jul 23
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.