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

Excel

Introduction

Excel is one of the most foundational tools critical to Data Analysts. It is useful for preparing data for analysis. Excel can be used from the point of data collection to creating dashboards. It is the foundational tool used by most Data Analysts in cleaning, reformatting, and structuring the dataset ready for analysis. Excel provides unique technical tools that support collecting, cleaning, analyzing, visualizing, and reporting. Removing duplicates, filtering and sorting, validating data, aggregating, and performing some statistical operations are some of the major tools that Microsoft Excel supports and are useful during data analytics.

Excel provides a ton of benefits to many organizations and businesses. It can serve as a database for storing and retrieving business data, i.e., sales data; it can serve as an electronic ledger, a data analytics tool, and many more functions.

Understanding Excel

Before diving into data analytics using Excel, we first need to understand Excel and all its functional components that are core to data analysis. This will give us an overview of the Excel interface and basic functions for the different components.

Interface

1. Ribbon - Contains all the command tabs you will work with. The tabs are: File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help, and many others. These tabs have more groups from which we can access and apply different commands to our dataset.
2. Toolbar - At the top right corner of the workbook. Used to hold and display the most common commands we use in Excel, like "Save", "Undo", "Redo", or/and any other command you may wish to add to the toolbar.
3. Scroll Bar(Horizontal and Vertical) - Scrollbars at the end of the worksheet. The vertical scrollbar is used to scroll through your worksheet up and down, and vice versa. The horizontal one helps you scroll through his/her worksheet from left to right and back.
4. Worksheet - Where you work from. It contains cells where you do your data entry.
5. Columns - Named alphabetically and move vertically in the worksheet.
6. Rows - Named numerically and moves across/horizontals in the worksheet
7. Cell - An intersection between a row and a column. Where we enter our data.
8. Formula Bar - Where you enter and edit your formulas in Excel.
9. Name Bar/ Address Bar - Displays the address of the active cell.

Workbook - One or more worksheets existing in the same Excel file.

Home Tab

Most of our formatting commands will be done from this tab. It is divided into around 8 groups:
- Clipboard: From here you can access the Copy, Cut, Paste, and Format Painter commands.
- Font: From here you can access text formatting commands. They include: Bolding, changing font face, font size, borders, highlighting, font color, italicizing, underlining, and many more commands.
- Alignment: You can access text alignment(Vertical and horizontal) commands, orientation, and indentation commands.
- Number: Where you can access different data type formatting features. i.e., date, currency, number, and many more.
- Styles: Conditional Formatting, Formatting as Table, and applying different cell styles.
- Cells: You can insert, delete, and format cells from this group.
- Editing: AutoSum, find and replace, filter, and sort command options are accessible from this group.
- Add-ins: Where you can access your Excel add-ins.

Insert Tab

A very important tab too. We can access the following groups essential for data analytics:
- Tables: We can work with Pivot Tables from this group
- Charts: We can create and insert charts from this group during our visualization and reporting.
- Sparklines: Insert sparklines for monitoring trends in our analytics.
- Filter: You can insert slicers and timelines, which are used for filtering our data and output in the dashboard mostly.

Formulas Tab

This tab is very essential and critical to data aggregation and statistical manipulation. The tab contains formulas and functions that are used to carry out mathematical and statistical manipulation on all numeric and textual data. It contains the following sections:
- Function Library: Contains Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trigonometric, and many more functions used while working with our dataset.
- Formula auditing: Contains tools that are used to understand the logic and structure of complex formulas(Dependents and Precedents).

Data

It contains the following groups, which serve an important role in preparing our data for analysis:
- Get & Transform: Used to import data from different sources, i.e., web and CSV files. Also, it is useful in doing transformation of datasets into Excel-compatible datasets.
- Sort and Filter: Used for sorting and filtering our dataset. This is important when you want to understand your data ranges or find anomalies that exist in your dataset for correction and further investigation.
- Data Tools: From here, we can get data validation commands, remove duplicates, convert text into columns, and many other commands that help maintain data consistency, reliability, and integrity during collection and analysis.
- Forecast: Used to do what-if analysis and forecast what will happen in the future to help with reporting and in making informed decisions.

Data Analysis

Introduction

