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: http://datascopic.net/cussin-calc 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: http://datascopic.net/cussin-calc
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]