{"id":2690,"date":"2010-06-16T03:03:26","date_gmt":"2010-06-15T21:33:26","guid":{"rendered":"http:\/\/windowsvj.com\/wpblog\/2010\/06\/handy-shortcuts-in-excel-2010\/"},"modified":"2010-06-16T03:22:39","modified_gmt":"2010-06-15T21:52:39","slug":"handy-shortcuts-in-excel-2010","status":"publish","type":"post","link":"http:\/\/windowsvj.com\/wpblog\/2010\/06\/handy-shortcuts-in-excel-2010\/","title":{"rendered":"Handy Shortcuts in Excel 2010"},"content":{"rendered":"

\"image\"<\/a><\/p>\n

Covering a few of Excel keyboard shortcuts and a few other little tricks that helps work faster.<\/p>\n

F9 \u2013 Partial formula evaluation<\/strong><\/p>\n

F9 is really helpful for debugging complex formulas, because it allows you to select smaller pieces of your formula and evaluating just that highlighted part. To try it out, just select the portion of the formula that you want to evaluate:<\/p>\n

\"image\"<\/a><\/p>\n

Be careful because any changes that are made in the formula bar will be committed to the cell if you press ENTER. It\u2019s a good idea to press ESC to preserve the original formula.<\/p>\n

CTRL + ` \u2013 Show formulas<\/strong><\/p>\n

If I want to get a quick understanding \/ review of all the formulas in a sheet, instead of selecting each cell individually and examining the formula bar, I can use Show Formulas. Here is what my workbook normally looks like:<\/p>\n

\"image\"<\/a><\/p>\n

And after pressing CTRL + ~ I see the formulas in the cells, not the values:<\/p>\n

\"image\"<\/a><\/p>\n

ALT + = for AutoSum<\/strong><\/p>\n

If your active cell is at the bottom of or to the right of a range of data, typing ALT + = will automatically put the SUM function into that cell, along with the range reference for the data above, or to the right (respectively).<\/p>\n

Before ALT + = <\/strong><\/p>\n

\"image\"<\/a><\/p>\n

After ALT + =<\/strong><\/p>\n

\"image\"<\/a><\/p>\n

Incidentally, if Excel doesn\u2019t know what range to put it, you\u2019ll just get =SUM(), which is better than typing it all out, right?<\/p>\n

CTRL + [ and CTRL + ] to select direct precedent & dependent cells<\/strong><\/p>\n

In a complex spreadsheet with formulas that build on and reference each other, you may want to know which cells are precedents of or dependent on a particular cell. For example, consider this spreadsheet (in show formulas mode CTRL + ` <\/strong>):<\/p>\n

\"image\"<\/a><\/p>\n

Cells A1, G3, and G5 contain a fixed value of \u201c1\u201d, and C1 depends on A1, E1 on C1, G1 on E1, and i1 on G1, G3, and G5.<\/p>\n

\"image\"<\/a><\/p>\n

Selecting cell i1 then typing CTRL + [<\/strong> shows me:<\/p>\n

\"image\"<\/a><\/p>\n

To select all <\/em>cells that are precedents of i1 (several levels of formula reference), type CTRL + SHIFT + {<\/strong><\/p>\n

\"image\"<\/a><\/p>\n

The converse works as well, for formula cells that are dependent on the active cell. Use CTRL + ]<\/strong> and CTRL + SHIFT + }<\/strong> for that functionality.<\/p>\n

Shift + F3 \u2013 Insert Function<\/strong><\/p>\n

Use Shift + F3 to insert a function from the dialog box .<\/p>\n

\"image\"<\/a><\/p>\n

<\/strong><\/p>\n

F4 \u2013 Repeat<\/strong><\/p>\n

Use F4 to repeat the last thing you did, a great shortcut especially if the action just took several clicks. For example:<\/p>\n

\u00b7 Inserting cells to the right<\/p>\n

\u00b7 Applying complex formatting to a cell (could also use the format painter)<\/p>\n

\u00b7 Inserting more rows or columns (after the first insert, see Part 1 of this series for an example)<\/p>\n

F12 \u2013 Save As<\/strong><\/p>\n

The Save As dialog will come up when typing CTRL + S as long as the book you\u2019re working on has never been saved<\/em>. But if you\u2019re working on a workbook that\u2019s already been saved, and now want to save it with a new name it\u2019s useful to be able to quickly bring up the Save As dialog. F12 or ALT + F, then A (if you like the file menu) will automatically bring up the Save As dialog.<\/p>\n

\"image\"<\/a><\/p>\n

ALT + F1 \u2013 Insert a Chart<\/strong><\/p>\n

Just highlight some data in the grid and type ALT + F1 to get a chart inserted onto the same sheet you\u2019re working with. If you don\u2019t like the default column chart that gets inserted, notice that on the Insert Chart dialog there is a button named \u201cSet as Default Chart\u201d, so all you need to do is pick the chart type you like and click that button:<\/p>\n

\"image\"<\/a><\/p>\n

If you wanted a chart sheet instead of a chart object on a worksheet, type F11 instead.<\/p>\n

\"image\"<\/a><\/p>\n

CTRL+H \u2013 Find & Replace dialog<\/strong><\/p>\n

\"image\"<\/a><\/p>\n

CTRL + SHIFT + F brings up the same dialog, but with the Font tab active.<\/p>\n

\"image\"<\/a><\/p>\n

CTRL + SHIFT + L for AutoFilter<\/strong><\/p>\n

As long as the active cell is in a range of data, typing this keyboard shortcut will turn on AutoFilter for that range, putting filter dropdown arrows at the first row of data.<\/p>\n

Select a cell in the range you want to filter:\"image\"<\/a>\"image\"<\/a><\/p>\n

CTRL + ALT + F5 for Refresh All External Data<\/strong><\/p>\n

Just open a workbook and type this keyboard shortcut to trigger a \u201cRefresh All\u201d, which refreshes all external data connections.<\/p>\n

CTRL + P\u00a0 for Print<\/strong><\/p>\n

This shortcut is simple enough, it brings up the Print dialog \/ user interface.<\/p>\n

CTRL + F1 for Collapse \/ Expand Ribbon<\/strong><\/p>\n

Use this to get more of a \u201cfull screen\u201d effect in Excel (and when reading Word documents).<\/p>\n

Expanded ribbon:<\/p>\n

\"image\"<\/a><\/p>\n

Before CTRL + F1<\/strong><\/em><\/p>\n

\"image\"<\/a><\/p>\n

After CTRL + F1<\/strong><\/em><\/p>\n

CTRL + Page Up\/Down for Sheet Navigation<\/strong><\/p>\n

Use these shortcuts to navigate through sheet tabs in the current workbook.<\/p>\n

\"image\"<\/a><\/p>\n

Before using Ctrl + Page Down<\/strong><\/em><\/p>\n

\"image\"<\/a> <\/em><\/p>\n

After using Ctrl + Page Down<\/strong><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

Covering a few of Excel keyboard shortcuts and a few other little tricks that helps work faster. F9 \u2013 Partial formula evaluation F9 is really helpful for debugging complex formulas, because it allows you to select smaller pieces of your formula and evaluating just that highlighted part. To try it […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,8],"tags":[92],"class_list":["post-2690","post","type-post","status-publish","format-standard","hentry","category-office-2010","category-tips-n-trix","tag-excel"],"_links":{"self":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/posts\/2690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/comments?post=2690"}],"version-history":[{"count":2,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/posts\/2690\/revisions"}],"predecessor-version":[{"id":2691,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/posts\/2690\/revisions\/2691"}],"wp:attachment":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/media?parent=2690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/categories?post=2690"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/tags?post=2690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}