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?

Click here to download the Excel files for this video.

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.

Video 4 – COUNTIFS

In this video, the Excel database formulae start getting really interesting! We move onto the topic of multiple criteria questions – analysis in which we wish to include data that conform to two or more pieces of information. How many males from the North region are in the database, for example? We deal first with the COUNTIFS formula. The COUNTIFS formula is similar to its close friend the COUNTIF (singular) formula, but allows us to include any number of criteria, whereas COUNTIF allows just one. The formula returns the number of records in the database that conform to all of the criteria we specify. With this level of sophistication, the formula can be tricky to build. Particular attention has to be paid to references and use of absolute, relative, and partial absolute references. Chris discusses references in the video and provides his tips for getting them right throughout the series. Trial-and-error is key!

Video 5 – SUMIFS and AVERAGEIFS

In the fifth video in the series, we deal with more ‘multiple criteria’ questions. In this case, we wish to do more than simply count entries that conform to multiple criteria; we actually want to perform an operation on the data – calculate an aggregate (SUMIFS), or a mean average (AVERAGEIFS). Chris works through each of these formulae step-by-step, building two analysis tables that provide powerful insights on the dataset. All in just over 15 minutes! Hopefully you’re now feeling the power of the formulae and can see applications in your real-life situation…

Video 6 – DSUM and DAVERAGE

"

The sixth and final techniques video in the series marks a departure from the previous formula-building approach. We examine two powerful Excel database formulae – DSUM and DAVERAGE. These formulae offer new possibilities: rather than having to edit a complicated formula to get the analysis we need, we can simply enter values into a table and watch the outputs change, cool! There is a trade-off however, setting up these formulae requires some preparatory work, and maximum precision is needed to get the formulae working. Once set up and validated, however, they provide quick, powerful, user-friendly analyses. We have found that on our real-world projects, the DSUM and DAVERAGE formulae can have a dramatic impact. Make sure you work along with Chris and get these Excel database formulae working for yourself.

Video 7 – Excel Database Formulae – Reflection and Discussion

In the final video of the series, Chris discusses an important issue in applying these formulae in the real world – how to account for a database that is changing in size. It is unlikely that your database is ‘static’; rather, you would want to add data to it over time. How can we set up the Excel database formulae so that the analysis encompasses the new data? Chris discusses three possible approaches and highlights his preferred option.

What did you make of the Excel database formulae for beginners series? Have you managed to get the formulae working yourself? Share your experience with the Tiger community on the platforms below: