MAKING EXCEL WORK FOR YOU
Microsoft Excel can be used as a Database and if you take a close look you will find specific Database Functions such as DGet, Dsum etc. Add to this PivotTables, the SubTotal Function, Sumif ,CountIf etc and you can build quite powerful databases in Excel. In addition the later versions have the powerful Table feature. So when you're ready to keep track of contacts, sales, expenditures, or whatever it is you're using Excel for, you will find plenty of tools to work with. However, to use these tools the data must be laid out correctly so that it organises what you're tracking in such a way that you can easily manipulate the data, create formulas to work with the data, or even export it to various formats and link it into existing applications (i.e., Database Applications). Below are some guidelines to follow to help keep your data nice, neat, and easy to work with: Keep your data in one continuous block. This means that you've got field names across the top row, and data starting directly underneath (row 2). Don't skip any rows in order to "break up" the data (you can easily use colours instead), and don't leave blank or unnecessary columns cluttering up your sheet. If you don't use it, get rid of it. Organized data is happy data.
It's best to use a header row and a good idea is leave clear the top two or three rows above the header, and then keep your data underneath.
Keep your data formats right.
Never use apostrophes in front of numbers or text. Using the apostrophe tells Excel to store whatever it is you've typed as text, and why would you want numbers stored as text? You don't. If you need leading zeros, you can use a custom format to accomplish it. If you need to sort numbers stored as text, they will sort incorrectly. By following those these simple rules, you'll be on the fast track to easy data analysis and manipulation!
Data can be added directly to the Database by a user. You can control what they enter using Data Validation to perhaps allow only Numbers or maybe dates, build a list of choices using Data Validation’s list feature. If your data is too big to manage, and you constantly have to scroll back and forward just to enter data, then a Data Form could make your life easier. To see what a Data Form is, we'll construct a simple spreadsheet.
Simplify adding data to your Excel Database
Using this method you can prevent the user from changing or deleting the entries by hiding the database worksheet from view.
Adding the data will require some simple VBA code.
Data can be added directly to the Database by a user. You can control what they enter using Data Validation to perhaps allow only Numbers or maybe dates, build a list of choices using Data Validation’s list feature. You can even create a worksheet to act as an input form so that the user can enter data one lineat a time.
Use the Excel DataForm
In earlier versions of Excel the DataForm was displayed simply by opening the Data menu then clicking Form. In later versions the command has to be added manually before you can display the DataForm
Not many users realise that Excel has an in-built Data Entry Form for adding and working with data that is set up correctly.
Basically the DataForm is the same in all current versions of Excel. However, accessing it changed with the introduction of Excel 2007.
© Roy Cox 2005 to 2013
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|