https://www.youtube.com/user/savvysexysocial

#SSSVEDA Day 3: Peek Inside My Excel Work Environment

/in /by

#SSSVED is Vlog Every Day in April, inspired by Amy Schimttauer at SavvySexySocial.

This vlog takes you into my Excel work style and environment: the things that you won’t learn in a workshop but the kinds of things that we each feel are essential to be in place when we sit down to whoop some crap data.

Some of my essentials: hot chocolate, 2 mice and my workbook that holds a massive amount of random data, I also hide the Excel ribbon quite frequently.

It’d be nice to hear what other Excel users do to keep themselves focused and efficient.

====
Interesting Links:
My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog2-2

Crowd sound fx courtesy of http://www.freesfx.co.uk/

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

SSSDEVA Day 1: Beware Ghost Peppers & Suspect Excel Charting

/in /by

A friend offers you ghost peppers and uses a chart to prove that the ghost pepper isn’t as hot as a poblano pepper, check the vertical axis on the graph. It might be logarithmic scale.

My blog: http://datascopic.net/blog2-2

Guerrilla Data Analysis 2nd Edition

Sound fx from: http://www.freesfx.co.uk/

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

Excel, Data & Event Planning

/in /by

Event planners are fascinating. Talking with them is invigorating because everything they do is about data: guests, food, client budget, beer & wine, seating, table rental, time, fees …

A good event planner has a handle on all that data.

In this video I show a data model/template that I’ve been developing in Excel, based on conversations with event planners over the years.

It’d be great to hear from some event planners about this template and how you manage your data.

(Hand-drawn images courtesy of Freepik.com)

My blog: http://datascopic.net/blog2-2

Guerrilla Data Analysis 2nd Edition

Using Excel to Model a Complex Commissions Plan

/in /by

Download the sample file here: http://datascopic.net/Cmodel

A commissions plan can be more complex than straight multiplication. This video, and this is an example of what it means to build a “model.”

Modeling involves taking someone’s intentions and interpreting those intentions in Excel so that the right thing happens. The thought process and layout are often more challenging than the math.

Thanks to ExcelByJoe for bringing up commissions payments in one of his videos.

My blog: http://datascopic.net/blog2-2

Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analy…

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

Converting a Grid into Clean Segmented Lists

/in /by

Over at Reddit /r/excel LSatyreD asked about converting a massive grid of ingredients into their corresponding recipes.

In this video, I show how to do this without writing code!
In Excel 2016, Get & Transform and a pivot table will get the desired result. (In Excel 2010 and 2013, Power Query is the old name for Get & Transform)

Also, see that a new recipe can be integrated with a simple ‘refresh.’

My blog: http://datascopic.net/blog2-2

Guerrilla Data Analysis 2nd Edition

The original Reddit question:

How To Consolidate A Sheet? from excel

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

The Tale of the Unwitting Analyst

/in /by

Lots of people are thrown into the deep end with data. They are the unwitting analysts.

This video tells the tale of someone who works at a nonprofit. In this case, data wasn’t his job. He just jumped in, took control and uncovered a key bit of insight.

He started with a report that suggested that a snail-mail campaign wasn’t worth the hassle. But he used pivot tables to “disaggregate” the data and “track a narrative” that the snail-mail campaign was vital.

I share this story because it shows how a lack of technical skill can be made up for with curiosity, drive and giving a damn.

One interesting part of the story is how the roles were defined. The small nonprofit does have a tech person and her job is keeping the website up. Any reports or reporting are up to the staff.

Hence: the necessity of unwitting analysts.

BTW: the conversation started because he wanted to thank me for my book. The pivot table section suggested to him that he could dig behind the report summaries and get at a more granular truth.

Those stories fuel my passion for Excel, data and sharing the knowledge.

Guerrilla Data Analysis 2nd Ed.

Vulture image courtesy of Momotte2stocks
http://animal-resources.deviantart.com/art/Cut-out-stock-PNG-49-Turkey-vulture-384033207

Creating Alerts and Formula Triggers for Unusual Usage Actvity

/in /by

We want to know if last week’s product usage was unusual compared to overall average usage.

This video shows how to set those alerts and use formula triggers to prevent calculations when the necessary data is incomplete.

Functions used are:
ABS – for calculating absolute value
COUNTA – for counting the non-empty cells in a range

You see Tables and Slicers, as well. This is a real Excel party!

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/B011DB4L4A

Excel Dropdown Lists for Improving Spreadsheet Layout

/in /by

Workbook Download: http://datascopic.net/dlist

This video takes you through spreadsheet layout using dropdown lists as a way to use ONE sheet where instincts would have us make 12 sheets.

The inspiration for this tutorial is many real-life situations where teeth stopped gnashing once we were able to reduce thousands of formulas and dozens of spreadsheets down to a single sheet that was easy to maintain. And dropdown lists were the key!

This video takes you through proper development before you get to the “AWWW HELL NAW!!!!” moment.

You will see: dropdown lists, tables, SUMIFS, COUNTIFS, and VLOOKUP

These concepts are also covered in my book:
Guerrilla Data Analysis 2nd Ed.

Your comments and feedback are welcome. Let me know if there’s an Excel feature or a data management concept you’d like to know more about. I would love to help and make a video.

Excel: 2 Mice and no Keyboard Shortcuts

/in /by

You see in my videos that I highlight, scroll and right-click a lot when there are shortcuts available. But this is the first time you get to see my 2-mouse setup.

2 Reasons Why I Don’t Use Shortcuts:
1. When going from software to software, I found it tough to memorize what shortcut does what in what software.
2. Years of sitting at a keyboard has resulted in shoulder problems. Having my hands so close together for so long is very uncomfortable.

Using 2 mice, altering between my right and left hands, and getting my hands off the keyboard reduces stress on my shoulders, wrists and neck. Crap data cannot win when I’ve got such stamina!

But I have much love and respect for the keyboard shortcut masters. They’ve got their style, I’ve got mine, you’ve got yours. And where all whooping crap data’s ass in this battle to keep our data clean.