_edited_edited.png)
DATA ANALYTICS WITH MICROSOFT EXCEL
MY JOURNEY AND INSIGHTS
My professional relationship with MS Excel goes back a long way and I have been exploring the possibilities with excel ever since. It all started with a “Migration” - the goal was to migrate the Employee data onto another RDBMS server. There were a lot of ways to achieve the goal. I remember seeing this simple spreadsheet with grids and underestimating its potential, I was so wrong!
The biggest challenges we faced were “Data & Datatype” inconsistency, duplicates & missing data. We performed what is now called “Data Cleansing” using excel. The functions “PIVOTTABLE”, “SORT”, “FILTER” & “VLOOKUP” came to our rescue. This remarkable tool laid the foundation for my data career. It provided a solid platform for data exploration, analysis, and cleansing, while I expanded my skills using a range of other data tools. Now, let’s look further into Excel & Data Analytics.
About Microsoft Excel & its history
There is a saying, “All great things begin small!”. The concept of spreadsheets upon which Microsoft Excel was built began the same way. “VisiCalc”, the predecessor of Excel is the brainchild of Dan Bricklin, it was conceptualized while he was a student at Harvard. During a presentation, his professor was creating a financial model on blackboard that was ruled with vertical and horizontal lines (resembling accounting paper) to create a table, and he wrote formulas and data into the cells. When the professor found an error or wanted to change a parameter, he had to erase and rewrite several sequential entries in the table. Bricklin realized that he could replicate the process on a computer using an "electronic spreadsheet" to view results of underlying formulae. Bob Frankston joined Bricklin & the pair formed the Software Arts Company and developed the VisiCalc program in 2 months during the winter of 1978-79. This was the beginning of graphical Spreadsheets & Excel. Excel, which started out as “Multiplan” in 1982 had some competition from Lotus 1-2-3, but it started outselling its contenders in 1987 when version 2.0 was released and never looked back. Since then, several versions of Excel have been released.
About Data Analytics
What’s Data Analytics?
In simple terms, Data Analytics involves taking raw data, analyzing it, seeing trends and patterns, & using the analysis to make decisions. As we become more and more familiar with this, we can make predictions based upon our analysis.
Different types of Data Analytics:
Descriptive Analytics – What happened?
Diagnostic Analytics – Reason why it happened
Predictive Analytics – What is going to happen?
Prescriptive Analytics - What should be done next
Various stages of Data Analytics:
The first step is to determine the data requirements or how the data is grouped. Data may be separated by age, demographic, income, or gender. Data values may be numerical or be divided by category.
The second step in data analytics is the process of collecting it. This can be done through a variety of sources such as computers, online sources, cameras, environmental sources, or through personnel.
Once the data is collected, it must be organized so it can be analyzed. This may take place on a spreadsheet or other form of software that can take statistical data.
The data is then cleaned up before analysis. This means it is scrubbed and checked to ensure there is no duplication or error, and that it is not incomplete. This step helps correct any errors before it goes on to a data analyst to be analyzed.
Microsoft Excel is one of the tools that can come in handy during steps 3 and 4 listed above. Spreadsheets have been used for decades to organize and clean data. Another reason for Excell's popularity is the tool’s simplicity and ease of use. Excel as a Data Analytics tool – My Experience While talking about excel as a data analytics tool, there is another instance that comes to my mind. During the initial years of my experience, I was working in HRM domain. As per the organization's protocol, salary adjustments were scheduled after the completion of the appraisal, which was recently conducted. We were tasked with retrieving the employee data from MS SQL Server database followed by coming up with “What if scenarios”. Our objective was to calculate the Cost to Company (CTC) based on the percentage increase in employee salaries, which directly correlated with the appraisal outcomes. At this particular organization, we could come up with the average salary based on the designation and experience. Our analysis process involved extensive back and forth exchanges as we received initial numbers of the percentage increase for financial impact assessment. However, as these numbers involving the CTC proved unsatisfactory, we faced the challenge of multiple revisions, with figures constantly fluctuating. We overcame this issue by having different sets of data & by using the functionalities in Excel. The management was able to key in the percentage and come up with the annual financial impact on the company by using the Excel template we created. I distinctly remember the meticulous data collection, which allowed me to derive the average salary for each designation and experience combination. My approach involved employing a formula that referred to an input cell representing the "percentage of increase" in salary, enabling precise calculations. The destination worksheet referred to the “percentage of increase”, number of employees and the average salary for each category and showed the financial impact it would have on the company annually. When I think about it now, we applied steps 3 & 4 mentioned above - that is data organizing and cleaning to obtain relevant data. This scenario also explains Predictive and Prescriptive analytics in its simplest form. Predictive Analytics - wherein we used the existing data to predict the financial impact on the company & Prescriptive Analytics - where the business leaders looked upon this data to make the best possible decision. This was all done using Excel. This project holds a special place in my memory due to a profound remark made by one of the executives involved. In the midst of our work, he boldly proclaimed, "I can foresee that this is going to be the future! A combination of the technical aspect and the functional aspect which will enable us to make better and more informed decisions." Those words resonated deeply with me and have stuck with me ever since. It was a moment of realization, realizing the immense potential of data in shaping the future of decision-making. This powerful statement served as a catalyst for my passion and curiosity towards data. The exposure I got through this project opened my eyes to the vast possibilities data analytics held, and I knew I wanted to be a part of this dynamic field and as time went on, that particular instance played a significant role in shaping my career path. To this day, I continue to reflect on that executive's remark, knowing that it was a pivotal moment that steered my passion towards data-driven insights. That project marked the beginning of my career in Data and Data Analytics, and I am grateful for the inspiration it ignited within me.
References