Open the spreadsheet page titled Basic Graphs. The tabs along the lower edge of the screen are:

  • Basic Statistics
  • Pareto
  • Time Series
  • Pie
  • Bar
  • Scatter
  • Example Data (for in-class activity only)

 

Basic Statistics

The Data Entry Column is where data can be entered, either manually or copy and pasted from other spreadsheets.

Figure 1 – Basic Statistics

 

Pareto

The preformatted Pareto chart dispenses the need for the user to have to create pivot tables or cumulative percentage formulae as these are pre-written. Instead, all that is required is to enter the details in column format and select the options at the top of the page.

Figure 2 – Pareto Chart

At the top left, the “radio button” requires selection of the data type that is being used, Attribute or Variable.

Below this, the yellow boxes indicate information that is required for the chart to work. “No, of Unique Categories” is optional. When left blank, this will display all categories, however when a value is entered here, it will override the number of categoriees displayed to what you have specified. This may be useful in large data sets.

For example: the cell is set to 5 (as in the example above) yet there are actually 7 categories in the data. The chart will then only display 5 bars, and these will be the 5 highest categories in the data.

*Care Point* if there is a spelling error in the data, excel will see this as another category and so could cause an error as described above. However, Excel is NOT case sensitive, so upper and lower case letters can be used without affecting the data.

The “What is the pareto measuring?” is what will be used as the title for the Pareto chart itself, such as defects, product type, colour, classification etc. You can manually override this.

The data columns are Category and Frequency. This could be simple totals for each category, or multiple entries, as per the example, where each row of data could represent the total per day, per hour etc, and Excel will then total these automatically.

Data can be entered either manually or copy/pasted in from another source. The chart will automatically update each time data is entered in to any of the cells.

 

Time Series Plot

The Time Series Plot is one of the simplest charts available and consists of 2 input columns; A time or date and a count for the data.

Figure 3 – Time Series Plot

The yellow cell indicates mandatory information, in this case “What is the plot measuring?” – this will be used as the title for the chart.

The Date/Time column is simply an entry either in standard date format (dd/mm/yyyy) or time (hh:mm or hh:mm:ss as required) – this format needs to be formatted correctly for excel to interpret.

The chart will automatically update whenever any new data is added or amended in the columns.

 

Box Plots

IMPORTANT NOTE: Boxplot functionality will not work in older (<2016) versions of excel.

The box plot facility will create a standard set of boxplots for multiple data sub-sets, for example, performance of different machines, different staff groups or individuals.

The yellow highlighted cell simply requires the name that will be used for the chart.

Figure 4 – Box Plots

Unlike the Pareto chart, it is not necessary to input the number of categories for the data, this will be calculated automatically by the system. The data is arranged in the columns as Category (such as name, machine ID, product name etc) and then the count of that data per entry, which could be per day, per shift etc.

It is not necessary to calculate the grand totals.

The data is presented graphically without additional labels. However, in order to obtain the exact figure for 1st or 3rd Quartiles, max, mean and min data, simply hover the mouse over the appropriate line on the chart and a “pop up” label will appear with the data in it.

 

Pie Chart

The pie chart facility arranges data in to a standard Pie Format, regardless of the number of data categories used. However, it is advised that for a large number of categories, consideration should be given to using an alternative chart, such as a Pareto.

Figure 5 – Pie Chart

The yellow highlighted cells at the top of the page are mandatory information required to make the chart facility work correctly. The highlighted cell is for the chart title.

As with other charts in this package, it is only necessary to list the individual data points (day, per shift etc) not the grand totals. Excel will calculate the overall totals and calculate the percentages for each category. These are then listed in each segment of the pie.

If desired, it is possible to re-colour the segments of pie, although it is recommended that the default format is maintained. If a change is required, simply right-click on the desired segment, and select the “Fill” option. Changing the segment colour will automatically change the corresponding colour in the key to the right.

Bar Chart

The bar chart facility is similar to the Time Series Plot and utilises the same type of input columns.

Figure 6 – Bar Chart

The yellow highlighted cell is for mandatory required information and will be used to provide the title for the chart.

The data in the columns is simply the date/time in standard format (dd/mm/yyyy or hh:mm) and the Total column is the count for the corresponding date/time entry.

This data can be manually entered or copy/pasted from another source as required.

Scatter Plot

The scatter plot facility is used to show the existence of a relationship or correlation between 2 data variables.

Figure 7 – Scatter Plot

The yellow highlighted cells at the top left side are mandatory entries to title the 2 axes of the chart. Variable 1 is the entity that is being influenced (or not) – in this case, “Defects” and the Variable 2 is the entity that may (or may not) be influencing the changes in Variable 1, in this case “Speed”. The data is simply entered in the columns, which will automatically be re-named based on the names that are entered in the title cells above. The chart will be automatically calculated as data is entered and will create a line of best fit. If a strong correlation exists between the 2 variables, this line will be at 45 degrees.

Advice on how to interpret this chart will be provided in formal training. At this time, the chart will not provide the “R-Squared” value as standard. However, to obtain this, simply right-click on the best fit line, and select “Format Trendline” – this will open an options box. Scroll to the bottom of this options box and select the “Display R-Squared Value on Chart”

*Care Point* – It is recommended that none of the other trendline options are adjusted at this stage as it will dramatically change the output of the chart itself.

If selected, the R-Squared value will be displayed on the best fit line itself, but is presented in decimal format, not percentage.

Example Data

The Example data tab is an activity based tab your trainer will take you through during your course. This data will be used to complete various basic graphs to become accustomed to some basic graphical analysis and using CESP.