Let us consider this example below:
For the initial stress element shown, draw the mohr’s circle and also determine the principle stresses and the maximum shear stress
1. Enter the Stress details
First enter the stress details in the excel sheet considering the sign conventions.
2. Draw the Diametre of the Circle

Plot the 2 end points on the graph
Plot the points (σ along xaxis & τ along yaxis). The two end points of the Diametre are:
 (σx , τxy) = (400 , 200) ; (Normal stress on the X – Face , Shear stress on the X – Face)
 (σy , τyx) = (300 , 200) ;(Normal stress on the Y – Face , Shear stress on the Y – Face)
 The point where it cuts the xaxis is the centre of the Mohr’s circle.
Distance of this point from the origin will be average of the normal stresses.
These three points are to be tabulated in the same excel sheet as below:
Select this table and insert the scattered graph (Insert →Scatter→Scatter with Straight lines and Markers)
3. Plot the circle around the diameter created
Radius will be the hypotenuse of the triangle as in the figure below:
Calculate Radius of the Mohr circle in any cell with the formula
Plot the circle with Radius ‘R’
We need Calculate Cartesian co ordinates of the points of the Circle whose Radius is ‘R’ & with ( (σx+σy)/2, 0) as origin( for 0 – 360 degree)
First create a table
(σ , τ) = ((σx+σy)/2 + Radius x Cos(angle in radians) , Radius x Sin(angle in radians))
Then complete the table till 360 degrees
Select the Chart (in which already the Diametre is plotted )→Right click →select data →add series →select all the Cartesian coordinates of the circle from the table
3. Highlight the Maximum/Minimum Stresses
Create a Table just below the σ –τ table
SERIES NAME 
X axis 
Y axis 
Max Normal stess 
σ_{avg }+ R 
0 
Min Normal stess 
σ_{avg }– R 
0 
Max clockwise Shear Stress  +INDEX(all σ values, MATCH(Cell containing +R, all τ values,0)) 
+R 
Max anticlockwise Shear Stress  +INDEX(all σ values, MATCH(Cell containing R, all τ values,0)) 
R 
Add series and give the series name, x values & y values
Format these max/ min stresses points by right clicking on the points and formatting marker properties
Now the Mohr’s circle is complete.Do a little bit of formatting the Mohr Circle looks like this:
you can download this by clicking on the link below:
Excel_MohrCircle_sadakchapobserver
(Note: every time when the stress values are changed , the formed circle may not be a perfect circle so the chart needs to be resized each time when the values are changed)
did you find this post useful? please don’t forget to leave your comments and suggestions. thank you
excellent method….thanks for the solution….
LikeLike
Mr Rajkiran,
thank you.
LikeLike