Tips for Increasing Your Excel Productivity

Data Input

Excel is an excellent vehicle for data input. It's fast and easy. You can input a row of 15 numbers in just a few seconds. But Excel provides a number of useful tricks that speed up data input even more. Below are a few tips that should make your data entry experience more productive. There is some variation among Excel versions but try them out. Most work.

Repeating an input value:

Enter a value in a cell (say 5%), then place the cursor over the lower right hand corner or the cell. When the + sign appears, simply drag the cell across all fifteen years and the values will automatically become 5%. If you wish increases by increments like 100, 200, 300, etc. then simply select the first three cells and drag the + sign to the remaining cells and Excel will figure out that you want to increase the cells in increments of 100.

Fill a selected range with a single input:

Select a range, then type the data into a cell, then press "Ctrl+Enter".
Example: select years 1 thru 15, enter 5%, then do [CTRL Enter].
All 15 cells will now have 5% entered.

Another way to copy a constant value to all the years is to enter a number and then simultaneously press Ctrl + Shift + Enter. This method is unique to IPS Models and is not an Excel feature.

Navigation

(for a listing of all keyboard short-cuts go to keyxl.com)

• Skip to the end of the document: Type "Ctrl+End".
• Skip to the beginning of the document: Type "Ctrl+Home".
• Skip to the end of the Line: Type "Ctrl+right arrow".
• Skip to the beginning of the Line: Type "Ctrl+left arrow".

Other Useful Shortcuts

• [Ctrl][Shift]A: Use this combination after typing a function name, and it will automatically enter the parentheses and the argument names for the function you typed.
• [Alt][F8]: Displays the Macro dialog box.
• [Alt][F11]: Opens the VBA Editor or switches to it if it's already open.
• [Ctrl][1]: Displays the Format Cells dialog box.
• [Ctrl][Shift][~]: Applies the General format.
• [Ctrl][Shift]$: Formats the current cells as Currency.
• [Ctrl][Shift]#: Applies the Date format.
• [Shift]F10: Displays the shortcut menu that applies to where the cursor is located. (or simply right click the mouse)
• [F6]: Moves to the next pane in a split worksheet.
• [Ctrl][F6]: Moves to the previous pane in a split worksheet.
• [Ctrl][Shift][F6]: Moves back to the last workbook window.
• [F3]: Opens a dialog box to paste a Named Range into a formula.
• [Ctrl][Shift][F3]: Automatically creates Named Ranges from the headers for the selected table of data with row or column hearders.
• [Shift][F3]: Inserts an equal sign (=) and opens the Paste Function dialog box to help you pick a formula.
• [Alt][=]: Inserts the AutoSum function.
• [Ctrl][;]: Inserts the current date.
• [Ctrl][~]: Shows formulas for cells.

[ Return to Top ]

Office 12 News

The next version of Microsoft Office (Office 12) has quite a few changes. In fact, it's the most radical change in 20 years. Early reviews of the "Beta" are generally positive. Below are some of the changes.

[ Return to Top ]

Recommended Books:

Excel Hacks: 100 Industrial-Strength Tips and Tools

This is a small paperback that has a great deal of useful tips for the Excel Power User and includes many VBA code snippets as well.

[ Return to Top ]

Usefull VBA Code Snippets

The resource links at the left provide many free (and for a small fee) code samplings to assist you with your programming needs. Below are just a few:

'The FileExists Function

Private Function FileExists(fname) As Boolean
'   Returns TRUE if the file exists
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True Else FileExists = False
End Function

'--------------------------------------------------------------------------------

'The FileNameOnly Function

Private Function FileNameOnly(pname) As String
'   Returns the filename from a path/filename string
    Dim i As Integer, length As Integer, temp As String
    length = Len(pname)
    temp = ""
    For i = length To 1 Step -1
        If Mid(pname, i, 1) = Application.PathSeparator Then
            FileNameOnly = temp
            Exit Function
        End If
        temp = Mid(pname, i, 1) & temp
    Next i
    FileNameOnly = pname
End Function

'--------------------------------------------------------------------------------

'The PathExists Function

Private Function PathExists(pname) As Boolean
'   Returns TRUE if the path exists
    Dim x As String
    On Error Resume Next
    x = GetAttr(pname) And 0
    If Err = 0 Then PathExists = True Else PathExists = False
End Function

'--------------------------------------------------------------------------------

'The RangeNameExists Function

Private Function RangeNameExists(nname) As Boolean
'   Returns TRUE if the range name exists
    Dim n As Name
    RangeNameExists = False
    For Each n In ActiveWorkbook.Names
        If UCase(n.Name) = UCase(nname) Then
            RangeNameExists = True
            Exit Function
        End If
    Next n
End Function

'--------------------------------------------------------------------------------

'The SheetExists Function

Private Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

'--------------------------------------------------------------------------------

'The WorkbookIsOpen Function

Private Function WorkbookIsOpen(wbname) As Boolean
'   Returns TRUE if the workbook is open
    Dim x As Workbook
    On Error Resume Next
    Set x = Workbooks(wbname)
    If Err = 0 Then WorkbookIsOpen = True Else WorkbookIsOpen = False
End Function

[ Return to Top ]