Actuarial Outpost

Actuarial Outpost (http://www.actuarialoutpost.com/actuarial_discussion_forum/index.php)
-   Software & Technology (http://www.actuarialoutpost.com/actuarial_discussion_forum/forumdisplay.php?f=17)
-   -   Excel Shortcut List (http://www.actuarialoutpost.com/actuarial_discussion_forum/showthread.php?t=47963)

C8 Guy 05-01-2009 10:54 AM

Quote:

Originally Posted by Phil (Post 2875586)
Similarly to hiding / unhiding rows, select some rows (or a range of cells over more than one row) and try
SHIFT ALT RightArrow

This "groups" the rows (puts a little [-] sign in the left margin; click it to roll it up and hide them.)

I prefer this method to ordinary "hide rows" because the [+] sign tells you that there are hidden rows lurking. Without that, it can be hard to notice the gap in row numbers or the one-pixel-thicker separator.

To ungroup the rows ("unhide"), use SHIFT ALT LeftArrow.

This appears to work with only rows or columns that are adjacent. Are there any slick ways to hide/unhide several columns that are not adjacent?

BG5150 05-11-2009 03:41 PM

Is there any shortcut to make a cell reference an anchor cell?

For example, I have 500 in A1.

In my formula, I have "+A1 * C1" I'd like the A1 to read $A$1. Is there a way, when I click A1 to add it to my formula to make it that way? It becomes a pain to create my formula, hit F2, edit it and then copy it.

Ron Weasley 05-11-2009 03:45 PM

Press F4

BG5150 05-11-2009 04:06 PM

Quote:

Originally Posted by Ron Weasley (Post 3640495)
Press F4

That doesn't help me with the original formula.

I Have a number in A1. I also have a list of other numbers in A2, A3, A4...

I need to multiply A1 * A2 and put it into B2, A1*A3 in B3, A1*A4 in B4, etc.

I also have a number in D1 and I need to multiply it by D2, D3, D4, etc and put the result next to it.

So, my first formula, in B2, I hit "+", click A1, *, click A2, enter. Then F2, edit the A1 to $A$1. Then copy the formula down all the rows.

I do the same thing for the second formula with D1.

Is there anyway to, when I click A1, to make it $A$1 in my formula?

Ron Weasley 05-11-2009 04:11 PM

Quote:

Originally Posted by BG5150 (Post 3640554)
So, my first formula, in B2, I hit "+", click A1, click F4, *, click A2, enter.

Does this help?

BG5150 05-12-2009 09:38 AM

Quote:

Originally Posted by Ron Weasley (Post 3640567)
Does this help?

Worked perfectly.

I've only ever used F4 as a repeat command.

Thanks!

E 05-12-2009 01:56 PM

Found a new one by accident today in Excel 2007. Pressing CTRL+SHIFT+L while you are anywhere within a contiguous range puts autofilters at the top of each column in that range. Pressing it again removes autofilters. Doesn't work in Excel 2003. I also can't find it documented online.

Noddy 06-04-2009 08:12 AM

Quote:

Originally Posted by E (Post 3642856)
Found a new one by accident today in Excel 2007. Pressing CTRL+SHIFT+L while you are anywhere within a contiguous range puts autofilters at the top of each column in that range. Pressing it again removes autofilters. Doesn't work in Excel 2003. I also can't find it documented online.

This made me think to look at the ALT key way to do it and 'alt+dff' works a treat. Thanks!

(ie. works when active cell is anywhere withtin a contiguous range and it toggles. I didnt realise you didn't have to select the column headers)

GatesIsAntichrist 06-04-2009 10:45 AM

Quote:

Originally Posted by Noddy (Post 3694894)
'alt+dff' works a treat. Thanks!

and 'alt+dfa Enter' for advanced, while sitting anywhere in the criteria range or database range. (Advanced filtering is not that difficult and has some fine advantages, though often eschewed. It's easier when you define range Criteria and Database. http://www.contextures.com/xladvfilter01.html is good primer. Add additional keystrokes for the garbage-interfaced XL2007.)

If it hasn't been mentioned already, with autofiltering you can nicely capitalize on alt-down arrow and first letter recognition. [Edit: I just checked: first letter recognition seems to have been assassinated in the 2007 interface. Those j/o's.]

Brad Gile 06-10-2009 12:05 AM

Quote:

Originally Posted by E (Post 3642856)
Found a new one by accident today in Excel 2007. Pressing CTRL+SHIFT+L while you are anywhere within a contiguous range puts autofilters at the top of each column in that range. Pressing it again removes autofilters. Doesn't work in Excel 2003. I also can't find it documented online.

There are several features in every version of MS Office that are undocumented, and this sounds like one of them. Typically, a developer in the Office Team creates a little feature for his/her own use and, for whatever reason, is never removed. You must remain aware that such undocumented "features" may disappear in future versions or service packs.


All times are GMT -4. The time now is 02:27 AM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Page generated in 0.23102 seconds with 9 queries