Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Excel serves as a valuable tool for handling data; however, before any analysis can begin, the data must be prepared. Datasets may contain blank cells, duplicate records, inconsistent entries, or values that do not align with expected formats. Cleaning the data aids in resolving these issues and makes the dataset more consistent. Following this, Excel tools such as formulas, Pivot Tables, filters, and charts become more effective in analyzing and comprehending the information.

Before making any modifications, it is advisable to retain an unaltered copy of the original dataset and work from a duplicate sheet. This ensures a reference point should any values be inadvertently altered or removed.

Formatting can enhance the readability of a large dataset without changing the actual data. The "Wrap Text" feature displays lengthy content across multiple lines within a single cell, while "AutoFit" automatically adjusts the width of a column or the height of a row according to its contents.

Before conducting any analysis, it is crucial to examine the data for potential issues that could impact the results. These issues include missing values, inconsistent entries, duplicate records, unnecessary columns, and illogical values. Missing and inconsistent values may appear as empty cells or as terms such as "None," "N/A," or "Unknown."

Although these values are not technically empty, as they contain text, they might still signify missing information. The meaning of a value should be verified before any alterations. For instance, "None" might signify missing information in one column but be a valid response in another.

There are various ways to address blank values based on the type of data in the column. A blank cell might be left empty if the correct value is unknown, replaced with "Unknown" for categorical data, filled with an appropriate value where its meaning is known, or investigated further before making any changes. For numerical columns, it is recommended not to replace the blank with text like "Unknown" as it may interfere with calculations.

Duplicate records can influence totals, counts, averages, and other calculations. To avoid potential inaccuracies, it is safer to select the entire dataset when eliminating duplicates. This precaution ensures that similar records with typos or inconsistencies are also considered.

Unusual values should also be scrutinized rather than automatically deleted, as some may be genuine. For example, having a hiring year of 1920 and an age of 50 might seem valid numerically, yet it would be illogical together. Investigating these values can help determine their legitimacy.

After addressing these checks, the dataset becomes more consistent and ready for validation and further analysis.

Filters can be employed once the dataset has been cleaned to view specific records without deleting or modifying the rest of the data. For instance, the HR dataset can be filtered by Work Experience equal to 7 years, Education Level equal to Bachelors, or Employment Type equal to Permanent. Filters prove useful when examining specific categories, identifying anomalous records, or concentrating on a smaller section of a vast dataset.

Sorting can arrange data in a specific order. Excel also supports sorting by multiple columns. For example, the dataset can be sorted by Work Experience, Education Level, and then Employment Type. This technique, known as multilevel sorting, ensures a structured presentation of the data.

Excel offers several functions for managing text, which can be beneficial when cleaning names, codes, email addresses, and other text fields. These functions begin with the equal sign (e.g., =LEN()) and include:

- LEN(): Counts the number of characters in a cell

- TRIM(): Removes extra spaces from text

- LEFT(): Extracts characters from the left side of text

- RIGHT(): Extracts characters from the right side of text

- MID(): Extracts characters from the middle of text

- FIND(): Determines the position of a character or text within a cell

- CONCAT(): Joins text from multiple cells together

These functions can be utilized individually or combined, depending on the desired outcome for cleaning or altering the text.

Conditional Formatting enables changes in the appearance of cells when they meet specific conditions, making it easier to identify important or unusual values. Common conditions include:

- Greater than

- Less than

- Between

- Top 10

- Bottom 10

- Above average

- Below average

For instance, employees who have worked for less than 18 years can be automatically highlighted using conditional formatting. Additionally, data bars offer an alternative method for comparing numerical values by displaying a bar inside each cell based on the size of the value, with larger values featuring longer bars for easier visual comparison without creating a separate chart.