How to round values half way between two boundaries is apparently a topic of contention in some sectors of the statistical world. This tip is the second part of a continuing discussion on this issue. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.).)

Read this ExcelTip: Rounding Religious Wars, Take Two
Microsoft Excel Tips - Learn Excel Faster!

In this post, I describe change points and show how to add change points to a trend chart with R. The chart shows El Nino - La Nina events and long term global temperature anomaly trends that I originally developed for the Excel Chart Doctor page. Readers can compare my R and Excel - VBA solutions for the same chart to compare R and Excel VBA charting programming.

The radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, here's how to go about it. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.).)

Read this ExcelTip: Inserting a Radical Symbol
Microsoft Excel Tips - Learn Excel Faster!

The Conditional Formatting capabilities of Excel are very powerful. Getting them to behave exactly as you expect can be a bit tricky, however. This tip describes one common pitfall when setting conditions, and it explains how to get around it. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.).)

Read this ExcelTip: Conditionally Formatting for Multiple Date Comparisons
Microsoft Excel Tips - Learn Excel Faster!

Excel 97 has a bug that causes the Worksheet_Calculate event not to trigger when someone picks a value from a data validation drop-down list. This tip discusses the problem, along with some ways you can compensate for it. ((This tip works with Microsoft Excel 97.).)

Read this ExcelTip: Triggering a Macro for Drop-Down List Changes
Microsoft Excel Tips - Learn Excel Faster!

Do you want to know how many blank cells are within a range? You can use the COUNTBLANK function, described in this tip, to get the answer. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.).)

Read this ExcelTip: Counting the Number of Blank Cells
Microsoft Excel Tips - Learn Excel Faster!

The following code might be useful. You can use it to insert rows, lines or pagebreaks where values change in a column by changing one line of code.

Sub InsertRowsAtColumnDifferences()
    Dim lStartRow As Long
    Dim lLastRow As Long
    Dim lCounter As Long

    On Error Resume Next

    If ActiveWorkbook Is Nothing Then Exit Sub

    If TypeName(Selection) <> "Range" Then Exit Sub

    With Selection

        lStartRow = .Row

        lLastRow = lStartRow + .Columns(1).Cells.Count - 1

        For lCounter = lLastRow To lStartRow Step -1

            If Cells(lCounter, .Column).Value <> Cells(lCounter, .Column).Offset(1).Value Then

                If Cells(lCounter, .Column).Value <> "" And Cells(lCounter, .Column).Offset(1).Value <> "" Then

                    Cells(lCounter, .Column).Offset(1).EntireRow.Insert 'insert rows
                    
                    '.Rows(lCounter - lStartRow + 1).Borders(xlEdgeBottom).LineStyle = xlContinuous 'add lines
                    
                    'ActiveSheet.HPageBreaks.Add Before:=Cells(lCounter, .Column).Offset(1) 'add pagebreaks

                End If

            End If

        Next lCounter

    End With

    On Error GoTo 0
End Sub
I will be adding and other code this to my utilities in the near future. But I would like to hear from your ideas too.

What new tools would you like to see? Are there any repetitous or tiresome tasks you perform that could be made eaiser? (Housework and taking out the garbage not included!)

Send me an email (look on the left of this blog for the address). If you can suggest something that might be useful for a large number of Excel users, I want to hear from you :-)

The Zoom tool allows you to magnify what you are viewing on the screen. It can be a real boon for old eyes or detailed information. This tip explains the ways you can control the magnification applied to your worksheets. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.).)

Read this ExcelTip: Zooming In On Your Worksheet
Microsoft Excel Tips - Learn Excel Faster!

Adding text boxes to worksheets is a snap. At some point you may want to make sure that the textbox you are adding is exactly the same size as the cell (or cells) that it covers. This tip explains how to make this happen. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.).)

Read this ExcelTip: Sizing Text Boxes and Cells the Same
Microsoft Excel Tips - Learn Excel Faster!

I need a quick way to create a filter by month on an autofiltered range. Right now I'm manually entering this:

That's tedious. One option is to create another column with just the month and year in it and filter on that, but I don't like my data cluttered. So I wrote this macro that creates the filter. It filters the list to one month based on the date in the activecell.

