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!!!