MAKING EXCEL WORK FOR YOU

Heading

Working with data in Excel

Data Layout in Microsoft Excel

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.

Titles/Headers

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!

Adding Data to the Database

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

Read more...

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.


 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy