Hiding/Unhiding Columns

Press Ctrl-0 (zero) to hide a column. Use the F5 key to navigate to a hidden column and then press Ctrl-Shift-0 (zero) to unhide.

Resetting Excel 2003 Color Palette

Excel 2003 tip: If you suddenly can’t change colors on a cell, reset your palette. Choose Tools, Options, Color tab, and then click Reset.

First Impressions of Excel 2010

.by David Ringstrom, CPA As you may have heard, the next version of Microsoft’s venerable Office suite recently entered “technical preview,” or public beta. While Excel 2007 introduced some dramatic changes, Excel 2010 seems to offer mostly refinements and only a few wholesale changes. Thus Excel 2007 users should have an easy time transitioning to …

Continue reading

Workbook/Worksheet Navigation Tricks

Press Ctrl-Tab to switch between open workbooks. Use Ctrl-Page Up/Ctrl-Page Down to switch between worksheets.

Date Series Formula

This formula allows you to create a series of dates that alternate between the 15th and the last day of each month: =IF(B1=DATE(YEAR(B1),MONTH(B1)+1,0),DATE(YEAR(B1),MONTH(B1)+1,15),DATE(YEAR(B1),MONTH(B1)+1,0))

IFERROR Function

Resolve #DIV/0! errors in Excel 2007 with =IFERROR(A1/A2,0) – puts a zero in the formula cell if either A1 or A2 are blank.

Creating A Series Of Letters

Enter this formula in cell A1 to return the letter A: =CHAR(64+ROW()).Copy down the column to create a series of letters.

Formula Bar Navigation Trick

Press F2 in the formula bar to enable the arrow keys to move left and right in the formula bar without changing cell addresses.

Adding/Removing Columns/Rows

Press Ctrl-+ (the plus sign on your keypad) to add rows or columns, use Ctrl-Minus (the – sign on your keypad) to delete.

Cell Border Trick

Apply border line to the top of SUM cell rather than the bottom of the cell above–avoids erasing border when copying formulas.

Whither Conficker?

By David Ringstrom, CPA   It’s only been a couple months since the Conficker virus completely dominated the media, yet there’s been a deafening silence on this front since April 2. All of the hype would have had you believe that Conficker was set to wreak havoc on April 1, but that ominous Wednesday passed …

Continue reading

Tricks for hiding and unhiding Excel rows and columns

By David H. Ringstrom, CPA Hiding and unhiding rows and columns are mundane tasks that many users take for granted. However, sometimes simple tasks can trip up Excel users, like unhiding just one row or column within a hidden set. Other users don’t know simple keystroke commands that can streamline hiding and unhiding columns or …

Continue reading

Tricks for opening and closing multiple Excel files

By David H. Ringstrom, CPA From time to time you may find yourself immersed in a large project that involves several related Excel workbooks. In such cases it can be tedious to manually open each Excel file when you need everything available at once. Excel has long had a helpful feature that is hidden in …

Continue reading

Buried gold: Excel’s Form command

By David H. Ringstrom, CPA Many users are not aware that Excel has long had a feature that enables you to browse through a table of data one record at a time. As shown in Figure 1, the Form command provides an easy way to navigate through a table of data. This command is easy …

Continue reading

Managing Excel Add-Ins

By David H. Ringstrom, CPA Add-ins are programs that plug-in into Microsoft Excel to add additional functionality. Some add-ins expand Excel’s core functionality, while others allow third-party products to add features to Excel’s environment. In this article I’ll discuss how to enable some of Excel’s hidden features, as well as give you some pointers on …

Continue reading