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

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

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/ref=sr_1_1?ie=UTF8&qid=1450857844&sr=8-1&keywords=guerrilla+data+analysis

The Dark Side of Working With Data

/in /by

In response to a previous video about using data to solve problems, someone brought up the question, “if you were supposed to be answering phones, were you supposed to be that deep in the data?”

(The original video: https://www.youtube.com/watch?v=SnKq5wWC4Wk)

Her question reminded me that data involves more than Excel, pivot tables and being a hero. Sometimes there’s a question of what you’re willing to risk in order to dig more and uncover more details. Risk your job or career? Risk being labeled a troublemaker? Risk uncovering dirt that the wrong person didn’t want you to see?

I tell these stories because these are the things that make an analyst’s job either difficult or exhilarating. The how-to stuff isn’t dramatic. The politics and potential consequences force an analyst to ask, “what are you prepared to do?”

My book Guerrilla Data Analysis can be purchased here:

Kevin Lehrbass and More About Named Ranges

/in /by

This is fun!
First, I try to figure out how tall Kevin Lehrbass is. I saw a picture of him and he’s almost in the sky.

In the second half of the video, I compare Kevin’s video on named ranges

and my video.

Kevin’s video goes beyond the named ranges and points out that contiguous data would be more beneficial than named ranges. In his case, formulas would be simpler and cleaner.

My video was inspired by a situation where consolidating the data wasn’t an option.

The upshot is: go for the clean data in a contiguous range … if that option is available to you.

Be sure to check out Kevin at his website: http://www.myspreadsheetlab.com/

Indirect & Named Ranges Part 2

/in /by

Erika asked for more detail about my previous video: Indirect & Named Ranges for a Quick Summary.

That video also has a link for downloading the workbook.

Here, I respond to Erika by going deeper into the named ranges.

Let me know if you have questions so that i can get you an answer.

Also, my book Guerrilla Data Analysis 2nd Edition is available at:
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/ref=cm_cr_pr_product_top?ie=UTF8

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/ref=cm_cr_pr_product_top?ie=UTF8

Why Data & Excel: feeling the true impact

/in /by

One important aspect of working with data and Excel is being in touch with the impact and the people who live with the consequences. Those ARE NOT big data and IT issues. Those are the issues of people who face the customers and need to be empowered to work with data.

When I wrote Guerrilla Data Analysts 2nd Ed., that reality is what inspired me to keep writing: empowerment of the people who are on the front lines.

Check out the book here:

Music used:
On the Tip by Jingle Punks

Future Gladiator by Kevin MacLeod is licensed under a Creative Commons Attribution licence (https://creativecommons.org/licenses/by/4.0/)
Source: http://incompetech.com/music/royalty-free/index.html?isrc=USUAN1200051
Artist: http://incompetech.com/

Intersection Feature & Named Ranges To Create a Summary

/in /by

This came from a real problem with creating a summary from a source of non-contiguous data. The solution used includes:
named ranges
the intersect feature
SUBSTITUTE function

That was after I considered SUMIFS and pivot tables. Sometimes a person gets to a place where a quick, dirty result is preferable to “the right way.” So, you go to battle and fight for a quick win.

Download the file here: http://datascopic.net/intersect

http://www.contextures.com/Excel-Christmas-Planner.html

Excel Tools for Planning Christmas

/in /by

Excel can even help plan Christmas! In this video I describe 4 tools:

1. Excel Christmas Planner
Debra Dalgleish

This has multiple components to help Christmas go right.

2. 2015 Advent Calendar
Mynda Treacy

Excel Advent Calendar 2015

3. Christmas Expense and Budget Tracker
TheSpreadsheetGuru.com
Chris Newman
http://www.thespreadsheetguru.com/blog/christmas-expense-tracker-excel-spreadsheet-template
4. Download the Naughty-Nice-O-Meter at my website:
http://datascopic.net/NNO2015
A fast-motion video for developing this tool from scratch:

Music in the video:
– “Dance of the Sugar Plum Fairies” Tchaikovsky

– Hip Hop Christmas by Twin Musicom is licensed under a Creative Commons Attribution licence (https://creativecommons.org/licenses/by/4.0/)
Artist: http://www.twinmusicom.org/

https://blab.im/oz-du-soleil-excel-in-the-wild-paige-worthy-data-and-real-talk-2

Trailer: Excel in the Wild Guest – Paige Worthy

/in /by

Paige Worthy is the next guest on Excel in the Wild. Paige is a Content Strategist, Client Services Manager, Writer, Editor … and has a love-hate relationship with data.

It happens here:

Wednesday 25NOV15 @
10:30am Pacific
1:30pm Eastern

We’re going to discuss data, spreadsheets, Excel from a “regular person’s perspective. This isn’t an expert-to-expert conversation where experts assume what non-experts need to be successful.

This will be a fun, insightful conversation about what’s REALLY happening out there in the wild with data and Excel.