Due to recent versions of Excel allowing over 1 million records on a work sheet, we provide more Postcode data in this format than in any other including the latest release of the Postcode & Constituency lookup data. However, with around 2.5 million Postcode records spread across 3 work sheets, finding the specific Postcode you want and viewing the information associated with it may be frustrating.
You can perform a quick search in Excel by hitting CTRL > F and entering a Postcode which will find the row you want, but then the information is displayed in columns across the page making it difficult to read.
So this short tutorial shows how you can find a Postcode using Excel's built-in data form which also makes reading the information associated with the Postcode much easier.
Setting Up The Built In Data Form
Unfortunately the Data Form in Excel is usually hidden and you have to add it to your Quick Access Toolbar before you can use it. The following steps will show you how to do this but once it is done, the Data Form will be available whenever you use Excel in future.
Click the little arrow at the top of Excel which is called Customise Quick Access Toolbar and click More Commands.
When you click More Comments, you will see the dialogue box below:
Click the left hand drop down list under Choose commands from and select the option highlighted in the image above: Commands Not in the Ribbon. The list box will then change to that shown below.
In the list to the left, find and click on the item call Form and then click the Add button in the middle. The box on the right should then contain the Form item as shown below.
When you click OK in the bottom right of the dialogue, you will return to Excel and will see the Form button in your Quick Access Toolbar at the top as highlighted in the image below.
Your Form button is now ready to use and the next steps will show you how!
Using The Built In Data Form
Now you have access to the Excel Data Form, the following steps show how to use it through illustration with our Postcode & Constituency Excel spreadsheet. In this spreadsheet we have all UK Postcodes and Parliamentary information spread across 3 work sheets (because each sheet is limited to approximately 1 million rows) as follows:
- AB-LN for all current Postcodes in the respective Postcode Areas.
- LS-ZE for all current Postcodes in the respective Postcode Areas.
- Retired for all Postcodes which are no longer in use by Royal Mail.
For our example, we want to find the Parliamentary information for Postcode PH7 3RP and as the form only works on a sheet (rather than the entire workbook) we need to click on the LS-ZE tab which contains all the Postcodes for the PH Postcode Area.
Once there, select any cell and then click your Form button in the Quick Access Toolbar to show the dialogue below.
Note that all the data elements are greyed out. This is because the spread sheet is protected by default to avoid accidental deletion or over-writing of the information it contains.
Click on the Criteria button to the right and you will see the dialogue below. Note that the data fields are now white allowing you to type into them. In the field for Postcode type PH7 3RP and hit Enter on your keyboard.
You will now see the form below displaying all the Parliamentary Constituency information associated with PH7 3RP in a nice and easy to read format.
We hope this quick introduction to the Excel Built-in Data Form proves to be useful but if you have any questions or suggestions we would love to hear from you.