Learning Excel For Data Science & Analytics

Excel is one among the most popular tools generally used in data analysis.
It allows us to perform various functions when handling data. These include;

• Data entry – this involves keying in data collected into cells of a worksheet, to maintain records, manage information and keep a track on information.
• Data cleaning – this simply means making data usable by removing duplicates, errors and inconsistencies from a dataset.
• Data analysis – using excel, we can transform raw data into actionable insights, which informs better decision making.
• Reporting – after cleaning and analyzing, the result is information that is easily understandable by various users to draw insights and make informed decisions.

Basic Excel Terminologies;

- Ribbon
This is the section of a worksheet where we have tabs and icons, arranged horizontally at the top of the worksheet. It contains various functions used to perform various operations while working on data.

- Cell
These are the grid-like spaces on the worksheet where we key-in data. These are the intersections between a column and a row.

- Cell Address
This can be referred to as the name or the address of a selected cell. It shows the intersection between a column and a row. In this case, “column – R, row -19”.

- Column
This is the vertical grouping of the cells. Columns are named alphabetically, which can be seen at the top of the worksheet. In this case, the column is named as “A”.

- Row
This is the continuous horizontal running of cells across a worksheet.
They are named in numbers. In this case, the row is denoted as “4”.

- Tables
This is a combination of rows and columns.

Functions

- Operators
You can perform various functions on a data set to derive various insights using Excel. These include the following functions;

1. Addition (+)
2. Subtraction (+)
3. Multiplication (*)
4. Division (/)
5. Exponent (^)

- Aggregate functions
These functions require a user to type in a specific function on Excel. These allows us to perform various operations such as;

1. SUM – performs an addition of a range of figures.
2. POWER – this raises a figure to a given power or exponent.
3. PRODUCT – Multiplies all numbers in a given range.
4. AVERAGE – this is the mean of a set of figures.
5. MEDIAN – this gives the middle figure in a set of numbers.
6. MODE – this shows the figure the appears most frequently.
7. MIN - this function gives the result of the lowest value out of a group of figures or numbers.
8. MAX – this function gives a result of the highest value out of a group of figures or numbers.
9. SQUARE ROOT (SQRT) – this allows us to find the square root of a certain figure.

- Statistical Functions
These tools or functions allow us to summarize and analyze data to arrive at insightful decisions. These functions include;

1. COUNT – These counts only numeric cells or columns
2. COUNTA – this function counts all non-blank cells
3. COUNTBLANK – This function counts just the blanks.

- Conditional Functions

1. COUNTIF / COUNTIFS – this function counts cells meeting a specific condition.
2. SUMIF /SUMIFS – this gives a result of a total of certain entries that meet a certain condition.
3. AVERAGEIF - this gives a result of the average of certain entries that meet a certain condition.
4. TODAY - this function gives the current date.
5. NOW – this function gives the current time.
6. YEAR – this function extracts the year from a date entry.
7. MONTH - this function extracts the month from a date entry.
8. DAY - this function extracts the day from a date entry.
9. NETWORKDAYS – this function calculates the total number of working days between two dates.
10. DATEDIF – this function gives the total difference between two dates.

- Conditional Formatting
This function allows us to highlight various entries in cells to make them observable based on certain specified conditions.

Conclusion

These functions and features offers an array functions and capabilities in Data Analysis. We are able to draw valuable insights and arrive at informed decisions. I am looking forward to further understanding more functions and formulae in Excel as i continue to better my skills in data analytics.