It's good practice to add the proper titles, headings, add line numbers to reports. The addition of line numbers to Excel based reports sounds easy . . after all it's just 1, 2, 3, 4, etc. However, in practice and with reports that include thousands of lines and/or modified by multiple people, the numbers often get out-of-sync.

Over time I've found that the following process works well for insuring that the line numbers are always in order. First make sure that any Excel based report has at least two empty columns. The first column will contain the line numbers and the second column will contain some type of identifier for what line is to be numbered.

To explain, start with the second column first. Any line that needs a number gets ")" added to the second column. Next, a macro like the one below below can be used to add the line numbers to the first column:

Sub AddLineNumbers()

    Application.ScreenUpdating = False

    Dim Constants As Range
    Dim Rng As Range
    Dim Cnt As Integer

    Cnt = 1

    Set Constants = Range("C7:C40").SpecialCells(xlCellTypeConstants, 2)

    For Each Rng In Constants
        If Rng.Value = ")" Then
            Rng.Offset(0, -1).Value = Cnt
            Cnt = Cnt + 1
        End If
    Next Rng

End Sub

In the financial statement below, line numbers have been added column "B".

Going through the process above, the two empty columns originally were columns "B" and "C". A ")" was added to each line within column "C" that needed a line number. The macro uses the SpecialCells method to identify any constants in column "C". Once the constants are identified, it loops through each constant and places a line number to the left of any constant that is a ")".

The VBA SpecialCells method combined with background colors can be a simple and effective method to perform high-level worksheet audits. The process works by using a macro to apply colors to individual cells flagged as per the SpecialCells criteria.

For example, in Excel 2003 when you go to Edit -> Go To... -> Special, the following dialog box appears:

Options are available that allow you to select cells with constants, formulas, blanks, etc.

The same can be done with VBA and the SpecialCells method. The advantage of using VBA is that you can apply background colors to make the cells quickly visible. Additionally, there are even more options available via VBA than what you see in the dialog box.

To explain, you're boss asks you to audit the accuracy of a simple financial statement. To do so, start by making sure that formulas appear where they logically should. A simple VBA procedure using the SpecialCells method as presented below can be used to color all of the cells with formulas in the desired range yellow (yellow is tied to the index value number "6" below - this may be different on your machine.)

Sub Flag_Formulas()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 6
End Sub

After running the macro, the financial statement looks like this:

A quick look at the statement reveals that something is wrong in cell H19. Instead of seeing a cell with a formula it looks like somebody hard-coded a number.

This same process can be applied to other types of cells. The macro below can be used to flag hard-codes numbers:

Sub Flag_Hard_Coded_Numbers()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 1).Interior.ColorIndex = 6
End Sub

This macro flags text entries:

Sub Flag_Text()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 2).Interior.ColorIndex = 6
End Sub

For colors, if you want to specify an RGB number rather than an index number you can use a statement like:

Rng.SpecialCells(xlCellTypeConstants, 2).Interior.Color = RGB(255, 255, 204)

Finally, if you look up "SpecialCells" in Excel's VBA help there are even more options available. You can flag cells with comments, validation, same formatting, and even more.

In our accounting shop, we build our budgets in Excel but submit them in another software called Hyperion. Hyperion requires us to submit in whole numbers. To do so, we need to round every formula in the final Hyperion load so that the budget foots and crossfoots.

Before submission, I'll use the following macro to wrap the ROUND function around every formula:

Sub Add_Rounding()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
            Rng.Formula = "=round(" & cellFormula & ",0)"
        End If
    
    Next Rng

End Sub

The macro works by:

• Looping through each cell in the range.
• Uses the Special Cells method to identify only those cells with formulas.
• Sets up a string called "cellFormula" which is a picture of the formula.
• Uses the InStr function to look at the cellFormula string and identify if it already has the ROUND function included. If so, the macro goes on to the next cell. If not, it adds the ROUND function to the formula.

You can use the same type of logic to apply the ISERROR function to a range of formulas:

Sub Add_ErrorTrap()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Error")) = 0 Then
            Rng.Formula = "=if(Iserror(" & cellFormula & "),0," & cellFormula & ")"
        End If
    
    Next Rng

End Sub

Until several weeks ago I had not used an RSS reader to access the sites that I like to visit. I'm not sure why other than it seems like the authors of those sites have only in the last year or two added RSS feeds or started their own blogs. As a result, until recently I had not even taken the time to view my own site in an RSS aggregator. I just assumed that the content had to look OK because I used the standard RSS template that ExpressionEngine provides.

Recently I viewed the feed from this site and each post looked like one large block of text as opposed to showing the proper line breaks between paragraphs, code examples, etc. Upon investigation I viewed the feed using Bloglines and Google Reader. Both aggregators confirmed that the feed was coming across in this manner.

ExpressionEngine provides very good support and I was able to quickly find a solution in their Knowledge Base. I changed my template, logged back into Bloglines and Google Reader, and . . . everything looked the same.

To make a long and frustrating story short, I found out the following about RSS and aggregators in general:

• As I made changes to my RSS template I thought those changes would be reflected in the aggregators immediately. However, RSS readers update only periodically and when they do they cache their results. Changes made to an RSS template may not show up in an aggregator for several hours. Furthermore, they may never show up depending on what aggregator you are using.
• Some aggregators, as the ExpressionEngine Knowledge Base article alluded to, do not show HTML as expected. Bloglines appears to be one of those aggregators. For that reason and although I don't really care for the interface, I've moved to Google Reader as my aggregator of choice.

In summary, I find it hard to believe that given the popularity of Bloglines it does not show whitespace as expected. And not understanding how aggregators cache their results resulted in several hours over several days of research and frustration on my part.

Depending on the version of Excel that you are using, there are two free addins available that allow you to tie the series labels in a chart to worksheet cells. Those addins are:

• The XY Chart Labeler available from Rob Bovey's Applications Professionals site.
• The J-Walk Chart Tools available from John Walkenbach's Spreadsheet Page.

A project that I worked on recently required that I tie the series data labels to one of many range references based on an input by the user. To do so, it was required that I build the same type of functionality that the addins provide directly into my project.

In a post to the Microsoft Excel Programming Newsgroup dating back to July of 1999, John Green describes how to create labels in a chart from the text in a range using VBA. The code originates from the book titled "Excel 2000 VBA Programmer's Reference" written by John Green, Stephen Bullen, and Felipe Martins.

As taken from the post, the code to tie the series labels to a worksheet range looks like this:

  Sub AddDataLabels() 
  Dim SalesSeries As Series 
  Dim pts As Points 
  Dim pt As Point 
  Dim rng As Range 
  Dim i As Integer 

  Set rng = Range("B4:G4") 
  Set SalesSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) 
  SalesSeries.HasDataLabels = True 
  Set pts = SalesSeries.Points 
  For Each pt In pts 
    i = i + 1 
    pt.DataLabel.Text = "=" & rng.Cells(i).Address _ 
             (RowAbsolute:=True, _ 
              ColumnAbsolute:=True, _ 
              ReferenceStyle:=xlR1C1, _ 
              External:=True) 
   Next pt 
End Sub

After finding the example above, I wanted to see if the code could be streamlined in any way. I first looked up the "Address" property as applied to the range object in Excel's VBA help and found out that the default values of the row and column references are "true". As a result, the Address property could be shortened and rewritten as:

rng.Cells(i).Address(, , xlR1C1, True) 

I remembered that in a prior post titled Declaring Line Chart Variables, Jon Peltier suggested a best-practice for declaring variables. Following those suggestions, I renamed the series variable "SalesSeries" to "Srs". I also eliminated the point variable and used a reference to Srs.Points.Count in its place. With those changes the loop could be rewritten as follows:

For i = 1 To Srs.Points.Count
    Srs.Points(i).DataLabel.Text = "=" & Rng.Cells(i).Address(, , xlR1C1, True)
Next i

Finally, by making the changes above I was able to simplify the code to look like this:

Sub AddDataLabels()
    Dim Srs As Series
    Dim Rng As Range
    Dim i As Integer
    Set Rng = Range("B4:G4")
    Set Srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    Srs.HasDataLabels = True
    For i = 1 To Srs.Points.Count
        Srs.Points(i).DataLabel.Text = "=" & Rng.Cells(i).Address(, , xlR1C1, True)
    Next i
End Sub

In a recent post at the PTS Blog titled Trendline Fitting Errors, Jon Peltier describes some of the problems that result from fitting data to trendlines created as a part of an Excel chart. Microsoft seems to acknowledge this problem via their Knowledge Base Article 211967, although the article is somewhat confusing.

Refering to the article and under the header "Symptoms", it states "The equation displayed for a trendline on an xy (scatter) chart is incorrect." Several sentences later, the article states "The trendline formula should only be used when your chart is an XY Scatter chart."

Finally, the article does state the reason why trendlines should only be used on XY Scatter charts: "Line, Column, and Bar charts plot only the Y axis as values. The X axis is plotted only as a linear series in these chart types, regardless of what the labels actually are. Therefore, the trendline will be inaccurate if displayed on these types of charts. This behavior is by design." I wonder why Microsoft continues to offer trendlines as a component of Line, Column, and Bar charts if they are incorrect?

Below are some useful resources for creating and interpretting trendlines:

• Choosing the Right Trendline for Your Data from Microsoft.
• Trendline Analysis with Excel from Process Trends.
• Trendline Charting Formulas from The Spreadsheet Page.