In data analysis, there are many tools that you can work with, but Excel may be the most important software around among them in order to act quickly on data. For example, if your data is in CSV or XML files, you can easily open them in Excel and start work on them. After you get your data into Excel, you can shape your rows and columns if needed and clean your data from unexpected or unwanted values by using some features of Excel. Of course, Excel has more to offer when we speak of data and besides cleaning data, you can also make data validation on it. Before going any further, if you haven’t read the introductory section of this series, you may want to read it first from this link and then come back and continue with this part.
So, long story short, we have written a very quick description of Excel in the eye of data analysis but let’s look at its more formal definition for the sake of being more accurate about giving a definition.
What Is Excel?
“Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1–2–3 as the industry standard for spreadsheets. Excel forms part of the Microsoft Office suite of software.”
Above definition comes from Wikipedia and gives us a more complete, formal and general understanding about Excel.
How Do We Use Excel in Data Validation?
There are lots of ways of using Excel for data validation, but we focused on XML — XSD relationship and VBA about this. In this series’ next chapters, you are going to see a simple example of how being used XML — XSD relationship through Excel. After that, we are going to examine how we can use VBA to check our data. You can also check one of Microsoft’s support pages about this topic which is “Apply data validation to cells”. In this Microsoft’s page, there is also an example workbook that you can try out your learnings and strengthen your knowledge.
Note: You can also check this blog post and some other interesting topics on my personal web page.
Next: Step by Step: How to Prepare A Test Data Set in Excel?
