Protect the data integrity and quality of your Excel sheets

A lot of us are working in Excel on a close to daily basis. But do we know how to use Excel in the most efficient way, avoiding common human errors and protecting the quality of our work sheets?

Johan Lemeire from Xylos gave us some great small tips to improve our Excel sheets immediately. Here are some things you can do.

When you are formatting rows and columns, you are actually formatting thousands of cells, although you only need a fraction of what you just formatted. This has an impact on the size of your file. So next time you are formatting, use the option ‘Clean excess cell formatting’. You end up with a file were only the necessary cells are formatted, everything else is clean!

This method is quite new, so it might be possible that your version of Excel doesn’t have this function yet (but trust us, it’s coming!). Instead of Vlookup, try using Xlookup for the same thing with a better result. Newer functions in Excel such as Xlookup are more flexible and dynamic.

How it works: Vlookup tends to look at the column you selected in the sheet (A, B,…). When you move this column, your results will vary (and usually we don’t want this to happen!). Xlookup looks at the range or an exact value, even if you move the content around.

You know the story: You receive a csv file, import it in Excel and you are left with a messy looking sheet. Dates aren’t interpreted as dates anymore, currency doesn’t appear where it should, you wanted all capitals in your first column,… And now you’ll have to spend your time cleaning the whole file.

You can save a lot of time using Power Query. This transforms your csv file before importing it in Excel. You can make sure all of the above problems are tackled up front. Possibilities are almost endless.

Do you want to know what’s new in Excel (or other Office tools)? Take a look at the Excel blog for all the latest updates. Most Office programs have a similar blog, so you can stay updated on other programs as well.

In Excel, you can also see what’s new: click on ‘File and go to ‘Account’. Then, click on the ‘What’s new’ button to see the latest additions to your version.

Extra tip: not everyone automatically has the most updated version of your Office tools. Look at your version (again, you click on ‘File’ and on ‘Account’) to see when your version is updated. This can be monthly, bi-annually,… When you see ‘Current’, you can trust you have the latest possible version at any time!