6 Essential Excel Tips
April 12, 2012 Leave a comment
We recently covered reasons why Microsoft’s Excel is so powerful in our article, “Exploring Excel“.
Here are the 6 most essential Excel tips that you need to get the job done:
1. Speed Copy
To quickly copy a cell in a row or column, select the cell you want to copy, then move the mouse until it is positioned above the lower right corner of the cell. The cursor will turn into a plus sign (+).
Now, hold the left mouse button and drag it by the row or column until you’ve selected all the cells you want to paste. Release the button and the first cell is copied to all selected cells.
2. Use Shortcuts
There are too many keyboard shortcuts for us to cover in this article. Here are the main ones that will save you substantial time:
CTRL+PgUp – Switches between worksheet tabs, from left-to-right.
CTRL+PgDn – Switches between worksheet tabs, from right-to-left.
CTRL+B – Applies or removes bold formatting.
CTRL+C – Copies the selected cells.
CTRL+D – Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F – Displays the Find and Replace dialog box, with the Find tab selected.
SHIFT+F5 – also displays this tab, while SHIFT+F4 repeats the last Find action.
CTRL+SHIFT+F – opens the Format Cells dialog box with the Font tab selected.
CTRL+V – Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents.
CTRL+ALT+V – displays the Paste Special dialog box. Available only after you have cut or copied an object, text, or cell contents on a worksheet or in another program.
3. Insert an image or company logo into the header of a spreadsheet
- From File menu choose Page setup.
- Select the Header/Footer tab.
- Click Custom Header.
- Select Left area.
- Click the Picture icon (second from right). In the Insert Picture dialog box, search for and select the logo or picture you want to add.
- Click Insert, Click OK.
4. Color Rows Based on Text Criteria
Useful for highlighting information quickly, you can utilize this tip to color rows based on the text input into the row:
1. Select a cell in the region, and press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A) to select the Current Region.
2. From the Format menu, select Conditional Formatting.
3. In Condition 1, select Formula Is, and type =$C1=”Desktop”.
4. Click Format, select the Font tab, select a color, and then click OK.
5. In Condition 2, select Formula Is, and type =$C1=”Server”.
6. Repeat step 4, select a different color than you selected for Condition 1, and then click OK.
7. In Condition 3, select Formula Is, and type =$E1=”Printer”.
8. Repeat step 4, select a different color than you selected for Condition 1 and Condition 2, and then click OK twice.
Be sure to distinguish between absolute reference and relative reference when entering the formulas.
5. Saving a customized workbook as a template
Why it is important to save a customized workbook as a template?
To allow you to save custom Styles.
To allow you to save Print Setup options as Header/ Footer themes.
To enable many users to use the same design and structure.
To save a workbook as a template:
1. Select File -> Save As.
2. In Save As dialog Box, In the File name box, type a name for the new template file
3. From Save As type box select Excel Template, and click Save.
4. Close the open template workbook.
6. Selecting cells that only contain text
By selecting cells that only contain text, you can distinguish between cells containing different types of data, which allows you to delete, fill or lock cells by type.
- Press F5, or choose Edit, Go To…;
- In the Go To dialog box, click Special.
- In the Go To Special dialog box, select Constants.
- Click OK.
Technique 2 – Conditional
- Select the data area.
- From the Format menu, select Conditional Formatting.
- In Condition 1, select Formula Is.
- In the Formula Box, enter the formula =Istext(A1).
- Click Format…, choose any format from the Format Cells dialog box, and click OK.
- Click OK.