Microsoft Excel is a powerful data entry and analysis tool. With Excel, employees can easily enter data and share it with their colleagues. However, over time the quantity of data can become overwhelming, and making sense of the data can be even more challenging. This may be where you need to use Excel data services to collate and use data effectively, here are just a few tips to help you along the way.
Improving the Quality of Entered Data
You can improve the quality of the data entered into your Excel spreadsheets by using the data validation tools to create rules around dates, times and numbers. For example, you may wish to capture birth date information. For the sake of this example, only dates between 1/1/1900 and 31/12/2020 are valid dates.
To set up a date-based data validation rule
- First highlight the cells or column which should follow the rule.
- Click the “Data” tab in the menu bar then click “Data Validation”.
- On the “Settings” tab
- In the “Allow” field, select “Date”
- In the “Data” field, select “between”
- In the “Start date” field, enter 1/1/1900
- In the “End date” field, enter 31/12/2020
- Click the “Error Alert” tab, and enter a “Title” and “Error message”
- Click “OK”
Try and enter an invalid date into one of the cells which has the rule, and you will see an error message, such as “Invalid Date – Please enter a date between 1/1/1900 and 31/12/2020” and your options will be to retry or cancel your data entry.
Analyzing existing data
Many Excel specialists in London will be able to help analyse your data directly from your spreadsheets and then output it in a way that suits your business. Here’s how to make use of Excel’s data validation tools and create rules to help you extract data.
You can analyze existing data as well using the data validation tools. You can identify cases where the data falls outside your desired rules by using the “Circle Invalid Data” functionality. Begin by defining the rule exactly as shown above, but don’t enter any information in the “Error Alert” tab.
Instead, just click “OK” after defining the rule then click the drop-down arrow next to “Data Validation.” Select the “Circle Invalid Data” option, and Excel will oblige by drawing a red circle around any cells containing data which does not obey the defined rules.
If you correct the data Excel will remove the circle automatically. You can also click the drop-down arrow next to “Data Validation” and select “Clear validation circles” and Excel will remove the circles for you.
You can define rules for multiple columns, then highlight all of your data (or press Ctrl-A), then choose “Circle Invalid Data” and Excel will circle all the cells which contain invalid data.