Extend your Excel graphics capabilities using R-Excel - no programming knowledge needed !

RExcel extends graphical capability of Excel - once you installed R-Excel and R program properly - you do necessarily need knowledge of R programming - most importantly R and Rexcel is free. Down side is that RExcel is only available for 2003, 2007 and 2010 Excel versions (download and instructions are here). Easiest way to install would be download the file RAndFriendsSetup2152V3.2-9-1 and follow the install steps.

The Excel add in RExcel.xla permits using R within excel in your figure tips (not necessarily need for programming skills). Here there are two types of servers - background (completely hidden from user and all of the interaction with R should be done within Excel) and foreground (you can access to R graphical user interface while working in R).  The backgound server should be installed with the R(D)COM server - which depends upon R package rcom, can be installed from Comprehensive R network. Installation is not that complicated, the above link provides packages that install everything needed.


Although there are different ways to use R within Excel, the most easy way is to use as worksheet function (R functions are called in worksheet). I think for the graphical purpose, this only can help alot to create nice graphics - extending RExcel capabilities.

The following steps are RExcel in Excel 2003. Once every thing set of correctly you can see RExcel in Add-Ins and a new menu appears before Help menu in the corner. Now under menu click run R.

Then you will see menu change and then click R commander with Excel menus. Now whole set of menu are activated for R use within excel. It includes Graphs too.

Select the data area and click put R dataframe, then it will prompt you to get the name of R dataset. So these cells will stay as R data set available for use in later sessions.

Here I have provided excelblogdata name to the dataset. The dataset name will be listed in Dataset drop down list.

Now let's do some plotting that we can not easily do in excel. Select XY conditioning plot from the Graphs menu in RExcel menu.

Now you can see nice trellis plot (uses R/lattice package) in R plot window.

Plotting 2 variables in Y axis.

Scatter plot matrix is useful when there are many variables. Here we have scatter plot matrix with histogram in the center.

.

Finally there are several other features useful for nice visualization in RExcel. Most importantly it takes some flavor of R to Excel. Happy plotting !

simple graphs with excel - things you know or might not know !

Excel is most frequently used for spread sheet application. When comes to graphics, it does great quality graphs however does not do what advanced user need to. However, many time we under estimate its ability and switch to less user-friendly and / or lower quality graphics software  In this post we will discuss on very simple graphics that excel can do (check if something is new for you):

(1) XY scatter plot
This is most common and informative graph in scientific world as it can compare two variable.

In scatter plot many times we want to add prediction lines with associate model. We can add more than one Y variable so that we can make comparison in trend.

(B) Bar (Row or Column Plots)
Bar plots are useful when categorical variable is plotted with a  numerical variable. Even if we want to plot numerical with numerical variable - one must be treated as categorical so that the bars are spaced as equal distance (not based on difference between them).

Sometime we might want to have some fancy looking 3D bar plots. They do not necessarily augment the plots, but looks nice for business charts .. 

(C) Line plot 

Line plot work in the same way as bar plot, however the lines are connected, useful to see a trend. In contrast XY plot where both axis are quantitative, here one variable is categorical. 

We can convert any of line series to bar chart so that we can represent one variable as bar and another as line. 

(D) Area plot:

Area plots are suitable in some cases where we are interested in looking area under the curve. We can overlay more than one series in the same plot. 

(E) Pie chart:

Pie chart  are also popular in business charts, however their suitability in science is much argued. They are useful show composition or share of total (like percent).

Some resources: 

(1) Basic idea on how to plot graph and edit its components, see this video. Here is another video tutorial that shows how to add error bars to the plot. 

(2) Video on plotting line graph, multiple lines , simple bar chart, simple pie chart, scatter plot ,  area chart, all different charts. 

(3) Video showing how we can plot multiple plot in single chart with additional axis. 

(4) Dynamic area chart 

create line plot with error bar plot in excel

XY Error bar plot:

Adding error bars to XY plot is simple task in excel. First we need to plot XY scatter plot with subtype scatter with data points connected by lines without markers.

(2) The format series and find Y error bars to be added (we can also add X error bars in the same way). You can specify different types of errors (fixed such as standard deviation or fixed value or percentage of the value) or user specified in error column (for example SE in above case).

XY plots (tricks and modifications)

XY scatter plot

XY Polt (scatter or line or combination) has both X and Y quantative values. The lines can be drawn, connected by smoothing based on data trend or directly connected as such.

We can just combine all of the different variations within single plot, just by selecting a series and change the chart type.  In the following case only Y2 is changed to smoothed line:

We can add error bars (specified from a series or fixed amount to the plot)

Connect the lines, in desirable connect.

Some tricks in Barplot

Barplot (column or row bar plots)

How to change negative number to different color or back to black number in axis:

Similarly we can create horizontal and vertical bars:

Adding lines to barplots:

If we like a to add new series and add series lines to all barplots,  the following is way to go:

However in many time we just want only or others represented as lines other as bars. Then let's do overlayed bar, select the series only and then change the plot type.

Adding error bars:
Click Format the data series where we want to add the error bar to.

With 2003 it is not possible to add separate labels such as mean comparisons  but is possible in excel 2013 it is possible to do so.

Grouped bars:

Over stacked bars, grouped bars provide side by side comparisions of the series.

We can add a number of series and then convert the series to different chart type (for example line type).

Sometime we might want to plot a constant line (such as representing a threshold), we can do so by just specifying a series with just a single value and plot this as line type. Note that the place of series affects the start and end point of the line.


 We can add the error bars to the data series we want to add to. We can specify error amount as fixed value or from a column.

A busy plot will look like the following:

Perhaps overlayed plot will be more suitable for this kind data where sum of the series is 1 (100%) and each component is composition of the whole:

Shading certain region in a XY plot

Shading certain portion in XY plot

(1)  Create a dummy data for the area range where it need to be shaded.

For shaded region chose small bin (see like 0.25, in the below case) and then in Y put alternative maximum point in Y ( here Y1 and Y2, calculated by formula: =MAX($B$2:$C$25))

(2) Create a xy smoothed line plot. Increase the line size so that they form a compact shaded area.

(3) Then we need to add the data series we want to plot. Use add data function to add two data series.

(4) Similarly we can create more than one shaded areas. 

Excel 2013 supports transparency in lines. Also there is no limitation on line size. So that we do not need to insert the shaded region first, we can trun the shaded region to be semi-transparent.