Thursday, November 6, 2008

Excel at Excel (Or My Quick Tips for MS Excel)

In this space, sometimes I'll offer strategic insight into the Business Intelligence space, and sometimes I'll offer helpful tips to the grunts* in the field, actually doing the heavy lifting of reporting, analyzing, and planning. These tips will cover mostly Microsoft products, since that’s what I specialize in. I have tips for SQL Reporting Services, SQL Integration Services, SQL Analysis Services, MDX, T-SQL, and most importantly, Microsoft Excel.

The major arrow of every analyst's quiver is Microsoft Excel. Without exception, it is heavily used at every single one of my customers. I won't give away all of my tips now. What would keep you coming back? Certainly not my charming personality...
Here are a few overlooked gems that Microsoft Excel offers you:

-By default, when you look at a spreadsheet, you see the results of the formulas, not the formulas themselves. You can manually set "Show Formulas", or you can just click CTRL-' (that a single quote.) Usually you can find that key just to the left of the 1 key on the top row of your keyboard.

-You can look at the same workbook in two different windows. You can keep "Show Formula" on one view of the workbook and the default results of the formulas on the other view of the workbook. This works great if you have multiple monitors, displaying each view on its own monitor.

-In Excel 2007, the status bar is better than ever. Highlight some cells and look at your status bar at the bottom of the screen. You'll see a count, sum, and average right in front of you. In addition, right-click on the status bar and check out all of your options. You'll be surprised at what you can view down there.

-Most people know how to save a workbook, but did you know you can save an entire workspace? When you work on those month-end reports, and you find yourself looking at the same four worksheets over and over again, save it as a workspace. The next time you work on month-end, just open up the workspace and those four worksheets will open right in front of you. It's hidden on the View tab of the ribbon. It's called "Save Workspace."

-If you want the same report to open each time you start your computer, just follow these steps. First, put a shortcut to MS Excel in the Startup folder of your Start menu. Then find the startup folder of MS Excel. Put your workbook in there. You can usually find it at "C:\Users\Ike(unless your name is not Ike, then just use your name)\AppData\Roaming\Microsoft\Excel\XLStart".

That's it for now. Keep checking back for more quick tips and hints.

*I use the word “grunts” with nothing but respect and fondness.