Juice Analytics

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?

Are you calling us out? Would you like dueling spreadsheets at 20 paces?

Fair enough. There are a number of more advanced features and skills that we did not include in this training document. The goal was to lay a solid foundation of knowledge with the features that we use most frequently. Pivot Tables is the notable exception — there are some pretty decent tutorials on PTs out there and we didn’t feel that we could do them justice in the time/space we had.

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.

The keyboard short cuts are great and I appreciate you sharing these for gratis. Just yesterday someone was trying to sell these to me for $3 a pop – okay it was a hard copy and something we were considering giving to our students but I think I’ll direct them here. Sincerely Billy.

http://www.trainingconnection.com

Thanks a lot, Billy. We always appreciate the props… and the traffic.

First, let me say that this is a great training tool that I plan on sharing with the rest of the office!

Second, one keyboard shortcut I noticed that is missing is the use of the F4 key to create an absolute cell reference when entering a formula. You can hit this key multiple times while hovering over the cell to toggle between A1, $A$1, A$1, and $A1. This shortcut has proved invaluable to me as I trudge through mind numbing formulas!

Thanks and keep up the good work!

I would like an exercise in Excel online free for users to practice on their excel skills. like a tuturiol so, one can see where their strengths and weakness are in Excel.

I have just completed my Certificate 111 Business Administration where I learnt some great new skills in EXCEL but I need to keep practicing these skills …is there anywhere where you can get practice sheets from

 Excellent article on excel training.I liked your article a lot.simple short and descriptive.Thanks for sharing.

Simple short and descriptive article.I appreciate what you have written.Thanks for sharing.
Keep sharing always.

really amazing sheets provided by you sir …

Simple short and
descriptive article.I appreciate what you have written.Thanks for
sharing.Keep sharing always.

Nice shared list.Thanks for sharing such smart tips.Keep sharing as always.