500 Tips
Spreadsheets
Microsoft Excel

Instant Insert

Insert new cells and paste data in a single step. When copying and moving cells, select the range of cells you want to move, point with your mouse to the border of the selection, and hold down the Ctrl key as you drag the range to the location. When you release the mouse button, Excel copies the cells to the destination. To insert data at the destination, press and hold the Shift and Ctrl keys as you drag your selection; existing cells are shifted down or right.

Easy Access to a Database

Want to convert an Excel table into an Access file? Choose Data/Convert to activate a wizard that steps you through the process. If this option is missing from your menus, select Tools/Add-Ins and make sure the AccessLinks Add-Ins choice is selected. If this choice does not appear, you will have to install it using Setup. Access needs to be installed for the wizard to work.

Rev Up AutoFormats for Quick Charts

Store your favorite chart formats as user-defined AutoFormats and quickly apply them to charts. To create a user-defined AutoFormat, open or create a chart with the desired formatting options. Choose Format/AutoFormat and click the User-Defined option. Press the Customize button to activate the User-Defined AutoFormat dialog. Press the Add button, name and describe your format, and save it for later use.

Faster Formulas and Functions

To create formulas and functions quickly, type an equal sign in the cell, then press the button in the formula bar to activate the Function Wizard. This wizard provides a list of most recently used functions and defines each argument you need to enter. The wizard's buttons automatically enter nested functions, too.

Well-Rounded Numbers

Create a custom number format to display currency values in thousands ($1,000 instead of $1,000,000). Choose Format/Cells, go to the Number tab, and select Custom under Category. In the Type field, enter the following: $#,;($#,). Once created, Excel will remember this format and display it in the Custom Format options.

Discard Duplicate Duplicate Entries Entries

To eliminate duplicate values from a column of text, select the column of text to be filtered and choose Data/Filter/Advanced Filter. In the dialog box, click on the Unique Records Only box. Choose Filter the List, in-place to hide the rows containing duplicate information, or choose Copy to Another Location to create a duplicate list with only unique values. Excel turns the row numbers blue to indicate which rows are included in a filter.

Track Data with Templates

To track data entered in custom forms, try the Data Tracking feature in the Template Wizard. Create a worksheet to use as the data entry form. Choose Template Wizard from the Data menu. The Wizard steps you through the process of linking fields from your form to a tracking database. Once finished, you'll have a custom template that anyone can open and fill out, with entries entered automatically in your tracking database.

[ Go to 500 Tips Table of Contents ]