• Site Map
  • About Us
  • Contact Us
  • Support
  • Product Feedback
  • Product Up-Date Request
  • Search
  • IPS
    Home Strategic Planning Models for Utilities Business & Financial Planning Software Real Estate Investment Software (Model) Personal Financial Planning Software Consulting Services Association Services


    RECOMMENDED BOOKS:


    KEYBOARD
    SHORT-CUTS:

    This site contains keyboard short-cuts for most applications and operating systems.

    VBA Programmers:

    The resource links below provide all kinds of useful tips and free code. And for a modest sum, they provide a substantial amount of code that will save you a great deal of time.


    OTHER EXCEL LINKS:

    corner
    Tips for Increasing Your Excel Productivity
    There are a few tips that can increase your productivity when using Microsoft Excel™. Below are a few that you may find useful.
    If you have any Excel tips that you would like to suggest, contact us or email us at info@IpsPlanningSolutions.com.

    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 ]


    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
    
    

    Source Code: A Few Useful Procedures (these taken from JWalk & Assoc.)

    [ Return to Top ]

    Terms of Use    Privacy Policy
    Copyright © 2000- Integrated Planning Systems, Inc.