Data is the mainstay of any sort of analysis that needs performance in Excel. And when it comes to data, there are loads of challenges that one can face – be it Spelling Errors, tenacious irregular spaces, unsolicited precedes, in appropriate cases, unrequired characters & irregular cell merge, all these in an excel spreadsheet give us all a lot of stress at times. On top of this, there are more such issues which one can face while performing analysis on any data.

In this post, I will be sharing few very simple ways, which one may use to sanitize data in excel-

#1. Getting Rid of Extra Spaces

It is highly difficult for anyone to identify extra spaces in excel spreadsheet. While some of you might be able to identify extra spaces in between text or number but it is most difficult to identify extra spaces after text or number. Thankfully, there is a simple & clean way to get rid of this issue in excel. Just Apply TRIM Function on the data where you see extra spaces and it will remove all such spaces.

Function - =TRIM(Text)

#2. Converting Number stored as Text back to Number

This is another very common issue that one faces, especially when data is imported from a database or any application, where number gets converted into text format due to which user can not perform any numeric calculation or analysis. The simplest way to convert the number from text format to number format is-

a.    Select the data which is to be converted to Number Format

b.    Press Ctrl +1

c.     A “Format Cell” Dialogue box will pop up.

d.    Go to “Number” option in Number Tab

e.    Press “OK”

#3. Removing unnecessary blanks

Blank Cells can really cause a lot of confusion if they are not handled at the very beginning. I am sure even you have faced problem caused by blank cells in data. Here is a smart way to identify all blank cells at one go and replace them with any character or value.

a.    Select the data to be analyzed.

b.    Press Ctrl + G

c.     Click on Special Button at left bottom.

d.    Select “Blank” option in Go To Special & Press “OK”.

This will highlight all blank cells in data. Then it is up to you to replace them with any value like “Not Applicable” or “Nil”. Then, press Ctrl + Enter.

#4. Merging Columns

Another very common task that I perform after extracting data from an external database is to merge content in two or more columns into one. Imagine a scenario where you have downloaded an employee master, wherein employee first name & the last name is coming in two different column & now you want to identify cases where two employees have the same name. For this, you will be required to merge both these columns to generate a new column containing employee first & Last Name. Following are most used ways to merge cells or columns-

a.    Use CONCATENATE Function to perform this task.

Function - =CONCATENATE(First Name, “ “, Last Name)

b.    Use & for merging two cells

Function - =First Name&” “&Last Name

c.     Use CONCAT Function to perform this task.

Function - =CONCAT(First Name, “ “, Last Name)

#5. Splitting text into Columns

Just like merging text of two or more columns into one, at times I am also required to split text of one column into two or more columns. Imagine a situation where you have downloaded an employee master, wherein employee last & the first name is coming in one column separated by a blank or comma & you want it to be in order where employee first name is followed by his/her last name. For this, you will be required to split text of one column to two columns using text to column function & then merge the two columns in desired order by using above-stated functions as stated in point 4.

Use Text to Column Function to split text. This is one of my favorite function in excel and is very handy at times. So, without wasting time, let us understand its use-

i.              Select the cell or column whose content you want to split

ii.             Go to Data >> Text to Columns

iii.            A Text to Columns Wizard will open. Click Delimited option & press Next

iv.           Check Comma, and clear the rest of the boxes, or check Comma and Space if that is how your text is split (Woods, Alan, with a comma and space between the names). You can see a preview of your data in the Data preview window. Click Next

v.             In this step, select the format for your new columns. Press Finish.

#6. Changing the Case of Text

How often we come across data where text cases are improper & irregular. Using excel case function, one can quickly convert text to any of these case formats.

LOWER Function -Converts all uppercase letters in a text string to lowercase letters

Function – =LOWER(Text)

UPPER Function -Converts all lowercase letters in a text string to uppercase letters

Function – =UPPER(Text)

PROPER Function - Capitalizes the first letter in a text string and any other letters in the text that follows any character other than a letter. Converts all other letters to lowercase letters.

Function – =PROPER(Text)

There are many other techniques to clean data in Excel, Which I will be sharing in my future posts.

Keeping Watching this space for more interesting tips & techniques…

Happy Learning Folks!!!

Also Read: Excel Formulas that Every Excel User Should Know (Click Here) ...

Hi Everyone!

Welcome, the topic of this post is “How Data Analytics is Proving to be a Game-changer for Internal Audit”. All of us, who have been performing internal audits or are getting it done, would vouch for the fact that it has evolved enormously over the years. Internal audit, which was perceived as a process to review financial records in a more detailed manner, when compared with statutory audit, has now evolved with times, to play a more strategic role by laying down a cohesive approach towards risk management. With the evolution, there has been a clear shift from a document-based audit to a data-based audit, whereby internal audit function is deploying modern technologies and techniques like data analytics to create more value for businesses.

Source: www.pexels.com

When we talk about data, it is something which is everywhere, from a mouse click to a tap on the mobile app, to a phone call, to a punch-in at office gate. The reason for the availability of such huge volume of data can be attributed to the change in the mindset of the consumers & easy accessibility of technology to them. Consumers, where some years ago, were not willing to share their personal details, are now with a change in their aspirations & needs not worried about sharing them. Thus, giving birth to the huge volume of data, which businesses are using to understand the customer need and the changes in their needs. Taking advantage of the availability of such huge volume of data & applying data analytics, internal audit function can create more value for businesses than ever before by providing valuable business insights, a greater degree of assurance and predicting emerging risks. Given below are some of the advantages that internal audit function can reap by using data analytics- 

Increase in Effectiveness of Internal Audit.

By using data analytics, internal auditors can audit the entire population instead of auditing a sample of transactions to form a judgment. Data analytics also empowers auditor to conduct several audit checks just by applying a program to generate exceptions which auditor needs to check in detail.

Enhancing the Efficiency of Audit Function.

Data Analytics also help in increasing efficiency of an audit function, as an internal auditor can always re-use the same data analytics script for periodical reviews, saving a lot of time & effort on the part of the auditor. Auditor can deploy the same in some other areas, resulting in higher effectiveness as well.

Mitigation of Risk & Fraud Detection.

Data Analytics also enables internal audit function to identify trends, that are appearing in datasets over a period of time indicating a potential threat to the organization. Data analytics also enables auditors to identify & uncover any fraudulent activities that are happening in the organization. Thus, audit function can create a greater value for the organization, by using data analytics, whereby they can ensure the organization is prepared for emerging risks and is secure against any type of fraud.

Greater Assurance.

Data Analytics also helps in delivering a higher sense of assurance to all stakeholders in relation to operations of the organization and ensuring that company does have proper controls in place to mitigate emerging risks in the organization. The higher assurance is achieved by the fact that internal audit can virtually review entire population to identify risks and frauds, by using data analytics.

Greater Focus on Strategic Risk

By deploying Data Analytics & combining it with machine learning, internal audit function can automate the routine audit checks of similar transactions and can shift their focus towards the strategic risks that might challenge organizations in future.

CASE- STUDY

Given below is the case study where internal audit function deployed Data Analytics to identify potential risk, which otherwise would have been a real daunting task for the auditors-

Background

ABC Private Limited was a big FMCG company having headquarters in New Delhi & 5 plants across India employing more than 3000 people.

Majority of staff strength comprised of workers & supervisors. The hiring was done by each Factory Department respectively.

The company had biometric devices installed across all locations to monitor daily in & out time of each personnel.

At each month end, HR Department generated the attendance report from biometric software and basis such attendance report, payroll was prepared.

Scope

Internal audit function was required to conduct payroll audit to ensure that –

• Employees are getting paid for the actual number of days worked by them.

• There are no ghost employees in the system.

• All statutory dues pertaining to employees are duly deducted & deposited.

Approach

Internal audit team deployed Data Analytics to -

• Ensure all employees were paid in accordance with the biometric record by plotting paid days as per biometric record and comparing it with paid days as per salary sheet.

• Review employee master to highlight missing key data points in it like PAN Number, Bank Account Number, Aadhar Number, Date of Birth etc.

• Identify any duplicate employees being created in the system and to check for duplicate payments being made to a single person.

• Review of employee records to check for any incorrect data being recorded in the system.

Results

Following observations were noted –

• In case of 17 employees, joining date was a Sunday (weekly off in the company), which was wrongly entered in the system resulting in the excess pay of Rs. 113,000.

• 78 employees were noted who were not recording attendance in Biometric System on a regular basis. Out of the same, 50 employee records were not properly filed in ERP & out of this, 23 employees were found to be fake.

• Excess overtime amounting to Rs. 337,000 was paid to the employees due to wrong calculation of overtime hours in case of 327 employees.

• The scope of process improvement in human resource management was identified to reduce fraudulent records and transactions.

While Data Analytics offer abundant possibilities in internal audit function, one should first understand the scope of using it. Further, like any other business function, internal audit’s forte lies in the adoption of new techniques & the use of Data Analytics is no different. The scope of using Data Analytics in internal audit is further widened when it is combined with Machine Learning to automate routine procedures.