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.

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.

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.

Excel Power Query: Layered Transpose & Unwinding Data

/in /by

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

Excel: Tools, Context and Possibility

/in /by

Quick description of why my recent Excel videos have focused on context and development of tools.

http://datascopic.net/bundledup

Using Excel to Count Single and Bundled Items

/in /by

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

Excel Users & IT: Same Team, Different Roles

/in /by

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.

http://datascopic.net/excel-video-tutorials/

Discussion: Spreadsheet Layout

/in /by

One weakness in spreadsheet development isn’t weak formulas or not knowing how to use pivot tables. The weakness is poor layout.

This is one topic that won’t show up in a help menu.

This video discusses spreadsheet layout and formula development. The key points for avoiding “octopus spreadsheets”:

– Don’t think about the results first. Think about organization so that you can get any result that you could ever possibly want.
– Watch the experts and how they lay out their data and organize their spreadsheets. They might not be discussing layout, but take note of where they’re putting their data and formulas.

These are great people to watch because they show how to build tools, in addition to standalone tips:
Debra Dalgleish youtube.com/user/contextures
Mynda Treacy myonlinetraininghub.com
Kevin Lehrbass youtube.com/user/MySpreadsheetLab
Chris Newman thespreadsheetguru.com
Mike Girvin youtube.com/user/ExcelIsFun/

On my website, here’s a list of apps/tools I’ve built in Excel along with tutorials and free downloads:

And here’s where you can purchase Guerrilla Data Analysis 2nd Ed.
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/ref=cm_cr_pr_product_top?ie=UTF8