How to Log Transform Data in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show you 4 methods of how to Log transform data in Excel. To demonstrate our methods, we have taken a dataset consisting of 3 columns: out of them, only 2 columns are named “Country”, and “Deaths”. This dataset represents 6 countries’ number of deaths up to June 1st, 2022.

how to log transform data in excel Intro


What Is Log Transform?

We can use Log Transform to convert any skewed dataset distribution to less skewed. This will help us to make a pattern for a dataset. Consequently, pattern recognition will help us to make meaningful decisions from the data, which is the ultimate goal for any data analysis task. This tool is highly effective for data analysts.

The Logarithm can be demonstrated as follows: p = Logq(r),

It also can be written as, qp = r.

Here, the base is q, which can be any positive number (except 1), however, base 10 is the most common base. The base selection will be depended on the statistical model of any particular research.

Another rule of thumb is that when you need to account for the relative changes in your dataset, you should use the Log scale to transform your data. However, this is not the absolute truth, depending on your dataset and the goal of the analysis, you will need to decide when to use the normal scale and when to use the Log scale.

Read More: How to Plot Log Scale in Excel


How to Log Transform Data in Excel: 4 Ways

1. Use of LOG10 Function to Transform Data in Excel

For the first method, we are going to use the LOG10 function to transform data in Excel. This function returns the Logarithm value of a number and the base will be always 10. We will see how we can change this base using another function in another method. Moreover, we have added a column named “Logarithm Value”, where we will return the transformed data.

Steps:

  • First, select the cell range E5:E10.
  • Secondly, type the following formula.
=LOG10(D5)

Use of LOG10 Function to Transform Data in Excel

  • Finally, press CTRL + ENTER.

This will AutoFill the formula to the selected cells. Additionally, we can see that it has transformed our data from normal scale to Log scale.

Read More: Excel Logarithmic Scale Start at 0 


2. Log Transform Data in Excel Applying LOG Function

In this section, we will use the LOG function without any base to transform data in Excel. This function is similar to the LOG10 function. However, there is an option to add a different base here. When we do not input anything for the base, Excel will assume the base is 10.

Steps:

  • First, select the cell range E5:E10.
  • Secondly, type the following formula.

=LOG(D5)

Log Transform Data in Excel Applying LOG Function

  • Finally, press CTRL + ENTER.

Thus, we have shown you yet another way of Log transforming data in Excel.


Similar Readings


3. Use of LOG Function with Base to Transform Data

In this method, we will use the LOG function again, but this time with base 5. The base cannot be – negative, 1, or 0. Without further ado, let us jump into the step-by-step guide.

Steps:

  • First, select the cell range E5:E10.
  • Secondly, type the following formula.

=LOG(D5,5)

Use of LOG Function with Base to Transform Data

  • Finally, press CTRL + ENTER.

Thus, we have shown you the third way of Log transforming data in Excel with a base other than 10.

Read More: How to Calculate Logarithmic Growth in Excel


4. Log Transform Data in Excel Incorporating VBA

For the last method, we are going to use an Excel VBA to Log transform data. Here, we will use the For Next Loop to go through our cell values. Moreover, we will use the VBA Log function to achieve our goal.

how to log transform data in excel

Steps:

Before, typing our code we need to bring up the VBA Module. To do that –

  • First, from the Developer tab >>> select Visual Basic.

Alternatively, you can press ALT + F11 to do this too. The “Microsoft Visual Basic for Application” will appear after this.

Log Transform Data in Excel Incorporating VBA

  • Secondly, from Insert >>> select Module.

Here, we will type our code.

how to log transform data in excel

  • Thirdly, type the following code inside the Module.
Sub Log_Transform_Data()
Dim z As Integer
For z = 5 To 10
    Cells(z, 5) = Log(Cells(z, 4))
Next z
End Sub

VBA Code Breakdown

  • First, we are calling our Sub Procedure Log_Transform_Data.
  • Then, we are defining the variable type.
  • After that, using the For Next Loop we’re going through our data. We have provided 5 as the starting value, as our data starts from row 5.
  • Then, we use the Cells property to write values.
  • Finally, we perform our main task using the VBA Log function, moreover, we have used the Cells property again to go through our cell values.

Now, we will execute our code.

  • First, Save this Module.
  • Secondly, click inside our code.
  • Finally, press the Run button.

how to log transform data in excel

In conclusion, it will transform the values in cell range E5:E10 as per the VBA code breakdown.


Things to Remember

  • First, if we do not provide numerical values inside our LOG functions then we will get the “#Value!” error.
  • Then, when the base is 0 or a negative number, we will get the “#Num!” error.
  • Again, if our base is 1, the “#DIV/0!” error will appear.

Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

how to log transform data in excel


Download Practice Workbook


Conclusion

We have shown you 4 quick-and-easy to understand methods of how to Log transform data in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!


Related Articles


<< Go Back to Excel LOG Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo