Dennis demonstrates how to structure city, state, zip, and name entries; avoid multiple dates; and keep titles in a single row.
- [Instructor] Here's a worksheet called Structure Data,…is there anything wrong with it?…In a certain sense, no.…The data might all be accurate,…as we look at columns A, B, C, etc.…We've got accurate data here,…but are we using Excel in the best way possible…to avoid errors?…No, we're not, and for a variety of reasons.…Suppose this list, which could grow,…and could be quite large eventually,…suppose we need to sort it by column A.…We can't sort by last with the data in that order.…Now, there are ways to restructure the data.…
And by the way,…in the Excel course on Cleaning Up Your Excel Data,…you'll find lots of techniques for doing that.…But our focus here,…is on how the data has been entered,…and how it should have been entered.…It would be better if those entries in column A,…were last name comma space first name,…or possibly even split the names into separate column.…Column B, another problem, and again,…the larger the list, the bigger the problem.…We cannot sort this list by state,…we cannot sort it by zip code.…
Dennis begins with the basics—how to display data so that errors can be easily spotted. Next, he offers handy tips to ensure data is entered correctly the first time, using the AutoFill feature and using AutoCorrect shortcut codes for frequently used entries. Dennis provides easy ways to validate your data, which is particularly helpful when multiple team members are contributing to the same spreadsheet. This includes restricting the data that can appear in a spreadsheet by setting value limits, pre-populating data with drop-down lists, and other methods. He also shows how to avoid mistakes in formulas, how to hide data that doesn't need to be seen, and how to use workbook protection to prevent errors, and more.
- Streamlining data entry steps
- Using Excel shortcuts and automation features
- Protecting worksheets and workbooks
- Validating data
- Basing entries on formulas
- Detecting errors in formulas
- Using Range Names
- Finding mistakes in large, complex spreadsheets