There are lots of exciting tools that help us manage data today. Excel 2016 was recently released and the new Get & Transform features are worth upgrading for.
Tonight, however, I stopped to reflect on why my professional life has been devoted to Excel and clean data. It’s not the technology and fancy tricks.
The songs in this video is from the YouTube Audio Library
Mast, by Silent Partner
The Night Falling, by JR Tundra
18NOV15 at 6:05pm Pacific I’m launching Excel in the Wild via ExcelTV!
In the first episode, rapper Cole Mize and I will be discussing rap and music from the perspective of data and how Excel can help.
Join us on Blab at:
Check out Cole Mize at:
http://colemizestudios.com/cole-mize/
https://www.youtube.com/user/ColeMizeStudios/videos
especially his video on structuring lyrics
Thanks to fellow Excel MVP, Jon Acampora, for recording this.
Check him out at Excel Campus:
Can an Excel file be both the source and destination of imported data via Get & Transform? It sure can.
Here I make up for a mistake I made in front of God & e’rbody on ExcelTV. The challenge:
The data looks like a report broken down by State. We want Rep data. So, without writing a single formula, Excel 2016’s Get & Transform feature allows us to reshape the data so that a pivot table is possible.
BTW: Check out the full ExcelTV interview here
It was much fun being back on the air with Rick Jordan and Szilvia.
Download the file here: http://datascopic.net/InMyHotel
This video is a though exercise more than a how-to.
Here, we look at a problem that someone described:
“I have a small hotel and don’t have an easy way of knowing who’s going to be here on a particular day.”
In this video I demo a tool that uses
a scroll bar
SUMIFS, and
a bar chart
The idea being, how can we input data, update the chart and minimize scrolling?
During a workshop someone asked if an image can automatically display based on a cell value. Well, that’s one component of my naughty-nice-o-meter. (https://www.youtube.com/watch?v=fpI3b5ETti8)
In this video we get Excel to show which of 3 pictures has the most votes.
Download 2 picture-pickers here: http://datascopic.net/2picpickers
You get the picker that’s in the video, and a picker that shows the order of 3 different images
Download the workbook here: http://datascopic.net/salmon_n_goat
Here, we need to look at partial strings of text in order to get totals.
This would be easy if, for example, “Salmon” saw in a cell by itself. But in this drill we have to be able to find “salmon” among other text in a single cell.
3 – Salmon Chowder
1- Ham & Eggs
4 – Curry Goat
7 – Salmon Steak
2 – Deviled Ham
How do we tally the number of Salmon dishes, Ham dishes, Goat dishes and Eggplant dishes?
The method in this video will show how to use the “–” that can seem spooky the first few times you see it. Also used: ISNUMBER and SEARCH.
Oh, and a little Machale Montano at the end. :)
Excel 2016 includes a treemap option among the chart choices.
What’s a treemap?
That’s what I wanted to know. So, here’s my exploration of the treemap: changing variables, changing the order and noticing when the resulting map is useful or confusing.
One main point for this video is to show behind-the-scenes of exploring and getting to know Excel: come up with some nonsensical data and just play around.
File download: http://datascopic.net/ComparisonTool
A friend was torn between 1. a personal massager that was really nice but had to be ordered online and 2. an ok massager that was at a nearby store.
In this video, we build a tool in Excel to compare several massagers based on multiple criteria and varying levels of importance. You will experience:
1. The thought process
2. Getting the math right
3. A way to break tied scores
4. Use of an icon to flag the top choice