...Excel add-in for quick data analysis  

 

VTools Logo

 

 

How to videos:


 
 
   


Following help is for Version 4. Scroll to the bottom for Version 3.3.3


VTools 4.0 User Guide

General Notes:

·         Anytime VTools is used to generate graphs/Stats, data used for graphs is stored in the “VTools GDS” sheet. Delete or modify this sheet with caution.

 

·         Data for these graphs is calculated (Go to Graph tips to see how). This calculated data is disconnected from the actual data. i.e. any changed you make to your data WILL NOT be reflected in graphs that are already created!

 

·         Visit the  Graph tips page to see how VTools does different graphs – in case you want to do them yourself without VTools

 

·         VTools menu is available under insert menu/ribbon of excel 2007 and up.

 

Let’s work with following dataset:

Clicking VTools button brings up the Chart Setup dialog:

Plot tab:

When you pull-up this dialog box, it scans the active sheet for data. Row #1 is treated as the header row.  All columns are scanned and classified as numerical or non-numerical columns.

In the plot tab:

1.      Optionally select columns containing categorical data (Group by). Data can be grouped by upto two variables. Item clicked first will be grouped by first followed by the item clicked next. Group by order is displayed just above the output label.

a.      Note: All columns containing data in the first row show up here.

2.      Select Value columns (columns containing data you want to plot)

a.      Note: Only columns that contain numerical data are shown

b.      Excel treats spaces as strings. This means cells that look like empty cells but have a {space} are treated as cells containing strings.  This will prevent those columns from showing up in the value list.

3.      Select plots you want to generate

4.      That it all the required information! Click OK to plot your graphs. (or go through rest of the tabs to further customize graphs)

 

Colors Tab:

In this tab you can set color used by VTools. Note: Non-registered versions of VTools neither save nor apply your custom colors.

Clicking on the color buttons brings up the “Color Picker” dialog box. Click on your desired color or click “Default” to revert back to default color.

Selected color will be saved and used from this point forward.

 

Options tab:

Check for update at Start-up: Enable this option for VTools to check if an update is available. Update check is done when the program loads, and link to download is shown when update is available. This option is enabled by default.

Share Usage Data: Enabling this will share (anonymously) usage statistics.  This will help me focus future improvements. By default, this is disabled. I would very much appreciate you enabling this option.

Show Statbox on Chart sheet:  Selected Stats are shown on the generated chart.  This Statbox can either be shown on the chart (overlay on chart) or at the bottom of the chart (show below chart)

Show count of X-axis: This adds “n= #” line on the X-Axis of box plot. (See example

Sort data by Label: Categorical data is sorted with this option. With this option disabled, categories are plotted in FIFO (first-in-first-out) method.

Basic Statistics: Select statistics you want to calculate. Optionally specify up to 4 custom percentiles to calculate.

 

Chart Sheet Menu

When VTools plot button is clicked from the chart sheet, following tab (This Chart) replaces Plot tab from the dialog box described above.

From here you can either

·         Go to the basic stats on the VTools GDS worksheet

·         Add or replace Statbox on the current chart

·         Add data labels to the box plot (Available only if the active chart is a box plot)

·         Fix Y axis (Available only of the active chart is a Normal probability  plot)

 

Sample Graphs:

Sample Graphs are based on the previously shown data snapshot.

Box plot:

 

Box Plot with boxes grouped together by supplier

 

Normal Probability Plot:

 

Cumulative Percent Plot:

 

 


3.3.3 Help/Description follows:



VTools worksheet menu

Plot Menu

<< Click on a menu item to jump to that topic.

VTools --> Plot

When you click Plot, Chart Setup dialog box appears.

Plot --> Input


Input tab


Here you see the Input tab of the Chart Setup dialog.

Default date range is the currently selected range on the active worksheet.

VTools supports four different data layouts:

Data Columns: data columns
Each column contains a separate data set.

Example: Defects by shift are logged as shown here.



Data and Code Column:

Only two columns are selected and one of them is a code/category column. Data & code

Example: Defects by shift are logged as shown here.


 

 

1st code and 2..n data columns:

Same as above except there are multiple data columns.

Example: Various defects by shift are logged as shown here.code and multiple data



 

 

 

 

1..(n-1) code columns and nth data column:
multi code and data


In this case, there are multiple code columns and a single data column.

Example:



Plot --> Output

Following shows output tab of the Chart Setup dialog.


Output tab


Most of the potions here are self-explanatory, except may be Box and Whisker.

The Box of the Box-Plot represents 25th, 50th, and 75th percentiles.

  • Upper Whisker: = Min (Max(data), 1.5 * IQR)
  • Lower Whisker: = Max (Min(data), 1.5 * IQR)

IQR := InterQuartile Range (75% - 25%)


On the Box & Whisker Graph (Box Plot) asterisk indicates Average


Plot --> Options

Following shows Options tab of the Chart Setup dialog.

Options

