This type of thing is just spot-on in terms of making life and tasks like this much less painful...dare I even say 'fun'. Maybe not that far. Perpetually unsatisfied though, two things missing from this app, at least as shown in the blog are: 1) A way for my wife to enter rules so that I can't select PB&J every night of the week, or even more than once every 3 months, and 2) automatic consolidation of ingredients (i.e. Monday needs 1.5 lbs of Chicken breast and Firday needs another 2 lbs. --> the grocery list should have me buy 3.5 lbs or recommend that I find two separate packages because the meals are more than X days apart (for fear of bacteria growth or something), but it at least lists them next to each other in the list for efficiency's sake.) There's few things worse in my sheltered world then having to return to the same part of the store again and again because items were out of order in the list compared to the store layout...which is a whole other possible product offering/service that might be interesting.

I sent this to my husband, the cook. As much as we loved it, we agreed that the hard part is not the ingredients, the hard part is getting the family to say what they want. He says, "What do you want for dinner this week?" and we say, "Whatever."

But your stuff is great, keep it up.

One problem with the prepopulated Excel shopping list approach is that if you're not careful you end up buying the same stuff and cooking the same recipes week in and week out.

:)

Alan

this is an interesting idea.
but one of the things about living in new york that i love is, i walk to grocery stores. so typically, i only buy about 2-3 days worth of groceries, at most.

when i lived in texas i would buy a weeks worth--but then friends would invite me out, i'd decide that what i'd wanted to eat 5 days ago isn't what i want to eat now...leading to wasted food. also needing to go to the grocery store to buy a weeks worth of food would "hang over my head" like needing to do 4 loads of laundry...

as i get older, i'm trying to live more and more by the idea that even if it seems more "efficient" to chunk up a lot of a repetitive task at once, it's more effective to break these repetitive tasks, which are boring and tedious, into small manageable pieces. e.g. iron 2 pants and a shirt, not 15 of each.

i grant that it's a lot easier for me to live this way as a single guy...

I LOVE this. Thank you for sharing. When I first looked at it I was concerned because the dropdowns did not have the recipes in alpha order so it was a little difficult to find the one you wanted. But then I figured out that I could sort the Recipe Main sheet by recipe and voila - alphabetized dropdowns.

Thanks again.

Alan, you triggered me: you can make the lists dynamical, I think. It just picks from a list that can be extended. It works like dynamic range names - same you can "grow" a chart everytime data is entered.

[...] Oh that reminds me of another thing that Petie and I have been meaning to try - this one I found on Juice Analytics…Cathy’s Recipe Manager is similar but you put your own recipes into this Excel spreadsheet and it creates the shopping list for you. We really will try this soon. [...]

This is all true, but the widespread use of spreadsheets also has some problems.

For example, the construction industry is obsessed with spreadsheets. They track everything from document flow to job cost and in many cases this works great. But when working on a $250,000,000 job, tracking budget and cost on a spreadsheet is just asking for trouble and this is when a database-backed solution is the better solution.

Spreadsheets may have an error in the formula on row 243 that throws off the whole budget and makes the cost engineer think that he's got some amount of money in his budget that he really does not... and this stuff happens all the time.

So while I love spreadsheets and use 'em all the time, they're not great for everything.

And that last paragraph is really scary - and TRUE.

There has benn enormous efforst to reduce computer sizees. Visualisze the Excel spreadsheet that uses company data in an ipod or cell phone. Excel users modifies and builds a spreadsheet outside od the central company information technology center. Visuallize now many, many employees doing this. Visuallize now with new 21st centrury devices that link computers directly to the brain (see the PBS series "22 Century"). These are now in existance. Visualize now that important company information now resides in this link, and may not be able to be be recaptured as a formal spreadsheet structure.
DAH

Water is fluid, soft, and yielding. But water will wear away rock, which is rigid and cannot yield. As a rule, whatever is fluid, soft, and yielding will overcome whatever is rigid and hard.
(Lao-Tzu, 600 BCE)

Let me quote the TV series "Yes Minister" (from IMDb):

[Discussing a hospital that has 500 administrators, but no doctors, nurses, or patients]
James Hacker: You think it is functioning now?
Mrs. Rogers: Minister, it is one of the best run hospitals in the country. It is up for the Florence Nightingale Award.
James Hacker: And what is to praise that?
Mrs. Rogers: It is won by the most hygienic hospital in the area.

Too often the corporate information system forgets to walk the last mile and give the users what/when/how they need. They have "the most hygienic" system because people don't use it (so they have no complains, which is great, isn't it?).

