Excel: An incomplete guide to magic.
Standard use: a grid! This is the MOST USEFUL thing in Excel.
Cantrips: sum
White Magic - these are within Excel's wheelhouse and what it does well.
1st level: concatenate, references, formatting
2nd: conditional formatting, Linking to other sheets
3rd: PivotTables, IF statements, Charts
4th: AND
5th: OR (OR is always mentally more difficult)
6th:
7th:
8th:
9th:
Dark Magic - use with caution. This is outside of what Excel is best at.
1st level: Vlookup
2nd: index & Match
3rd: Offset
4th: Scripted VBA
5th: VBA
6th: VBA variables
7th: VBA parameters and meaningful subroutines
8th: Excel Front-ends and buttons
9th: Excel as ODBC Tool
What am I missing? Would you describe these different?
no need to ask me questions, unless you really want to. Just state your opinion and discuss with each other.
Subscribe to:
Post Comments (Atom)
Wait, pivot tables are only level 3...I can't even figure out what they are supposed to do...
ReplyDeleteMost of the time I use excel, I need more advanced math than sum. Weighted averages are common. Also sorting.
ReplyDeleteThey give speed of thought analysis for data structured in a particular way.
ReplyDeleteI can't believe I didn't do a whole math line up. You wanna take that on for us, Jonathan Beverley ?
ReplyDeleteLocked cells should be in there somewhere
ReplyDeleteI'd argue that Index(Match), while it IS "fancy VLOOKUP" , but not at all Dark. I intentionally possess no skills at any of the other stuff on the Dark list, but Index(Match) is both straightforward and incredibly useful in my experience.
ReplyDeleteJosh McGraw “Just a little bit of dark magic, really it’s just ~grey~ magic, can’t hurt anybody.”
ReplyDeleteJosh McGraw I intentionally put everything that heads down the path of treating Excel like a database as dark magic.
ReplyDeleteWhite magic:
ReplyDeleteNamedRanges
Trend lines (actually Charts would deserve a split up in quite some useful applications)
F11
randbetween
Make good looking tables
Dark magic:
Filter
Sharing with users with different language versions (e.g. comma vs point decimal)
.xls*x*
Array functions, the deep magic that hardly anybody knows but can shake worlds
ReplyDeleteAndrew Ragland That may be a fifth level math spell.
ReplyDeleteGerrit Reininghaus I'm surprised to see filtering as dark magic!
ReplyDeletexlsx, yeah ... And once you realize it's a zip file? Life gets weird.
Gerrit Reininghaus Unless, do you mean ADVANCED filters?
ReplyDeleteBEGONE FOUL DEMON OF THE NIGHT! (that is: dark magic for sure!)
William Nichols yeah, it is Dark Magic if you use Filter->Sort by without knowing exactly when it permanently changes the order of your list and when it doesn't.
ReplyDeleteWilliam Nichols: I'd try, but I don't see how AND/OR are higher up than pivot tables, so I can't really place. In my mind, the weaker set is basic stuff like AVERAGE(), MIN(), MAX(), the greater POWER(), STDEV(), and FLOOR().
ReplyDeleteAlso, I think I'd give $-references a call out.
Gerrit Reininghaus I keep a "sequencer" column, numbered 1-N, in the "true" order. Any time I get done playing with filters, I just "sort low-to-high" on that column and am back where I started.
ReplyDeleteJosh McGraw $-references are the Lightning Strikes of Excel, I totally agree.
ReplyDeleteWilliam Nichols did you know that in German it's common to call people who know how to use Excel beyond the basics as 'Excel-Hexe' which rhymes nicely (because many Germans pronounce Excel with emphasis on the first e, i.e. more like Exl, and translates to 'Excel-witch'?
'
Gerrit Reininghaus In the US military there is a derisive term for staff personnel who just spend all their time tweaking PowerPoint slides instead of doing actual work: the "PowerPoint Ranger." In that vein, I've been described as an "Excel Commando."
ReplyDelete