plotting an equation in excel

For plotting equation we need X and Y, thus Y is function of X.

We need two things to specify:

(1) Bin of estimate - if bin of estimate is low will produce nice and smoother line.

(1) X and Y limit, this depends upon the equations and the portion of the curve you want to see.

Examples:

X^2 *Y^2 =1

By solving the quation, we get:

X = (1/Y^2)^-2

Please note operators in Excel:

Subtraction - minus sign ( - )

Addition - plus sign ( + )

Division - forward slash ( / )

Multiplication - asterisk (* )

Exponentiation - caret (^ )

Also order of operation is  Brackets, Exponents, Division, Multiplication, Addition, Subtraction.


Let's calculate values creating formula in Excel:

The formula for the first cell becomes:

=(1/(A2^2))^-2

Here the bin is set to 0.1 and range of X values from -4 to -4.

Data series ready, just plot scatter plot with smoothed line:

We can add other formulas to the plot, just by adding the series. To do so we need to repeate calculation at certain bin interval.

Plotting a circle:

(x-4)² + (y-2)² = 25

We need to plot two equations:

2+sqrt(25-(x-4)^2)

2-sqrt(25-(x-4)^2)

Between range of -1 to 9, create X bins and calculate the values.

Similarly we can plot any equation or density functions.

create stock chart in excel

Excel has very nice functions to plot stocks. Here is an example on how to plot Open-high-low-close type stock plot. We need the columns in same order - date, followed by open-high-low-close. Under stock select stock and subtype: open-high-low-close . 

Now we got the plot:

Now policing the plot to get final product. Now we can format the data series display and color etc. We might want to get rid of legend.

In similar way we can create High-low-close type stock chart.  

Creating fancy looking pie charts in excel 2013

Mostly in business presentation pie chart dominates - as this looks fancy !

We just click the two data series and then get results:

Now we do many things to it, as we need:

(1) It is like pizza - you can just take a slice out ! may be favorable one. We can animate it in power point slide as if we are pully it during presentation.

(2)  Under design menu there are several build in designs - you might want to apply some of them:

(3)  3D pie chart : 3D effects are often liked as they look fantastic ! Appropriateness in terms of scietific presentation is always questionable ! You can pull out segments of 3D pie chart in the same way.

(4) Sub pie chart: You can further develop another pie to show component of selected segment.



Many to do - but just want show some taste !

3D-histogram in Excel

Histogram can be both 2D and 3D. By 3D I do not mean 3D bars rather threre are two variables (X and Y and frequency is plotted in Z axis).

After  preparing data category (see the article), we can create a 3D histogram. So we need a two way frequency count table  like this:

The insert 3D column chart. We can then modify this remove gaps (looks like a block).

We can creat columns with different shape( may be just for fun !).

We can change the type to 3D- wire frame plot.

Point graph in Excel

A simple graph where only single variable is plotted in one axis with points and each point are space based on the value.

Here we need to create XY scatter plot , where we add dummy values to X or Y axis, depending upon whether we want to plot toward X or Y axis.

Now w want to center the points, just chanve the limits in Y axis from 0.8 to 1.2. We can hide the Y labels, that we do not need.

In similarway we can create multiple series of point plots. Here we need dummy X series (or Y series depending upon direction of points to be plotted).

Presenting data in maps in power view in Excel 2013

Power view in Excel 2013 enables you to visualize your data in a map. This uses Bing map and you need to be connected to internet.

Let's do some basic stuff:

We have data entered in the following format, here we have genographic locations columns followed by populations in three census.

Slected data and then click the power view icon.

Then we start to open new power view window.



Now click the map icon. then a new map will be generated. The map used by the excel is a Bing one. Sometime some name may mismatch or match to even other countries !! We just want to zoom to state we are interested here. We can




We can zoom the maps so that we can look more detailed map in certain regions.

We change the variable where the buble size is based on or we can apply filter by moving the filter bar.

You can change the background map to color road layout or satellite.

Hope this will raise your curicity and get starte, there is much to do.

Creating population pyramid in excel

Population pyramid are overlayed histograms. First we need a frequency table with age group category versus total population (of percentage). If percentage in not calculated beforehand we can just calculate using excel formula.

Formula used to calculate % in male : =(B2/$C$19)*100.

For females, we will make the values negative, so we can do so by  multiplying with -1.

 =-1*(C2/$C$19)*100

Now we need to add 2D clustered bar.


To make the plots compact set series overlap to 100% and gap width to 0.

Now we want to get rid of the negative numbers in female side. We can format the number in the axis to postive and black, select red format for negative number and then modify red by typing black within the []

