Level 2 - Lesson 20 - Extracting Data
Category: [General,Excel] Demo Available
Extracting Data
This lesson will be used to look at the various methods available within Excel to extract required information from databases and tables. There are many ways in which this can be done, but some ways are far better than others. By far the most important factor in this is how the information is laid out in the first place. By following these six rules (also mentioned in Lesson 1), life will be much easier come time to extract the information
When putting in headings bold the font, this will help Excel recognise them as headings when you use one of its functions such as sorting dat
When putting data into the data area of your spreadsheet try to avoid blank rows and columns if possible. This is because a lot of Excels built in features will assume a blank row or column is the end of your data. It also helps with a lot of formulas.
Have your data sorted if possible. Excel is very rich in what are known in Lookup & Reference formulas and many of these rely on your data being sorted in a logical order
Use real dates for headings and format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2011, 1/2/2011, 1/3/2011 etc then format them as mmmm. This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road when you need to use them in formulas
Avoid merging cells if possible. Use Centre across selection instead. This can be found by going under Alignment options on the Home tab and selecting from the Horizontal box. Merging cells can cause all sorts of problems later on that that will have you scratching your head.
Don't put in one cell what could go in more than one cell. For example, say you have the names of 100 people to put in your spreadsheet. Don’t put their full name in one cell. Instead put the First name in one cell then their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily, the same cannot always be said for the reverse.
The Functions that we will look at in this lesson are best suited for extracting information. These are the Lookup & Reference Functions, the Database Functions and Array Formulas.
However, before we start, we must stress that we will only use array formulas as a very last resort. Our reason for this is that over the years we have been helping others use Excel, we have consistently found that when users first learn how to use and write array formulas, they tend to fall back on them all too often. The major drawback to this is that you will end up with a workbook that becomes extremely slow in re-calculating. You must remember also, that Excel by default re-calculates when you open, save or enter data into a cell that a formula is dependent on. All too often we have seen workbooks that have become virtually unusable due to the amount of array formulas.
The other subject we will look at is the means and methods of deciding which formula to use in which situation. As Excel has tons of Functions available, this can sometimes seem a bit daunting. Keep in mind that although Excel is very rich in Functions, you will find that most likely you will be using the same set of Functions over and over again. You must always keep in mind that Excel was designed for all people from all walks of life and businesses to use, by this I mean it contains many Functions that would be of no use at all to some people, but be the "be all and end all" to others. A couple of brief examples of this would be some of Excel's engineering and financial Functions, unless you were in that field of expertise they would be of no use at all.
We will look at the means and methods of deciding which formula to use in which situation first and then we will move on to ways and means of extracting information from tables and databases.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples