Workbook Tips
How to copy your filtered data to a location in a different worksheet:
1. Activate your destination worksheet.
2. On the Data menu, point to Filter, and then click Advanced Filter to bring up Advanced Filter dialong box.
3. Click Copy to another location, click in the Copy to box, and delete any entry in the box.
4. Click the top left corner cell of the area where you want to paste your rows.
5. Click the List range box, delete any entry in the box.
6. Activate your source worksheet and select your entire list.
7. Click the Criteria range box and delete any entry in the box.
8. Select your criteria cells.
9. Click Ok.
Note: If you want to get unique records, skip step 7 and 8. Just click Unique records only.
How to make a replica of a single formula:
You are going to edit your formula and want to back it up.
You do not want the original cell references changed.
Here is a way to accomplish your task in a few quick easy steps:
1. Click the cell (destination) right below your formula cell (source). If your destination is not empty then insert a row below the source row.
2. Hit CTRL and the apostrophe(" ' ") key.
3. Hit Enter.
4. Move the the formula by dragging the edge of the cell with the formula replica to any location of your choice.
If you want to back up an array formula, take these steps:
1. Click the cell (destination) right below your formula cell (source). Insert a row below the source row if necessary.
2. Hold down CTRL and the apostrophe(" ' ") key.
3. Hit Enter.
4. Move the the formula by dragging the edge of the cell with the formula replica to any location of your choice.
How to back up your formulas before editing them without changing all the references:
You want to Make a replica of a single range or multiple ranges of formulas without changing the references.
1. Select the the formula range. For multiple ranges, hold down CTRL and select each one.
2. Group the source sheet with another blank sheet (Insert a new sheet if necessary).
3. Click F2 + CTRL + Enter.
4. Ungroup the sheets (Right-click on the tab for the target sheet and select Ungroup Sheets).
5. In the target sheet, move the copied range(s) to a different location of your choice by dragging and dropping the edge of each range.
6. Regroup the sheets.
7. Click F2 + CTRL + Enter.
8. Ungroup the sheets (Right-click on the tab for the source sheet and select Ungroup Sheets).
Note: This is an alternative to this tip.
|