Data analysis is the process of investigating a dataset to uncover patterns, trends, and insights for informed decision-making and to drive growth and success in business and organizations. Through data analytics, businesses and organizations can solve problems, improve operations, and optimize stakeholder decisions. Therefore, data analysis involves transforming raw opinions or data into meaningful facts or information.

Data analysis is crucial across fields like marketing, sales, health, finance, sports, and retail industries. It helps industry owners to be decisive and informed for a better future that fosters growth.

Types of Data Analytics

These are the types of data analytics. They all serve to achieve a common goal.

1. Descriptive - Used to answer the question: what happened? It investigates and summarizes historical uncover trends and patterns. Involves data aggregation and visualization.
2. Prescriptive - Used to answer the question: what is likely to happen next? It uses models to foretell what the future looks like based on past events. Involves machine learning and linear regression.
3. Prescriptive Analytics - Answers the question: What should we do? It recommends actions by combining data analysis with optimization techniques. Involves algorithm optimization.
4. Diagnostic - Answers the question: Why did it happen? Uses statistical methods to identify the cause. Involves correlation analysis and hypothesis testing.

Steps in Data Analysis

There are several steps involved in data analysis. The sequential steps are important if you want to begin collecting data by yourself.

We will use a publicly available dataset to illustrate the steps involved in data preparation before the actual analysis. Some of the steps may require actual data collection; hence, our dataset will not be used in those steps but rather in the preceding steps.

We are going to use the Cafe Sales - Dirty Data for Cleaning Training Dataset, available for download from Kaggle.

Note: Before you start the process, ensure that you keep a copy of your data.

Data Preparation

This is the process of collecting, cleaning, transforming, and analyzing the collected data. It readies

Data preparation ensures its accuracy, reliability, consistency, and regulatory compliance. These increase the level of trust and collaboration.

Data Collection

Data collection involves gathering raw opinions and facts right from the source.

There are various methods involved in data collection, which include but are not limited to: Conducting interviews, surveys, API integrations, IoT devices, web scraping, observation, or getting it from third-party sources.

The data is then loaded into tools like spreadsheets for further activities like cleaning and analysis.

Data Quality

Before the cleaning process, data is assessed to determine whether it meets the data standards that can help us avoid costly mistakes such as misleading results and flawed decisions.

Ensuring data is accurate, timely, complete, consistent, relevant, and of integrity is the key objective in this step.

Data Cleaning

Data cleaning ensures that datasets are complete, accurate, and consistent before analysis. This is achieved by getting rid of errors, fixing broken data, and addressing all issues that the dataset might have.

Data cleaning helps us to handle missing values, duplicates, outliers, inconsistent formatting, and irrelevant data.

From this point forward, we will be using our dataset to demonstrate how we can handle these issues we have listed. We are cleaning our data using Microsoft Excel. Open your CSV dataset using Excel.

Open the Dataset

- Navigate to the project folder `excel-data-cleaning/`
- Then go to the data folder `data/`
- Double-click on the file `dirty_cafe_sale.csv`; it will open in Excel.

Saving a copy of your dataset in Excel format

- In  excel on the Ribbon click on the *File* Tab, then go *Save AS*
- Choose your save location. In our case, it is in the project root folder, then in the `dashboard/` subfolder.
- Give it the name, in our case *cleaned_cafe_sales.xlxs*
- Click on *Save*

Now you have made a copy of your dataset, which is a working file. So inspect your file carefully to determine the missing details and existing mistakes that are visible.

You can also bold your column titles, convert the dataset into tables, and apply other basic formatting, like changing the font if the current font is not readable.

To Bold Titles

- Select the first row containing titles.
- Click on the *Home* Tab
- Navigate to the *Font* group section. 
- Click on the *Bold* icon(*B*)

Alternatively, you can use the shortcut Ctrl + B to bold your selected titles.

Converting the Dataset into a Table

- Select all your data.
- Click on the *Insert* tab
- In the _Tables_ group section, click on the Table icon.

Alternatively, you can use the Ctrl + T Keyboard shortcut after selecting your data. This helps us to easily filter and organize our data.

Filtering and Sorting

You can filter and sort your dataset to check for blanks and missing information.

*Filter*

Applying a filter to your data allows you to select a specific type of data you want to view in a given column. In our case, we want to view Blanks in the Item column.

