How to Create a Default Value in Excel

• Tweet

• Tweet

When you want to input a lot of data very quickly it helps to be prompted with defaults. For example if you are adding lots of records for the same company it would slow you down to have to keep typing out the whole thing over and over. Even better, if most of your records are identical other than some minor differences you can give yourself a head start by pre-populating most of the required data.

It will be much clearer when we go through an example. Picture a system where we have to enter address details but 99% of our addresses will be for people living in the USA. We will make the spreadsheet populate the country so we can focus on the country only in the rare non-USA exceptions.

Once we have entered our address data we can have a macro that populates the Country cells

In this case we will go with a button which when pressed will fill in the cells, but obviously there are other options available.

Draw a button and the user will know what to do when they want to complete the form. Filling out these cells when the button is clicked is really simple.

Just assign the following macro to the button in the dialog that appears and you are good to go.

Automatically Filling Cells without a Button

What if you don’t want the user to have to click? Can we automatically fill any blank cells? What would be neat is if the spreadsheet would automatically enter USA whenever we erase the contents of a cell. This way an empty cell will always revert to the “USA” value.

Well we can do this using the Microsoft Excel events system.

Excel provides us with a Worksheet_Change event. Any code we enter into this Sub routine will execute as the user enters data. Pretty cool, huh?

There is a danger though with altering the spreadsheet data using such an event. A possibility exists where the spreadsheet changes and changes in a never ending loop, because every time you change the spreadsheet then Excel will notice the change and fire up the change event all over again!

So to avoid this unfortunate potential we can turn off the event while we do our thing, we use Application.EnableEvents = False to switch it off, then of course remember to set it back to True when we are done to turn it back on again.

Summary

Obviously we kept the example simple for the sake of clarity but I am sure you can imagine scenarios where being able to “guess” the required data and pre-populate using defaults would save your users a great deal of time. How might you use this in your projects?

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter

Incoming search terms: