Excel is an amazing application, alright. But you need to agree
to the fact that it gets a bit complicated from time to time, what’s with its myriad functions and formulas that you need to keep in mind. Sometimes you wish you knew about database capabilities which would lessen all the hassles. This Excel Database Tutorial will definitely come in handy in that regard, helping you sort your data in different ways and reorganize it in any way you want it.
To use excel workbook as a database, your information needs to be streamlined and properly structured. The techniques we’re going to talk about in this Excel Database Tutorial will not work on unstructured data, but that’s easy to manage as well. Let’s have a look.
- Every column in an excel has the same data category as every row in the column
- All the rows contain all the fields of data for a single entity such as a person, organization or object. Though it is similar to a database record, it is known as sets of data in Excel’s terminology.
- First row in the list should have a unique name at the top of each column.
- The first row of the worksheet doesn’t necessarily be the first row of the list.
- The column headings in rows should be formatted differently.
- No blank rows should be there in the list.
- Every row and column should contain data in similar format
- Other data in worksheet should be put outside the boundaries of the list
If you want to sort data alphabetically or numerically, you can use the quick sort button on standard toolbar in ascending or descending order. However, the way of sorting which needs you to select an entire column by clicking on the column letter and then clicking the quick sort button on toolbar becomes quite frustrating and monotonous. In order to sort the right way, put the cell pointer in any cell in the column you need to sort and then click one of the sort buttons. If the data wasn’t sorted before, this kind of sorting would fix the list. Though you can’t go back to the original order once you’ve performed this kind of sorting, you can add additional column to your list sequentially so that you can resort to the column numerically.
Use Excel’s fill series to quickly number the new column sequentially:
- In the first cell which needs to be numbered, type number 1
- Type number 2 in the next cell down
- Select those cells
- Position your mouse pointer over fill handle in lower right corner of the cells you’ve selected
- Drag the mouse pointer after clicking to the end of the column
- If you want to return the last to its original order, select one of the cells in the numbered column and then select Sort Ascending button.
Learn Excel Database Tutorial in AudioSolutionz in detail. Expert speaker Dennis Taylor is speaking in a live webinar on using Excel as a Database which would make Analyzing data easier. The webinar will be taking place on Tuesday, June 17, 2014.
