In this post you’ll discover … blah blah blah
WHAT IS AN EXCEL DATABASE?
- A database contains one or more tables. Each table stores information about one subject.
- The column headings of the table are called fields.
- Each row of data in the table is called a record. Each record is a set of data that stays together, even when the table is sorted or filtered.
- In excel language, a database may be referred to as a table, or more simply, a list.
THINGS TO CONSIDER WHEN CREATING AN EXCEL DATABASE
The following points should be observed when designing a list:
- The table should have one header row.
- Use descriptive labels for the column headings.
- Do not insert blank rows or columns in the middle of the table, although blank cells are okay.. Excel determines the boundaries of a table by the position of blank rows and columns. It is a common mistake to insert a blank row between the column headings and the first row of data. In this instance, use formatting instead, e.g. a bottom border on the header row, or shade the header row a different colour.
- Each column must contain the same type of data (e.g. text, currency)
- Formulas may be used to calculate table data.
TABLE FEATURES TO LOOK OUT FOR
Here are some things to look for.
1. The header row uses a dark cell colour and a lighter font to distinguish it from the main body of the table.
2. Each column heading has a drop-down arrow that can be used for sorting and filtering.
3. The rows are banded. Every odd row is one colour and every even row is a different colour. This helps you visually, so you don’t accidentally read data from a different row..
4. In the bottom right-corner of the table is a corner marker. This marks the end of the table. If you’re not sure whether a table has been formatted or not, this marker is the clue to look for.
5. While the table is selected, the Table Tools are displayed in the title bar. The Design tab of Table Tools has all the tools you need to work with tables, including 7 tick boxes (check boxes)..
CHANGING THE TABLE DESIGN
You are not locked into a colour scheme On the right-hand side of the Table Tools Design ribbon:
1. Hover over a thumbnail to see how the colours look on your table. This feature is called Live Preview.
2. Click a thumbnail to switch to the new design.
3. Click the More button (pictured) in the bottom-right corner of the gallery to display the full gallery.
SWITCHING THE FILTER ON OR OFF
When you use the Format As Table feature, the filters are switched on automatically.
There are 2 ways to toggle them on or off::
1. On the Table Tools, uncheck the Filter Button check box.
2. On the Data tab, click the large FILTER button (about halfway across).
HEADER ROW
If you want to hide the header row and just show the data, untick the Header Row tick box. I’ve never found a use for this.
Banding
If you wish to remove the row banding, untick the Banded Rows box.
If you prefer, you can tick the Banded Columns to get stripy columns, or you can tick both but it’s a bit messy!
FIRST COLUMN & LAST COLUMN
The first column of your table may contain row labels.
The last column of the table often contains row totals.
If you want to format these rows differently, tick the First Column or Last Column boxes.
The table design you have selected determines how the first and/or last column are formatted.
For example,
TOTAL ROW
Ticking the Total Row box adds a new row to the table. Excel doesn’t look to see if you already have a total row, it just adds one regardless.
But with that said, it’s cool. Let me show you.
The last column is totalled automatically. If you don’t want this you can remove it or change it.
The thing is, you don’t always want to total the data. Consider the Hourly Rate. Adding up these figures isn’t helpful. But maybe an AVERAGE Hourly Rate might be useful. To do this:
1. Click cell D8 (the Total cell for Hourly Rate
2. Click the drop-down arrow that appears on the cell and choose Average from the list.
It’s as simple as that. No formulas – it’s a feature that’s built into formatted tables.
ADDING DATA TO AN EXISTING TABLE
When you type or paste new data onto the end of a formatted table and press Tab or Enter, the table formatting will extend to include the extra rows and the corner marker moves down as a reminder that the new rows belong to the table..
USING THE AUTO-COMPLETE FEATURE
When you start typing into a cell, Excel checks all existing entries in the same column to see if it can find a unique match for the letter(s) that have been entered. When/if it finds one, it offers to autocomplete the rest of the entry for you.
This saves you typing and is particularly useful for long descriptions.
For example, in the Car column of our sample table, Holden is the only car in the list that starts with H, so as soon as H is typed, Excel can autocomplete Holden after just one letter.
If there was a Honda in the list as well as Holden, Excel now requires the first 3 letters to reach a unique entry – HOL for Holden or HON for Honda.
pICKING FROM A DROP-DOWN LIST
In similar fashion to AutoComplete, Pick From List may be used.
1. Right-click on the cell.
2. Choose Pick From Drop-Down List. A drop-down list of unique entries from the same column is displayed.
3. Click on an entry from the list.
I hope you found plenty of value in this post. I’d love to hear your biggest takeaway in the comments below together with any questions you may have.Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
