excel

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel

Excel

resolvedResolved · Low Priority · Version Standard

Lisa has attended:
Excel Intermediate course

Excel

How can I create databases effectively? Thank you

RE: Excel

Hi Lisa

Thankyou for your question

There are 2 key steps to creating an effective database, data validation and data normalisation.

Data validation is the process by which you ensure that only the correct types of information can be entered. For example if you have a column that contains employees dates of birth you will need to make sure that:

1. People can only enter dates into the relevant column and
2. That the dates entered are consistent with someone older than 16.

In excel data validation can be accessed from the data menu

Data normalisation appears once your database becomes slightly more complex. If it is a simple list, then it is what we call a flat file database, and normalisation does not apply.

However, once for instance you have a situation where you have more than one column for the same information, e.g. contact1, contact2, contact3 in a customer database, then you need to normalise the data. This involves splitting it into a number of diffeent tables, and would require to move from Excel to Access.

Hope this helps

Regards

Stephen

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Use RANDBETWEEN to generate random numbers

There may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify.

The function looks like this:

=randbetween(LOW,HIGH)

where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.

This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:

=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION).

where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).

One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.

View all Excel hints and tips


Server loaded in 0.08 secs.