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.

Sieve Plates

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.

Mesh Opening of Sieve Plate

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 Coarse-Grained Soils

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.

Sieve Analysis Graph in Excel

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.

Create Sieve Analysis Template

  • 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:

Using SUM function

  • Then, to calculate the percentage value of retained mass on each sieve, insert the following formula into cell D5:
You must have to use the Absolute cell reference for C11 which contains the total mass.

calculate the percentage value of retained mass on each sieve

  • 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.

Dragging Fill Handle

  • 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.

Formatting Cells to Percentage

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

calculating cumulative percentage retained for the sieve analysis

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

Dataset to Plot Sieve Analysis Graph in Excel

Read More: How to Plot Semi Log Graph in Excel

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”.

Inserting Scatter graph

  • 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.

Plotted Sieve Analysis Graph in Excel

  • 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, doubleclick on each axis title and rename it.

Inserting Axis title of Sieve Analysis Graph in Excel

  • 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.

Edit the Legends in Select Data Source Window

  • 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.

Select 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.

Plotted Sieve Analysis Graph in Excel with axis title and legends name

Read More: How to Plot Time Series Frequency in Excel

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:


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.

Related Articles

<< Go Back To How to Create a Chart in ExcelExcel ChartsLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF