Keywords:confidence intervals, confidence levels, macro, function, excel, how to
Version:Excel 97 or later
Download:
In excel there are no native function for calculating confidence interval (CI) a function called CONFIDENCE to calculate the confidence interval, but it works slightly different from the custom confidence function I present here.
Since I need to do this calculation on a regular basis i decided to simplify this work by writing a VB function that will automatically calculate the confidence interval in Excel for a given range of values and confidence level (CL) between 1-99%. It will also present the output in a format that is suitable to copy and paste directly into reports and result sheets. There are many excellent sites that cover the theory behind CI, so I won’t repeat it here.
One of the drawbacks in Excel is the very limited built-in color palette. When doing presentations and reports I often wish to use colors different than the standard offer to create visually more appealing graphs that blend in with the rest of the lay out.
The good news is that it is possible to change the standard palette to user defined colors, but doing so either involves VB scripting and can seem quite distracting to the average user, or tedious clicking as you change colours one-by-one via the standard Excel menues. Running the following command in the VB editor would chance the color of the first swatch to red:
Due to the popularity of the Box-plot and whiskers chart in Excel article published some time ago I decided to revise the article with a new, improved way of creating box-plot charts containing more information. The approac is slightly different as you will see in the following step by step instruction.
Keywords: in-cell graph, in-cell chart, text chart graph
In-cell graphs are built in Excel 2007, but these are notorously hard to read and actually does more to confuse the reciever than anything else. There are an alternative option which is very simple to use. Using the REPT function in excel repeats a symbol or string x times. The syntax is:
=REPT($string, repetions)
Now assume we have a column (A) of data, adding =REPT(”|”;A2/5) in the adjecent column (B) would yield something similar to the image below, after som simple formatting.
Keywords: Excel, data points, custom images, change default
It is surprisingly easy to change the somewhat boring, default data points in excel to something more of your own taste and imagination. Here’s how to do it:
1. Create some random values and a default line graph.
Keywords: Excel, box-plot,box plot, median, whiskers, percentile chart. 
