Home » Computers » Software

Database Tips from an Excel Training Course

Dec 16, 2007
The benefit of an Excel training course is not just learning new things but also learning new ways to do old things. An experienced instructor provides hints and shortcuts that have you designing spreadsheets in new ways.

For example, let's look at tips from Excel training courses that can help in maintaining a database. Excel is an easy way to keep a simple database and a few design steps can save you huge headaches later.

Hide Blank Formulas
The simplest databases are a collection of data records but some have formulas off to the side providing analysis of each entry. However if any data is missing from the record then these formulas clutter up the sheet with "#NUM!" and "#DIV/0!" and other errors.

Consider a database of payments from a client, a typical example in an Excel training course. Each line contains, among other information, the date of invoice and the date of payment. You have a formula on each line that calculates the time between invoice and payment using a formula like "=G2-F2" to subtract the invoice date (F2) from the payment date (G2). Until you have a payment date, this field will read as a very large negative number.

Instead, enter this formula: =IF(G2="","",G2-F2). This says "If there is no payment date, leave this field blank otherwise calculate the payment delay". Even better, try "=IF(OR(F2="",G2=""),"",G2-F2) which leaves the payment time field blank if either date field is blank.

Mark Blank Formulas
There are a lot of reasons you might have blank cells that actually contain formulas. They could be examples of the above, formulas that won't calculate until all data is present. Another reason, and another trick you might pick up in an Excel training course, is that cells used for intermediate calculations can be formatted to be white text on a white background so they don't clutter up the sheet.

These blank cells are easily overwritten by a careless user so need to be marked in some way. Using a light gray or other colored background is a good way to indicate that there is something there without detracting from the main information on the sheet.

Use Border Rows
Databases outside of Excel training courses are seldom static. As you add new records to them you don't want to update every formula that refers to the old range. By using border rows, the formulas all update automatically.

Take one row above and below your database and mark it in some way. You might put symbols such as asterisks or dashes, or you might color the background of the cells to create borders. In any formula that refers to the database, be sure to include these border rows in the range designation. The range automatically adjusts as you insert new lines even if you add them before the first row or after the last because you are still adding them within the two border rows.
About the Author
Author is a freelance copywriter. For more information on Excel training course, please visit http://www.microsofttraining.net
Rating:
Please Rate:
(Average: Not rated)
Views: 132
Print Email Report Share
Article Categories
    • Artists
    • Gambling
    • Humanities
    • Humor
    • Movies
    • Music
    • Photography
    • Tattoos
    • Television
    • Classic Cars
    • Motorcycles
    • Recreational Vehicles
    • SUVs
    • Trucks
    • Vans
    • Branding
    • Business Opportunities
    • Careers and Jobs
    • Corporate
    • Customer Service
    • Direct Mail
    • Entrepreneurship
    • Ethics
    • Financing
    • Franchising
    • Home-Based Business
    • Human Resources
    • Import and Export
    • Leadership
    • Management
    • Market Research
    • Marketing and Advertising
    • Negotiation
    • Network Marketing
    • Networking
    • Organizational
    • Presentation
    • Project Management
    • Public Relations
    • Small Business
    • Strategic Planning
    • Team Building
    • Telemarketing
    • Training
    • Ask an Expert
    • College and University
    • Home Schooling
    • K-12
    • Languages
    • Online Education
    • Psychology
    • Accounting
    • Credit
    • Currency Trading
    • Debt Consolidation
    • Insurance
    • Investing
    • Leasing
    • Loans
    • Mortgage
    • Mutual Funds
    • Personal Finance
    • Stock Market
    • Structured Settlements
    • Taxes
    • Wealth Building
    • Coffee
    • Cooking
    • Gourmet
    • Recipes
    • Wine and Spirits
    • Acne
    • Aerobics
    • Alternative Medicine
    • Beauty
    • Cancer
    • Cosmetics
    • Depression
    • Diabetes
    • Diseases and Conditions
    • Fitness Equipment
    • Fitness
    • Hair Loss
    • Heart Disease
    • Medicine
    • Men's Health
    • Muscle Building
    • Nutrition
    • Skin Care
    • Supplements and Vitamins
    • Weight Loss
    • Women's Health
    • Yoga
    • Arts and Crafts
    • Babies
    • Collecting
    • Elderly Care
    • Genealogy
    • Hobbies
    • Parenting
    • Pets
    • Pregnancy
    • Woodworking
    • Feng Shui
    • Gardening
    • Home Appliances
    • Home Security
    • Interior Design
    • Landscaping
    • Affiliate Programs
    • Article Marketing
    • Auctions
    • Audio
    • Banner Advertising
    • Blogging
    • Broadband
    • Domain Names
    • E-Books
    • E-Commerce
    • Email Marketing
    • Ezines and Newsletters
    • Forums
    • Internet Marketing
    • Link Popularity
    • Pay-Per-Click
    • Podcasting
    • RSS
    • Search Engine Marketing
    • Search Engine Optimization
    • Security
    • Social Media
    • Spam
    • Video
    • Viral Marketing
    • Web Design
    • Web Development
    • Web Hosting
    • Copyright
    • Cyber Law
    • Intellectual Property
    • National, State, Local
    • Patents
    • Regulatory Compliance
    • Trademarks
    • Buying
    • Selling
    • Baseball
    • Basketball
    • Boating
    • Cycling
    • Extreme Sports
    • Fishing
    • Football
    • Golf
    • Hockey
    • Hunting
    • Martial Arts
    • Running
    • Scuba Diving
    • Soccer
    • Swimming
    • Tennis
    • Dating
    • Divorce
    • Marriage
    • Weddings
    • Astrology
    • Buddhism
    • Christianity
    • Faith
    • Hinduism
    • Islam
    • Judaism
    • Meditation
    • Metaphysical
    • New Age
    • Cable and Satellite TV
    • Cell Phones
    • Communication
    • Gadgets and Gizmos
    • GPS
    • Satellite Radio
    • Video Conferencing
    • VoIP
    • Addictions
    • Coaching
    • Goal Setting
    • Motivational
    • Stress Management
    • Time Management
    • Clothing
    • Electronics
    • Fashion
    • Gifts
    • Jewelry
    • Causes and Organizations
    • Environment
    • History
    • Holidays
    • Men's Issues
    • Nature
    • Philosophy
    • Politics
    • Women's Issues
    • World Affairs
    • Air Travel
    • Camping
    • Cruises
    • Destinations
    • Outdoors
    • Article Writing
    • Book Reviews
    • Copywriting
    • Fiction
    • Non-Fiction
    • Poetry
    • Quotes
    • Screenplay
    • Tools and Resources