Thoughts From The Cake Scraps

An eight – above average but falling short of the best. This is what I find when I ask people about their Excel skills.  In fact I would wager that this is what most people would say about their skills with any tool provided one thing.  The sole criteria for being an 8 is that they have used the tool enough such that their usage would be self-defined as frequent or occasionally (as opposed to used once or once a year).  The problem is, of course, that we don’t live in Lake Wobegon.  But if you ask people, person after person will tell you an 8.

I am not immune to this.  In fact, I claim to be an 8 of 10 in the business non-statistical application of Excel.  To me this means that I am not responsible for knowing the advanced mathematical and statistical formulas used in Excel, nor do I know how to write advanced VBA code from scratch.  What it does mean is that I know my way around PivotTables (and getpivot – both syntax options), am well versed in formatting data/graphs, can use a variety of text formulas (len, mid, right, left, proper, etc.), lookup formulas (vlookup, hlookup, index, etc.) and other advanced worksheet formulas (indirect, match, find, search, sumif, etc.), know how to create a dynamic named range, write basic VBA code, and create basic custom number formats (including if/then logic).

I know that I have shortcomings as well.  I don’t have the syntax for custom number formats memorized, array formulas still take a bit to figure out (and I don’t often think to use them), my VBA skills could use some polish, there are a bunch of keyboard shortcuts I don’t have memorized, and I don’t use many advanced Excel Add-Ins that turn it up another notch.  I’m sure there are others, but the point is that I know I have room for improvement and see examples of really talented individuals when I read blogs or when I am searching for an answer to an issue.

The point is that I feel like I am a well informed 8.  I am not saying I am an 8 because it just feels right.  I’m saying I’m an 8 because I put in time to learn what I don’t know.  I have been in rooms where I’m not the best, but clearly quite a bit more advanced than a large percentage of the room. And yes, I would wager money that I am not so smart that I can escape the unknown unknowns.  So I am an 8 and I know it for a fact.

And hence the 8 of 10 Paradox.  Or, rather, the Downing Effect wherein “people with a below average IQ to overestimate their IQ, and of people with an above average IQ to underestimate their IQ” and “the ability to accurately estimate others’ IQ [is] proportional to one’s own IQ”.  Applied more broadly it is called the Dunning-Kruger Effect.

So remember, as you go through life, people will always be comparing themselves to others.  Knowing your results in comparison to the average just makes us feel better.  But there will be times when there is not quick test score to compare; no easy benchmark to measure against.  There will only be an opinion.  At that moment you should really stop, be honest with yourself, and consider which side of the Dunning-Kruger effect you are on.

I give this post an 8/10.  What would you give it?

This has been a Thought From The Cake Scraps.

I was working on my laptop today and was playing around with my newly installed Office 2007.  I really need to get to know it more, but work still uses 2003 and I have become quite good at using the 2003 version.  Some co-workers are even generous enough to put me into the category of Excel Expert.

I’m not sure about all of that, though I have done some posts on cool Excel tricks, but I do know one thing.  I don’t have nearly as good of handle on 2007 as I do on 2003.  The I remembered something that @Fantastical7 told me that had changed my life on my desktop computer.  The Office 2007 Search Ribbon.

This is the best download for Office that you have never heard of.  If you are having trouble figuring out where that old command went in the new ribbon structure, this add-in is perfect for you.  No more scouring the internet for answers to simple questions.  Just simple answers that are quick and, best of all, are exactly what you are looking for.

At the time of this post, only 64, 169 had downloaded this add-in.  I have to say that that is a bad indicator.  I just wonder how much time people have wasted looking for the buttons when this add-in makes it super simple.

So, what do you think of Office 2007?

This has been a Thought From The Cake Scraps.

Every now and then a person stumbles across a small tip that makes life easy. Well, maybe just easier. Today I share one of those tips with you, the shift key.

We all use the shift key, but few people know about the additional power that it can provide. Gone are the days where it is used just for making a letter upper case or YELLING on the internet.

On a Windows OS,  holding the shift key while right clicking an item in a folder allows you to open the document read-only.  Very helpful if you don’t want to lock people of of the document, but still want it open.

The second and more helpful trick that I learned is in Excel.  Have you ever wanted to have a single page summary, but just couldn’t get it to work because the column widths for the stuff above doesn’t match with the stuff you want below?  Shift can help.

