Oz singing Karaoke at the MVP Summit: Disco Inferno
Thanks to fellow Excel MVP, Jon Acampora, for recording this.
Check him out at Excel Campus:
Excel 2016’s Get-&-Transform for importing and cleansing Data
/in Videos /by Oz du SoleilCan 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.
Using Excel to show future hotel reservations
/in Videos /by Oz du SoleilDownload 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?
Select an image based on a cell value
/in Videos /by Oz du SoleilDuring 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
How can we tally orders based on partial strings of text?
/in Videos /by Oz du SoleilDownload 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: treemap exploration
/in Videos /by Oz du SoleilExcel 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.
Product Comparison Based on Importance of Criteria
/in Videos /by Oz du SoleilFile 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
Excel: Can a sheet be deleted safely?
/in Videos /by Oz du SoleilHere’s a problem:
You’re working with a workbook and need to delete some worksheets, but it’s not clear if deleting a sheet is going to crash formulas on other sheets.
Here, the Inquire add-in gives us a diagram showing what’s tied to what.
Cussin’ Calculator: Using Excel’s SUMPRODUCT
/in Videos /by Oz du SoleilDownload the Cussin’ Calculater here:
PROBLEM: How can you assign numerical values to text and have Excel generate a sum based on entry of the text?
ANSWER: SUMPRODUCT
Here’s how to tally the amount of cussin’ your kids are doing.
Download the spreadsheet here: