How to Plot Sieve Analysis Graph in Excel (with Quick Steps)

If you are searching for a solution or some special tricks to plot the Sieve Analysis graph in Excel then you have landed in the right place. There are some easy steps to plot the Sieve Analysis graph in Excel. This article will show you every step with proper illustrations so you can easily apply them for your purpose. Let’s get into the central part of the article.

What Is Sieve Analysis Graph?

Sieve Analysis is the particle size analysis method to determine the number of particles of different sizes present in a soil sample.

Usually, we use this for coarse-grained soils. In this method, we have to pass the soil sample through several Sieves. Sieves are devices that can separate different sizes of elements.

In sieves, there are mesh openings of a particular size. It can be ranged from 4.75 mm to 80 mm and for more intensive research, there are sieves of sizes 75 microns to 2 mm. We give the names of the sieves with the size of their mesh openings. So, if we see a sieve plate named 60mm then it can filter the elements up to 60mm through that sieve.

Types of Coarse-Grained Soils:

We can divide the soil grains into two types depending on their size.

Gravels: The soil grains that are greater than 75 mm are called gravels. They can be filtered through Dry Sieve Analysis. For filtering gravels, the available sizes of sieves are 4.75mm, 10mm, 20mm, 40mm, and 80mm.
Sand: The soil grains that are less than 75 mm are called Sand. To separate sand grains, you have to use the Wet Analysis Method. For filtering sands, the available sizes of sieves are 2mm, 1mm, 600 microns, 425 microns, 150 microns, and 75 microns.

Types of Sieve Analysis:

Dry Sand Analysis: we use this method to separate soil grains of size greater than 4.5mm which we call gravels. In this method, you have to hammer the bigger lumps of soil and make them smaller. Then, filter them through various sieve plates. And, thus you can separate the soil into different sieves according to the sizes.
Wet Sand Analysis: When the soil grain size is less than 5 mm then, the sand remains attached to the larger gravels, and the effect of gravity on them is poor. So, the sand particles can’t be filtered through the sieves automatically. For this, you have to add water into the soil so the water will take the soil particles with it and get filtered through the sieves. Then, you have to use the microwave to remove the water from the soil and take the weight measurement to plot the sieve graph.

How to Plot Sieve Analysis Graph in Excel

In this section, I will show you the quick and easy steps to plot the Sieve Analysis graph in Excel on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

Step 1: Create Sieve Analysis Template

The sieve analysis graph plots % retained on each sieve with the sieve size. So, first, you have to calculate the percentage retained on each sieve and the cumulative percentage retained from the dataset. For this,

First, create 4 columns to create a sieve analysis template.
Here, I have given the name of 1st column “Sieve Size” to take input of the size of the sieves used in the process.
Then, 2nd one named “Mass Retained” will contain the mass retained in the sieve through the process.
Then, you will calculate the percentage and the cumulative percentage in the 3rd and 4th columns.

After inserting data, you have to use the SUM function to calculate the total mass retained through the sieves. It will be equal to the sample size that has been taken for the analysis.
Insert this formula into cell C11:

=SUM(C5:C10)

Then, to calculate the percentage value of retained mass on each sieve, insert the following formula into cell D5:

=C5/$C$11

You must have to use the Absolute cell reference for C11 which contains the total mass.

Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

After that, you will get the percentage of the retained mass on each sieve but in numerical format.
To convert the cells to Percentage format, go to the Home tab in the top ribbon.
Click on the dropdown menu in the Number format box.
Then, select the Percentage.

Then, you have to create the cumulative percentage retained for the sieve analysis.
Insert this formula in cell E5:

=SUM($D$5:D5)

Then, drag the Fill Handle icon to apply a similar formula along the column.
And, thus you have created the Sieve analysis dataset.

Step 2: Plot Sieve Analysis Graph

After creating the dataset, you have to create the sieve analysis graph based on the dataset. For this, you have to follow the below procedures:

First, select the cells of range B5:B9, D5:D9, and E5:E9. Here, we have excluded the 10th row which contains the value of pan. As “Pan” is not the size of the Sieve plate, it will cause a disturbance in the graph.
Then, go to the Insert tab in the top ribbon.
Click on the Scatter Chart icon and select “Scatter with smooth line and markers”.

As a result, a scatter graph will be created as shown below.
Now, double-click on the title of the chart and rename it to give a suitable title.

Now, you have to give proper axis titles.
Click on the chart and you will get a Plus (+) icon on the top-right corner of the chart.
Click on the Plus icon and you will see the list of Chart Elements.
Here, mark the checkbox of Axis Titles.
Thus, the axis titles will be visible in the chart.
Then, double–click on each axis title and rename it.

Still, We can’t see proper titles for the legend series. So, the viewers of the graph can’t understand properly the meaning of graphs.
For this, you have to change the title of the data series.
To do this, click on the chart and go to the Chart Design tab.
Under this tab, click on the Select Data Source.
And, a pop-up window named Select Data Source will appear.
Here, select Series1 in the list and then click on the Edit button above.

Then, a new pop-up window named Edit Series will appear.
As series1 is the graph of “% Retained on Sieve” vs “Sieve Size”, select cell D5 as the Series Name.

Similarly, for Series2, select cell E4 as the Series Name.
Thus, the Legend Entries are changed and show the proper meaning of the graph.

Now, the Sieve Analysis Graph is complete.

How to Interpret Sieve Analysis Graph

From the Sieve Analysis graph, you can get a quick idea of how the percentage of size is changing with the grain size of the sample. Here, the Blue curve shows the percentage value of mass retained in each sieve of the total sample mass. The Orange curve shows the Cumulative percentage retained after each passing each which starts from the sieve of 80mm.

Download Practice Workbook

You can download the practice workbook from here:

Plot Sieve Analysis Graph.xlsx

Conclusion

In this article, you have found how to plot the Sieve Analysis graph in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back To How to Create a Chart in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!