Do you know these Double-click Tricks in Excel?

Oh dear god! 9 o’clock in the morning and I’ve already learned about 3 new hitns. :-O Evertytime I start thinking that I know Excel quite well, somebody posts a article and persuades me that I do NOT.

Btw, I use the mouse also to minimize and maximize the Excel window by double-clicking on the top of the window. Might be useful for somebody although it’s a well known trick.

Almost I use the Auto-fill to copy data on my work.

Thanks.

Since I found you can do it, I always double-click on a worksheet tab to rename it rather than right-click, select rename – much faster.

i have learnt “Jump to last row / column in table with double-click ” very first time. All the others tricks are in used.

May i request to put more such tricks which increase the productivity of the works. Many of such trocks have been informed by many users in comment forum. Also, new one will be posted by the users.

May i request to put all such tricks with appropriate heading and post for ever. All the new tricks will be put in the same post. Pls expore the possibility.

Thnx

I like the menu collapse trick. Nifty one that!

Favourite double-click…well…has to be the column width one then.

The “Jump to last row / column” is definitely my LEAST favorite double-click-trick – if I’m not accurate when double clicking a cell for in-cell-editing, this “trick” takes me places I don’t want to go, often far away from the cell I was working with, requiring a few seconds for finding my way back.
Does anyone know how to disable this “trick”?

Ooh, jump to last row is new to me ! That probably explains some of the random cursor movements I sometimes get

I am always amazed at what I can learn about a product I thought I knew much about. I most appreciate the minimizing the ribbon as I like to keep that out of the way most of the time.

One other double-click trick I use (and I learned this from a co-worker/faculty-member here at WCU) easily selecting words. If you have multiple words you need to select, double-click on the first word and then (while keeping the mouse button pressed) drag to select the remaining words. This works in many different apps, not just Excel. Hope this is helpful to someone.

Regarding the trick “Adjust column widths by selecting multiple columns and double clicking on the separators”, it’s also my favorite one and what I usually do is click the corner cell (without row & column name) then the whole sheet is selected, then double click the column separator or row separator. You will ahve each column or row coverred.

If you double click on a pivot table on any Data Cell (not on the column headings or row headings) a new Tab is created that includes only the data behind that number.

I use that a lot, is kind of a drill to detail functionality.

Great Site, Great Tips!

Thanks

Using a mouse with excel? Ha–peons!

As a follow up:

Adjust column widths by selecting multiple columns and double clicking on the separators

Alt-O-C-A

Double-click in the corner, just above scroll-bar to include a split

Alt-W-S

Auto-fill a series of cells with data or formulas by just double clicking

Ctrl+D

Jump to last row / column in table with double-click

Crtl+Right, Crtl+Down

Lock a particular feature and reuse them with double-click

1.) Copy (Crtl+C)
2.) Paste Formats (Alt-E-S-T)

Jakob,

This won’t disable the double-click to get to the end trick, but instead of double clicking to edit a cell’s contents, (I always have my left hand on the keyboard when mousing) I simply hit the F2 key to edit a cell.

That has helped me to prevent the problem you mention (accidently clicking the border and selecting a diff cell)

The Adjust column widths by selecting multiple columns and double clicking on the separators works in Open Office too! thanks for a useful tip

If the check box “Edit directly in cell”, in Tools.. Options.. Edit, is ticked, then double-clicking on a cell opens it for editing directly in the cell. I prefer to leave it unticked and use F2 to edit in the formula bar.

But if “Edit directly in cell” is unticked, then double-clicking on a cell selects all the cells in the current sheet that are referenced by the cell, for tabbing through and editing, just like Ctrl-[, provided the first reference is in the same sheet. If the first reference is in another sheet or another open workbook, double-clicking goes there instead. This is great for diagnostics and debugging.

Double click on an embedded object (a shape or a chart element) to bring up the Format (This Object) dialog. Very helpful, and saves lots of time, so I guess that’s why they removed it from Excel 2007.

Highlight an entire table by clicking on any cell within the table and then press Ctrl + Shift + 8

About the auto-fill trick- this works with formulas too, however the dealbreaker was that it auto-incremented cell references too.

Until I learned another trick- when making a cell reference you don’t want to change, use a dollar sign to prefix the bit you want to keep the same e.g. $A$6 will never change the reference to cell A6.

some really useful tips,
i never knew theres a split option available,
and these are really very useful and will save alot of time

My favorite tip:
Auto-fill a series of cells with data or formulas by just double clicking

Very cool! I knew most of them, but there were 2 I hadn’t seen yet – the double-clicking to keep features on looks especially useful! No more going to the top to click again after I already just clicked.

Thanx for putting this up here.

ya! very useful tips ,thanks for share~!

wow,. thanks for sharing

Hello,

All of them but mostly is “Adjust column widths by selecting multiple columns and double clicking on the separators”

Many Thanks as much as every hint

Regarding “Jump to last row / column in table with double-click”:-

Contrary to the general belief that Ctrl+up /down/left/right is always user friendly, there are certain advantages of using the double clicking trick on the edges of a cell:
1) if you use ctrl+down/right in a blank column / row, it will take you to last cell of the respective column / row which is very irritating sometimes, whereas if you double click the edges of any cell and if there is no non-blank cell in the column/row henceforward your cursor will not be moved
2) if there are blank cells around the selected cell and if you double click the edge, the cursor moves to the blank cell immediately preceding the next non-blank cell.

