Introduction to Excel and how we use the tool for Real World Data Analytics

In the real world, #Excel is a Microsoft Office tool that is used for basic data entry, analysis and modelling of the data.

On basic data entry, Excel is used to input data in cells. The types of data that can be input include texts, numbers, dates, special characters, and text strings that include numeric, special characters and numbers. Input of data can happen inside the cell or at the formular bar. We can also change font type and font size, bold (CTRL+B) and italicized (CTRL+I), highlight and fill values in the cells, change the background color, align the cells, wrap texts and merge cells.

On data analysis, we have data cleaning and data validation.

Data cleaning is used to check if the data is consistent throughout the worksheet. Here, we check on any missing values, duplicates, blanks, and inconsistent data types.
Where there are missing values or blanks, we replace (CTRL+H) the values using pseudo blanks i.e. Null, Unknown, Not Provided, N/A. Please note that on each category, we should harmonize the data by using the same pseudo blanks.
Where there are duplicates, we use the conditional formatting tools to highlight the duplicate cells criteria.
Where there are inconsistent data types, the reasons may be inconsistent date format or numbers that have been input as texts.
A quick way that I learnt in class to know about inconsistencies, for date and number, data is aligned on the right, and for texts, data is aligned to the left.

Data Validation is a tool within #Excel that restricts what users can type in a cell to prevent inconsistency in the data input. The way to use this tool is to select the column the user wants to validate, go to Data Tab, then Data Validation tool. On the Validation Criteria, click on the dropdown under allow, select list, Under the source, input the data you may want validated separated by commas.
Here we need to be careful not to have typing errors. If any values do not match the restricted value, the data will have an error.
Remember to check the Ignore blanks and the In-Cell dropdown checkboxes.

There are basic functions which are used in #Excel

• Upper Function – Convert texts to upper case =UPPER(A2) where A2 is reference to the cell.
• Lower Function – Convert texts to lower case =LOWER(A2)
• Proper Function – Capitalizes the first letter then the rest are small letters =Proper(A2)
• Trim Function – Removed extra spaces in the cell. The spaces can be leading (before the text) or trailing (after the text) =TRIM(A2)
• Length Function- Find the number of characters in a string =LEN(A2)
• Left Function – Extract the number of characters from the left most part of a string i.e. =LEFT(A2,3) extracts the 3 left most characters of the text
• Right Function - Extract the number of characters from the right most part of a string i.e. =RIGHT(A2,3) extracts the 3 right most characters of the text
• Mid Function – Extract the number of characters from the start of the character =MID(A2,2,4) Extracts 4 characters from the second character of the string in the cell.
• Concatenate – Combines text strings i.e. =CONCATENATE (A2," “, B2) will return the text string for A2, then Space denoted by “ “, and B2. Thus, the result will be A2 B2.

Calculation Functions

• Addition Function – Brings the sum value of the selected cells. =A2+B2
• Subtraction Function – Subtracts the values of the selected cells =A2-B2
• Multiplication Function – Multiplies the values of the selected cells =A2*B2
• Division Function – Divided the values of the selected cells. =A2/B2
• Exponent Function – Raises the value of the selected cells to the power of n =A2^3 raises to the power of 3.
• Average Function – This function returns the arithmetic means of the values =AVERAGE(J2:J21)
• Maximum function – This function brings the maximum value of the numbers in the range. =MAX(J2:J21)
• Minimum Function – This function brings the minimum values of the numbers in the range. =MIN(J2:J22)
• Count Function – This function brings the number of records in the range.** =COUNT(J2:J21)** -_ Count IF function_- This function beings the number of records meeting a certain criterion. Example, if we want to know how many students are in Nairobi, the function is =COUNTIF(F2:F21, "Nairobi")
• Sum IF Function – Provides the sum of the cells meeting a certain criterion. Example, if we want to know the total fee paid by students from Nairobi, the function is =SUMIF(F2:F21,"Nairobi",J2:J21) F Column being the city the students are from and J Column is the fees paid per student.

Date and Time Function

• Today Function – Used to return the current date =TODAY( )
• Now Function – Used to return the current date and time =NOW( )
• Year Function – Used to Extract the year from a date =YEAR( )
• Month Function – Used to extract the month from a date =MONTH( )
• Day Function – Used the extract the day from a date =DAY( )
• DatedIF Function – Used to Calculate difference between 2 dates. Example, if we look for difference in days, months or years between 23/03/2025 (M) and 06/04/2026, (L) for days =DATEDIF(M11,L11,"d") 379 Days, months =DATEDIF(M11,L11,"m") 12 Months, years =DATEDIF(M11,L11,"y") 1 Year.
• Network Days- Used to calculate the difference between 2 dates excluding weekends. Example, , if we look for difference in days, excluding weekends between 23/03/2025 (M) and 06/04/2026, (L) =NETWORKDAYS(M11,L11) 279 Days.

In the real world, we use data analysis and interpretation in the following fields;

Accounting and Finance
Excel and data analysis is used in budgeting, calculating sales, cost of sales, profit and loss, forecasting and shareholders are able to make informed decisions based on metrics.

Project management
Excel and data analysis is used to allocate tasks, track the progress of the tasks, track the costs allocated to the project and analyzing against the budget to ensure that they stay on course.

Health Management
Excel and data analysis is used to track a number of metrics including, the number of patients per region, country. Track the number of births and deaths, track the number of vaccines and immunizations given per given period, and track the impact of a program exercised in a given area.

Sales and Marketing
Excel and data analysis is used to track the consumer insights, forecast the sales in a given period, conduct market analytics to evaluate the impact of a marketing campaign, analyze the trends at a given time, analyze the return on investment of the campaigns that will enable the marketing team to optimize their campaigns.

With these functions, excel is therefore a powerful tool used for analysis. This makes it easier to interpret the information and what the numbers are saying and make conclusive decisions. In as much as I use excel from time to time, this learning program has deepened my learning curve, and I appreciate that use of excel needs constant practice to clean and validate the data.
The exposure to other analysis tools in Microsoft Excel has shown me that there is still so much to learn to make sense of the numbers and thereafter interpret these numbers in such a way that the shareholders are able to comprehend and make decisions based on these insights.