Excel Dashboard With High and Low Values

My clients sometimes ask for help with building Excel dashboards, so they can present a summary of their data to their customers and co-workers. In a dashboard, you want to make the best use of limited space, and only show key information. For example, instead of showing all the sales data, you can show just the highest and lowest values.
It’s easy to pull the top and bottom values from a list, by using the MIN and MAX functions. It’s a little trickier though, if you want to show the high and low amounts for a specific product in a long list. In this example, I want to calculate the MIN and MAX for each product, then put that information on the dashboard

Create a MIN IF or MAX IF formula

There’s no built-in MINIF or MAXIF function, but you can use MIN or MAX with the IF function, to create your own. The steps are shown in the video, at the end of this article.
First, to get the minimum quantity sold for File Folders, the array formula in cell D8 is:
=MIN(IF($G$2:$G$17=C8,$H$2:$H$17))
After you type the formula, press Ctrl + Shift + Enter, so it is array entered.
The same technique is used in cell E8, with MAX, instead of MIN:
=MAX(IF($G$2:$G$17=C8,$H$2:$H$17))
minmaxminifmaxif01

Excel Dashboard Course

If you’d like to add dashboard skills to your Excel tool kit, I recommend the upcoming Excel Dashboard Course offered by Mynda Treacy from My Online training Hub. Mynda is an accountant, and her dashboards focus on the numbers, not the fluff.

There are 9 sessions in the course, with video tutorials that are short and to the point. They cover the key steps and features, and you can practise the techniques in the sample files. Replay the videos as often as you need, for up to 12 months. The course includes 6 weeks of support from Mynda, so you can post questions, read comments, and ask her to review your completed dashboard.
This course is not for Excel beginners, because the fast pace could be overwhelming. Lots of material is covered, very quickly. And, if you’re already a dashboard expert, you won’t need this course. It’s designed for Excel users who are beyond the basics, and who enjoy learning by seeing a demo, then practising the new skills.
You can see the course details and a sample video here: Excel Dashboard Course
Registration is only open for two weeks, until August 14th, and if you sign up early, you can get a 20% discount. Check the sign page for the deadline dates.

Watch the Min and Max Video

To see the steps for creating MIN, MAX, MIN IF and MAX IF formulas, please watch this short video tutorial.

_______________________________

Blog Stats Dashboard Template

One of the things that I do while drinking my morning coffee is to check the statistics for my website and blog. It’s interesting to see the fluctuations in traffic, and discover where’s it coming from. I use Google Analytics and my web host also has a daily statistics file that shows me the hits and downloads.
Not that I’m obsessed or anything, but I track the statistics in a couple of Excel files, and even have a line chart for the monthly totals. Today I saw a blog statistics dashboard that a Canadian PR firm uses for its clients, and it puts my little system to shame.
Apparently the reports are created monthly, with lots of cutting and pasting, but you could probably automate something similar in Excel. It has a nice clean look, and you customize it to show the statistics of interest to you or your clients.
You can download the dashboard template in pdf format, and make changes to it, if you have Adobe Acrobat. I had to change the font before I could edit the text, but maybe that’s because I’m still using Acrobat 5.0.