Shortcut keys instead of using the mouse

Open Word, Excel or PowerPoint, press the ALT key on the keyboard one time.  You should notice a letter on each tab of the ribbon.  Press the letter that corresponds with the ribbon tab. Now each function will display a letter, letter combination or number keystroke.   They display in upper case letters but they are not case senstive. If a combination of letters is displayed press the first one followed by the second one.

Files open in "Read Only"

When opening an Excel file and the user receives the following message:

"The file has been converted to a format you can work with, but the following issues were encountered.  The file has been opened in read-only mode to protect the original file.  This workbook uses more rows and/or columns than are supported in this version of Excel."

Remove the Set Print Area if one has been set.  Use the Print Preview to view the sheets.  In the lower left hand corner look for the number of pages.  If grid lines or colored areas were placed on a whole row or whole column you may be seeing hundreds of pages.  Grid lines and colored areas should only be placed on actual data.  You must clear all other unnecessary formatting.

Convert UPPERCASE to lower or Proper case without retyping

Stop the retyping and follow these steps.  This is a two part process. First you will create a formula to convert the text then you will copy and paste the value into a new column.

1.  Insert 2 blank columns to the right of the column in question (A).  The example below shows column A in UPPERCASE column B and C are blank, column D has a location.

2. In this example a formula would be placed in the first cell to the right of the first name B2. 

3.  Type the following formula  =Proper(A2)  pressing enter will convert the UPPERCASE NAME to the proper case name.  Example below shows both the formula and the proper case name.

4.  Now using the Autofill handle in the lower right hand corner of B2 click hold and drag this down the column to copy the formula that converts the character case.  DO NOT DELETE COLUMN A at this time.

5.  Now that column B has the proper case displayed you must copy Column B and paste it into  column C using Paste Special.  Reminder:  Column B is only displaying a value of a formula not the actual text.  Click on the column heading B to select the column. Copy using CTRL+C or the copy icon.

6.  Paste Special:   Right mouse click in cell C2, then select Paste Special from the quick menu.  The Paste Special dialog box will appear.   

7.  Click on the radio button for Values.  This will paste the value of the convert formula into column C.

8.  If Column C looks correct you can now delete Column A and B.

Note: There are two other formulas that change case as well: =UPPER(cell)  will convert to all upper case characters.   =LOWER(cell) will convert to all lower case characters.  Replace (cell) with the actual cell location.

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:

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. 

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