TIPS & TRICKS FOR EXCEL 2007
This collection of tips and tricks will save you time and maybe some frustration. Some tips can be used in more than one Office application, many of these carried over from older versions of Excel.
Viewing Two Parts of a Document Simultaneously
Keeping Column or Row Headings Visible
Hide what you don’t need or want to see
Naming a Worksheet
Number signs appear in the cells
Using Excel as a Data Source for Mail Merge
Selection
Techniques
There are several selection techniques you can use while working in an Excel document. Here are two you may not have known about.
A large block of text: If the text to be selected goes past your screen, click and drag is too fast, select the first cell. Using the right scroll bar, scroll to the last cell to be selected, while holding down the SHIFT key, click. You have just highlighted your selection.
Non-consecutive cells, rows or columns: Hold down the Control key when selecting the cells, rows or columns
Opening
Multiple Documents
If
you need to work with more than one document at a time, Microsoft makes it
easy to open multiple files. There is a catch: these files must be in
the same folder. Use the selection techniques to select the files, then
click the Open button.
Selecting One File: Click once on the file.
Selecting Consecutive Files: Click on the first file, point to the last file. While holding down the SHIFT key, click on the last file.
Selecting Non-consecutive Files: While holding down the CTRL key click on each file.
Viewing Two Parts
of a Document Simultaneously
When working with
a long document you may want to see two parts of the document same time.
To split the screen:
- Select the View tab, from the Window grouping click on Split.
- The
document screen view will appear to have a blue line drawn
through the middle. Moving your mouse up or down adjusts
the amount you view in each screen.
- Remember
each pane is showing the same document. Use the vertical scroll
bar to display the desired portion of the document in each
pane.
- To return to a single view, either double click on the split bar or from the View, Window grouping click Remove Split.
Keeping Column or Row Headings Visible
When a worksheet becomes wider or longer than a screen view, you can’t see the column or row headings. You may need the column headings or row heading to properly enter data. The feature you need is called Freeze Panes.
The Window grouping on the View ribbon has the feature Freeze Panes. There are three options for you to choose from:
Freeze Top Row: this keeps the top row visible while you scroll down through the rows in a document.
Freeze First Column: this keeps the first column visible while you scroll to the right through the columns in a document.
Freeze Panes: this keeps both rows and columns visible as you scroll through the document. To freeze both rows and columns, you must first click below the needed row and one column to the right. Example selecting cell B2 would freeze row 1 and column A.
Hide what you don’t need or want to see
Select the column/row or columns/rows to hide. The shortcut is to right-mouse click on the selected column or rows and select Hide from the menu.
Example: columns D and E are missing. The column separator becomes thicker.
To bring the hidden column or rows back into view, select the column before and after the hidden column. In this example you would select column C and F. Right mouse click and you will find Unhide on the menu.
Note: You will find the Hide / Unhide feature on the Home ribbon, under the Cell grouping. The Format drop-down has a Visibility Hide & Unhide, Rows, Columns and Sheets.
Naming a Worksheet
An Excel Workbook can contain several Worksheets; by default sheets are numbered. (Sheet 1, Sheet 2) You can easily give your worksheets names that make more sense to you by right-clicking the Sheet# tab located directly above the status bar, select Rename from the menu. The name appears highlighted. Type a new name, then press Enter.
Number signs appear in the cells
If you see cells filled with ######### signs that means the column is not wide enough to display the actual number. Widen the column by placing the mouse pointer on the column separator until you see the double arrow bar click and drag to widen the column or double click on the column separator. Excel will automatically widen the column to the widest information in that column.
Leading Zero’s were dropped
If you typed or imported data like Social Security Numbers or Zip Codes and it begins with a zero the leading zero is dropped from the cell. Select the cell, column or row. From the Home Ribbon, click the dialog box launcher in the Number grouping. Select Special under the category then select the appropriate format.
Using Excel as a Data Source for Mail Merge
If an Excel Spreadsheet is used for Name and Address type records, you can also Mail Merge that data in Word. To successfully use a spreadsheet for mail merge Row 1 must be column headings and the data must start on Row 2. Example:
Linking Cells
You can link cells in a single sheet or between sheets or between Workbooks.
Select and copy the cell to be linked. Move to the appropriate cell in that sheet, another sheet or another workbook. Right click in the cell and select Paste Special. From the Paste Special Dialog box click the Paste Link button.
Within the same sheet the pasted linked cell reference appears like this: =$B$11 (with absolute markers)
A Pasted Linked cell in a different sheet same Workbook would reference like this: =Sheet1!$B$11 (Sheet1 would be replaced with the name of the sheet if you have named followed by the cell location).
A Pasted Linked cell in a different Workbook would look like this: =[Book1]Sheet1!$B$11 [Book1] would be the name of the Workbook File followed by the sheet name followed by the cell location
