Software

Perform multiple analyses easily with a Clear Criteria button

If you need to analyze data in an Excel worksheet using a series of criteria, you don't have to manually delete entries from the criteria row. See how you can create a button to clear those entries for you.
You've set up a worksheet so your department manager can perform various analyses on the employee expense data. For example, to find the totals for the Division 1 Managers, she would enter 1 in the Division criteria field and MN in the Position criteria field. Rather than have the manager delete both entries from the criteria row to perform the next analysis, you can save her some time by including a command button like the one in Figure A, which will clear them all at once automatically.

Figure A

To create the button, follow these steps:

  1. Go to Tools | Macro and click Record New Macro. In Excel 2007, click the Developer tab and then click Record Macro in the Code group.
  2. Enter ClearCriteria in the Macro Name box (Figure B) and click OK.

Figure B

  1. Select A2:P2.
  2. Press the Delete key.
  3. Click A2.
  4. Click the Stop button on the Macro toolbar. In Excel 2007, click Stop Recording in the Code group on the Developer tab.
  5. Right-click on any toolbar and select Forms. In Excel 2007, click the Insert Command button on the Developer tab.
  6. Click the command button control under Form Controls (Figure C) and drag it to the spot where you want to place the button in the worksheet.

Figure C

  1. Click ClearCriteria in the Macro list and then click OK.
  2. Right-click the command button and select Edit.
  3. Change the Button1 caption to Clear Criteria.
  4. Click anywhere outside the command button.

Now, when the manager is ready to enter new criteria, she simply clicks the Clear Criteria button to delete any previous entries.


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

0 comments