|
Using Excel as a Database and Professional Excel Help |
Hiring an Excel Professional may well be necessary if you need help with Excel, cannot find a workable solution in reasonable time and the problem is of significant importance to the business. There are many common Excel problems and issues that may indicate help is required.
The "Excel Database" - Using Excel as a Database
Excel is of course great for analysing data and working out complex calculations, but it is also a great way to get started with managing information like a database. However while this is great to start with, down the line things can get complicated. Often issues stem from Excel being used as a database. Issues include:
- information and formulas being too easy to change or delete
- duplication of information being easier than with a database
- finding information is more difficult
- large amounts of information make a spreadsheet very slow to open, close and use
- the information is not structured as well as in a database, where rules governing relationships are enforced
Simply put, for handling large anounds of related information especially for multiple people to access a database should be used, and for smaller scale data management tasks with a single user a spreadsheet is fine.
Complex Formulas
Excel formulas can become very long and unwieldy. This is often a sign that what you are trying to do is either could be done in a simpler way (your approach is wrong) or is simply not suitable for Excel (maybe better managed with a database). Look into lookups: VLookup and HLookup, these formula functions can help and if you're not using them it may well be worth reading up, they're quite simple. They allow you to "lookup" values in a table (in another worksheet maybe), thereby abstracting some of the complication and possible making your formulas simpler. An Excel professional can help you with this.
Document Production
A common Excel problem, or requirement, is to produce a document (maybe a report or quotation) from the data in an Excel spreadsheet. The way most people do this unless they are very technical or programmers is to copy and paste the information into a new special worksheet or to a Word document. This is OK but quite slow and a bit manual and error-prone. A better solution is to have a new button on the toolbar called e.g. "Create Order" which when pressed pops up a screen containing pre-filled boxes and drop down lists etc to select the information (such as customer address, quantities, items, whatever) from the spreadsheet and fill in anything else which may be needed. Pressing OK from this screen would create the document, inserting the values into the correct positions. Obviously the advantage here is speed and accuracy.
Amazingly to many people this fine solution is available within Excel itself using it's built-in programming language VBA. If you wanted to try it yourself Google "excel vba tutorial" or something similar. However for a more business-orientated approach it would be best to get some Excel help - hire someone or a company who specialises in this to do it. It's not that expensive these days as it's hardly cutting edge technology. This approach can be good for tiding a company over if it is not ready to move to a full software / database system.
Multiple Users Accessing an Excel Workbook
Unfortunately Excel is a single-user application - one one person can open and edit an Excel spreadsheet at once. Trying to open an already-in-use spreadsheet receive a message similar to the following:
"File in use: Filename is locked for editing by username. Click 'Notify' to open a read-only copy of the document and receive notification when the document is no longer in use."
There's no way around this Excel problem. It may be time to move to a database if you need multiple people accessing and editing company-wide information
Movement of information from Excel to a Database
There's no easy way of doing this as far as I know simply because the way the spreadsheet is structured is never the same. There may be some tools out there but in my experience, to do the job properly requires writing VBA code to import (or "suck") the info out of Excel and export (or "squirt") it in to the database. If you are moving to a database you will need a knowledge of database technology anyway so writing the code shouldn't be out of your reach. Realistically this will be done by a specialist professional.
Recognise Excel's Limits and Take Action
Problems can sometimes be solved by approaching them a different way or being armed with better knowledge (there is lots of Excel Help on the web), or hiring an Excel professional. It may require use of a different tool altogether such as a database - an Excel proessional should be able to tell you this. It's as well to recognise when it would be more productive to get some Excel help so minimum time is wasted so you can get on with your business.
-
11 March 2010
Paul Rigby works for Awgar Stone Ltd as a director and software developer and has been producing Excel and database solutions to various industries for over 10 years.
|
