![Search icon](https://hackernoon.imgix.net/search-new.png?w=19&h=19)
Whether you're a student, looking to change jobs, or in a new role that requires data analysis skills, learning how to analyze data might be the skill you need to supercharge your professional growth. However, many of those starting out feel lost among the range of new formulas to learn or don't even know where to begin in using data to understand business problems. In this guide, I'll be taking you through a simple, three-step process - Prepare, Analyze, Consider - that will allow you to turn raw data into meaningful insights using the perfect beginner's tool: Microsoft Excel.
Understanding the Challenge
Before getting into the process, it’s necessary to understand why the transformation of data is so important. In this day and age, unlocking business and organizational performance is all about effective decision-making based on insights from data. But raw data isn't easy to comprehend in its unprocessed form. This is where a data analyst comes in: to use this raw matter and transform it into a meaningful format that discloses trends, patterns, and insight for decision-making.
Step 1: Prepare
The first step in any data analysis process is preparation. In this step, you become acquainted with the data you have, enriching it as required, and identifying any potential limitations in the data in answering the questions you might have.
1.1 Understanding Your Data
The first step is coming to a clear understanding of what each of the variables in the data represents and how it might relate to the business problem at hand. For instance, if one is working with sales data, then data items such as revenue, margin, sales and invoicing dates, customer regions and districts, and product categories will likely be encountered. Each of these may be defined slightly differently at your current organization than a previous one you may have worked at. Appreciating these nuances is a key part of the analysts skillset.
1.2 Data Cleaning
This is critical step of preparation where duplicate values are removed, missing values are found, replaced or inferred, and consistency in data formatting is checked and standardised as necessary. Excel has various built-in tools for data cleaning or cleaning up dirty data. Most important among them are:
1.3 Data enrichment
Sometimes the data that you hold may not be sufficient to address the business problem at hand. Data enrichment means the addition of more data from other sources to fill these gaps. For instance, if you are analyzing sales data, you might want to enrich it with demographic data to understand customer segments better.
1.4 Understanding Data Limitations
Nearly every ‘real world’ dataset has limitations as data gathered by business processes or surveys, for example, cannot possibly anticipate every future question that might be asked of it. It is important to be aware of these so that you can undertake your analysis correctly and communicate these limitations back to the stakeholder who asked you to investigate the problem. It could be missing data (an invoicing system was faulty for one more days in the date range, underreporting sales), biased samples (very few respondents were aged 65 or over), or the data contained entries which do not conform to current business rules (such as a since-replaced product category which has been discontinued). Identifying and adapting your analysis, and how you communicate your findings, is essential to draw the right conclusions and issue the most accurate analysis.
Example: Sales Data Preparation
Let's say you work for a national retail store. Your raw data would pertain to the date of sale, type of product sold, and the revenue for that sale. Here is how you would prepare this data:
Step 2: Analyze
Having prepared your data, the next step is to analyze it. This involves exploring the prepared data to uncover patterns and summarizations that might help understand and address the business problem.
2.1 Selecting the Right Tool
Excel offers many tools for analysis such as formulas, pivot tables, and charts. What tool you choose, can be decided based on the nature of analysis:
2.2 Using Formulas
Excel’s strength lies on its huge range of formulas that enable all sorts of calculation tasks. Some of those which are essential in data analysis include:
2.3 Creating Pivot Tables
Pivot tables are immensely powerful tools in summarizing data. They allow you the to choose how you want your data to be grouped, say by variables of your choice, and let you summarize it all without typing any formula. For instance, you can build a pivot table that shows ‘sales’ by ‘product’ and ‘product category’ to give you the sense of which product categories are performing best.
2.4 Creating Charts
Charts are important in visualizing your analysis. There are many chart types supported by Excel, such as bar charts, line charts, and pie charts. The decision in selecting the right chart type is based upon what you are trying to represent and making this insight as easy to interpret for the reader. For instance, line charts make very good candidates when trying to show trends over time whilst pie charts when trying to show proportions.
Example: Analyzing Sales Data
Using the example of the retail store once again, suppose we must analyze which region has contributed the most to selling a respective product category. The following steps might be taken:
Step 3: Consider
The final step is considering how you would best illustrate your data for maximum impact, and how to share these insights in a way that would be actionable and relevant to your audience.
3.1 Knowing Your Audience
Good data presentation entails knowing your audience. The adequate level of detail and the type of information that you need to present varies by audience. A senior executive in all likelihood wants a high-level summary, whilst a store manager needs more detail: perhaps performance by salesperson.
3.2 Focus on Relevance
Discuss the most valuable insights relating to the business problem at hand. Try not to overload your audience with information. Highlight the key findings which add context to your analysis and those findings which have actionable recommendations - these will most strongly support decision making.
3.3 Use Effective Visualizations
The visualization of your data forms a critical part of data presentation. Charts, graphs, and tables make data accessible, readable, and engaging. Best practices in effective visualization include:
3.4 Offer Actionable Insights
Finally, the most important objective of the data analysis: to provide actionable insights. It means presenting clear recommendations based on your findings. For instance, if your analysis shows that one category of product is underperforming, recommend ways to improve its sales or highlight the contributing factors.
Example: Effectively Presenting Sales Analysis
In the given example of a retail store, you have identified that the best performing sales category is 'electronics,' specifically in the North region and yet this is performing very badly in the South West. Here’s how you might share this information:
Conclusion
Converting raw data into useful insights is one of the most important skills for those wanting to make decisions with data. In this way, through these the three steps - Prepare, Analyze, and Consider - it is possible to turn raw data into meaningful insight with Microsoft Excel. Using this popular and widely available tool, anyone can unlock the value of raw data and make an impact on business performance.