Excel annoyance #57 – the formula thingy. Probably officially called intellisense, this tool tip is designed to give you the arguments needed for a formula. In the example you see here, you’ll notice I’m trying to enter an INDEX Formula. The formula thingy pops up right in the way of the cells I need to click in order to select my arguments.
A few weeks ago, I discovered you can simply click and drag that thing out of the way.
So am I the last person on earth to figure this out?
RELATED STUFF
No, you’re not the last. There’s a guy in a small town in Northern Wyoming who still hasn’t figured that out. After he reads this, then everyone will know.
Then I must be the 2nd guy. Good tip. To avoid this I always use the Insert Function tool…no muss no fuss.
Everyone also keeps telling me about this sandwich thing called a Whopper. Apparently it’s as good as “Cheese Cake” – whatever that is.
And now I know too! (about the formula thingy, not the sandwich)
Ha! Great tip. That pesky box has been pissing me off so much for so long that I moved from Northern Wyoming to New Zealand just to get around it.
While on the subject of this, why does the tip display underlined blue text that does nothing when you click on it?
This is a good tip. It is also easily forgotten when the time to use it is at hand. Another issue about the Formula thingy is I don’t figure out how it works. For example, I will type in ‘=Today” or “=Now.” So the formula pops up, I think, “Oh nice.” Hit enter, and excelt spanks me. I need to click on it. I’d think I’d learn, but it gets me often.
Well how about that, I read the RSS and thought what heck got a few minutes and they say it may save my job lets see what Bacon Bits is about, good grief Freddddddd the first one and now you know something new. You know the simple things really get ya. Always thought you click on that thing and poof goes the sheet and everything with it. Thanks a lot, you got a new reader.
I’m all for tips, but how about something with some meat next? Enough bacon bits, let’s have some real bacon.
C’mon df…Mike’s strickly free range, and free range bacon tastes better than intensively farmed stuff. Plus it makes for happier datapigs. Don’t fence him in.
This is such a nice tip… I didnt know you could move the formula thingy… another thing I use often (more so when you have long formulas) is, you know how with those big formulas, when you try to type them in the cell formula hides the cells behind them making it impossible to see which cell you are trying to link to (ofcourse if you remember the references etc you wouldnt crib…). You can just change the alignment of cell from right to left or otherway just to see where the references are ending up.
I dont eat bacon, but baconbits, yes
Down here in South Africa . . . I must be the last person to find that out . . . Thanks Mike
Duh, thanks for the tip! I must be the person after the last person to learn this. BTW, I have been eating cheesecake every day since discovering it last week but I am getting tired of it where do I get one of those “Whopper” thingees?
Nice blog.
Anyway to subscribe this to Google reader?
I believe you have that option by clicking on the green RSS icon at the top of this blog.
Datapig – these autocomplete prompt don’t get in the way so much if you type formulas in the formula bar rather than directly in the cell.
@Charles – when you see a function you want, hit TAB to select it (this also inserts the first bracket ready for your arguments. If it does not need any arguments, you can then hit Enter to complete it and it closes the bracket for you.
Try this with =su and use the arrows to choose one of the many functions begining with that letter-pair. Arrow down the list, TAB to select, enter necessary arguments, enter to complete)
The best information i have found exactly here. Keep going Thank you
I still have to finish reading your pivot table data crunching book, already saw all videos and now a blog; you don’t know how much you helped me in my work.
Thanks for everything!!!
Regards from Mexico.
Your pivot table and Dashboard reporting books have been a great help to me too, Mike.
Suggestions for 2nd editions: more on using a stripped back getpivotdata function to reference a pivottable based on row and column ‘pointers’ in the dashboard report itself
By stripped back, I mean without all the field references that it would generate automatically, but with just enough references to get say revenue info based on information a user selects from a validated cell in your pivottable itself. this way, users could alter their dashboard themselves by adding or changing say customer names or product SKUs, without having to go near a pivottable.
On the pivottable front, would be great to see some VBA code to store and retrieve all pivottable filter settings, in the way that custom views works for other filters.
That said, these books are pure gold as is.
Outstanding work!! Often it is the little things that can make be of greatest help.
Just to add, you can turn off the function tip in Excel 2007. Of course once you do, you will realize how much you need it.
Select the Office Button (top left corner)
Select Excel Options (bottom right corner)
Select Advanced in left pane
Under Display section in right pane
Uncheck Show function ScreenTips
Now i am the last to know. Great post!
This is way old trick dude
Jeffrey Weir, you may have figured this out in the last 2.5 years, but I was just reading this post (I’m now the official last person) and five minutes later I was typing a formula and realized that the blue thing is a hyperlink, i.e., if you’ve typed in some arguments you can click the hyperlink for one of the arguments and your cursor will go to section. So now I learned two new things here today.