What are the Most Useful Shortcuts for Microsoft Excel?
Giving a fish Vs teaching to fish... If there's a particular sequence of actions you perform routinely, you might be able to capture it using the Macro Recorder and assign your own key shortcut to it. One I did that way is "Open a copy of the Active Sheet as a Sheet in a New Workbook (sometimes you want to make edits and capture some of the sheet into a PPT and then discard). What's more useful to know : you can make (almost :) arbitrary key combinations (if you go through the Macros menu, Excel limits you to CTRL+Letter and CTRL+SHIFT+Letter. But, what if you want CTRL ALT N to do something useful? Application.OnKey! Where should you put that? In your Personal Macro Workbook. https://github.com/ananthchellappa/excel/blob/master/Unlimited%20Keyboard%20Shortcuts%20in%20Excel.pdf )
My favorites - which I use *all* the time.. for some of which I actually spent some $ developing through Excel VBA coders :
- CTRL H : Return "Home" (If one of the first two worksheets is called (any of, case insensitive) home,toc,contents,readme or index, make it the Active Sheet)
- CTRL ALT T : Create a table of contents (If one already exists, update it) (Handy for starting with a big Workbook inherited from someone else - let's you navigate with links rather than just the tabs on the bottom row :)
- CTRL SHIFT X : If active cell is in a table, then create a new sheet with that cell's entire row data displayed in an easy to read format, with a CLOSE button. Try it out! (New sheet is called Legend) (Very handy for very wide tables with lots of data..)
- CTRL ALT G : Toggle the grid
- CTRL SHIFT H : by default this is "Hide current row". I remap this to "Toggle active cell fill (Yellow)" (use Application.OnKey to remap reliably..) (toggle highlight)
- CTRL SHIFT C : Create a copy of the active sheet in the same workbook
- CTRL ALT N : Open a *copy* of the active sheet as a new Book - so you can hack at will
- CTRL SHIFT ] : Enter "Highlight row and column of active cell" mode (with a confirm dialog when custom highlighting detected on the sheet) (aka CrossHair) Useful for presentations
- CTRL SHIFT [ : Exit "Highlight row and column of active cell" mode
- CTRL SHIFT F : Filter table (if already filtered using another column, then add to the filtering) by only showing (of the currently visible rows) only those for which this column's entry matches the active cell's value
- CTRL SHIFT N : Set filter to the next value in this column's element list
- CTRL SHIFT B : Set filter to previous value in this column's element list
- CTRL SHIFT E : Exclude filter (add to Table's existing filter) - of the visible rows, filter out (don't show) rows for which this column's entry matches active cell's value
- CTRL ALT F : Clear the Table's filter (and dismiss the filter drop-down buttons)
- CTRL ALT C : Clear filter from this column (of active cell) only
- CTRL SHIFT A : Re-arrange the rows of the table by sorting this column in ascending order
- CTRL SHIFT D : Re-arrange the rows of the table by sorting this column in descending order
- CTRL ALT SHIFT DEL : Delete active sheet without confirmation!
- CTRL Right Arrow : Move column (of active cell) right (aka swap its position it with its right neighbor)
- CTRL Left Arrow : Move column to the left (aka swap its position with its left neighbor)
- CTRL - : (native, not custom) : Delete selection (row or cell or column) (- = “minus”)
- CTRL T : Add a copy of the current row of the Table immediately above and increment serial number.
- CTRL SHIFT T : Add a copy of the current row of the Table above the first non-blank row of the table (i.e., at the top) (and increment serial number intelligently)
- CTRL SHIFT V : Fill the column with a serial numbers, in ascending (top-most is lowest) order (it’s always useful to have a serial-number column so that you can always send the table back to its original state after re-arranging based on other columns
- CTRL SHIFT W : Fill the column with serial numbers in descending order. This is not a bad idea - you can see how many rows you have at a glance :)
- CTRL ALT A : Re-arrange table in "correct" order using the serial number column. This is why I always add a serial number column to anything I get from anyone - then I can sort at will knowing that I can always get back to initial state
- CTRL SHIFT > : Enter row-only highlight mode (Sebastian Bulz)
- CTRL SHIFT < : Exit row-only highlight mode (Sebastian Bulz)
- CTRL J : Center stage - you have a frozen section at the left that you want always visible. Get the columns of the selected cells adjacent to
- that section by hiding the intervening columns (and vice versa - so this one toggles it). Very handy for analysis of data. Thanks Khurram Mukhtar
- CTRL ALT E : Go to end of block (go down this column to the first cell that differs from current cell)
- CTRL ALT S : Go to start of block (go up this column to the first cell that differs from current cell)
- CTRL ALT H : Filter the table to show the rows where any cell has highlighting that differs from the other cells in the row
- CTRL ALT SHIFT H : Filter the table to show the rows such that cells in this column (of active cell) have highlighting differing from other cells in their row
- CTRL ALT SHIFT S : Launch the save filter form (you have a filter applied to the table and want to save it)
- CTRL ALT SHIFT V : View the saved filters
- CTRL ALT SHIFT F : Apply the default filter from the saved filter list
- CTRL ] : Increase font in current cell
- CTRL [ : Decrease font in current cell
- ALT UpArrow : Increment (date or number in current cell)
- ALT DnArrow : Decrement (data or number in current cell)
- CTRL ALT SHIFT N : Toggle format of numeric cells in the selection : if engineering format (Eg. 100k) change to regular (100000), and vice versa
- CTRL ALT D : Fill in the blanks if you have (in a column, A,,,B,,F,,,,,C,, then you get A,A,A,B,B,F,F,F,F,F,C,C
- CTRL ALT W : Create digital timing diagram using cell borders (Thank you Daniel Jazbec)
- CTRL ALT SHIFT BkSpc : Delete the hidden rows from the table (with confirmation pop-up)
- ALT SHIFT D : Delete row of active cell
If you're not working with a table, why are you using Excel? :)
To get these, just download my PERSONAL.XLSB from https://github.com/ananthchellappa/excel
Comments
Post a Comment