When conducting analysis using parametric methods, it is generally required that the data follow a normal distribution. However, in reality, data collected from the field often do not meet this expectation.
Tests such as the Shapiro-Wilk test or the Kolmogorov-Smirnov test may show that the data are not normally distributed. This situation often leads researchers to attempt transforming skewed data into another form to achieve a more symmetrical distribution.
Data transformation is widely used around the world to normalize data distribution and reduce data variability. One commonly used transformation is the natural logarithmic transformation.
Natural logarithmic transformation is a statistical technique used to convert numerical data using a logarithmic function with base e (2.718). As previously mentioned, natural logarithmic transformation is frequently used to help normalize skewed data distributions to make them more symmetrical and closer to a normal distribution.
Additionally, for data with a wide range of values—for example, variables measured in thousands or millions—applying a natural logarithmic transformation helps stabilize and homogenize the data.
For instance, if we have income data of $1000, which then increases to $2000, the absolute difference is $1000. However, if the data are transformed using natural logarithms, the difference between Ln(1000) and Ln(2000) is only +0.6931. Thus, natural logarithmic transformation is especially useful for dealing with large and unevenly distributed data.
Natural Logarithmic Data Transformation in Excel
Given the importance of understanding and being able to independently perform natural logarithmic transformations, in this article I would like to explain how to do it using Excel. Why Excel? Because it is an application that is almost universally available.
It turns out that performing natural logarithmic transformations in Excel is not difficult and is very time-efficient. Even if we are dealing with thousands of data points, it’s not a problem because we can easily copy and paste formulas in Excel.
As a practice example in this article, I will provide income data for 10 workers at company XYZ. Based on the collected data, the income details that we will use can be seen in the table below:
Once we have entered the data above, we need to make sure that the data in Excel is formatted as numbers. If the data is not yet in number format, please adjust it first. Once the data is in the correct number format, we can proceed together to perform the natural logarithmic transformation in Excel.
Steps to Perform Natural Logarithmic Data Transformation in Excel
The first step, using the example dataset above, is to create an additional column to the right of the income data column (which should already be in number format). Note that in Excel, we will use the LN() function to perform a natural logarithmic transformation.
Before we start practicing the transformation in Excel, here is an important note: when performing a natural logarithmic transformation, the data must not contain zeros or negative values. This means only data with positive values greater than 0 can be used.
Next, move your cursor to the first cell in the “LN” column in Excel. Then type the formula =LN(5000) and press Enter. The first data point has now been successfully transformed into its natural logarithmic form. This step can be seen in the table below:
Then, simply copy and paste the formula by dragging the small square (fill handle) down to the last data row. The detailed analysis results can be seen in the table below:
At this point, you have completed transforming the original data into natural logarithmic form in Excel. It turns out that this process is easy and quick to do. Alright, this is the article I wanted to share for now. I hope it’s useful and adds to our knowledge and understanding. To continue receiving the latest article updates, please visit the Kanda Data website regularly. Thank you.