Great tricks, I have to use the mouse more…

Off-topic: Which animated gif-maker did you use for creating the (very effective) demos?

If you were truly efficient in excel, you wouldn’t use the mouse – you would use just the keyboard to get around. The mouse just wastes time.

jrl – Different people have different styles. If all you’re doing is navigating a worksheet and entering data and formulas, keyboard shortcuts are probably the way to go. If you’re using higher powers of thought and analysis, the rate limiting step isn’t keyboard vs. mouse, it’s neuron to neuron communication within the cerebral cortex.
 
I’m a reformed Mac user, so I use the mouse a lot, but I also grew up on a varied assortment of pre-PC boxes in the lab. So I’m probably a strong mix of mouse and keyboard.

Mouse? I’m typing this with my nose. Granted, other people in the internet cafe are giving me funny looks.

Whoops, just sneezed. Damn swine flu…

I have my mouse wheel set to page down and page up when I click it to the left or right. Holding ctrl and using this in Excel means I can quickly get around the workbook when using my mouse.

As a bonus, it also works to switch between tabs in Firefox.

Can anyone tell me how to switch between worksheets without a mouse?

NEVERMIND i’ve figured it out
cntrl + page up/down

@E, When I use Ctrl + page up/down it jumps to the next cell up, down or sideways ?? I would like to go to the next sheet. Must be some setting to turn off or on ?? Any ideas ??
A shortcut or neat trick I use is when you want to delete a collection of data – select the area of data to be deleted then ..
Alt+H+E+C .. gone .. what the HEC !

Hi
Can anybody say me jow can I use “Auto-fill a series of cells with data or formulas by just double clicking” for rows????? any help thank you all

@Chandoo,
Thank you for your reply, but I am looking for doing that for rows not for the columns. For Columns double click on the down right black point will work but what about autofilling in the rows? I tried but it did not work.
Thank you,

@Manoochehr: We’ve been asking for years and years, but there is still no way to autofill sideways, only down. With the much wider sheets in 2007, this would be a very useful addition, wouldn’t it?

@Jon @Manoochehr …here’s a possible work around.

If you select the cell that you want to copy sideways, then hold down Cntl + Shift + Left arrow, then you will have selected a row of cells either to the edge of the data in the row above, or to the edge of the spreadsheet.

If you then hit F2, and then Cntl + Enter, your formula will be filled across.

Good excel 2007 tip, Chandoo. You could also add the fill icon to your Quick Action Toolbar if you struggle to remember the shortcut.

@ Chandoo, Jeff, Jon
Thank you all, but I cant underestand what (HFIR) in (ALT+HFIR) mean in excel 2007?

Hi Manoochehr. Press Alt then press the H key then the F key then the I key then the R key one at a time. This is just a long-winded keyboard shortcut, really.

Thank you all for nice tricks,
Let me jump to another subject. Do you think that plotting contours in excel be possible??? I looked at the excel template but it was not possible to plot contours… anybody got an idea?
Thanks,
Manoochehr

Manoochehr – can you elaborate on what you mean by ‘plotting contours’?

@ Jeff,
In contours we have 3 series of data such as x, y, z. where x,y are for example locations and z could be weather forecasting data. following link will show a picture of what I mean.
http://gri.sourceforge.net/gridoc/html/example5.png
Thank you,

Manoochehr: I think it is possible to do this, as long as you have x and y data for each pressure band. But I don’t think it would be easy to get excel to intelligently apply the different data points. Were you wanting to do this for weather maps or some other work purpose, or were you just curious whether excel could do this? Do you have any sample data you could post anywhere?

