Here’s something really quick, and no frills.
Someone asked about writing an IF statement to distinguish between bold and non-bold text.
No, you can’t write an IF statement. But you can take advantage of 2 features:
1. Find/Replace can find the bold and replace with a colored fill
2. Excel can sort and filter by the colored fill.
Here is head-to-head comparison of Google Sheets (GS) and Excel, and how they handle a few basic data-related needs. We look at:
– Input Controls (Data Validation)
– Pivot Tables
– Pivot Table Calculated Fields
My concern is that the people who tout GS as a legitimate Excel replacement really don’t know what they don’t know. They probably don’t know that it can help them to control cell inputs–something that GS does very poorly (unless you write JavaScript code).
These are basics. See for yourself.
Classic use of VLOOKLUP is interrupted by the fact that we don’t have a master list to retrieve our values from. We just have 1300 names and area codes.
Ok! We can create a master list using Text-to-Columns, and Power Query’s Online Search and UnPivot.
You’ve got to check out UnPivot!
My daughter Jekyll has joined a band. One thing that young bands struggle with is everyone’s desire to play all the time.
In this video, we study the song Pressman, by Primus. They are 3 guys (Les Claypool, Tim Alexander, Larry LaLonde) who are a brilliant example of a small band that makes clever use of instrumentation to generate endless ideas.
I map the song out in Excel to show the unique features in the instrumentation and arrangement.
I tried to have a birthday party for my son, and it was ruined by all the bad kids that were invited.
Easy use of excel tables and pivot tables helps tally what’s owed by each parent–whether they had 1, 2 or 3 kids at the party.
One parent convinces me to factor in the cost of the gift one of his children brought.
This is an example of Excel used with no formulas. There’s simple use of basic features to get at some important details: did the children to enough to keep the dog?
You see: tables, pivot tables, pivot charts
The trick is in the layout. All of the data is in 3 columns. Notice that walks and baths aren’t separated. Everything is in one range.
It’s one thing to search/sort/filter for specific, orderly attributes (e.g., a coat has a size, a price, a color and a season). But what if you need to get at data that has attributes that aren’t in any order?
Example:
Drink A = Whiskey, Basil and Coffee
Drink B = Whiskey, Orange Juice and Lime
Drink C = Coffee, Whiskey, Cointreau and Cayenne Pepper.
Drink D = Frangelico, Lime, Basil
How do you filter for the drinks that have both Whiskey and Coffee? It would be inconvenient to get the ingredients lined up.
This video shows a solution and I will follow up with a more detailed blogpost. Until then, you can download the file here:
Review of 3 products from California Blazing Chile Farms:
Carolina Reaper Sriracha
Ghost Pepper Sriracha
Carolina Reaper Caramels
You have been summoned to a meeting to understand your role in a plan to counter the anti-VLOOKUP people. Take it very seriously.