Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In this course, Dennis Taylor shares easy-to-use database commands and methods for maintaining an Excel database. The course covers sorting, adding subtotals, auto-filtering, and using the Excel Advanced Filter feature and specialized database functions.
In Excel there is a category of functions called database functions, and although less frequently used in the past because of the SUMIF family of functions has been expanded greatly, these database functions rely on the kinds of criteria ranges used with the Advanced Filter. If you haven't used the Advanced Filter these might seem a little strange to you. But there are some advantages here and there. And these functions do mesh well with those Advanced Filter applications, particularly when you've got unusual calculations SUMIF and COUNTIF can't handle.
Now for reference in Column J I've simply listed here the various database functions. If you remove the letter D from each of these of course you probably recognize most of those anyway. Now here is a strange thing. In Excel 2007 and in Excel 2010 if you're a little bit curious about these database functions, you heard a little bit about them, if you go to the Formulas tab you say, "Well, I don't see them here, they must be here under More Functions." But we're not seeing them; they're not shown here.
Maybe that's some indication as to what Microsoft plans to do with these functions in the future. But we don't see them. However, if you click the fx button on the left side of the Formula Bar and look at the categories that are available here you will see Database functions, and here are those 12 functions. And what they all have in common is that they refer to a criteria range as the third argument. And if you have worked with the Advanced Filter you probably recognize on the screen here that potentially this is a criteria range. This is or maybe the two of them together are.
So, let's talk about how we might use one of these, for example DAVERAGE, =daverage. We'd like to find the average salary of our full time people. Now I think a lot of you know possibly you could be using one of the SUMIF, COUNTIF, AVERAGEIF functions here. Let's choose this function as an example of how we might use the others. We're looking at a database. I'm simply going to drag across columns A through F. In other situations you might be highlighting all the cells. Make sure you do include the title row there. Comma. Which field are we trying to tabulate data from? From left to right it's the seventh column, Column G. So we put in the number 7 here, comma, and how about this criteria range? And here your knowledge of using Advanced Filter would come into play.
We simply want to highlight the cells J1 and J2. What's the average salary of our or full time people, that's what we are calculating right now, and there it is. If we wanted to know the total salary of those salaries that are greater than 80,000 or maybe more interestingly a count of how many salaries are over 80,000, perhaps we'd use DCOUNT, =dcount. So once again like in the previous example we're looking at this database here. Comma.
We're tabulating a data from Column G so that's the seventh column, comma, and our criteria range this time around is these cells here. Tell me how many cells have salaries greater than 80,000, and there are 71 of them. So perhaps you can begin to see how this has some merit. Again they seem to be on the wane in terms of being widely used in Excel worksheets, but there's some real power and capability.
And if you do work with the Advanced Filter concept you're aware of how these have some real potential here and there are quite a few variations on how you use the Advanced Filter. And these functions do mesh nicely with that capability.
Find answers to the most frequently asked questions about Managing and Analyzing Data in Excel 2010.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.