What Is Excel? | Excel Ignited Day 1 | SSSDEVA

/in /by

“What is Excel?”
I’ve been asked this over the years by people who are curious about Excel or want some insight into what Excel can do.

Answering the question is difficult because of the endless ways that people have used Excel to serve their needs.

In this video I answer “What is Excel?” several ways, in summary:
– Excel is a development platform for managing data
– Excel is the second best software for ANY purpose
– Excel is just a tool that serves a purpose.

This video is Day 1 of 31 days of videos as part of #SSSVEDA: Vlog Every Day in August (VEDA). The SSS part is Amy Schmittawer’s SavvySexySocial

SSSVEDA: Vlog Every Day in August 2016 [SIGN UP]

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

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

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

Intro to Excel Ignited (Illuminating Excel for Beginners)

/in /by

For VLOG EVERY DAY IN AUGUST 2016 I’m dedicating it to 31 consecutive days of basic Excel and calling it Excel Ignited!

My regular work had focused on complex Excel skill and data management needs. But Excel Ignited will start with the most basic in Excel. That initial spark that grows into a roaring fire to help us get at the data we need.

I invite those who are interested to ask questions. Help me to help you light this fire.

My book: Guerrilla Data Analysis 2nd Edition

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

http://datascopic.net/FFF-Modulo

FFF: Powery Query (Get and Transform) – Shifting a column up by a single cell

/in /by

Excel’s PowerQuery (Get & Transform) forces the user to think in terms of full column, full rows, full datasets. This creates a problem:

What if you need to shift a column up by just 1 cell? That’s easy in native Excel. In PowerQuery this takes several steps.

In this example, artists aren’t matched with the right songs. To get them matched properly in PowerQuery, I show:
Index Column
Modulo
Conditional Columns
Fill Up

Check it out!

Download the sample workbook:

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

FFF: Splitting names from a cell that has other data in it

/in /by

The goal is to split a name out of a cell that included the person’s age, and the make, model and year of the car they drive. (download the workbook: http://datascopic.net/ParseGT)

I started this video with the idea of showing you a complex array formula solution. But I kept thinking about an easier way. Ah! I found TWO easier ways:
1. SUBSTITUTE and
2. Get & Transform (Power Query) Split and Merge columns.

The video starts with an explanation of where Funky Funktion Friday has been over the past few weeks. I’ve been on a mission with John Michaloudis of MyExcelOnline. We developed a course that focuses on data cleansing. You can check out the bonus videos on this link:

Now. I’m back. Back to spread the funk!

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

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

Power Query – consolidating 2 columns of data using Don’t Aggregate

/in /by

This video is in response to a question about a previous video where I showed how to unwind some data. (https://www.youtube.com/watch?v=zlEp9IXdaoI)
Delta Sport asked about going in the opposite direction: starting with the unwound data, wind it back up again.

This is challenging. It calls for multiple steps in both native Excel and Power Query. Excel Features you’ll see:

Pivot (in Power Query)
TEXTJOIN
Don’t Aggregate
Merge Columns
Add Custom Column
Index Column

My book: Guerrilla Data Analysis 2nd Edition

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

FFF: Part 3/3: Get & Transform – Custom Columns and Null Values (and Runningman Challenge)

/in /by

For this Funky Funktion Friday, we continue expanding on the party list from the previous videos. This time we have 2 issues:

1. A new column is added to the source data, how do we get it to show up in the final list?
2. We need a sum but Get & Transform doesn’t treat “null” the same as 0.

I show how to handle these in this episode of Funky Funktion Friday.

The previous videos are here:
Full Outer Join

If Statements in Get & Transform

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

Kevin Lehrbass & Oz Compare Solutions: Alphabetizing Titles in Excel

/in /by

Kevin Lehrbass and I went head-to-head and came up with 2 solutions for 1 problem: how can Excel alphabetize a list of titles and ignore: A, An and The?

Kevin uses a sweet array formula while I went the guerrilla route and implement a manual step. We both agree that any solution has to consider the context of what YOU need it for and how frequently it needs to be modified.

Download the sample workbook:
http://datascopic.net/Kevin-Oz-Titles

Check out Kevin’s discussion of the challenge:

Kevin Lehrbass at YouTube:
https://www.youtube.com/user/MySpread…

Kevin’s Blog: My SpreadsheetLab:

Home

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

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

Music used:
Hitman by Kevin MacLeod is licensed under a Creative Commons Attribution licence (https://creativecommons.org/licenses/…)
Source: http://incompetech.com/music/royalty-…
Artist: http://incompetech.com/

FunkyFunktionFriday: Part 2/3: PowerQuery, IF Statements & Custom Columns

/in /by

Continuing from last week’s FunkyFunktionFriday, we expand on the Full Outer Join and I show you how to write IF statements in the PowerQuery (Get & Transform) editor.

You’ll see how to add a custom column, do more data shaping and boost your overall familiarity with the gifts that continue to flow from PowerQuery.

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

Funky Funktion Friday – Part 1/3 Excel’s Full Outer Join in Power Query

/in /by

Today’s FunkyFunktionFriday continues from last week and explores the joins in Excel’s Power Query (Get & Transform).

We start with a list of people who were invited to a party, and a list of people who are coming and the food they’re bringing. It would be great to have a single list that not only brings the data together but also matches people who are on both lists.

Please, check out the video,

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

“Protofunk” Kevin MacLeod (incompetech.com)
Licensed under Creative Commons: By Attribution 3.0 License
http://creativecommons.org/licenses/by/3.0/