Excel Workshops: Formulas/Dashboards

21DEC12:

Excel Workshop: It was great!

On Wednesday 19DEC12 12 students met at Chicago’s Holiday Club to learn about Excel formulas. We had a good time!

The Holiday Club provides a great venue in their back room. Their waitress, Emma has been our assistant both times we’ve held workshops there, and the managers are very helpful.

And thanks to Frank Massi of Dabble for facilitating this transfer of knowledge.

During the course, we reviewed the Top 5 Excel Functions. We discussed formula syntax and a few Dos and Dont’s. A few clever participants asked “what if …” and we tested their what-ifs to see what would happen.

What if you don’t lock down the lookup range in a VLOOKUP and then copy the formula down? Answer: the lookup range start moving and fails to find the lookup value if it’s fallen outside of the range. You can see that in the VLOOKUP screencast.

Next Workshop: Excel Dashboards & Pivot Tables

An issue came up around the problem of having massive amounts of data and not having an easy way to know what’s in it. Imagine:

• 500 rows
• 28 columns
• More than you need to know about your inventory, their warehouse location, vendors, cost, sales person, etc.
• The 500 rows are updated daily with inventory numbers, new items, discontinued items.
• The list only grows. It never shrinks because we need the history on discontinued items.

At-a-glance, you want to know 5 things:

1. How many products categories do we have, and how many from each vendor?
2. By month, and by warehouse what is their rate of being out of stock?
3. What products are out of stock and who are the associated sales people?
4. Of the products that have been discontinued, how many are still in stock?
5. Which of the 8 sales people are assigned to which of the 39 vendors?

A Dashboard or summary worksheet would be a huge help because you don’t always need to see everything.  28 columns represent 28 pieces of data; the rows represent 500 different products. Given the 5 things we want to know, they are just 7 bits of data, not 28. On our Dashboard, we wouldn’t include things like: part number or vendor fax number. We might need that info but not on an hourly or daily basis.

A good spreadsheet dashboard is uncluttered and provides only the most important information. Other data can be retrieved on an as-needed basis.

Stay tuned. I’m working on the Dashboards Workshop proposal and will announce the date and time once everything is set. Meanwhile, DataScopic hosted a guest blogger from Australia. Mynda Treacy of My Online Training Hub provided excellent information on Excel reporting and dashboards. Have a read.