I remember the days of my undergraduate school, how people spend lots of money to enroll for computer training program. A lot of computer programs then were called computer appreciation. Prominent among the trainings were word processing, the office suite and some graphic design with graphic tools like Corel draw. Excel was one of the software people were trained in. I looked so common and seam to be one of the easy software to learn then.
Before data analytics and data science gained its popularity, excel was basically used for storing records. The formular in were used majorly for accounting purposes and some scientific computations. My interaction with most persons using the software made me see less value in the application. I guess, there were few experts, or few people with good knowledge of the application. I have used the application for over 18 years, and I felt like an expert user.
During our introductory class in data analytics, I remember rating myself 7 out 10 in the use of excel. I thought this is a tool I have used for some time now; I should be a master in the use of excel. Well, the class started, and I was very surprised when I saw what can be done with excel.
Microsoft Excel is a powerful tool that can be used to create anything if a computer program is concerned. There are various tools used in data analysis, however, MS excel is the basic and the easiest to use tool in that area of computing. Just as computer system and mobile applications are proliferated, you can hardly get a windows system without this software installed, and yet we do not know the power of this application
Structure of Excel.
MS excel has similarities with relational database management systems like the Microsoft SQL, MySQL and other database systems that store data in rows and columns. Although, structured in rows and columns, MS excel store data in rectangular spaces called “Cells”. Each cell s referenced by the column letter and row number. The columns are labelled alphabetically form right to left while the rows are numbered from top to bottom. From the 2016 version of the excel upwards, the total numbers of rows is “16384” and the number of columns is “1,048,576”. Data in excel is stored in worksheet. A workbook is made of multiple worksheets.
Rules in data analysis with Excel.
Most of us who claim to be expert users of excel us it the wrong way. there are lots of things we do in excel wrongly. Some of this practice makes data analysis very difficult. A lot of times, we spend so much tie in preparing the data before we start the analysis. Some of these best practices are in storying data for analysis in excel are;
- The data to be analyzed should always be in separate sheet. The data sheet must be separate from the analysis sheet and the report sheet. The analysis sheet is where all the calculations are performed, while the report sheet is where we have the reports or the dashboard.
- No merging of cells in the data sheet
- No empty rows or columns in the data sheet
- No calculation on the data sheet
- Data should be in the right data type
- To perform analysis on the data, the datasheet should be converted to a table
While working with excel is very common, a lot of attentions need to be paid in the way data is stored in the excel sheet. When data is not properly stored it can result in wrong analysis and result. Consequently, wrong decisions are made based on the wrong analysis made on poorly prepared data. Managers and data analyst should take the pain to prepare and store data respecting the rules of data analysis with excel.