INTRODUCTION
I used to think Excel is a boring spreadsheet full of rows and columns only used by accountants and nobody cared about it. Everything changed the moment I jumped into data analytics. I came to realize that Excel really is a spreadsheet software developed by Microsoft that allows you to make calculations and analyse data. It is also the most powerful tool in data analytics. Excel is used in almost every industry you can think of.
Real World Use Cases of Excel
1. Education Institutions
Excel is used to monitor the trend in performance of students in schools by calculating their score, the mean mark and highlighting the top performers.
2. Business
Excel is used by most companies and firms to track down their sales, profits and losses. It can also show their product's performance in various tables and charts.
3. Healthcare
Health institutions use Excel to monitor their patients and keep their track records. It can also calculate the number of patients and medicines distributed within the health facility.
4. E-Commerce
A major example is Jumia. It uses Excel to calculate discounts distributed among different products and identify the most sold products.
Key Formulas in Excel
Excel is run by different formulas.
1. IF Statement
One of the most effective formulas is the IF statement. This gives a specific command. For example:
=IF(A2<30,"young","old")
This statement tells us that in cell A2, if its value is less than 30, then it should be classified as young and the rest should be old.
2. SUM Statement
The SUM statement adds up the highlighted column. For example:
=SUM(A2:A20)
This command sums up the values from the second row up to the twentieth row.
3. VLOOKUP
VLOOKUP searches for a value in one column and returns a matching value from another column: =VLOOKUP(A2,product_table,2,FALSE)
This formula searches for the value in cell A2 in the first column of the table and returns the corresponding value from the second column.
Data Cleaning
Before analysing data, you ought to clean it for easier analysis. One of the major ways of cleaning is by removing duplicates. You can also replace some values by simply pressing Ctrl+H. This shortcut allows you to find a value and replace it.
Another common formula is TRIM.
This special command eliminates unnecessary spaces before, after and between words inside the cells. Example:
=TRIM(A2)
It eliminates unnecessary spaces in cell A2.
Sorting and Conditional Formatting
Sorting allows you to arrange the selected column in either ascending or descending order, whereas conditional formatting allows you to change the colour of the highlighted cell depending on what you want to highlight. It makes it easier for you to scan and spot patterns in large data instead of going through every row.
Data Visualization
Excel goes beyond calculations and writing formulas.
1. Pivot Tables
Pivot tables in Excel summarise data in seconds without writing formulas and making calculations, simply by dragging and dropping the columns you want to analyse.
2. Charts and Graphs
Excel allows you to present your data in the form of graphs and charts for easy understanding. You can also use a pie chart to show how various parts make a whole.
Conclusion
At first, I underestimated Excel, but learning it has completely changed how I see data. It is now one of the first tools I would recommend to anyone starting in data analytics.
Top comments (0)