Utilize Advanced Features

Excel offers many other features that you can use to work with your data.

These include Templates and Data Validation. Links to more help on each of these features is available at the bottom of the page.

Templates

If you often use the same workbook layout, you can save it as a template and use it again in the future.

To use templates, we first have to set the default personal templates location.

Click File then click Options
Click the Save tab
Navigate to the Save workbooks section
Navigate to the Default personal templates location box
Type this file path: C:\Users\[UserName]\Documents\Custom Office Templates
- Replace [User Name] with your user name
Click OK

Unless you want to change the default personal templates location, you will only need to set this once.

Excel will then create a new workbook based on this template. Be sure to Save this new workbook.

Data validation in Excel controls what you can enter into a cell.

These first three steps are the same for any type of data validation.

Click the first cell where you want to add data validation
- Do not select the Header cell
Hold down the Shift key on your keyboard to select all cells where you want to add data validation
Navigate to the Data tab and the Data Tools section
Click Data Validation

Follow the instructions below to learn how to:

Let’s Navigate to Sheet 1 to practice data validation.

Click Cell G1 and rename it
- Example Number of Mentees
Click Cell G2, hold down the Shift key on your keyboard, and click Cell G23 to highlight the cell(s) where you want to add data validation
Navigate to the Data tab and the Data Tools section
Click Data Validation
In the Settings tab:
- Set the Allow drop-down box to Whole number
- Set the Data drop-down box to between
- Enter the allowed Minimum and Maximum range of whole numbers
  - For example, Minimum: 0 and Maximum: 5
Click OK

Now, let’s test our data validation

Excel let us enter this value since it is a whole number between 0 and 5.

If you try to enter a number outside the specified range, you will see an error message.

Microsoft Excel Data Validation Error Message

Click the first cell where you want to add data validation
- Do not select the Header cell
Hold down the Shift key on your keyboard to select all cells where you want to add data validation
Navigate to the Data tab and the Data Tools section
Click Data Validation
In the Settings tab:
- Set the Allow drop-down box to Date
- Set the Data drop-down box to greater than or equal to
- Enter the allowed Start date
  - For example, Start date: 01/01/1999
Click OK

Now, if you try to enter a date before 01/01/1999, you will see an error message.

Click the first cell where you want to add data validation
- Do not select the Header cell
Hold down the Shift key on your keyboard to select all cells where you want to add data validation
Navigate to the Data tab and the Data Tools section
Click Data Validation
In the Settings tab:
- Set the Allow drop-down box to List
- In the Source box, type your data values and separate them with commas without spaces
  - For example, Yes,No,Maybe
Click to check the In-cell dropdown box
Click OK

Now, when you want to enter text, you can only select from the allowed options.

Click and drag to highlight the cell(s) where you want to remove data validation
Navigate to the Data tab and the Data Tools section
Click Data Validation
Click Clear All
Click OK

Excel also gives you the option to specify the input message or error alert to make is less generic.

Highlight the cells where you want to specify the error alert
Navigate to the Data tab and the Data Tools section
Click Data Validation
Navigate to the Error Alert tab
Check the box next to Show error alert after invalid data is entered
Under the Style drop-down box, select Stop, Warning, or Information to change the icon
Add a title and error message that describes the data validation requirements

To learn more about data validation, open Excel and navigate to the Data tab and the Data Tools section.