Hints and Tips

Excel Database Tips

What is an Excel database?

A database in Excel is a table in which every row is a record (all the information about one entity – for example a transaction, stock item or noise reading) and every column a field (each item of information about the entity – such as the transaction date, amount and description).

How do you create a database in Excel?

You can create a database in Excel by typing the data directly into the worksheet or by importing data from another program:

  • In some instances the data may be stored permanently in Excel and all the data entry, editing and reporting is done in that program.
  • In other instances the data may be stored permanently in another program (perhaps an accounting program such as MYOB) and periodically exported to Excel for analysis or report production – see the Data, Text to Columns command below.

Whichever method you use, the first row of the database must be the Field Name Row and describe the contents of each field. For Excel to work effectively there should be no blank rows or columns in the database.

What can you do with a database in Excel?

Excel provides some simple tools to manipulate databases. Some of the more useful are the:

  • Data, Sort command to sequence data from smallest to largest / oldest to newest by the contents of any field.
  • Data, Filter command to view only the records that satisfy specified criteria (last month’s transactions / out of stock items).
  • Data, Subtotals command to calculate numeric fields by category (total payments by item / average noise reading by day).
  • Data, Text to Columns command to import a text file from another program and convert it to an Excel database.
  • Data, PivotTable and PivotChart Report command to ‘cut and slice’ data in a variety of ways and produce summary tables or charts. For a detailed explanation of Pivot Tables click here. You can learn more about Pivot Tables on NDA’s Excel Advanced course.

What can’t you do with a database in Excel?

The main limitation of Excel as a database is that you cannot easily link two or more separate tables of information. To do this you need a ‘proper’ database program such as Microsoft Access. People often use Excel as a cheap and simple method of setting up a trial database and exploring the possibilities. In many instances Excel is all that is required; in other instances the application may outgrow Excel and may need to be converted to Access.

How to get more value from your data

To get more value from your data:

  • Create records that capture as much data as possible so detailed analysis can be performed. If a transaction records the time, location, department, salesperson, item sold, quantity sold, unit cost and customer you can extract more management information that if you just record a sale and the total amount.
  • Avoid the temptation to summarise data to reduce file sizes. Retaining all transactions each month will enable you to analyse data in more detail than if you summarise the data as total debits and credits for the month.
  • Retain historical data from previous years so you can analyse trends over time – what stores / items / sales staff are doing better / worse this period than last period?

Some useful Excel database keyboard shortcuts

  • To delete a record, click anywhere in the row you want to delete, press Shift+Spacebar to select the row and Ctrl+Hyphen to delete the row.
  • To insert a new record, click anywhere in the row beneath where you want to insert the new record, press Shift+Spacebar to select the row and Ctrl+Plus to insert a blank row.
  • To jump to the bottom of the database, click anywhere in the data and press Ctrl+Down arrow.
  • To jump to the top of the database, click anywhere in the data and press Ctrl+Up arrow.
  • To select the entire database, click anywhere in the data and press Ctrl+A.

How NDA can help with your database

NDA offers training at both Introductory and Advanced levels in setting up databases in Excel. We also offer training at all levels in developing Access databases. The Excel training should be entirely sufficient to enable you to fly solo. Access can be a little more complex and some professional support may be required. Many of our clients follow the steps listed below in developing Access databases:

  • Attend an NDA Access course to learn about general database principles, develop simple databases and experiment with the development. of more complex databases.
  • If necessary, engage NDA’s software developers to design the data model and program a database.
  • Use skills developed at the NDA training courses to maintain the database, develop data entry forms, queries and reports.

Contact Us   |    Privacy   |    Access and Equity   |    Links