how to validate cell values in excel

Estimated reading time: 2 minutes

Validating cells in Excel quickly – how to do it easily!
Are you working with large spreadsheets and looking to quickly at data validation exercise to save you time?

The aim would be to run your code and test it against some predefined rules you or your data analyst would have written to make sure it brings back the expected checks.

If you look at the below, this is the final output of this video, highlighting two cells that are over budget based on the companies predefined budget.

The structure of this code can be broken down into the following steps:

• Read in the excel file, see a previous example here How to import data into excel
•  Run the first function,  checks if the spreadsheet cell value is over or under budget.
•  Run the second function that takes the value from the first function and applies the colour red to the cell if it is over budget.

 

Finally

You can expand this code to incorporate more functionality, such as:

• Change the colour of the cells, to have multiple colours returned.
•  Update the two functions to include more business rules.
•  You could check if the file is empty before processing as shown here How to check if a file is empty

Please subscribe to our YouTube channel, the button is the right-hand side of the page if you would like to see more like these.

Data Analytics Ireland