Smith College Admission Academics Student Life About Smith news Offices
  Home > Offices > Information Technology Services > TARA > Microsoft Excel > Excel Tips & Tricks  
   Reset Password     Smith Directory    Smith Portal   Server Status  Smith Software

Getting Started
25Live Scheduling
Account Forms
Active Directory
Admin Systems*
File Transfer
Google Apps
   Smith Mail
Internet Browsers
   Backup & Restore
   Related Apps
   Other Info
Microsoft Office
Microsoft Windows
Mobile Devices
Smith Network
Smith Portal
Standard Software
Terminal Emulators
Viruses & Spyware
Web Development
Wireless Access

*Smith login required



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.

Selection Techniques

Opening Multiple Documents

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

Leading Zero's dropped

Using Excel as a Data Source for Mail Merge

Linking Cells

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


Back to top>

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.


Back to top>


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:

  1. Select the View tab, from the Window grouping click on Split.

  2. 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. 

  3. Remember each pane is showing the same document. Use the vertical scroll bar to display the desired portion of the document in each pane.

  4. To return to a single view, either double click on the split bar or from the View, Window grouping click Remove Split


Back to top>


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. 

Back to top>

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.


Back to top>

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.

Back to top>


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.

Back to top>


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:


Back to top>

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





Back to top>



Copyright © 2015 Smith College Information Technology Services  |  Stoddard Hall   |  Northampton, MA 01063
413.585.4487  |  Questions or comments?  Send us email
 |  Last updated January 10, 2013

DirectoryCalendarCampus MapVirtual TourContact UsSite A-Z