Useful Tips of using Excel in Data Analysis

Excel is a very powerful tool in cleaning and analysing the data. Data is useful in our daily life. For restaurant boss, they want to know which set has the highest order.For blogger, they want to know how much people visit their website.

Based on the data, they can gain some insights and made improvements in the future. Excel is a popular application in career life too.Company needs data to improve customer satisfaction and production yield.

I work as design validation engineer.  My job is to ensure the R&D prototype has passed on the all the test and produce good distribution data.  Few R&D prototypes represents the entire population of production.  I need to ensure the sample results produce consistent pass results.

Figure below shows example how results in excel sheet looks like:

Test Points and multple sheets

                                                         

My results excel sheet has multiple tabs. Each tab consists of 1000 rows.

I have one template of excel consists of formula to calculate the parameters of distribution.

Thanks to my colleague. He shares to me the template.

 I need to copy the results from its from each tab in results excel sheet to the analyse sheet. I have multiple sheets.  I need to copy one by one from each sheet to the template. Ideally, all tab in test sheets must have same amount of row of test points. However, I realise that some test points in certain tab is missing.

This cause that I need to countercheck the test points when copying one by one. It is time consuming for me. Imagine I go through 1000 data sheet by sheet after copying, I may made mistake during analysis. Today, I realised that I have made some mistakes in excel when I review with my Project manager.

 

Solution:

1. Standardize the test points across the sheet. Copy and paste the data without double check.
2. Automate the process of data analysis. Extract the data from the results sheet and append to the analysis sheet.

 

In conclusion, if we wish to duplicate the excel sheet for data collection purpose, we must ensure that the test points are consistent throughout the sheets. Happy working!