Here’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.
Download 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:
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: