Microsoft Excel has undeniably played a major career in the life of every data analyst or a data scientist. It is without a doubt one of the foundational or fundamental tools that every beginner in data analytics has to at least master before advancing to more tools. I have worked with excel before in my career solving various tasks, hence I have some familiarity with the tool. Starting a data science course demanded a repeat of the foundational concepts in excel, which proved to be critical. I have been able to develop a different perspective and skills on how excel is important to data analysis. In this module, Getting Started with Excel for Data Analytics: From Basics to Data Cleaning, I have been able to understand excel use cases, master basics in excel, and the steps it takes to have a clean data.
Excel Definition and Use Cases
In simpler terms, Excel is basically a statistical software package that allows users to organize, format as well as calculate data with the use of grids and columns. In most institutions, Excel is one of the most commonly used tools to handle data hence it has a number of use cases. Some of these cases are as follows:
i. Recording data: Recording data is the most common function that most people depend on excel, otherwise called data entry.
ii. Data Analysis: Excel helps to ensure that data can be transformed into meaningful insights.
iii. Reporting Insights from analyzed data: Meaningful insights from the data can be reported for ease of understanding for the stakeholders.
iv. Business Intelligence: Insights generated from excel data analysis can be used to informed the business or organization on the correct course of action.
v. Project management: Excel plays a vital role in handling projects through supporting the users to keep track of project milestones as well as timelines.
Excel Basics
Starting up with excel, exposes the users to a blank page with so many tools that could easily be confusing. To start with, excel has an interface called the ribbon located at the top, with commands which help to navigate between different functionalities in excel including File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help, Power Pivot etc.
Quick Access Tool Bar
The next item is the Quick Access Tool Bar which is located at the top left corner of the excel worksheet. It contains commands such as Save, Undo, and Redo as shown in the image below:
Formula Bar
In excel, there are times when a user will be required to use formula. In this case, the function or the formula used will appear at the formula bar for the selected cells.
Name Box
While working with excel, the name box is used to show the selected cell. In this diagram shown below, the selected active cell is indicated by the name box as A1.
Columns
The columns represent a vertical arrangement of cells in an excel sheet. In the diagram below the columns is denoted by the alphabet letters, such as Column A, Column B, Column C etc.
Rows
The rows refer to the horizontal arrangement of cells in an excel sheet. Rows in excel are normally identified using the numbers, 1, 2, 3, etc. as shown in the diagram below.
Cells
The cells refer to the intersection between a row and a column within an excel sheet. In the sheet below, the selected cell is C3, which is an intersection of Colum C and Row 3.
Worksheet Tabs
Worksheet tabs are the tabs located at the bottom of the sheet, that allows users to navigate from one sheet to another within the workbook. The addition butting, allows the users to add new sheets into the workbook. One can as well rename, delete the sheet, move or copy by right clicking on it.
Entering and Editing Data in Excel
One of the roles that excel supports in organizations and institutions is data entry. Data can be entered in different formats in excel and once it has been entered, it can also be edited. The main formats are as follows:
i. Texts
ii. Numbers
iii. Dates
iv. Formulas
Formatting Data in Excel
Once the data has been entered, the next important step is to ensure that the date makes sense. One of the best approaches to do so is to format the data in the correct format. In this case, data can be formatted in various ways using excel. Some of the ways are as follows:
Text formatting
In text formatting, data can be customized into various formats using functions such as bold, italics, underline, increasing font size, selecting right font, and it can be accessed in the image shown below:
Cell Alignment
This approach refers to how cells are positions in a cell and it could be left, center, or right aligned. Accessing the alignment option in excel is as follows:
Number Formatting
Numbers can also be formatted in various ways to represent their true value whether it could be currency, percentage, data etc. as follows:
Data Cleaning with Excel
After data has been entered into excel, it is important to clean it so that the final output can have a proper meaning which can be used to generate insights for decision making. Cleaning data in excel goes through a number of processes for it to be ready for use.
Data Sorting
One of the steps involved in data cleaning is data sorting which involves arranging data in a specific order, either ascending (A to Z) or descending (Z to A) once the required row has been selected or the enter sheet.
Once the column has been selected, right click then select sort to pick the appropriate option.
Opening custom sort also opens up a new set of options that could allow the user to further sort their data in a precise manner and this depends on the information that the sheet contains.
For dates, the information can be sorted in the form of oldest to newest or vice versa.
Filtering
This step involved selecting only data that meets a specific criterion.
Data Validation
Data Validation plays an essential role when it comes to ensuring data on the workbook is clean and this is mainly achieved through restricting only what users can be able input in the cell by providing specific options.
Text Functions used for Cleaning Data
Various text functions can be used to clean data by the helping to standardize the text formats and this is as follows:
i. UPPER () which is used to convert texts into upper case.
ii. LOWER () allows for texts to be converted into lower function.
iii. PROPER () ensures that the first letter of each word is capitalized.
iv. TRIM () removes any extra space that could be within a cell.
v. LEFT () removes the characters that appears towards the extreme left.
vi. RIGHT () ensures that the right most characters are removed
vii. MID () returns the middle character
viii. LEN () provides the length of the text string provided.
ix. FIND () helps to determine the position of substring and in most cases, it is case sensitive.
x. SUBSTITUTE () Replaces text within a string
Conclusion
It is without a doubt that excel has a wide range of functions for any person that is dealing with data. Understanding excel basics is important foundation knowledge as it helps to ensure that one is able to integrate various functionalities to achieve a desired end goal. Moreover, getting to know how to clean data using the functionalities stated above is essential. These functionalities will ensure that you are working with clean data therefore, the output will be desirable and that it will be used to make important business decisions.
Top comments (0)