- Select the data
- Click on the *Home* tab
- On the _Editing_ group section click on *Sort & Filter*
- From the drop-down, click on *Filter*
- A filter icon is displayed on the title column.

- Click on the Filter icon on the title of any column. In the drop-down list of commands, in the Fields section, uncheck _Select All_, scroll through the field list and select *Blanks*, and click *OK* to see only blank fields.

Note:

• To clear filters, click on the filter icon on the respective column, then click on Clear Filter from Item.
• You can also sort the field using the steps. However, you can click on Sort A to Z - Ascending or Sort Z to A - Descending order.
• You can also filter or sort custom, i.e., Filter by color or Sort by color, in case you have already applied conditional formatting.

Since our column has blanks, we need to count the blanks to know how many there are using the Excel function:

=COUNTBLANK(B2:B10001): there are 333 blank cells, which translates to around 3.33%. This will help us to understand the amount of data that we need to clean and how it will affect analysis or the level of bias. Below 5%, we will clean our data by replacing blanks with Unknown. We will do cleaning later.

Conditional Formatting

Conditional formatting highlights duplicate values, helping in spotting patterns or identifying anomalies.

Applying Conditional Formatting

- Select the columns or range to inspect; in our case, the  _Quantity_ Column.
- Navigate to the *Home* tab.
- On the *Styles* group, Click on _Conditional Formatting_
- Choose a criteria to conditionally format your column i.e. _Highlight Cells_, then Go to _Duplicate Values_, choose a color scheme and Click *OK*.
- All duplicate values in the column will be highlighted.

These are some of the basic tools in Excel we'll use while dealing with missing data, detecting outliers, duplicate detection, identifying inconsistent formats, irrelevant data, and detecting erroneous entries.

*1. Removing Duplicates*

Removing duplicates helps analysts to avoid redundancy, which can distort analysis, inflate counts, and skew findings that can lead to wrong and misguided business decisions.

To remove duplicates

- Select all data
- Click on the *Data* tab
- In the *Data Tools* section, click on the Remove Duplicates icon

- the fields from which you want to get rid of duplicates. In our case, we want to remove all matching records, so we'll select all fields.

- Click *OK*. All duplicates will be removed, with a pop-up dialog window indicating the total number of duplicates removed.

In our case, there were no duplicate records; thus, the message was "No duplicate values found."

*Handling Missing Values*

Missing values can result from human error, system limitations, incomplete forms, or data integration issues. All these can lead to inaccurate analyses, biased models, and misleading conclusions due to the variation resulting from missing data.

Excel provides multiple imputation techniques we can use to handle missing data. These techniques include, but are not limited to, replacing missing values with the mean, median, or mode. This depends on the type of data you are dealing with. For example, a data analyst replaces the product rating with an overall average of the product rating across all blanks in the product rating column to ensure consistent review data.

We can use the following techniques in Excel to handle missing data:

1. Filtering and Sorting to Find Blanks

You first have to find where missing values are.

Apply filters as discussed above on the item column to find blanks. Replace all blanks with the text value.

Steps

- Click on the filter icon on the *Item*column.
- Uncheck all Fields, then select only the *Blanks* field and click *OK*
- On the first cell of the *Item* column, type the text _Unknown_ and copy it down through the blank cells.

To Copy the Text

- Select the cell where you typed the replacement text("Unknown")
- Drag your pointer to the bottom right of the selected cell; your pointer turns into a dark cross at the tip of the bottom right corner.
- Click and drag the cross down the empty cells to copy the text into the blank cells
- Click and save your work once all blank cells are filled, and clear the filters.

Alternatively, you can use Find and Replace. To use Find and Replace:

- Undo the changes you have made and clear all filters first.
- Select the Item column
- Navigate to *Home* tab, on the *Editing* group click on _Find & Select_ 

- From the dropdown options, click on Replace
- In the Dialog box, leave the Find what: text box, leave it blank. We are finding blanks
- On the Replace with textbox enter "Unknown" text, and click Replace All to make the changes.

!

- A message box will be displayed showing how many blanks the changes were made to.

Also, you can use the IF() function to find and replace missing values in a column. The syntax is =IF(B2="", "Unknown", B2)

Explanation

• B2="": Cell referencing the value contained in it; also the condition to check if B2 is blank.
• Unknown: Value returned if B2="" is TRUE, that is, it is blank.
• B2: If B2="" is FALSE, it retains the value or text held in B2.

