Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.
Introduction to excel Microsoft Excel is a spreadsheet that allows one to collect, organize, analyze ,calculate and visualize data in a more efficient manner. When you open excel the first thing you see is a toolbar known as a ribbon, it contains toolbars with various options that helps to make changes on any data presented on the worksheet Excel workbook The excel work book has a worksheet that…
Microsoft Excel is a versatile spreadsheet program that enables users to collect, organize, analyze, calculate, and visualize data. Upon opening the software, users are greeted with a toolbar called a ribbon, which provides various options to manipulate data within a worksheet. An Excel workbook comprises one or more worksheets, each consisting of rows and columns. Rows run horizontally and are identified by numbers (1, 2, 3...), while columns run vertically and are represented by letters (A, B, C...).
Before diving into data analysis, it is crucial to perform data cleaning to ensure the accuracy and reliability of the information. Data cleaning encompasses several commands, such as filtering, sorting, removing duplicates, correcting case formats, and aggregating data. By employing these techniques, data cleaning helps generate precise pivot charts, tables, and dashboards, facilitating informed decision-making.
During the data cleaning process, several common issues may arise:
1. Duplicates: Repeated rows containing the same employee information. To rectify this, select all data, navigate to the Data tab, and click on the "Remove Duplicates" button.
2. Irregular capitalization: Employee names and department titles may be presented in varying cases, such as "NAIROBI," "nairobi," or "Nairobi." To standardize the case, use either the =PROPER() function for names or the =UPPER() or =LOWER() functions to convert text to consistent formats.
3. Missing data/Blanks: Certain cells might remain empty, e.g., blank entries for Department or Salary. Properly addressing these gaps is essential for generating reliable results.
4. Inconsistency in text: Locations and job titles may be written differently, e.g., "nainobbi," "Nairobi," "NAIROBI," "waitre," "waitress," and "watrss." Use the Find & Replace tool (Ctrl + H) or clean the data using the TRIM() and PROPER() functions to achieve consistency.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
