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.

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:

Directory of Excel Videos & Tutorials

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

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

Using Excel To Figure Out Who Owes What

/in /by

Thanks to “Bob” who commented on a post at TheSpreadsheetGuru

In response to Chris Newman’s “Excel Christmas Expense Tracker,” Bob said he’d be interested in an additional feature: how to determine who owes what after multiple people have agreed on splitting the expenses on multiple gifts.

(See the complete question here: http://www.thespreadsheetguru.com/blog/christmas-expense-tracker-excel-spreadsheet-template#comment-2420421320

While Chris is travelling for the holidays, he invited me to share my solution. So I’m glad to be a Little Helper!

The main challenge in Bob’s request is in modelling the problem properly. The math isn’t hard. We have to understand the potential directions of the money.

Using SUMIFS, Absolute Value (ABS), and Wingding fonts, this video shows one solution for Bob’s inquiry.

If anyone has other solutions, please do share.

Download the solution at: http://datascopic.net/wow