Tanjima Hossain
If you are looking for ways to plot a particle size distribution curve in Excel, then this article will serve this purpose. So, let’s get into the main article to know the detailed steps of plotting this curve easily.
Download Workbook
What Is Particle Size Distribution Curve?
The Particle Size Distribution curve helps determine the arrangement of different sizes of particles in a sample. By considering their sizes we can determine the average particle size easily, the particle of size smaller than the average size will be considered as smallest particle size, and the particles having a larger size than the average size is known as the largest particle size. In the particle size distribution curve, we will try to combine these various sizes of particles to give an idea about the particles remaining in the sample.
5 Steps to Plot Particle Size Distribution Curve in Excel
Here, we have the following dataset containing Sieve No., Particle Size (mm), and Mass Retained (g) of some particles in a sample. By using this dataset we will calculate the Percent Finer of these particles and then plot these values with respect to the Particle Size (mm) of the particles. In the following steps, we are going to describe the whole procedure of doing this job.
We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
Step-01: Calculation of Percent Finer to Plot Particle Size Distribution Curve in Excel
Firstly, we will calculate the Percentage Finer values in the % Finer column, and to get the values we will need the Percentage Retained values in the % Retained column.
To get the values in the % Retained column, we will need to get the total retained mass of the particles first.
- Enter the following formula in cell D11.
=SUM(D4:D10)Here, the SUM function will determine the added value of the range D4:D10.
Now, move to the calculation of the Percent Retained values.
- Apply the following formula in cell E4.
=(D4/$D$11)*100Here, the value of the mass of the particle in cell D4 will be divided by the total mass in cell D11, and then the quotient will be multiplied by 100. By using absolute referencing in cell D11 we are fixing this cell for the formulas of the rest of the cells.
- Drag down the Fill Handle
In this way, we will get percentages of the retained particles in the %Retained column.
Lastly, we will now determine the percentage finer of the particles to plot the curve.
- Enter the following formula in cell F4.
=100-E4Here, the value in cell E4 will be subtracted from 100.
- In cell F5, write down the following formula.
=F4-E5Here, we are subtracting the % Finer in cell F4 to the % Retained of cell E5 of the following row (Row 5).
- Drag down the Fill Handle tool to copy this formula pattern throughout the rest of the cells.
Eventually, you will have the % Finer of the particles for all of the sieve numbers.
Read More: How to Create a Distribution Chart in Excel (2 Handy Methods)
Step-02: Inserting Chart to Plot Particle Size Distribution Curve in Excel
In this section, we will insert a chart using the Particle Size and % Finer columns to form the Particle Size Distribution curve.
- Go to the Insert tab >> Charts group >> Insert Scatter (X, Y) or Bubble Chart dropdown >> Scatter with Smooth Lines and Markers.
After that, we will get this blank chart and to get the full chart Right-click here.
- Choose the Select Data option.
- Click on the Add option of the Select Data Source dialog box.
Then, you will have the Edit Series dialog box.
- Choose the range $C$4:$C$10 as Series X values and the range $F$4:$F$10 as Series Y values and finally press OK.
After that, you will be taken to the Select Data Source dialog box again where you will see the newly created series named Series 1.
- Press OK.
Eventually, we will get the following chart.
Read More: How to Plot Normal Distribution in Excel (With Easy Steps)
Step-03: Formatting Chart Area
Here, we will format the chart to make it more understandable.
- Right-click on the chart and then select the Format Chart Area
Afterward, the Format Chart Area wizard will appear on the right side of your worksheet.
- Go to the Size & Properties tab and then use 4 inches as Height and 7 inches as Width.
In this way, we have increased the chart area for a great visualization.
Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation
Step-04: Formatting X-Axis and Y-Axis
- To edit the X-axis, double-click on this axis to select it and then right-click to bring the Context Menu
- Choose the Format Axis
Then, the Format Axis wizard will appear on the right side of your worksheet.
- Go to the Axis Options tab and then do the followings.
Bounds → Maximum → 10.0
Units → Major → 10.0
Vertical Axis Crosses → click on Maximum axis value
- Check the boxes of the Logarithmic scale option and the Values in reverse order
Now, scroll down to see the remaining options.
- Select the Inside option of the Major type and Minor type Tick Marks, the Next to Axis option as Label Position, and General as the Category of Number.
Then, our chart will turn into like the following and we have to edit the Y-axis now by right-clicking here and selecting the Format Axis option.
In the Format Axis pane, go to the Axis Options first and then do the followings.
Bounds → Maximum → 100
Units → Major → 10.0
Vertical Axis Crosses → click on the Axis value option (the box beside this option automatically shows 0.0)
After scrolling down we will see the remaining options.
- Select the Inside option of the Major type and Minor type Tick Marks, the Next to Axis option as Label Position, and General as the Category of Number.
After formatting the axes we will have the following chart.
Read More: How to Plot Weibull Distribution in Excel (with Easy Steps)
Step-05: Customizing Chart Elements
- Click on the symbol of the Chart Elements.
- After selecting the symbol beside the Gridlines option, check the Primary Minor Vertical
- Check the Primary Horizontal and Primary Vertical options under the Axis Titles
After enabling the stated options, we will see the minor vertical gridlines in the chart which will help us acknowledge this chart as a Logarithmic chart. Furthermore, we will have space to write the titles of both axes.
- We have changed the X-axis as Parcel Size, mm (log scale), and the Y-axis as % Finer.
Then, we changed the font size of the titles from 9 to 11 and the numbers in the axes from 9 to 10. In this way, we can understand this plot easily.
Read More: How to Make a t-Distribution Graph in Excel (with Easy Steps)
Practice Section
For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.
Conclusion
In this article, we tried to plot a particle size distribution curve in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