StatBox adds a textbox on the chart and displays selected statistics.

  • n: Total number of data points
  • xx%: xxth percentile value
  • IQR: 75th percentile – 25th percentile
  • P (Anova/t-Test): result of Anova or t-test (two tailed assuming equal variance)
  • P (Levene/F-Test): result of Levene’s Test or F-test (two tailed)
  • Note: VTools uses modified Levene’s test (using medians)
  • P (Kruskal-Wallis): result of the Kruskal-Wallis test

Outlier elimination: The drop down list lists two methods for outlier elimination. The outlier eliminated data is used ONLY for P-Value calculation.

  • 3 Sigma: Data outside of Average ± 3 Sigma is eliminated.
  • 1.5 IQR: Data greater than 75th % + 1.5 (75th % - 25th %) and less than 25th % - 1.5 (75th % - 25th %) is eliminated.

Save as Default: Current Input, Output, and Options setting are saved as default and will be loaded every time chart dialog is displayed.

 


 

VTools --> Options

Following dialog appears when you select Options from the VTools menu.

Options --> General

General Options


First three checkboxes in the General tab are self-explanatory.

Number Format: Here you specify number format used in the StatBox. Note: P-values use fixed number format of ‘0.000’

Options --> Chart


Following shows the Chart tab.

chart options


Left three text boxes specify default chart title for the respective charts. (This can be over-ridden by the value from Chart Setup dialog’s Options tab. )

Right three text boxes allow you to customize each chart using your own macro.


 

VTools --> Cleanup

VTools creates a hidden sheet for each VTools graph. After you delete one of these graphs, its associated sheet becomes useless. Use 'Cleanup' from VTools menu to delete these orphaned sheets.

VTools --> Reset UI

Use this option to reset User Interface after VTools crash (which hardly ever happens ;-) )

 



 

VTools Chart menu

Following is the menu you will see when you click VTools while on a chart sheet or if you select VTools from the right click menu on the chart

worksheet menu Click on a menu item to jump to that topic.

 

VTools --> Stat Box

Selecting StatBox, brings up the following dialog box.

statbox

 

Please see the ‘Chart Setup’ dialog explanation above for StatBox parameter help. Other buttons let you format indicated properties.


VTools --> My Style

VTools --> My Style --> Learn: Click this will save following properies of the active graph.

Fill and line properties: StatBox & Plot Area
Font properties: Chart Title, Axis Titles, Tickmark Labels, and Legend
Gridlines: Gridlines on/off and line color, weight and type are saved for the given chart type.

These properties can be applied to all new graphs by checking the "user My Style" option in the chart setup dialog box.

VTools --> My Style -->Apply: Clicking this will apply saved 'My Style' properties to the active chart.

 


 

VTools --> Data Labels

Selecting Data Labels (available only while on graph sheet with VTools Box Plot) brings up the dialog box shown here.

label datapoints

Check needed checkboxes and those values will be displayed on the graph.



VTools --> Toggle Outliers

This will toggle outlier display on the box plot. (available only while on graph sheet with VTools Box Plot)

VTools --> Fix It

This is a general purpose clean up procedure that

For Box Plot

- Fixes labels' position after changing Y axis min/max

- Matches color of the outliers with that of the associated box

For Normal Probability Plot

- Fixes labels' position & grid after changing X axis min/max

 




 

Tips:

  • Remember: You can select data before opening 'Plot' dialog box of VTools.

  • Use 'Fix it' from VTools chart menu to correct various label alignment issues on charts created by VTools.

  • In excel, Dates are stored internally as Numbers; it is the cell formatting that makes it look like Date/Time.
    For this reason, using date/time in VTools produces undesirable results. You should convert your date/time column to text before using it in VTools.
    Following example shows how to convert date/time data to string in excel.
    =TEXT(B2,"mm/dd/yyyy hh:mm AM/PM")
  • VTools creates a hidden sheet for each VTools graph. After you delete one of these graphs, its associated sheet becomes useless. Use 'Cleanup' from VTools menu to delete these orphaned sheets.

  • After selecting your data range, clicking the 'VTools' chart menu while holding down the shift key will bypass the chart setup dialog box and create chart(s) using your default settings.

  • Clicking "Reset UI" fixes user interface after VTools crash.

 




Notes:

ANOVA/t-Test:

  • H0 : µ1 = µ2 = … = µn
  • Assumptions: (1) Approximately normally distributed data. (2)Equal variance.
  • Note: t-Test (two tailed) assuming equal variance will be done if n = 2.

Levene/F-Test:

  • H0: s1^2 = s2^2 = … = sn^2
  • Note: VTools uses modified Levene's test.
  • Note: F-Test (two tailed) will be done if n = 2

K-W (Kruskal-Wallis):

  • Ho: all n distribution functions are equal
  • Note: This is a nonparametric test since the data is rank transformed.

VTools News

Aug 1, 2017

VTools version 4.20 is released. Download your copy from the download page.

VTools logo

Support VTools by purchasing your copy today.

Purchase VTools

 

       

home | download | user guide | user forum | links | e-mail

Last updated: Oct 15, 2012