Microsoft Excel, a staple of the Microsoft Office productivity suite, is used by everyone for everything imaginable—from simple lists to complex calculations—but most of us still have a lot to learn about using the popular spreadsheet program. This short list of Excel tips for Excel 2002 and later should provide help in most every area, including basic settings, PivotTables, advanced cut and paste functions, macros, and keyboard shortcuts. Enjoy!
TIP 1: Change the applied function.
Most everyone knows that if you multiselect a series of cells, you can look on the status bar to view the Sum of the cells' values. But not everyone knows that you can right-click the Sum in the status bar and change the applied function to Average, Count, Count Nums, Min, or Max.
TIP 2: Change the default number of worksheets.
It's a common pet peeve that every Excel workbook has three worksheets, regardless of whether you're using all of them. You can change the default number of worksheets in a workbook to whatever you want by going to Tools, Options and selecting the General tab to adjust the Sheets in a new workbook setting.
TIP 3: Format PivotTable reports for SQL Server Analysis Services.
If you want to format Excel PivotTable reports for Microsoft SQL Server Analysis Services, download the Excel Add-in for Analysis Services (http://go.microsoft.com/?linkid=4351028). You must be running Excel 2002 or later. Some of the add-in's instructions are counter-intuitive, so you might want to first watch the TechNet Webcast "Creating Free-form and Structured Reports with Excel Add-in for Analysis Services" (http://www.microsoft.com/uk/windowsserversystem/bi/webcast/exceladdinwebcast.wvx).
Tip 4: Retain PivotTable formatting.
PivotTables forget all your painstaking formatting every time you refresh them. A good workaround is to use the AutoFormat feature (from the Format menu). It might not provide the exact formatting you want to apply, but it's an acceptable compromise when you want the formatting to survive a refresh.
TIP 5: Use the VLookup and HLookup functions to reduce manual work.
The VLookup and HLookup functions search for values vertically (VLookup ) or horizontally (HLookup) in an array. VLookup searches a specified column for values based on given criteria and returns corresponding cell values (or a formula that uses them) in the same row of the cell with the exact or approximate match. HLookup works the same way but evaluates rows instead of columns. Users can see these functions in action in the examples at the bottom of the VLookup topic on the Office Online Web site at http://office.microsoft.com/en-us/assistance/HP052093351033.aspx.
TIP 6: Use a Named Range as PivotTable input.
When building a PivotTable based on a range of cells from another spreadsheet, you need to do a lot of rework if you add data to the referenced spreadsheet. To avoid this rework, create a Named Range by selecting the participating columns' range, then click the Name box, which is at the left end of the formula bar, and type a name. Use the new Named Range as the input for your PivotTable so that whenever you add data, you can simply refresh the PivotTable without redefining the range.
TIP 7: Minimize workbook file sizes.
When you build workbooks with PivotTables in them, the data is cached in the workbook and can create large file sizes. Here are two tips that can help minimize file sizes:
- If you have several PivotTables that use the same data, in the PivotTable and PivotChart Wizard always specify as your data source Another PivotTable report or PivotChart report. Excel will use significantly less memory if the workbook is based on data from an existing report.
- You can choose not to cache the data in the workbook (unless you're accessing an Analysis Services OLAP cube) by clearing the Save data with table layout check box in the PivotTable and PivotChart Wizard's PivotTable Options screen. The drawback is that you must refresh the PivotTable each time you open the workbook.
TIP 8: Use Paste Special options.
If you do a lot of creative cutting and pasting, you'll appreciate Excel's Paste Special functionality. When you want to paste content into a cell, go to the Edit menu and choose Paste Special. Experiment with the different options for pasting the clipboard content as just values, keeping the formulas, with or without formatting, and so on. The Transpose option can be a real timesaver when you have values in a row that you need to pivot to a column or vice versa. For convenience, Microsoft also provides the most common options from Paste Special in a separate Paste Options contextual menu that appears in the lower right of any newly pasted selection.
TIP 9: Use macros.
Excel users come in two varieties—those who use macros and those who don't. If you perform many repetitive tasks, you should get familiar with macros. You'll find a Macro submenu located under the Tools menu; for a primer, check out the Office Online topic "Create a macro" at http://office.microsoft.com/en-us/assistance/HP052047111033.aspx. Creating macros is as easy as recording a series of actions, but once you realize the time savings and accuracy of automating a manual process, you'll be motivated to learn a little Visual Basic for Applications (VBA) so that you can get under the hood and tweak your recorded macros to do more and more. Be aware that malicious macros can be a security threat; Microsoft has added features that disable macros by default, so you might need to change your Macro Security Level in the Macro submenu or enable macros on a per-workbook basis every time you open a workbook.
TIP 10: Use keyboard shortcuts.
True efficiency with Excel lies in the mastery of keyboard shortcuts. To get started, peruse Microsoft's documentation (http://office.microsoft.com/en-us/assistance/HP052037811033.aspx), select five tasks you perform regularly, and memorize their shortcuts. As using the shortcuts becomes second-nature, pick out your next five to learn. You'll quickly find yourself moving around your worksheets a screen at a time (Page Down, Page Up), actually deleting cells (Ctrl+Hyphen) instead of only their contents, or even digging up rarely documented shortcuts such as moving from worksheet to worksheet in a workbook (Crtl+Page Down and Ctrl+Page Up).
TIP 11: Close multiple workbooks.
If you open a lot of Excel workbooks at one time, at some point you'll need to close each one. You can close them all at once by holding down the Shift key before selecting the File menu. When you do so, Close becomes Close All.
