Keeping Crap Data Out Of Our Dataset: Demo of Excel Data Validation

This is a demonstration of what’s possible with Excel’s data validation.
Too often spreadsheets have no layer of protection so, the doors are open wide for crap data to enter our datasets. We’ve got to stop being naughty and close those doors

Here, I show 3 spreadsheets and demonstrate various protective methods:
dependent dropdown lists, protected cells, protected sheets, cells that can only accept integers between 1 and 12.

I don’t go into the how-to detail. The goal here is to show the uninitiated what can be done. There are plenty of how-to tutorials around the internet. Sometimes we just don’t know what’s possible..

You can find me at: http:DataScopic.net

Oz Reviews Sriracha Lays Potato Chips

Pleasantly surprising. As a lover of sriracha, I had to try these out if for no other reason than to say that “yes, I’ve tried them.”

There’s some effort that’s been put in here. After a few chips, I have that warm tingle in my mouth. So, I’m not disappointed like I thought I would be. I’m not in love with these, either; but I’d buy them again.

My preference for spicy food is the level of heat that takes you into the capsaicin-induced euphoria while being balanced with flavor. We’re far from that level of heat still, the effort Lay’s made is impressive.

And thank you to Tyler Raineri of Lake Zurich, IL for the idea!

Rosemary Hayes and Rufflentuck can be found at: http://www.etsy.com/shop/rufflentuck

Excel 2013: Slicers & Tables

UPDATE: Mac users have Slicers as of Excel 2016

New in Excel 2013, a wonderful analytical tool: using Slicers with Tables to analyze and/or filter data sets.

Slicers could be used with Pivot Tables and Pivot Charts, and Excel 2013 takes it a step further. EXCELLENT!

VLOOKUP w/TRUE to assign dates in custom periods

Here’s an example of using True in a VLOOKUP because an approximate match is required.

We have a list of dates, and the company uses custom start and end dates to mark monthly periods. Here, “June” is officially 26MAY thru 24JUN.

In order to get 26MAY to show up as June, we use:
=VLOOKUP(A2,$G$2:$H$13,2,TRUE)

IMPORTANT NOTE: I explain the search incorrectly. It’s a common myth that the True starts at the top and goes until it finds the next highest entry. The real explanation is found here:

True conducts a “binary search” as I explain in the other video.

Sampling Jim’s Habanero Ghost Cheese

Oz du Soleil samples Habanero Ghost Pepper Cheese from Jim’s Cheese Pantry
This is a fantastic cheese that balances the heat of the peppers, makes excellent use of the ghost peppers inside of a cheese that stands up and matches well with the peppers.

An all-around excellent experience. Add some red wine, and we’ve got LIVE ACTION!

Generating Random Data to Set Up a Pivot Table Tutorial

Blogpost associated with this video: http://datascopic.net/generate-data/

Often we need to generate a set of bogus data in order to create examples or test solutions. In this video, I show how to generate fake call center data in order to show an example of a pivot table.

This includes: RAND, RANDBETWEEN, date formatting, Find-Replace

Basic Excel Table Demo

Simple use of Excel Tables

Basic Demo of Excel Tables to Warn of Upcoming Deadlines

Quick video to show the use of Excel tables, and how a table can be compromised.

Excel Inventory Control System DEMO

This video shows an Inventory Control System that was built in Excel. The purpose is to demo the various layers to consider in creating such a document:
– Distinct Incoming, Outgoing and Inventory Sections
– Required fields so that complete info is entered
– Alerts that give users an idea of anything that’s missing
– Protected cells so that formulas can’t be accidentally corrupted
– Prevention against a user requesting something that isn’t in inventory
– Prevention against a user requesting more than what is in inventory
– Use of dependent drop lists to control input and output

I don’t go into the “how-to” but if anyone is interested, please let me know.

NOTE: Several people have contacted me about this document.
I’ve made it available for download at a low price at http://datascopic.net/downloads/inventory/