Then click on the Table Name box and give your table a sensible name with no spaces i.e. If you’ve never used the Excel table feature before, you’re missing out! Excel tables are essential for dealing with large, complicated spreadsheets and help tremendously when dealing with Excel add-ins such as Power Query and Power Pivot.īegin by selecting your data set (Ctrl + A) and then press Ctrl + T to turn the data into an Excel table.
MAC EXCEL 2013 DATA VALIDATION HOW TO
Here’s how to do this (file available via the link below to follow along):
For example, if your company is currently selling to Australia, Thailand, New Zealand and China, you can enter these values as a list into one section of your workbook, and then use Data Validation to prevent misspelled and other variants of these country names from being entered into a cell.ĭata Validation is a great tool, but what happens next month if your company starts selling to Indonesia and Russia? How do you automatically extend data validation into subsequent rows of your data entry table to avoid errors? The best way to solve this problem is with dynamic ranges and tables. If someone enters “United States of America” in a cell, “United States of America ” (extra space after “United” and “America”) in another cell, and then wishes to use “United States of America” as a formula criteria, functions such as SUMIFS and VLOOKUP won’t work properly as “United States of America” was not entered consistently throughout each area of the workbook.Ī great way of preventing this problem is to restrict the values that can be inputted into a cell via Data Validation. Many spreadsheets I’ve come across have a common problem – inconsistent data entry. The answer to these common questions may surprise you. Why is Excel returning an error message? Why doesn’t my formula work? What did I do wrong?