Copy the area that you want and then hold shift and go up to Edit > Paste Picture Link.  You now have a picture that is easily moved around, no matter the above column structure.  Better yet, if you change anything in the original range, it changes along with it!

Update: As commenter Ryan, of RyanMalesevich.com fame, points out, this is for Excel 2003.  For 2007, no shift is needed.  Just click the down arrow below “Paste” > “As Picture” > “Paste Picture Link”

Do you have any tips to share?

This has been a Thought From The Cake Scraps.

In the world of dashboard and report generation every little trick can help.  The trick might save you time, make something possible, or just look pretty sweet.  I think that any one of these is a great reason to employ an Excel trick, but I have to be honest; I am partial to the last one.

The reason that I love Excel tricks that look sweet is that often times if it looks sweet then it is also more likely to be used and appreciated by others.  On top of all of that it can be fun just seeing what Excel can do.  With all of that in mind, I give you my first post on Excel Tips and Tricks: Creating a Dynamic Data Validation Range.

The first tool that you will need is the “INDIRECT” formula.  The INDIRECT formula takes a text string and evaluates it as a range.  So, whereas normally a formula might say ‘=sheet1!A6’ and INDIRECT would look like ‘=INDIRECT(“sheet1!A6”)’.  The main difference is that the second formula is text and can be broken up into text segments and concanated together like this ‘=INDIRECT(“sheet1″&”!A”&6″)’ which is the key because then any component within the INDIRECT can be part text part cell references, calculations, formulas, etc.  Here’s and example of that ‘=INDIRECT(“sheet1″&”!A”&3*2)’ or if cell B1 had a value of 6 in it ‘=INDIRECT(“sheet1″&”!A”&B1)’.  All of these formulas would return the value that is in sheet1!A6.

Whew! Take a deep breath after that.  Perhaps take a second to play around with it because the next part relies on it.

The difficult part about using data validation in a cell is that the data validation range must be in the same sheet as the cell you are validating.  Unless you use a named range.  You cand define a named range in 2 ways.  The first is to select what you want and then in the box in the upper left corner of the sheet type a name.  The second is to use Insert > Name > Define (2003) or Formulas > Defined Names > Define Name >Define Name (2007)

Here’s the setup.  I am entering “List of Letters” in Sheet2!A1 with letters A – F in column A rows 2 – 7.  Select Sheet2!A1:A7 and type “Data” into that box in the upper left corner.  You should now have a named range called “Data”.

Then in cell Sheet1!A1 Use Data Validation and select List for the “Allow” drop box.  For the source type “=Data”.  You should now have a drop down in Sheet1!A1 that has all of the values from Sheet2!A1:A7.  Now you have applied Data Validation to a cell that pulls from a list on a separate sheet.  Nice.

Now for that extra piece of flare.  We need to redefine this named range.  For this go back into the Define Name – select the name and click edit; in 2007 use the “Name Manager”.

Now, we get to use the INDIRECT from before.  We will start off nice and simple.  While the current name should be defined as ‘=Sheet2!$A$1:$A$7’ we will just make that and INDIRECT so we get : ‘=INDIRECT(“Sheet2!$A$1:$A$7”)’.  Now we want to change this so that we know how far down to go.  Right now we go to A7, but what if things are added.  We would have to redefine the name every time.  Luckily, we can use the COUNTA formula to count the number of non-empty cells.  In this case COUNTA(Sheet2!A:A) should equal 7 (one for the heading and one for each letter).

Lets throw that into our named range definition in place of the last “7” so that we get: ‘=INDIRECT(“Sheet2!$A$1:$A$”&COUNTA(Sheet2!$A:$A))’.  Notice that we have to put in the quotation and ampersand right before the COUNTA because we are building a text string.  As a final touch lets say that we don’t want the heading the drop-down box in Sheet1!A1.  We have to change the row that the range starts on so we get : ‘=INDIRECT(“Sheet2!$A$2:$A$”&COUNTA(Sheet2!$A:$A))’.

As a final wrench, lets just say that the list on sheet2 is moved from A1:A7 to A:3:A9 with A1 and A2 blank.  Here we would just have to add something to the formula on the end to pad it for the blank rows and change the starting row.  In this case we would end up with ‘=INDIRECT(“Sheet2!$A$4:$A$”&COUNTA(Sheet2!$A:$A)+2)’

Let me know your thoughts on this post and if this worked for you!

This has been a Thought From The Cake Scraps.