Introduction
Excel is a powerful tool used in data analytics, it is one of the most used tools for working with data. You can use Excel to collect, organize, clean, analyze, visualize, and present data without needing to write code. It is also the easiest way you can learn important analytical concepts while working with an actual spreadsheet.
Definition of Excel and Data Analytics
Excel is a spreadsheet that allows you to store information in rows and columns.
Data analytics refers to the process of examining data to discover useful information, patterns, and insights that can help with decision-making.
For example a company like LC Waikiki has thousands of sales records. The raw data might contain:
- Customer names
- Sales representatives
- Store Locations
- Product Names
- Sales etc.
Understanding the Excel Interface
Workbook- This is basically an excel file.
Worksheet- This is an individual spreadsheet inside a workbook. The worksheet tabs are found at the bottom.
For example:Sheet1 | Sheet2 | Sheet3
Rows- They run horizontally across the spreadsheet. They are identified by numbers.
Columns- They run vertically. They are identified by letters.
Cell- This is the intersection of a row and a column.
Cell Range- This is a group of cells. A1:C10
Example of a Worksheet
Formatting data
Formatting makes data easier to read. Correct formatting becomes especially important when analyzing dates and numerical values.
You can use the following formatting options:
- Date
- Currency
- Time
- Percentage
- Number
- Fraction
- Text
Basic Excel Formulas
SUM adds numbers together. =SUM(F2:F6)
AVERAGE calculates the mean. =AVERAGE(F2:F6)
COUNT counts cells containing numbers. =COUNT(F2:F6)
COUNTA counts cells that are not empty. =COUNTA(B2:B6)
MAX returns the largest value. =MAX(F2:F6)
MIN returns the smallest value. =MIN(F2:F6)
IF function allows Excel to make decisions. =IF(F2>500,"High","Low") This type of logic becomes very useful when analyzing large datasets.
Sorting Data
Sorting allows you to arrange your data according to a particular order. Like Largest → Smallest ,or alphabetically from A-Z,Z-A etc
Filtering Data
Filtering allows you to display only the records that meet specific conditions.
What is Data Cleaning?
Data cleaning is the process of identifying and correcting problems in a dataset so that the data is accurate, consistent, complete, and suitable for analysis and decision-making.
Some of the issues one can encounter in a large data set may include:
- Invalid values
Inconsistent capitalization- These can be standardized by using functions like =UPPER(A2) to change characters to uppercase, =LOWER(A2) to change characters to lowercase, =PROPER(A2) to change characters to proper capitalization.
Missing values
Duplicate records- This is when the same record appears more than once.
Spelling mistakes- Find & Replace (ctrl+H) can be used to fix spelling mistakes
Incorrect dates- You should make sure dates are stored consistently and recognized as actual dates rather than text.
Extra spaces- Excel can treat them as different text values. The **TRIM **function helps remove the unnecessary spaces. =TRIM(A2)
Wrong currencies
Numbers stored as text- =VALUE(A2) can be used to fix this.
Before analyzing data, these issues should be addressed.
How to use Find & Replace for Data Cleaning
Find & Replace is one of the simplest and most useful Excel cleaning tools. You can open it using Ctrl + H. It is useful in correcting abbreviations, unwanted characters, spelling errors, inconsistent labels and category names.
Data Validation
Data validation helps control what users can enter into a spreadsheet. This reduces the likelihood of users entering inconsistent values. This is particularly useful when creating spreadsheets that other people will use to enter data.
Conclusion
When you receive a new dataset, carefully go through the entire worksheet before creating charts and calculating averages. Understand and inspect the dataset for irregularities and any inconsistencies. And after fixing the errors review the clean dataset again just incase. Learning Excel for data analytics is a gradual process and you don't need to learn every Excel function before you can start analyzing data. The skills you develop in Excel can be transferred to different areas of data analytics.
Top comments (0)