21. Fill a highlighted range with the same item
Highlight a range, type the data, then press Ctrl+Enter.
22. Using mouse to click on a cell to fill a formula with the cell address
When you enter a formula and your formula require a cell address, you can use mouse to click on the cell. The address of the cell will be entered into the formula.
23. Using 'Delete' icon to delete row
By using the Delete icon, you can avoid many clicks in order to delete a row or a selection of rows. This icon is available under the menu Tools/Customize.
When you click the Tools/ Customize menu, the Customize dialog box will appear.
Click the Commands tab.
Under the Categories label, select Edit item from the list.
Under the Commands label, search for the Delete Rows icon. Drag the icon to toolbar area and drop it there.
Close the Customize dialog box (press Esc key).
From now on, you can just click on the icon to delete a row. You can remove the icon by dragging the icon back into the Customize dialog box.
24. Use 'Esc' key to dismiss a dialog
Press Esc key to close a dialog, rather than looking for your mouse and clicking on the Cancel button.
25. Fill a range in a column with a series of numbers.
Write a beginning number into the start cell. Point your mouse to the cell's bottom-right handle until it changes into a plus sign (+). Press the right mouse button. While still holding down the button, drag your mouse to highlight the range area that you want. Once you release the mouse, a list of actions (floating menu) will appear. Select item Fill Series. A series of number will be inserted for you.
26. Using 'Paste' icon to paste copied item
By using the Paste icon to paste a copied item, you can avoid many clicks in order to paste repetitively. This icon is available under the menu Tools/Customize.
212
When you click the Tools/Customize menu, the Customize dialog box will appear.
Click the Commands tab.
Under the Categories label, select Edit item from the list.
Under the Commands label, search for the Paste icon. Drag the icon to toolbar area and drop it there.
Close the Customize dialog box (press Esc key).
From now on, you can just click on the icon to paste a copied cell content. You can remove the icon by dragging the icon back into the Customize dialog box.
27. Using 'Select visible cells' icon to select visible cells
By using the Select Visible Cells icon, you can avoid many clicks in order to select visible cells after you filter the rows. This icon is available under the menu Tools/Customize.
When you click the Tools/Customize menu, the Customize dialog box will appear.
Click the Commands tab.
Under the Categories label, select Edit item from the list.
Under the Commands label, search for the Select Visible Cells icon.
Drag the icon to toolbar area and drop it there.
Close the Customize dialog box (press Esc key).
From now on, you can just click on the icon to select visible cells. You can remove the icon by dragging the icon back into the Customize dialog box.
28. Using menu 'Insert/Copied cell'
Sometimes we want to copy contents of cells and paste them into cells that already has content and push the cells with content down or to the right. After copying the cell content, use the menu Insert/Copied cell to insert the copied cell. From the Insert Paste dialog box, select either Shift cells right or Shift cells down. The current cell or highlighted cells will be moved downward or to the right (its content will not be overwritten). This operation is also useful if we want to reorganise positions of our data.
213
However, if we 'cut' rather than 'copy', the menu that appears is Insert/Cut cells and this will leave the 'cut' cells empty.
NOTE
Be careful when inserting, because only a highlighted cell is moved. This means if you highlight some of the cells in your data area, it may cause you whole data to become disorganised.
29. Using 'Custom View' to remember a filtered data
After filtering your data, you can ask Excel to remember that filtered view. Click on the menu View/ Custom Views. The Custom Views dialog box will appear. To capture the current view, click Add button. In the Add View dialog box that appears, give a descriptive name for the view in the Name box. Click OK. The dialog will close and you will see the dialog Custom Views again.
The name you set in Add View dialog will appear in the Custom Views dialog box.
Click Close to close the dialog. Next time, when you want to see the view, you can open the Custom Views dialog, click on a view name, and then the Show button. This way, you can avoid using filtering operation each time you want to see the same filtered data. If you make changes to the data, the view will also be updated automatically.
NOTE
After you use the view. Excel will freeze the last row of the view. This requires you to use menu Window/Unfreeze panes to unfreeze the frozen row.
If you want to delete a view, open the Custom Views dialog, then click on a view name, and click Delete button.
214
No comments:
Post a Comment