Regards

Jeff

GOOD TRIKS & INTERSTING IDEA . SO VERY MUCH THANKS. ANIL CHOUDHARY

Does anyone know how to turn *off* any of the double-click features? Specifically, the one in which you double-click on the side of the cell to jump to the last cell in the column. I do this accidentally all the time and it drives me a little nuts.

My favorite tip was about double clicking on the side of a box to jump to the end of the table. i always accidentally would do that, and it really ticked me off, but now that i know what the purpose is, and its not just randomly messing with my head…. i am happy =) Thanks for the awesome tips, they can save people hours

Regarding the trick “Double-click in the corner, just above scroll-bar to include a split”… if you do that, then you will have to drag the split bar to the correct location… you can save a small amount of time by not double clicking that small rectangular bar above the vertical scroll bar directly; rather, simply (single) click/drag it to the desired location. And in case anyone missed it, there is a small rectangular bar to the right of the horizontal scroll bar that works in the identical manner (double click or single click drag) for a vertical split.

I am also very annoyed with double clicking on the cell border taking me to the end of list. I am working with big sheets of data so i have them zoomed out as much as possible. This makest it difficult to hit precisely the inside of cell.
Moreover I need to edit cells in several places accross the sheet with pasting suffix to every value. With one hand on the mouse and the other on Ctl+V it would work perfectly fine if not for the jumping functionality! Every time I am cursing bloody analysts who came up with this feature.

Regarding “Jump to last row / column in table with double-click”, I learned that it is not about “Last” row or column. If you double click on upper border of the cell, you go to the first value in that column. In case you are already on the first value you will go to first row of that column. Similarly, by double clicking left or right borders of a cell pointer, you move in the respective direction.
Very useful tools btw and I really appreciate the great work Chandoo is doing since long.

I am not able to see the Adjustment cursor in Excel 2007 ..while adjusting column width using mouse..

Please help. Is there any option to enable?

Is it possible to disable the double-click to jump to the last row in a table feature? It can be very annoying in small cells on large tables when trying to double click select text in the cell and it jumps from cell A50 to A529 or whatever the last cell in the column is… I’d love to disable the feature.

Thinking about Jon Peltier’s comment We’ve been asking for years and years, but there is still no way to autofill sideways, only down…could always use a macro that fills down, then copies the resulting range, then does a paste special/transpose to paste it across.

Jeff -

That would only take the values we filled down and extend them to the right. If I fill down elapsed years, say, and fill right interest rate, transposing years to the right will make a mess of my table.

@Rob: I feel your pain, brah.

@Rob & Saulo
Double Clicking in cells in a Table doesn’t jump to the bottom of the Table for me
I haven’t done anything to the standard Excel Install to turn it on/off ?

PLEASE help me!
I hate the jump to last or first “Trick”. That is not a trick, it’s a curse every time it happens to me when editing small cells!
There should be a way to disable this horrendous feature!
I would pay a small amount for whoever finds a solution!

Domenico -
 
Don’t encourage them to remove a useful feature. Instead change your habit.
 
Don’t double click to edit a cell.
 
Click once, then press the F2 key.

look, I am for the freedom of choice, all right? I remove what I want to remove from my excel sheet. I am not saying to remove it completely. If you like it, go for it, it’s all yours!
you should be able to disable this feature if it is of any use to you.
For me, it is quicker to double click on the cell, because what I do all the time is copy the content. When you double click in the center, the content of the cell is preselected, so that works for me, except for that 1 out of 3 times I happen to click the border by mistake, being the cells small and because Excel does not allow you to change the default raw size (only default column size, strange btw!)

I have the solution for all people frustrated like me!

Go to excel options, then click on advanced, then un-check the option ‘Enable fill handle and cell drag-and-drop’.

For the experts, the code is:
Application.celldraganddrop = false

happy exceling!

Domenico -
 
Default row size is based on default font size. You could increase the font size, but then that defeats the purpose of viewing at a zoom factor that makes the cells too small to double click on.
 
Glad you’ve found a workaround. Sounds to me, though, that disabling cell drag and drop is a steep price to pay to change the cell-edge double-clicking behavior. But that’s according to my habits, not yours.

from now on I am starting the double-click process
click click! (it’s to say thanks twice lol)

Hi all,
someone in these comments suggest about switching between sheets. ALT and up or down arrow. it does not seem to be working for me. do you have any suggesstion?

Hi Excel Lover!!! Its Ctrl+Page up or Page Down.

Cheers