To achieve this, you need to create a blank column to the right of the item column, then enter the function in cell C2, copy and drag down the formula, then copy the values held in cell C2:C10001, then paste special (As values only) the values into cell B2. All the other text values will be replaced.

*Text Cleaning and Standardization*

Inconsistencies such as trailing whitespaces, unprintable characters, and mixed text cases are common in text-based data. However, Excel offers functions that we can use to correct these errors and mishaps.

What is an Excel Function?

A function in Excel is an inbuilt formula used to perform mathematical, logical, statistical, or financial operations in Excel. A function must begin with an equal sign.

In cleaning text data, we use functions such as:

a. TRIM()

Used to remove extra spaces that were typed into cells and fields.

In our dataset, we can apply the function to Item to remove any trailing or extra spaces.

We need a new column to do this, then later copy and paste special the values of the new column.

In Cell C2 of the new column, type the formula on it and press Enter, then copy it down the column to get the rest of the values.

=TRIM(B2)

b. CLEAN()

Used to remove any unprintable characters. Also requires a new column and later copying the values into the original column.

We are going to work on the Item column, then copy the values to the original column.

In cell C2 enter =CLEAN(B2)

Copy the formula down to auto-fill values.

c. LEFT(), RIGHT(), and MID()

• LEFT() is used to extract the specified number of characters from a text value starting from the left. I.e. Extracting the first three characters of the Transaction ID we use =LEFT(A2, 3) and we get "TXN"
• RIGHT() extracts the specified number of characters starting from the right. I.e., extracting the last three characters of the transaction ID in cell A2: =RIGHT(A2, 3) we get "124"

• MID() is used to extract a specified number of characters starting from a specified position. I.e. extacting the 4 characters of the Transaction ID starting position 2. =MID(A2,2,4) we get "N_99"

  d. UPPER(), PROPER(), and LOWER()

• UPPER() - Converts a text string into all uppercase.

• PROPER() - Converts a text string into proper case; that is, it capitalizes the first letter of each word. We will convert our Item column to proper case for consistency.

• LOWER() - Converts a text string into all lowercase.

To convert our Item column into proper case, we need another column, then copy the values back to the original column.

- Use this function in cell C2 `=PROPER(B2)`
- Copy it down through the column for all values.
- All the Item values have been converted into proper case.

Standardizing Dates

Dates may be formatted as:

- MM/DD/YYYY
- DD/MM/YYYY
- YYYY-MM-DD

And sometimes they may be misinterpreted depending on the local regional settings.

To format a date, you may use the Excel built-in date feature by following the steps below:

- Select the date column
- Go to *Home* tab, on the _Number_ group, from the drop down list choose _More numbers Formats_.
- From the dialog window, choose _Dates_ from the right section, then specify the date format you prefer and click *OK* to apply the changes.

We'll format the Transaction Date and change the column to Date format for consistency.