I agree with the basic points made -- databases allow rigorous control of data; spreadsheets provide the flexibility of asking and analyzing every eventuality that arises, not just those that are built into a database construct.

So, my practical question is -- what's the balance? Realistically, how can we evolve a system that provides the best elements of both, or is that a pipe dream?

I wouldn't put down OpenOffice as a decent alternative (actually, NeoOffice, based on OpenOffice, is the best).

At work I depend on Offce2000 (especially Excel) and SQL Server 2000. VBA work is a huge part of my livelihood. So I know quite well what's missing from the Mac version. I also believe it was a mistake to remove VBA. However, given that fact, NeoOffice is quite a nice alternative on its own with a strong level of compatibilty with Microsoft Office. If you add zero cost of ownership then it becomes quite attractive. I have Parallels on my Mac at home but if any Mac owner were to ask me about this issue I'd point them to NeoOffice first.

Looks good! I'll soon be using the clever in-cell graphing, and conditional formatting examples 2 & 3 on some larger tables.

A quick note: check cell F17 of "Absolute vs. Relative references." It looks like the formula should be $c$10, not $c$60.

Hey, I just started reading your company's blog. Some really interesting stuff, keep up the good work!

Loved the Excel tips. The only thing I would add is that if you are doing sheets of numbers and want to include the REPT function to add some nifty graphs, you can add a full block (under add symbol) to once cell in the sheet and use that as the character in REPT. This actually gives you solid bars that look exactly like Excel charts. Repeating the "|" character looks a little strange, imho.

Looks like there will be no need for this in the new Office release though, I think there is some type of cell shading function/bar function based on the cell's numeric value. Haven't played with a beta release so not too sure what it does exactly.

Paul

lol, I've just seen the original thread for the in-cell Excel graphics - I think my idea has already been mentioned previously - at least 100 times!

Paul

[...] Juice Analytics’ Excel training worksheet is available for download. It covers their previously identified core Excel skills. [...]

Some quick comments:
- Very useful to a quick and basic understanding what you can do with Excel. Many people tell me they know Excel, but often not so quite ...
- I like the what-why-how pattern.
- I miss general sheet design principles. There is barely a structured approach available (a huge lack - and a genuine opportunity), e.g. to separate parameters in different cells, to plan ahead, to document yr formulas, to colour input cells, to breakdown into more cells to facilitate troubleshooting, to use names for parameters rather than referenced cells to ease reading formulas, etc.
- In "absolute vs relative references", I wld add the other two options (relative col- absolute row, and v.v.) Also, mention F4 to toggle (I see many people manually add the $).
- I don't like "always FALSE" in yr VLOOKUP instruction. Especially in huge dBases this can be painfully slow. I do agree that TRUE can give problems, but I think the absolute statement is a step too far. I suggest to change into "always use FALSE, unless you hv a reason not to" (ok, this looks like I am a lawyer - forget it).

I hope these comments don't sound negative. Overall it's very useful. You guys continue to impress me. Keep up the good work!

This is a great idea, and I'll be using it with my stat computing class this week.

The only thing I don't like are the charts with two axes - this is a bad idea! It's very easy to manipulate the scales to mislead. I think it is better to use two charts, clearly illustrating that there are two different data sources (and leaving no doubt which axis belongs to which series).

on Keyboard Exercise 3, Control-Shift-* selects the current region, so you don't need to use the arrows keys.

Good as far as it goes. It's the kind of thing I feed as tidbits to colleagues when I am feeling generous :)

But I too (like Henck) am waiting with bated breath for more on Data-Transform-Present, sheet design and so forth.

Keep up the good work

The document looks great, but I might add a tab on managing printing from Excel. Kill the gridlines; center Horizontally; fit to 1 tall 2 wide; and, columns or rows on multiple pages are everyday things that users may need an introduction to.

Keep doing what you are doing. Great stuff.

[...] » Excel Training Worksheet - Juice Analytics A surprisingly helpful tutorial on Data Organization in Excel. (tags: tutorial excel) [...]

Just a minor point. In your keyboarding examples, you use Alt-E-D-R and Alt-E-D-C. In my experience, Ctrl+- (Control and minus key) instead of Alt-E-D is easier to remember, as well as being one keystroke less.

Several comments:
1) Just discovered your site. Love it. Been wanting to get some Excel chops, and this seems a good starting point.
2) Shout out to my homeys (I live in Reston).
3) Wahoowa! (PhD in CS, 1995).

[...] If you happen to find yourself surrounded by Excel gurus and are having trouble keeping up, as anyone who is new to the financial industry may find, you might find Juice Analytics’ Excel Training Worksheet handy. [...]

