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!
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.
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.
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.
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.
Kevin Lehrbass offered a solution to a pretty common challenge with data. Data often comes out of a database looking as shown in the video: condensed rows in a single column. It’s not useful that way.
We have to “parse” and “unwind” the data.
Check out Kevin’s solution which he handles in native Excel with formulas:
My video offers a solution in a feature of Excel that’s called Power Query in Excel 2010 and 2013. In 2016 it’s called Get & Transform
For another example of unwinding data using Power Query:
Explosion sound effect courtesy of http://www.freesfx.co.uk
Quick description of why my recent Excel videos have focused on context and development of tools.
Spreadsheet download:
Problem:
There are 30 orders for bass strings and 12 orders for an Advanced Bundle that has 2 sets of strings in each.
How can we get Excel to tell us that, to fill the day’s orders, we need:
30 + (12*2) = 54
sets of strings?
Complicated formulas can help. However, this video shows how good SPREADSHEET LAYOUT and simpler formulas can be a better way to go.
This video is followup to a previous video where I discuss the importance of layout.
I also cover good spreadsheet layout in
Guerrilla Data Analysis 2nd Ed.
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/ref=cm_cr_pr_product_top?ie=UTF8
After watching this video of Felienne Hermans (https://youtu.be/wbiVK6HKHHg) I felt that she means well, but she painted Excel and Excel users quite negatively.
Near the end of her video she showed images conveying BI solutions as the cockpit of an airplane and Excel as a rusted bike with flat tires.
I don’t think she meant to be insulting but throughout her talk I kept thinking “they really don’t know who we are.” The folks who control the conversations about data seem ever-wary of spreadsheet users and not always for good reason.
In my video I describe the Excel world, from my own perspective, and I suggest 2 things:
1. We all (Excel users, IT, DBAs, etc.) need to see that we’re on the same team.
2. Excel users need to speak up and be involved in conversations. We kick MUCH ass when we’re on our game. We are not rusty bicycles.
A lot of the solutions suggested by IT professionals DO NOT work for Excel users. In order for there to be more workable solutions, we ALL need to be in the conversation.