Excel Database Formulae For Beginners
Create cool database analysis quickly with Excel Database Formulae for Beginners!
Excel is great for logging data and keeping small databases. But, very few people know how to get the analysis they need from the data quickly and without too much stress! Too often, people resort to sorting data and splitting up the database to simplify the problem; but, this ‘critical mistake’ complicates the workbook and is often counter-productive. What are the alternatives?
Correctly applied, Excel database formulae allow us to get the analysis we need quickly and without too much stress. A few hours learning the skills could have a dramatic impact on your work. Make sure you download the exercise files (above) and work along with Chris. Enjoy!
Video 1 – Introduction to Excel Database Formulae
In the first ‘in-car’ video, Chris talks about the real-world motivation for the series, and the value of the techniques covered. Excel offers a vast array of functions; but, we believe, not all are required to reach a good level of competence. Rather, a subset of techniques learned effectively can deliver disproportionate benefit. The powerful Excel database formulae covered in this series fall into this subset of important techniques. Chris also talks about a ‘critical mistake’ in spreadsheet development – how users tend to split up databases across multiple sheets in order to make the analysis easier. This actually serves to complicate matters and will discourage the user in the long run. Command of Excel database formulae allows you to get the analysis you need without complicating workbook structure.
Video 2 – COUNTA and COUNTIF
The first techniques video covers two basic formulae that are essential for working with databases. The COUNTA formula tells us how many entries are in a database, and the COUNTIF formula allows us to answer ‘single criteria’ questions such as ‘how many females are there in the database?’ Remember to download the spreadsheet files (link at the top of this article) and work along with Chris. Throughout this series, we use PC keyboard shortcuts to navigate Excel and speed up / de-stress the formula-building process. Try the keyboard shortcuts yourself and get Excel working for you like a pro!
Video 3 – SUMIF and AVERAGEIF
COUNTIF allows us to identify how many records conform to a particular ‘criteria’, or piece of information that interests us. The SUMIF and AVERAGEIF formulae allow us to perform an operation on records that conform to a criteria. What do we mean by an ‘operation’? The operation that SUMIF performs is to aggregate the data, whereas AVERAGEIF returns an average of data that interests us. So, you can get important summary measures such as aggregates and averages for subsets of the data without having to sort or split up the database. Cool! Throughout the series, Chris checks the accuracy of the Excel database formulae by sorting the database and applying a simple formula, effectively getting the same result a different way. It is good practice to ‘validate’ your analysis like this – it should help build confidence in the values that the database formulae return.