I regularly have to export contact information from our database, and I never knew a function could split the full name into two columns. Thank you for sharing this tip!

[...] Now - Skapa dashboards i MacroMedia Flash av dina Excel-filer Jump to Comments Oftast när du ska visa dina sälj- eller kunddata har du samlat på dig det i Excel och sedananvänder du de diagram som finns där för att visa utfallet. Dessa diagram är inte alltid så upphetsande och givetvis kan du göra dem snyggare antingen genom träning eller andra program som skapar snyggare Flash-diagram såsom Swiff Chart från Glob FX. Det program som jag tänkte rekommendera heter CX Now och är gratis och fungerar med Excel-filer som du importerar och konverterar till allehanda Flash-filer för presentationer. Flashspelare finns numera på nästan alla datorer som har en webbläsare vilket gör att du inte har några problem med att mottagaren inte kan läsa filen. CX Nows storebror heter Crystal Xcelsius och har givetvis massa andra funktioner men den har också en prislapp, vilket CX Now inte har. Templates på vilka typer av dashboards du kan göra med CX Now och Crystal Xcelsius hittar du här och här. Vill du kunna skapa mer scenario-inriktade filer och få till en “wow-effekt” (t.ex “om jag ökar min marknadsinvestering med X%, får jag +Y% i intäkter”) måste du lära dig att använda VLOOKUP-formeln i Excel. Juice Analytics har en bra träningsfil i Excel som kan få dig uppdaterad på VLOOKUP och de viktigaste formlerna och genvägarna i Excel. [...]

This is a great summary and found some things i didn't know. I would consider covering Index/Match as it is totally essential for real excel mastery and much more robust than VLookUp. It's a bit tougher concept, but that's all the more reason to include it. I think that you guys would do a good job explaining it.

Very nice for beginners. But I thought you guys were hardcore. Where's SUMIF? The database functions? Pivot tables? Using SUMPRODUCT to do SUMIFs with multiple criteria?

Great start!

But I have to echo Aaron on VLookup vs. Match + Index. Not only is Match+Index more robust, it also uses less memory and calculates more rapidly. Rather than being an advanced tool, Match + Index should be taught to the exclusion of Vlookup & Hlookup in any excellent Excel training.

Also needed are Indirect() and Offset(), although these are only indispensable for more complex worksheets.

Then there is the little known trick with aggregate functions (such as {=SUM(IF(a1:a10>0,a1:10,0))} ). This is far more powerful than the SUMIF() function. To get the curly braces, use CTRL+SHIFT+ENTER after typing the formula.

I realize you may not choose to include the above, but I figure mentioning them might inspire others.

Finally, the chartjunk example still has an unnecessary 3-D component. Reducing the graph to 2-D, eliminating the grey background, etc. would be an improvement.

I love this site!!!

I just got a new computer with Excel 2007. Confusing?
Where is "HELP"
Where is "Format"
Where is "Tools" "Insert"
How do I "unhide a column"
Is there an instruction manual on line?

.
J - Just
U - Umazing
I - Interesting
C - Complete
E - Excel

Package....

Kudos to team !!!

Great set of worksheets. One complaint - while some of the exercise worksheets contain an answer key, others (notably the conditional formatting one) do not, making it difficult to verify if the correct solution was used.

great stuff; especially the in cell graphics; simplicty all over!!

when summing values of a column which may include #N/A results, rather than IF-fing all rows in an additional column, why not use =SUMIF(range,>0,range)

happy excelling

it is very nice work thanks
her i see an excellent idea about learning excel
i am an ict teacher
can you send me any sheet about ict learning please

hello

i have downloaded this training file its really useful, thanks alot.

Excellent!

Maybe it's an enigma, wrapped in a riddle?

Sorry, good post. I think Gladwell's use of the terms puzzle and mystery are a little silly, and not really how most people would differentiate between the two different concepts. A much better terminology comes from Herb Simon's work on bounded rationality: he argues for thinking about decisions as being on a spectrum from programmed (simple answers, algorithmic solution, theoretically simple to automate) through to unprogrammed (difficult, no single solution, high levels of uncertainty, impossible to automate). Even better than Simon's terms are Peter Keen's 'structured' and 'unstructured' (but they mean exactly the same thing).

You're exactly right in your analysis, though. Much of business intelligence seems to boil down to the use of structured techniques for unstructured problems.

I don't think it's an either/or answer. I'd say figuring out what the customer wants or needs is a mystery - there's a million possibilities and way too much info. Once you've figured out what the customer wants/needs, you have to fulfill it and that's where the puzzle comes in - how best to meet those needs.