Archive for the ‘Excel Hints’ Category

Removing #N/A error in Excel using ISNA()

Posted on February 17th, 2009 by Joseph Harris

The #N/A error happens most often when a lookup function cannot find a result. You can manually remove the all of your #N/As or you can stop them in your original formula. A simple and effective way of trapping the #N/A errors and replacing them with whatever you would like is to use the ISNA() [...]

Automatic Updating “Dynamic” Charts in Excel

Posted on February 16th, 2009 by Joseph Harris

One of the most time consuming parts of producing a scheduled report is updating all of the charts in the report. Many times, an analyst only wants to show the last 7 days or the last 6 months of data and not the entire data set. To achieve this, the analyst changes the the Y [...]

Vlookup Multiple Criteria in Excel

Posted on February 9th, 2009 by Joseph Harris

The standard vlookup function allows for one condition to be looked up. For example, if I have the data in rows 1 through 7 below, and I want to determine the favorite color of Dan, who was born in Jan and is 16 years old, the standard vlookup function will not work. The standard vlookup [...]

Conditional Formatting with Multiple Criteria in Excel

Posted on February 3rd, 2009 by Joseph Harris

Microsoft Excel has a limit of 3 criteria in Conditional Formatting. We can get around this with a bit of VBA code… Private Sub Worksheet_Change(ByVal Target As Range) Dim intclr As Integer If Not Intersect(Target, Range(“A1:z100″)) Is Nothing Then Select Case Target Case “ted” intclr = 6 Case 6 intclr = 12 Case 11 To [...]

ColorIndex Reference – Excel VBA

Posted on February 3rd, 2009 by Joseph Harris

A chart that can be used to look up the colorindex integer to see the corresponding index color [...]

Images and Pictures in Charts – Excel Graphs

Posted on January 5th, 2009 by Joseph Harris

Quick an easy method of making your Excel graphs more appealing, by adding pictures.