We can format the plot to your taste (for example color etc). We can convert it to area plot too by just clicking the change plot type and selecting area type.

Sometime we might want to overlay groups, in above case we plotted group1 (male and female). Now we can add group 2 by just adding data series. The calculations for group 2 are similar to group 1. Use select data source and add data series to add group2male and group2female.

You might want to add transparency to bars so that overlayed bars will not shadow to others.

Creating strip plot in excel

In strip plot we have vertical or horizontal lines spaced at particular values in X or Y. In case multiple strip plots the value in  X and Y axis just are meant to plot the strips.

Here is a trick to develop strip plot using XY scatter plot with error bars.

(1) First of all add simple scatter plot, SN is a dummy variable with serial number. We need to edit the data source.

(2)  Now we want to plot strips in X axis, while the series A, B, C, D in Y axis, So now I created a dummy variable for place where these A, B, C, D will be positioned - i.e. 1, 2, 3, 4 in Y axis.  X value will be columns labeled with A, B, C,D while Y value will dummy constant 1, 2,3,4 for each series respectively.

(3) Now we need to add error bars in Y axis, remove X axis bars. Use a fixed value depending upon how long bar you like to see (for example 0.2), plus and minus bars. Remove bar cap and change the color and size whatever you like to. Remove the points and keep just error bars. You can connect the bars with lines too.

The strip plots are ready. If you like to change the strip length by thrid variable, you can do so by simple trick.



xy bar plot (both X and Y axis are continous variables)

Usually with row or column plots in excel, one axis is categorical dat and other is numerical. However with some tricks, we can create a bar plot (row or column plot). Here is the trick.

(1) With a dummy dataset (where A column as names - not numbers and B column has number ) create a bar plot.

(2) Replace A column values with small bins created (minimum unit of data - for example 0.1 if our data is single decimal point or 1 if our data has no decimal point) in the range of Y data. Now trick is to fill Y value. For example if the width of bar is from 0.3 to 0.12 and height is 8, then we need to fill the cells like this:
X           Y

0.03	8
0.04	8
0.05	8
0.06	8
0.07	8
0.08	8
0.09	8
0.1	8
0.11	8
0.12	8

If there no data point in the X region keep it blank. Remove gap between bars by setting the series option bar gap to 0. You can manipulate labels (if over crowded) displayed after certain intervals.

In similarway we can plot stacked barplot.

overlay plots over pictures (such as photo, diagram or maps)

Sometime we are need of overlaying the data points or bars over different types of plots. For this we need coordinates of the data points where we want to plot the data points. There are different image analysis software that can do so. Here is use of WebPlotDigitizer online free tool to do so using a leaf anatomy plot.

Examples:

(1) Plot a dummy plot. Here we are setting maximum and minimum co-ordinates to 200 in X and Y.

(2) Insert desired plot in plot area.

(3)  Now we need print and scan or take snip of the plot and save file as jpeg.

(3) Find cordinates to points in the plot  to be plotted using the image analysis software WebPlotDigitizer  :
 First load the file in the web application.

Click the 0, 200 in X-axis and 0,200 in Y axis before proceed.

Now click the point in figure where you want the data to be plotted. Once you are done you can request a csv output. See purple points are selected data points. You can cross check if it is working properly by clicking the known data points (Yellow diamonds in the following figure).

Now copy and paste in file with CVS extension (as suggested in the window) and open then paste in excel sheet.
The co-ordinates of plotting data points can found by simply guessing (for coarse work) in the plot or using some short of tracing act over printed plot. For maps (such as google maps we can find the co-ordinates of the data points)

(4)  Npw we can simply plot different types of plots - connect lines, connected lines with error bars or bubble plots over the figure. To avoid misalignment if you change any features of the data, the range of the data mush be refreshed. We need to remove the data plotted by previous dummy data set and add new series.

See the trick of plotting 4 extra points in right corner (not in anatomy area) are meant for plotting legend scales. Now the following line connected plot with error bars. Off-course we can just plot the error bars corresponding a value we want to plot.

Creating bubble plot: We can create bubble plot in similar way, which may be more useful where the size of bubble is scaled to certain factor.

A transparent version:

We can similarly overlay over map in the similar way:

Here is example with plotting in chromosomes using same trick:

We can combined with other type of plot used as picture, for example here we used add bars to the dendogram.

Just for fun, we can overlay with other type of plot in background (which is not possible in current excel) - just bars in 3D pie - 

Important Note: The scale limit in X and Y axis must be same as used while finding coordinates  otherwise the plot points will misaligned.