6 Essential Excel Tips

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.

Click here for a full list of Excel keyboard shortcuts or a printable Quick Reference card.

3. Insert an image or company logo into the header of a spreadsheet

  1. From File menu choose Page setup.
  2. Select the Header/Footer tab.
  3. Click Custom Header.
  4. Select Left area.
  5. 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.
  6. 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.

Note:
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.
Technique 1

  1. Press F5, or choose Edit, Go To…;
  2. In the Go To dialog box, click Special.
  3. In the Go To Special dialog box, select Constants.
  4. Click OK.

Technique 2 – Conditional
Formatting

  1. Select the data area.
  2. From the Format menu, select Conditional Formatting.
  3. In Condition 1, select Formula Is.
  4. In the Formula Box, enter the formula =Istext(A1).
  5. Click Format…, choose any format from the Format Cells dialog box, and click OK.
  6. Click OK.

Exploring Excel

Excel is one of the most useful programs you have in Windows, but for many, it is also the most intimidating.  It is a great resource for compiling and organizing data quickly and efficiently.  Our next few blogs are intended to help you explore the world of Microsoft Excel, from the basics and advancing to some pretty cool stuff.  To start off, let’s go over some basic cell formatting information.

Cells are the rectangular boxes that appear when you open an Excel worksheet.  They have reference numbers and letters based on what column and row they are in, which gives you an easy point of reference.  Columns run vertically and are listed by letter at the top of the screen, while rows run horizontally and are listed on the left-hand side of your screen.

Each cell in a worksheet can be formatted with many properties. The six tabs of the “Format Cells” window contain all of the formatting options. Multiple cells can be formatted in one step by first selecting the cells by highlighting the ones you wish to format.

To move cell contents, right-click in the selected cell and click Cut. To copy cell contents, click Copy. Then right-click in the new location and click Paste. To paste a group of cells, right-click in the cell where the top left cell of the group should be located, and click Paste. Remove the animated border around the original cell by pressing the ESC key, or start typing in a new cell.

To insert a new row in a spreadsheet, right-click on a row number, and click Insert. Excel always inserts the row ABOVE the row that was clicked on. To delete a row, right-click on the row number, and click Delete.  To insert a new column, right-click on a column letter and click Insert. Excel always inserts the column to the LEFT of the column that was clicked on. To delete a column, right-click on the column letter, and click Delete.

Each cell in a worksheet can be formatted with many properties. The six tabs of the “Format Cells” window contain all of the formatting options. Multiple cells can be formatted in one step by first selecting the cells.  The “Format Cells” window can be accessed in all versions of Excel from the right-click menu. In Excel 2007 and 2010, some formatting options are available on the Home Tab on the Font, Alignment, and Number groups. To see formatting options not displayed, click the little arrow in the lower right corner of the group and the “Format Cells” window displays.

From there, you can format the alignment, font, borders, and data from several different tabs.  You can determine many of the formatting styles for appearance in Excel the same way you would in Word or Powerpoint.  However, as Excel is built to work with numerical data, much of the formatting will deal with formulas you use to populate data in the cells.  We will be exploring data formatting—the most exciting part of Excel—in further posts.  Hopefully this basic introduction helps get you comfortable with Excel.  It’s not too different from other Microsoft applications and can do some very cool things!