Top tips for Excel: Working with data

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002

Ever wish you had an Excel expert at your shoulder while you work, always ready with great tips and tricks for working with your data? If so, this article is for you. See the following sections to learn new and faster ways to work with data in Excel.

Selecting data

  • Select nonadjacent cells or ranges by selecting the first cell or range, and then holding down CTRL while you select other cells or ranges.
  • To select only the blank cells in a region, select the region, click Go To on the Edit menu, click Special, and then click Blanks.
  • Selecting a large range is easy. Just click the cell at one corner of the range, scroll to the opposite corner, and then hold down SHIFT while you click the cell at that corner.
  • You can specify the direction you want the selection to move after you enter data in a cell (for example, to the right if you work in rows). Click Options on the Tools menu, make sure the Move selection after Enter check box is selected on the Edit tab, and then click a direction in the Direction box.
  • Move to a specific area of the worksheet (such as the current region or the last cell) by clicking Go To on the Edit menu. Click Special, and then select the option you want.

Editing data

  • Instead of using the formula bar to edit the contents of a cell, you can edit directly in the cell by double-clicking it.
  • If you don't want to edit directly in cells, click Options on the Tools menu, click the Edit tab, and then clear the Edit directly in cell check box.
  • To increment a single number, hold down CTRL while you drag the fill handle at the corner of the selection.
  • To increment weekdays, quarters, or other series, select the first item and then drag the fill handle at the corner.
  • Create your own custom fill series to fill adjacent cells in a repeating pattern. Click Options on the Tools menu, and then click the Custom Lists tab.
  • Right-clicking a cell, object, or chart item displays a shortcut menu with useful commands.
  • Specify how data in a selected cell or range should be filled into other cells by dragging the fill handle at the corner of that cell or range with the right mouse button, and then clicking a fill option on the shortcut menu that appears.
  • When automatically filling cells, Excel can increment both the quarter and the year at the same time. For example: 1Q93, Q193, 1st Qtr 93, 1st Quarter 1993.
  • Delete a range of selected cells by holding down SHIFT while you drag the fill handle (located at the lower-right corner of the selection) up and to the left.
  • You can distribute the contents of a cell (or column of cells) across multiple columns. Select the cell or column, and then click Text to Columns on the Data menu.
  • Quickly insert a range of empty cells by holding down SHIFT while you drag the fill handle at the corner of a selection.
  • You can review all named cells or ranges you used in a workbook by clicking the down arrow next to the Name box on the Formula bar.
  • Start the spelling checker by pressing F7.
  • To move cell contents, drag the border of the selected cells to another location. To copy cell contents, hold down CTRL while you drag.
  • You can edit the contents of more than one sheet at a time. Hold down CTRL while you click the tabs of the sheets you want to edit, and then modify the data.
  • Back up every time you save. In the Save As dialog box, click Tools, click General Options, and then select the Always create backup check box.

Formatting data

  • To indent text in a cell, you can use the Increase Indent and Decrease Indent buttons on the Formatting toolbar.
  • An easy way to include a custom style from another workbook is to copy cells formatted with that style and paste them into the new workbook.
  • Right-clicking a column or row header displays a shortcut menu with the Column Width, Row Height, Hide, and Unhide commands.
  • If you double-click the Format Painter button on the Standard toolbar, it will stay selected so that you can apply the same format to multiple areas.
  • To freeze a row at the top of the window, select the row below it, and then click Freeze Panes on the Window menu.
  • To apply a built-in table format to a range of cells, click AutoFormat on the Format menu.
  • You can set the default font for future workbooks. Click Options on the Tools menu, and change the Standard Font and Size options on the General tab.
  • To change the default text format for the entire workbook, click Style on the Format menu, and then change the format of the Normal Style.
  • To convert preformatted HTML text to a table, select it, and then click Text To Columns on the Data menu.
  • Group rows and columns to easily show and hide them. Select the rows and columns, point to Group and Outline on the Data menu, click Group, and then click Rows or Columns.
  • To display numbers in thousands or millions, create a new number format. Click Cells on the Format menu, and then click the Custom category. In the Type box, type 0, or #, for thousands, and 0,, or #,, for millions.
  • Enter a value in dollar format by typing a dollar sign ($) before the value.
  • Enter a value in percent format by typing a percent sign (%) after the value.
  • Display negative numbers in red by clicking Cells on the Format menu. On the Number tab, click the Number or Currency category, and then select a red format under Negative numbers.

PivotTables and PivotChart reports

  • Change the name of a PivotTable® field by selecting the field button, and then typing another name.
  • Move the legend on a PivotChart® report by clicking Chart Options on the Chart menu. On the Legend tab, select the placement option you want.
  • Customize a PivotTable report by clicking Format Report on the PivotTable toolbar.
  • Pivot fields directly on the sheet without starting the PivotTable Wizard by dragging the field buttons.
  • You can reduce the amount of space a PivotTable report needs by dragging some row or column fields to the page field drop area.
  • Show or hide detailed data for a cell in the data area of a PivotTable report by double-clicking that cell in the table.
  • You can reposition an item within its PivotTable field by dragging its border.
  • Any changes you make to the source worksheet data can be included quickly in your PivotTable report. Simply click Refresh Data on the PivotTable toolbar.

Functions and formulas

  • If you need help choosing a function and entering its arguments, click fx (Insert Function) on the Formula bar.
  • Instead of clicking the AutoSum button, you can also press ALT+EQUAL SIGN (=).
  • To copy the formula in the active cell to all cells in the selected range, press F2, and then press CTRL+ENTER.
  • Insert subtotals into a range by clicking any cell in the range, and then clicking Subtotals on the Data menu.
  • To decrease all values in a range by 10 percent, type .9 in an empty cell. Copy the cell, and then select the range. On the Edit menu, click Paste Special, and then click Multiply.
 
 
Applies to:
Excel 2003