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

Microsoft Excel has become a staple tool in the workplace, particularly for data analysis. In my own experience, I never considered Excel as a go-to application for such tasks. However, my background in Finance has shown me that the program is often used to create impressive financial models through the use of various formulas.

Before conducting any analysis, it is crucial to clean or adjust the data, as it often comes with inconsistent responses that make it difficult to use directly. This is where data cleaning comes into play, as it ensures that the data is in a format that can be easily utilized for analytical purposes.

One helpful tool I discovered recently is Data Validation. This feature allows you to restrict the type of input received in each column, making the data easier to work with later on. To set up Data Validation, simply select the cells you want to apply the restriction to, navigate to the Data tab on the ribbon, click on Data Validation, and then choose the desired validation criteria from the Allow dropdown menu.

In the Source field, type out the allowed values, separated by commas, and click OK to enforce the validation rule.

Filtering is another essential tool in Excel that can provide valuable insights into your data. By applying filters, you can display only the rows that meet specific criteria, allowing you to quickly assess the data's structure and identify any necessary adjustments. To use filtering, click anywhere within your dataset, go to the Home tab, select Sort & Filter > Filter, and then click on the dropdown arrow in the header row to choose the desired values to display.

This filtering capability is particularly useful when dealing with large datasets, as it enables you to focus on the most relevant information without overwhelming yourself with unnecessary details.