“If you master 30% of excel, you could easily land a job faster than a primary school teacher or some graduates with specific undergrad majors,” a friend once told me this immediately after graduation from Maseno University.
The above words seemed untrue or rather exaggerated until I completed my excel session at LuxDevHq and realized there are so many things that MS excel could do specifically in the analytics field.
There are so many ways MS excel can be used in data analysis. Today we are going to delve into some of these aspects with a key focus on beginner concepts that are crucial to analytics and visualization.
Data Cleaning and Validation
Data cleaning entails fixing and removing errors in data to ensure accuracy and consistency before any analysis and reporting. Some of these errors include wrong entries, duplicates, inconsistent formats, etc. Duplicates are often removed only from columns that require unique entries such as employee ID. In the example below, we select the data range (employee id) and go to the data segment on the ribbon and click on the remove duplicates option.
In the cleaning process, we can also remove extra spaces and check on the cases. The formulas below can be used to achieve this objective depending on the requirement.
Trim (=TRIM(C2:C6) ) used to remove extra spaces.
Upper (=UPPER(B2:B6) ) used to change the case to capital letters while lower (=LOWER(B2:B6) ) makes them in small letters. Proper (=PROPER(B2:B6) ) on the other end achieves sentence case.
Validation
Data validation controls the type of data users can enter a cell. It is important because it reduces data entry errors, improves data quality, and makes analysis more reliable. Data validation is accessed from the Data tab by selecting the required cells and choosing Data Validation. Excel offers several validation types, including drop-down lists, number limits, date restrictions, text length limits, and simple custom rules to prevent incorrect data from being entered.
Ms Excel Functions for analysis.
Ms excel has so many functions that are crucial for computations that provide insight into the data provided. Some of these functions include:
- SUM – Adds numbers together =SUM(A1:A10)
- AVERAGE – Calculates the mean of numbers =AVERAGE(A1:A10)
- COUNT – Counts cells that contain numbers =COUNT(A1:A10)
- MAX – Finds the highest value =MAX(A1:A10)
- MIN – Finds the lowest value =MIN(A1:A10) In this case, the column in question is column A with the range running from A1:A10
Pivot tables, Graphs and Interactive Dashboards.
Pivot tables are practically a solution to the rather “hectic” use of some functions. They are used to quickly summarize and analyze large sets of data. Essentially, they allow users to calculate totals, averages, counts, and percentages without using complex formulas. Pivot tables also make it easy to group, sort, and filter data, helping users compare information such as sales by month, department, or product.
Pivot table
Graphs, also known as charts, present data in a visual format that is easy to understand. Excel graphs such as bar charts, line charts, and pie charts help users identify trends, patterns, and comparisons briefly. Visual representation makes data clearer and more effective for reporting and presentations.
Combo chart(bar and line graph)
Pie chart
Interactive dashboards combine pivot tables, charts, and controls such as slicers and drop-down lists into a single view. They allow users to interact with data by filtering and updating results instantly. Dashboards automatically reflect changes in the data, providing quick insights and supporting better decision-making.
Dashboard
Conclusion
And there you have it! Even as a beginner just poking around Excel, you’ve got the tools to clean, organize, and make sense of your data like a mini data detective. Sure, you might fumble a formula or two (who hasn’t?), but with pivot tables, charts, and dashboards at your fingertips, you’re already on your way to turning numbers into insights. Lets, keep experimenting, don’t fear the functions, and remember—every Excel wizard started exactly where you are right now. Looking forward to your feedback.
Top comments (0)