January 1, 2017

Chart Builder, ggplot2 in Excel

Facebooktwittergoogle_plusredditlinkedin

The Chart builder tool allows users to construct charts using the R package ggplot2 in Excel without having to write any code. Charts are built up in layers each with a specific type of chart.

The current version only offers a basic set of ggplot2 options but still enough to make it a useful tool for producing charts. Future version of Sharp-R will incorporate more features of ggplot2.

Building Charts

To get ggplot2 charts in Excel open the interface simply by selecting the data you wish to plot and click the ‘Chart Builder’ button on the Sharp R ribbon. The selected data must be in data frame form. Data frame form means that the data is in a block with each column containing the same number of rows of values. Values can be either numbers or text. The first row of data will be used a labels.

The selected data is then sent to R and the interface will open. If at any point you wish to change the data in the interface then select another suitable data set and click ‘Refresh Data’. The chart interface will be cleared and the new data loaded ready for plotting.

Charts are built up in layers and the interface allows different layers to be selected and displayed. Right clicking a layer in the list allows it to be added to the chart. This new layer is then displayed in the ‘Active Layers’ list. Each active layer also has a check box which can be used to toggle if the layer is shown in the chart or not. Except for the Facet and Title, it is possible to have multiple layers of the same type.

Selecting a layer from the ‘Active Layers’ list brings up the layer controls allowing data to be selected and changes made to the charts layers appearance. As data and options are selected the displayed chart will update to represent the changes.

As with using ggplot2 through the command line it is possible to create charts that are impossible to plot. If this is the case then the chart display will be blank.

Unlike other charting systems ggplot2 can do much of the aggregation and summary of data for you saving the time of having to keep manipulating your data to get it in the right format. So you can generate a scatter plot of 2 variables and then simple assign a third to the colour option and you have scatter plot with points coloured according to the third variable. This can also be applied to the shape and size of the points. Most other types of chart options also allow this type of operation.

It is important to note the differences in types of data. Data for ggplot2 can be values or text or a mixture of both. Columns of Excel data that do contain a mixture of values and text will be converted to text on import to R.

To get your chart from ggplot2 in Excel simple right click the chart to get is pasted into the worksheet as a high quality png image.

Factors

Values can either be continuous, i.e. containing any value, or discrete meaning that they contain a restricted number of values say using integers 1 to 5 to indicate the group the data belongs to. The difference between these discrete and continuous variables can be important when using ggplot2.

The ggplot2 interface as with R can treat variables that are discrete as factors, i.e. contain a certain number of discrete levels. This term factor if often displayed on the chart interface as an option, and allows ggplot2 to treat a variable as either depending on your requirements. So a scatter plot using the standard option, results in a continuous colour scale,

ggplot2 scatter plot continous scale

and now using factor, forces it to be treated as a factor with a discrete colour scale.

ggplot scatter plot discrete scale

Bar

Allows data to be displayed as bars or columns.

  • X Variable: The variable to display on the x axis.
  • Y Variable: Should be a continuous or numeric variable. Selecting one defaults to the show the sum of the variable for all X values.
  • Colour: Allows a variable to be used to determine the colour of the bars. Can also be set to specific value using the button on the right hand side.
  • Dodge: If the fill is set to a variable then the bars will be plot alongside each other as opposed to being stacked.
  • Flip: Toggles the orientation of the x axis so can show bar horizontally or vertically. Effects all layers.
  • Stat: Instead of each bar just representing the count other statistical values can be selected. If a Y variable is selected no bar will be shown unless a option other than ‘count’ is selected.
  • Alpha: Adjusts the transparency of the layer.

Box

Produces a Box plot giving an idea of how the data is distributed.

  • X Variable: The variable to display on the x axis, the variable that is used to determine the grouping.
  • Y Variable: The variable to display on the y axis, the variable that is summarised by the box.
  • Colour: Sets the variable or specific colours to be used.
  • Bin Width: Used to set the width of the bins. Only applies if the x data is a continuous variable and the factor option is left unchecked. If left at zero then default is applied.
  • Flip: Flips the x axis so allows vertical or horizontal boxes. Applies to all layers.
  • Outlier Size: Adjusts the size of the outliers. If zero then default size used.
  • Alpha: Adjusts the alpha level of the plot.
  • Notch: Toggles the display of notched box plots.
  • Width: Sets the width of the notch if displayed. If set to zero then default width is used.

ggplot2 box plot in excel

Facet

Facet produces multiple charts which are used to split different groups of data. There is only one facet setting for each chart.

  • Horizontal: The variable to use to determine the plots horizontal separation.
  • Vertical: The variable to use to determine the plots vertical separation.
  • Free Space: By default sub plots are kept the same size, selecting this option allows automatic adjustment of the size of the plots to suit the data shown.
  • Free Scale: By default the scale of each sub plot is the same, selecting this option results in the scales on each of the sub plots being optimised for the data shown.

ggplot2 facte in excel

Histogram

Produces a histogram.

  • X Variable: The variable to show the histogram for.
  • Colour: If a variable is selected then it will be used to group the data into separate histograms. Alternatively a specific colour can be selected.
  • Bin Width: Used to adjust the width of the bins used for calculating the histogram. The value refers to the x axis scale.
  • Density: If selected then the y axis shows the density instead of the count.
  • Alpha: Adjusts the transparency of the layer.

ggplot2 histogram in excel

Line

Allows data to be represented as lines.

  • X Variable: The variable to use for the x axis.
  • Colour: Set the line colour to a specific value or use a variable to determine the line colour.
  • Size: The thickness of the line.
  • Y Variable: The variable to display on the y axis.
  • Line: Used to adjust the type of line displayed, solid, dot, etc.. There are 6 types altogether.
  • Alpha: Adjusts the transparency of the layer.

ggplot2 line in excel

Point

A scatter plot showing a point for each data point.

  • X Variable: The variable to show on the x axis.
  • Colour: Assign a variable to use for the colour of the points, or set a specific value.
  • Shape: Assign a variable to use for the shape of the points. There are currently 20 defined shapes.
  • Y Variable: The variable to show on the y axis.
  • Size: Set the size of the points to a variable or a specific value.
  • Alpha: Set the transparency of the layer.

ggplot2 point in excel

Title

Allows the text for the main chart title to be edited as well as for the X and Y axis. There is only one Title setting for each chart.

  • Main Title: The text to display in the main title at the top of the chart.
  • X Axis Title: The title on the X Axis.
  • Y Axis Title: The title of the Y axis.
  • Vertical Labels: Toggles the orientation of the axis labels.

 

ggplot title in excel

 

Theme Editor

All the ggplot2 images generated by the interface apply a theme which is defined by Sharp-R. This theme can be adjusted as required. The small icon in the bottom right hand corner of the ‘controls’ group on the ribbon is used to access the theme editor.

ggplot theme editor

  • Changes to the settings will be used to update the sample chart so their effect can be seen.
  • The Font size sets the size of the text for all the chart elements. The size option for all of the other text elements is a relative measure from this.
  • The Image settings define the size in centimetres and resolution of chart images produced by the chart builder. These settings also determine the charts images produced by the functions.
  • The theme is defined in the R environment when the Add In is active as ‘theme_sharp()’, so can be applied to any ggplot2 charts defined in the functions.
  • Clicking ‘Default’ will put all the options back to the original default values.
Facebooktwittergoogle_plusredditlinkedin