Alternatively, you can use the TEXT() function to format dates. The syntax is, e.g., =TEXT(H2, "MM/DD/YYYY), where H2 is a field containing a date.

Other date functions can help us work best with dates. I.e. NOW(), TODAY(), DATEDIF(), YEAR(),MONTH(), WEEK(), DAY()... You can research them.

e. CONCAT()

Used for joining two or more texts. For example: Joining the first and last name to make one name.

i.e. =CONCAT(A1," ", B1)

This is important for preparing data for standardized formatting, reporting, or creating lookups across datasets.

There are many more functions that can be used to handle text data.

*Handling Outliers*

Outliers are errors resulting from values that are much higher or lower than the expected range. For example: finding a person with an age of 223 in the age field.

The errors may result from human error, measurement inconsistencies, or valid but extreme occurrences.

We can use Filter & Sort to work around these inconsistencies. We can also use basic statistics to determine these errors.

Basic statistics: Mean, Standard Deviation, Median, and Mode

Mean and Standard deviation are data-driven methods to define what is considered a normal dataset.

AVERAGE() function is used to calculate the mean. i.e., calculating the average Quantity in our data.

=AVERAGE(C2:C10001) We get 3.

STDEV() is used to find the standard deviation of a sample. i.e the standard deviation of our Quantity column:

=STDEV(C2:C10001) we get 1.4

Values more than 2 or 3 standard deviations from the mean may be considered outliers.

MODE() and MEDIAN() functions are used to find the value with the highest frequency and the middle number, respectively.

For missing values, depending on the type of data you are working on. The mean, mode, or median can be used to fill in the missing values, considering that the data distribution is normal.

*Data Validation and Restriction*

Data validation ensures that data entered into a worksheet conforms to specific rules or formats, minimizing the risk of errors or inconsistencies. It is important before data collection is carried out. However, you can validate your data if already been collected to avoid future data entry errors.

For example: We are going to validate our Location field to only allow "In Store", "Take Away", or "Unknown".

To access data validation:

- Select the _Location_ column in our dataset.
- Click on the *Data* tab from our Ribbon
- Click on the validation icon.     

- From the dialog box, choose List as the validation criteria

- Enter the list of items, separated by commas, OR refer to a list range.
- Here is how it looks on the worksheet.

Error Alerts and Input Message

These are useful guides during data entry.

Input Message: A brief output that appears when the user selects the validated cell, offering guidance on the expected input.

Error Alert: A message that appears if the user tries to enter invalid data. Stop: blocks entry, Warning: Allows the entry but prompts the user, and Information: gives the user feedback without blocking.

These messages, if well designed they reduce confusion and improve the quality of the data.

*Pivot Tables*

Pivot tables are tools in Excel that we can use to summarize our data. They are important tools used during visualization to create charts.

To create a pivot table in Excel:

Select the data

• Click on *Insert_ tab, and from Tables group click o Pivot Table
• From the dialog box, choose where to enter your pivot table; choose a new sheet

• Click OK
• Select the fields you want to summarize using your Pivot Table.

Summary of the Complete Data Cleaning Process

• Selected all my data and converted it into a table. I used the shortcut Ctrl + T.
• Added BOLD to my column titles and increased their font size to 14.
• Used filters and sort to inspect my data. Accessed the dataset to find missing values, outliers, or any inconsistencies within the data.
• Found blanks, columns with ERROR, and UNKNOWN as their input. I used Find and Replace to fill the blanks with Unknown, and also replaced ERROR and UNKNOWN with Unknown for consistency.
• Quantity Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Secondly, I set the data type for the column to Number type, a whole number without a fraction part.
• Price Per Unit Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Converted the column to the Currency type with the United States Dollar sign as the currency prefix, formatted to 2 decimal places. From observation through sorting and filtering, using the item and its respective price per unit, all items add up to the same price for the individual item. I had to manually fill in the blanks for individual items with the correct price.
• Total Spent Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Converted the column to the Currency type with the United States Dollar sign as the currency prefix, formatted to 2 decimal places. From the observation through sorting and filtering, total spent was the product of Quantity and Price Per Unit. So, I had to recalculate the total spent on each transaction using the PRODUCT() function in a new column, then used copy and paste special to paste the new values into the Total Spent column.
• Recalculated the Quantity since some values were blank, yet they had the Price Per Unit and Total Spent filled. To get the Quantity used the formula =[@Total Spent]/[@Price Per Unit]. Then used Paste Special.
• Payment Method Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with Unknown. Validated the field as a list field with the following options: "Cash", "Credit Card", "Digital Wallet", and "Unknown".
• Location Column: Found blanks, columns with ERROR, and UNKNOWN as their input. Used Find and Replace to fill the blanks with Unknown, and also replaced ERROR and UNKNOWN with Unknown for consistency. Validated the field as a list type with only three fields: "In-Store", "Take Away", and "Unknown"
• Transaction Date Column: Found blanks, columns with ERROR, and UNKNOWN as their input replaced ERROR and UNKNOWN with Blanks for consistency. The blanks were untouched. Then converted the field to a date type with the format "DD/MM/YYYY".
• Inserted a pivot table summarizing the quantity of each item.
• Found the average quantity, price per unit, and total spent.
• Found the maximum and minimum price per unit.
• Found the Total sales using the SUM() function. i.e. =SUM(E:E)

These are the operations performed on the dataset.

Conclusion

We have learnt great and unique technical skills in data analysis. We have found how important data analysis is and the best way to carry it out. If standardized, results will be meaningful in helping stakeholders make informed decisions that drive growth. Data cleaning is the most time consuming in the whole process, but if done right, it will serve a great deal.