Sub FilterOnMonth()
   
    'Create a filter in the active column within a sheet's autofilter
    'Use the month of the activecell to filter the range
   
    Dim lMonth As Long
    Dim lYear As Long
   
    If IsDate(ActiveCell.Value) Then
        lMonth = Month(ActiveCell.Value)
        lYear = Year(ActiveCell.Value)
       
            'Check if there is an autofilter
            If ActiveCell.Parent.AutoFilterMode Then
           
                'Make sure activecell is within autofilter range
                If Not Intersect(ActiveCell, _
                    ActiveCell.Parent.AutoFilter.Range) Is Nothing Then
 
                    'Create filter
                    With ActiveCell.Parent.AutoFilter
                        .Range.AutoFilter ActiveCell.Column - .Range(1).Column + 1, _
                            ">=" & DateSerial(lYear, lMonth, 1), _
                            xlAnd, _
                            "<=" & DateSerial(lYear, lMonth + 1, 0)
                    End With
                End If
            End If
    End If
   
End Sub

Function keys are used to perform common tasks in Excel. If you want to disable one of the function keys, it's rather easy to do. Here's how to do it. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.).)

Read this ExcelTip: Disabling a Function Key
Microsoft Excel Tips - Learn Excel Faster!

Over on Brian Jones' blog there are two recent articles that may be of interest to our readers here.  They deal with using the Open XML SDK to programmatically interact with Excel's XML-based file format.  One deals with "document assembly", which is essentially building a file mostly from scratch based on other data that is fed from some other system (e.g. a database).  The other deals with using the SDK to read data from an Excel file.  If these scenarios interest you, have a look see:

Document Assembly Solution for SpreadsheetML

Reading Data from SpreadsheetML

 

[enlarge]

As mentioned earlier that there are many excel spreadsheet regarding retirement planner, this is another model of excel spreadsheet designed to accommodate financial aspects of retirement.

Author : Maya Herrera - Solutions-phils.com

(more…)

Excel 2002 and 2003 include a Task pane that provides quick access to common tasks. The Task pane is normally visible when you first start Excel. If it isn't, here's the reason why and what you can do about it. ((This tip works with Microsoft Excel 2002, and Excel 2003.).)

Read this ExcelTip: Task Pane Doesn't Appear Properly
Microsoft Excel Tips - Learn Excel Faster!

Want to join snippets of text together in a cell? You can do this using the CONCATENATE worksheet function, as described in this tip. You can also use the ampersand operator, which is a shorthand method of using the worksheet function. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.).)

Read this ExcelTip: Using the CONCATENATE Worksheet Function
Microsoft Excel Tips - Learn Excel Faster!

[enlarge]

This is just a simple excel spreadsheet of personal budget plan to be used as your personal expenses reference.

Author : Gilbert L. Fries

(more…)

[enlarge]

A financial plan is your roadmap to your future. A roadmap that’s detailed and accurate can help you get there with less trouble and less expense. A roadmap that’s vague or inaccurate can lead you down rough roads and into dead ends.

That’s the words the author try to tell you. Basically he try to direct you to reach your financial dreams by making you discipline with your financial budget. In this excel spreadsheet, all you have to do just filling the information needed step by step, starting from setting up your goals.

Author : Bill - Themoneybelt.ca

(more…)

[enlarge]

This is a complete personal financial plan spreadsheet which cover many aspects about financial issues around us. Instead of just giving a single spreadsheet, the author separate all financial aspects into 60 excel worksheets. Some of them are how to manage your investments, debts, taxes and insurances. You can select and work with the sheet that suitable with your personal financial issues and ignore other sheets.

Author : University of Utah

(more…)

When creating conditional formats, you are not limited to only one condition. You can create up to three conditions, all for the same cell or range of cells. ((This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.).)

Read this ExcelTip: Using Multiple Test Conditions
Microsoft Excel Tips - Learn Excel Faster!

[enlarge]

This another comprehensive spreadsheet on retirement planning issue. In order to use this spreadsheet, you have to get some help from your financial consultant or try to learn about basic financial.

Author : Paula Speranza-Hadley

(more…)